Only add String as new Item to Listview if New
Using the following code to add results from sql to a listview. Is there anyway that I can make a comparison from the reader against all currently existing item strings? Thanks in advance. :duck:
vb.net Code:
LstResults.Items.Clear()
If con.State <> ConnectionState.Open Then
Try
con.Close()
Catch ex As Exception
End Try
con.Open()
End If
Dim comm As New SqlCommand("select * from maxcom WHERE LastName like '%" & TxtLastName.Text & "%' OR FirstName like '%" & TxtLastName.Text & "%'", con)
' OR Position like '%" & TxtLastName.Text & "%'"
' OR FirstName like '%" & TxtLastName.Text & "%' OR Position like '%" & TxtLastName.Text & "%' OR "
Try
Dim reader As SqlDataReader = comm.ExecuteReader()
While reader.Read()
LstResults.Items.Add(reader("FirstName").ToString() & " " & reader("LastName").ToString() & " " & reader("Extension").ToString() & " " & reader("DIDNumber").ToString())
LstResults.Items.Add("-----------------------------------------------------------------------------------------------------------------------------------------------------------------")
End While
reader.Close()
Catch ex As Exception
End Try
If TxtLastName.Text.ToString.Contains(Chr(32)) Then
Dim line As String = TxtLastName.Text.ToString
Dim i As Integer
Dim aryTextFile() As String
aryTextFile = line.Split(Chr(32))
For i = 0 To UBound(aryTextFile)
Dim comm2 As New SqlCommand("select * from maxcom WHERE LastName like '%" & aryTextFile(i).ToString & "%' OR FirstName like '%" & aryTextFile(i).ToString & "%'", con)
Dim reader As SqlDataReader = comm2.ExecuteReader()
While reader.Read()
LstResults.Items.Add(reader("FirstName").ToString() & " " & reader("LastName").ToString() & " " & reader("Extension").ToString() & " " & reader("DIDNumber").ToString())
LstResults.Items.Add("-----------------------------------------------------------------------------------------------------------------------------------------------------------------")
End While
reader.Close()
Next i
End If
con.Close()
TxtLastName.Text = ""
TxtLastName.Focus()
Re: Only add String as new Item to Listview if New
First up, you should generally avoid adding items to a control, particularly a ListView, in a loop. The preferred method of adding multiple items is to create all the items first and then add them in a batch by calling AddRange. If you really must add the items one by one then make sure that you call BeginUpdate on the ListView first and then EndUpdate at the end.
As for the question, the simplest way to determine whether a ListView already contains an item displaying particular text is using LINQ, e.g.
vb.net Code:
If myListView.Items.Cast(Of ListViewItem)().Any(Function(lvi) lvi.Text = myString) Then
'myString is already contained in an item in the list.
End If
Re: Only add String as new Item to Listview if New
Getting an error:
Unable to cast object of type 'System.String' to type 'System.Windows.Forms.ListViewItem'.
vb Code:
For i = 0 To UBound(aryTextFile)
Dim comm2 As New SqlCommand("select * from maxcom WHERE LastName like '%" & aryTextFile(i).ToString & "%' OR FirstName like '%" & aryTextFile(i).ToString & "%'", con)
Dim reader As SqlDataReader = comm2.ExecuteReader()
While reader.Read()
Dim text As String = reader("FirstName").ToString() & " " & reader("LastName").ToString() & " " & reader("Extension").ToString() & " " & reader("DIDNumber").ToString()
If LstResults.Items.Cast(Of ListViewItem)().Any(Function(lvi) lvi.Text = (Text)) Then
'myString is already contained in an item in the list.End If
Else
LstResults.Items.Add(text)
LstResults.Items.Add("-----------------------------------------------------------------------------------------------------------------------------------------------------------------")
End If
End While
reader.Close()
Re: Only add String as new Item to Listview if New
I personally can't yet fully understand the syntax of the command:
If LstResults.Items.Cast(Of ListViewItem)().Any(Function(lvi) lvi.Text = (Text))
But rest assured I cannot execute that code. Text cannot be a member of the cast because text is a string and not a ListViewItem... or did i miss something?
Re: Only add String as new Item to Listview if New
Works fine for me. Just tested a ListView contain items with Text of "A", "B" and "C" with this code:
vb.net Code:
Public Class Form1
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim values = New String() {"X", "B", "Z"}
For Each value In values
If Not Me.ListView1.Items.Cast(Of ListViewItem)().Any(Function(lvi) lvi.Text = value) Then
Me.ListView1.Items.Add(value)
End If
Next
End Sub
End Class
and two new items with Text of "X" and "Z" was added.
The LINQ code gets the Items collection of ListView, casts each one as type ListViewItem and returns then as a list, then checks whether any of them return True when passed to a function that returns whether its Text is equal to the specified value. The example above uses Any and Not, but you could get the same effect by removing the Not, changing Any to All and inverting the equality comparison:
vb.net Code:
Public Class Form1
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim values = New String() {"X", "B", "Z"}
For Each value In values
If Me.ListView1.Items.Cast(Of ListViewItem)().All(Function(lvi) lvi.Text <> value) Then
Me.ListView1.Items.Add(value)
End If
Next
End Sub
End Class
Re: Only add String as new Item to Listview if New
Rewrote the process could anyone please show me why it is not functioning?
vb Code:
Dim i As Integer = 0
Dim answer(0 To i) As String
LstResults.Items.Clear()
If con.State <> ConnectionState.Open Then
Try
con.Close()
Catch ex As Exception
End Try
con.Open()
End If
Dim comm As New SqlCommand("select * from maxcom WHERE LastName like '%" & TxtLastName.Text & "%' OR FirstName like '%" & TxtLastName.Text & "%'", con)
' OR Position like '%" & TxtLastName.Text & "%'"
' OR FirstName like '%" & TxtLastName.Text & "%' OR Position like '%" & TxtLastName.Text & "%' OR "
Try
Dim reader As SqlDataReader = comm.ExecuteReader()
Dim text As String = reader("FirstName").ToString() & " " & reader("LastName").ToString() & " " & reader("Extension").ToString() & " " & reader("DIDNumber").ToString()
While reader.Read()
answer(i) = (reader("FirstName").ToString() & " " & reader("LastName").ToString() & " " & reader("Extension").ToString() & " " & reader("DIDNumber").ToString())
i = i + 1
answer(i) = "-----------------------------------------------------------------------------------------------------------------------------------------------------------------"
i = i + 1
End While
reader.Close()
con.Close()
Catch ex As Exception
End Try
If con.State <> ConnectionState.Open Then
Try
con.Close()
Catch ex As Exception
End Try
con.Open()
End If
If TxtLastName.Text.ToString.Contains(Chr(32)) Then
Dim line As String = TxtLastName.Text.ToString
Dim aryTextFile() As String
aryTextFile = line.Split(Chr(32))
Dim t As Integer
For t = 0 To UBound(aryTextFile)
Dim comm2 As New SqlCommand("select * from maxcom WHERE LastName like '%" & aryTextFile(i).ToString & "%' OR FirstName like '%" & aryTextFile(i).ToString & "%'", con)
Dim reader2 As SqlDataReader = comm2.ExecuteReader()
While reader2.Read()
If answer.Any(Function(lvi) lvi <> (Text)) Then
answer(i) = (reader2("FirstName").ToString() & " " & reader2("LastName").ToString() & " " & reader2("Extension").ToString() & " " & reader2("DIDNumber").ToString())
i = i + 1
answer(i) = "-----------------------------------------------------------------------------------------------------------------------------------------------------------------"
i = i + 1
Else
End If
End While
reader2.Close()
Next t
End If
Do Until i < 0
LstResults.Items.Add(answer(i).ToString)
i = i - 1
Loop
con.Close()
TxtLastName.Text = ""
TxtLastName.Focus()
Getting issue that:
Dim reader2 As SqlDataReader = comm2.ExecuteReader() results in VVV
There is already an open DataReader associated with this Command which must be closed first.
Re: Only add String as new Item to Listview if New
I think you'll find that the error message mentions the connection, not the command.
The error message is quite clear on what the issue is: you can't open a data reader on a connection that already has an open data reader. I would say the cause is the fact that you are just ignoring exceptions rather than actually handling them. If exceptions are being thrown, you can't just add a Catch block and ignore it. Turning off the warning lights in your car won't stop the engine breaking down.
At the moment, you open a data reader and then, if an exception is thrown, you don't close it. If you actually do catch an exception then you need to clean up in a Finally block, to ensure that it gets done whether an exception is thrown or not.
If an exception is thrown, work out what it is and why it's being thrown and, if possible, remove the cause.
