Add Items to a ListView from a database

Question

I am a Vb nooby and I have trouble to add specific Items to my Listview from a database.

I would like to compare the value of a combobox with a column value of a table. To proof if they are equal like apple = apple When they are equal the whole data set should be added to my ListView. (Only data sets which have the equal value like the selected item of the combobox)

Please Help !!

Thanks a lot and best regards

Answer 1

You can try below code..

Imports System.Data.SqlClient
Public Class Form1
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim da As SqlDataAdapter
Dim ds As DataSet
Dim itemcoll(100) As String
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.ListView1.View = View.Details
Me.ListView1.GridLines = True
conn = New SqlConnection("Data Source=SQLEXPRESS;Initial Catalog=Northwind;Persist Security Info=True;User ID=id;Password=pass")
Dim strQ As String = String.Empty
strQ = "SELECT * FROM Northwind.dbo.Products"
cmd = New SqlCommand(strQ, conn)
da = New SqlDataAdapter(cmd)
ds = New DataSet
da.Fill(ds, "Table")
Dim i As Integer = 0
Dim j As Integer = 0
' adding the columns in ListView
For i = 0 To ds.Tables(0).Columns.Count - 1
Me.ListView1.Columns.Add(ds.Tables(0).Columns(i).ColumnName.ToString())
Next
'Now adding the Items in Listview
For i = 0 To ds.Tables(0).Rows.Count - 1
For j = 0 To ds.Tables(0).Columns.Count - 1
itemcoll(j) = ds.Tables(0).Rows(i)(j).ToString()
Next
Dim lvi As New ListViewItem(itemcoll)
Me.ListView1.Items.Add(lvi)
Next
End Sub
End Class

You can try this link.

Answer 2

Thanks for your help. In my solution, I just set a parameter into the sql statement.

Public Function getRahmenvertrag**(ByVal costumerID As Integer)** As List(Of Rahmenvertrag)


    Dim sqlCom As New SqlServerCe.SqlCeCommand
    sqlCom.CommandText = **"SELECT * FROM Rahmenvertrag LEFT OUTER JOIN Kunde ON Kunden_FID = Kunden_ID WHERE Kunden_ID = @Kunde "**
    **sqlCom.Parameters.AddWithValue("Kunde", costumerID)**

Private Sub ComboBox1_Click(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged

    ListView4.DataBindings.Clear()
    ListView4.Items.Clear()

    If IsNothing(ComboBox1.SelectedItem) = False Then


        For Each Rahmenvertrag As Rahmenvertrag In controller.getRahmenvertrag(ComboBox1.SelectedItem.kunde_ID)

            With ListView4.Items.Add(Rahmenvertrag.bezeichnung)
                .SubItems.Add(Rahmenvertrag.inhalt)
            End With
        Next
    End If
End Sub