List Box Option Selection

Katie B

Sorry I know the subject isnt great, but it hard to get this question basis in a subject box. :D

I have a list box called degree programme, once a selection is made in this listbox, I want the modules associated with selection to appear in my modules listbox. I'm doing this through an access database. I'll post the code below which i've pieced together from things ive read, but I dont know how right/wrong it is.

private void lstboxDegreeProgramme_SelectedIndexChanged(object sender, EventArgs e)

{

String strItem;

foreach (Object selecteditem in lstboxDegreeProgramme.SelectedItems)

{

strItem = Convert.ToString(lstboxDegreeProgramme.SelectedItem);

string connectionString =

"provider=Microsoft.ACE.Oledb.12.0;" +

"data source=C:\\Users\Documents\\StudentRegistrationDatabase.accdb";

OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);

OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();

myOleDbConnection.Open();

myOleDbCommand.CommandText = "SELECT * FROM Modules WHERE CourseID = '" + strItem + "''";

OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();

while (myOleDbDataReader.Read())

{

lstboxModules.Items.Add(myOleDbDataReader["ModuleID"].ToString() + myOleDbDataReader["ModuleName"].ToString());

myOleDbDataReader.Read();

}
I keep getting an error on the highlighted line saying Syntax error in string in query expression 'CourseID. But when i've put breaks in it runs up until that and i've been re-writing the code for the best part of today. Any help (as normal) is appreciated :D

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Javeed M Shaikh13y ago
Accepted answer
so i am assuming that the value in strItem is CourseID + " " + CourseName, try this, I am just replacing part of your code ( add eror handling and optimize the code as required) :

String strItem;
string[] marray =null;
foreach (Object selecteditem in lstboxDegreeProgramme.SelectedItems)
{
marray = selecteditem.ToString().Split(' ');
strItem = marray[0]
....
....
.... your remaining code
Katie B13y ago
Thank you (again) javeed. That's makes more sense now, I'd never seen the split command used. But I am still new to it, so I'm still learning. :)
Javeed M Shaikh13y ago
split will return a string array of the string which are delimited by unicode character.

for example:

string mvar = "I,am,Katie"; // store some text with comma seperate
string[] marray = mvar.split(','); // now we create a string array and specify the delimeter in this case it is a comma
// below is how the array is formed after the split command
marray[0] will contain "I"
marray[1] will contain "am"
marray[2] will contain "Katie"
Katie B13y ago
Thank you! This did work last night, but can I ask how the .split('|') works? From looking into it myself, I think when two items share a cell, it splits these into two separates so when the sql looks for them it finds a match. But I haven't a clue how accurate or correct that is! :D

Thanks Kate
Katie B13y ago
lstboxDegreeProgramme.Items.Add(myOleDbDataReader["CourseID"].ToString() + " " + myOleDbDataReader["CourseName"].ToString());

The strItem is suppose to get the value "CourseID" which is displayed in the listbox above.

Once the strItem has the "CourseID", I would like it to then compare it with a value in another table in the database, and if the "CourseID" is matched with a value in the other table.

I'm Not sure how I get just the CourseID from the bit of code above.

Any help would be nice.
k
Javeed M Shaikh13y ago
check the following :

what is the value you are getting in variable strItem ?
check is myOleDbDataReader.HasRows is return true or false ?
remove the " myOleDbDataReader.Read();" from inside the while loop
Katie B13y ago
Thank you, that got rid of that error!

It runs now, but when I double click on an item in the listbox it doesn't display anything in the next listbox.

updated code:

private void lstboxDegreeProgramme_DoubleClick(object sender, EventArgs e)
{
String strItem;
foreach (Object selecteditem in lstboxDegreeProgramme.SelectedItems)
{
strItem = Convert.ToString(lstboxDegreeProgramme.SelectedItems);

string connectionString =
"provider=Microsoft.ACE.Oledb.12.0;" +
"data source=C:\\Users\\Botterill\\Documents\\StudentRegistrationDatabase.accdb";

OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);
OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();
myOleDbConnection.Open();
myOleDbCommand.CommandText = "SELECT * FROM Modules WHERE CourseID = '" + strItem + "'";
OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();

while (myOleDbDataReader.Read())
{
lstboxModules.Items.Add(myOleDbDataReader["ModuleID"].ToString() + myOleDbDataReader["ModuleName"].ToString());

myOleDbDataReader.Read();

}

}

}
Javeed M Shaikh13y ago
Hi Katie,

replace this:

myOleDbCommand.CommandText = "SELECT * FROM Modules WHERE CourseID = '" + strItem + "''";

with

myOleDbCommand.CommandText = "SELECT * FROM Modules WHERE CourseID = '" + strItem + "'";