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, Oct 14 '13 at 10:31
Actually yes , I tried to do something like : string text += Item.ToString() + "\n"; but not really working ... — Stack overflow, Oct 14 '13 at 10:33

score 1 · Accepted Answer · edited May 23 '17 at 12:10

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. — Steve, Oct 14 '13 at 10:50
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 — Steve, Oct 14 '13 at 11:04
Two datetimes can be compared using the > operator without problems. Have you tried to check the code flow using a debugger? — Steve, Oct 14 '13 at 11:18
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/39177/discussion-between-stack-overflow-and-steve) — Stack overflow, Oct 14 '13 at 11:28
Sorry . it's working , I just changed something so ... thank you again — Stack overflow, Oct 14 '13 at 11:43

foreach item in ListBox | SqlReader

1 Answers1