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

1

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";
                }
            }
         }
    }
}

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Oct 14, 2013 at 10:41

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Steve Over a year ago

Look at update, now the item is taken directly from the listBox items collection at the current position inside the loop.

Steve Over a year ago

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 Over a year ago

Two datetimes can be compared using the > operator without problems. Have you tried to check the code flow using a debugger?

Stack overflow Over a year ago

let us continue this discussion in chat

Stack overflow Over a year ago

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

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related