Compare array with SQl table
For each connection in an array called ALLconn, I would like to compare it to my sql table. If exist, then add to my listview. Here is my code below, but it does not seem to work:
Dim LoginFilter As Object Dim SelCurrAllSessions As SqlClient.SqlCommand = New SqlClient.SqlCommand("Select * from CurrAllSessions", LFcnn) SelCurrAllSessions.CommandType = CommandType.Text LoginFilter = SelCurrAllSessions.ExecuteReader For Each conn In AllConn While LoginFilter.Read() If conn.UserName.ToString() = LoginFilter.Item(0) Then ListBox1.Items.Add(LoginFilter.Item(0)) End If End While Next
Well you need to change the order of the loops
While LoginFilter.Read() For Each conn In AllConn If conn.UserName.ToString() = LoginFilter.Item(0).ToString Then ListBox1.Items.Add(LoginFilter.Item(0).ToString) Exit For End If Next End While
This is necessary because in your original code, the internal while run till the end of the data loaded from the database then, when you try to check the next conn, you cannot reposition the reader at the start of the data loaded by the database.
It's the other way around, use Contains to check if the string is contained in the collection, then you can add it to the ListBox:
Using LoginFilter = SelCurrAllSessions.ExecuteReader() While LoginFilter.Read() Dim connection = LoginFilter.GetString(0) If AllConn.Contains(connection) Then ListBox1.Items.Add(connection) End If End While End Using
Actually, your Question is uncompleted but still as per my understanding your trying to read result of SqlCommand.ExecuteReader(). When you read that Result it will reading from first to last and that is only one time you can read. if you try to read again it will show up an error because no more content to read from an object Loginfilter.
So, Either you can store that reading into an array and continue with your Foreach and while logic with newly created array or you can do reading from LoginFilter and compare into forech connections in AllConn array or List.
Please feel free to response me if you need more explaination, I will send you in C# version.