Question Display data from mysql database on ListView

[ronzarevele]

Good day, i just want to ask a help from you with my problem in vb.net.. I have 1 listview in a form, in that form, it has also 1 textbox as txtsearch then a button to query. Whenever i type a string in a texbox then click the button, the data from the database will be displayed on the listview. My code is working. It can search from the database and display on the listview. Now my problem is, if i type the same string at a 3rd time, the data wont display on the listview. I dont know what's the problem on this. I search it on google but no luck. Here is my code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1

Dim strSQL As String = "SELECT * from tbl_all where itemCode= '" + txtsearch.Text + "' and DateBuy = '" + ComboBox1.SelectedItem.ToString + "' "
Dim con As MySqlConnection = New MySqlConnection("Data Source=localhost; Database=samal_db;" & _
"User ID=root; Password=;")

con.Open()
Dim cmd As MySqlCommand = New MySqlCommand(strSQL, con)
dr = cmd.ExecuteReader
ListView1.Items.Clear()
'If dr.HasRows Then
While dr.Read()

Dim LV2 As New ListViewItem
LV2.Text = dr("itemCode").ToString() 'Fills the first column of the row
LV2.SubItems.Add(dr("Description").ToString()) 'Fills the 1st subitem (2nd column)
LV2.SubItems.Add(dr("Qty").ToString()) 'Fills the 2nd subitem (3rd column)
LV2.SubItems.Add(FormatNumber(dr("Price").ToString(), 2, TriState.False, TriState.True, TriState.True)) 'Fills the 2nd subitem (4th column)
ListView1.Items.Add(LV2)

End While
dr.Close()
cmd.Dispose()
con.Close() 
End Sub

 

[jmcilhinney]

Have you debugged the code? Presumably not. You don't just read code to see why it doesn't work because the issue is often not obvious. You need to watch the code in action. That's why you run it in Debug mode to begin with. Place a breakpoint (F9) on the first line of the code and then, when execution breaks, you can step through the code (F8 or F10 depending on settings) line by line to view the application state at every step. You can use the Autos, Locals and Watch windows and more besides to evaluate variables and other expressions to see whether the state of the app is as you expect. As soon as reality differs from expectation, you've found and issue.

Apart from that, do you really need to use a ListView? If you want to display tabular data then a DataGridView should be your first choice. That would simplify your code significantly. Also, you should be using parameters to insert values into SQL code. As it stands, you're wide open to SQL Injection attacks. To learn why and how to use parameters, follow the Blog link in my signature below and check out my post on Parameters In ADO.NET.