foreach item in ListBox | SqlReader

Question

what I'am trying to do here is to select the connected people, but I don't really know how to do this. I have x names on the Listbox. I want to check for every name the login & logout times and if login is bigger then logout time, it types near the name "Connected", "not connected" on ListBox. Thank you in advance .

foreach (var Item in listBoxControl2.Items) { try { SqlConnection sqlConnection = new SqlConnection(ConnectDatabase); SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Connection = sqlConnection; sqlCommand.CommandText = "Select login_time_value,logout_time_value ConnectionTime.dbo.Avalaible where name = '" + Item.ToString() +"'"; sqlConnection.Open(); SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(); while (true) { bool flag = sqlDataReader.Read(); if (!flag) { break; } DateTime login = sqlDataReader.GetDateTime(0); DateTime logout = sqlDataReader.GetDateTime(1); if (login > logout) { } else { } } sqlDataReader.Close(); sqlConnection.Close(); } catch { } }

Please properly format your source code. This time I've done that for you. — villekulla
– villekulla, Commented Oct 14, 2013 at 10:31
Actually yes , I tried to do something like : string text += Item.ToString() + "\n"; but not really working ... — Stack overflow
– Stack overflow, Commented Oct 14, 2013 at 10:33

Community · Accepted Answer · 2017-05-23 12:10:57Z

There are many things that could be changed in your code, but to answer just to your problem, I would change the loop to use a simple for loop so you could access directly the items in the listBox and change the text for the matched items.

for(x = 0; x < listBoxControl2.Items.Count; x++) { while(sqlDataReader.Read()) { DateTime login = sqlDataReader.GetDateTime(0); DateTime logout = sqlDataReader.GetDateTime(1); if (login > logout) { listBoxControl2.Items[x] = listBoxControl2.Items[x] + " connected"; } else { listBoxControl2.Items[x] = listBoxControl2.Items[x] + " logged off"; } } }

The problem with the foreach is that you get a copy of the string text, you have to replace the original and this is easier with a for loop.

About the other problems.

Where is the FROM clause in the command text?
Move the opening of the connection outside the loop.
Use the using statement to open/use/close/dispose the disposable objects (Why?)
Use parameterized query when building command texts to pass to the database engine (Why?)

So the updated code could be

string cmdText = "Select login_time_value,logout_time_value ConnectionTime.dbo.Avalaible " + "FROM ??????????" + "where name = @name"; using(SqlConnection sqlConnection = new SqlConnection(ConnectDatabase)) using(SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection)) { sqlCommand.Parameters.AddWithValue("@name", "dummy"); sqlConnection.Open(); for(x = 0; x < listBoxControl2.Items.Count; x++) { string name = listBoxControl2.Items[x].ToString(); sqlCommand.Parameters["@name"].Value = name; using(SqlDataReader sqlDataReader = sqlCommand.ExecuteReader()) { while(sqlDataReader.Read()) { DateTime login = sqlDataReader.GetDateTime(0); DateTime logout = sqlDataReader.GetDateTime(1); if (login > logout) { listBoxControl2.Items[x] = name + " connected"; } } } } }

Look at update, now the item is taken directly from the listBox items collection at the current position inside the loop.
I have removed the else part of the IF, if you have to update also in case of logout > login then the else is required
Two datetimes can be compared using the > operator without problems. Have you tried to check the code flow using a debugger?
Sorry . it's working , I just changed something so ... thank you again

Collectives™ on Stack Overflow

foreach item in ListBox | SqlReader

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related