Populate a Listview with data from SQL database in VB.Net 2008

Hello,
I  have a form with a listview of two columns "Employee Name" and "Component"

Now, I need to populate data for these two columns from a SQL database dataset.

How to achieve that?
Please assist.
Thank you
angel7170Asked:
Who is Participating?
 
John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
Try this ...(Another Version)
lvlist is your ListView Name
'In a module cory and paste this...
Module modProcedure

    Public Sub FillListView(ByRef lvList As ListView, ByRef myData As SqlDataReader)
        Dim itmListItem As ListViewItem

        Dim strValue As String

        Do While myData.Read
            itmListItem = New ListViewItem()
            strValue = IIf(myData.IsDBNull(0), "", myData.GetValue(0))
            itmListItem.Text = strValue

            For shtCntr = 1 To myData.FieldCount() - 1
                If myData.IsDBNull(shtCntr) Then
                    itmListItem.SubItems.Add("")
                Else
                    itmListItem.SubItems.Add(myData.GetValue(shtCntr))
                End If
            Next shtCntr

            lvList.Items.Add(itmListItem)
        Loop
    End Sub
    
    Public Function GetData(ByVal sSQL As String)
        Dim cn As SqlConnection
        Dim sqlCmd As SqlCommand = New SqlCommand(sSQL)
        Dim myData As SqlDataReader

        cnPersonnel = New SqlConnection(My.Settings.MyConnectionString1)

        Try
            cn.Open()

            sqlCmd.Connection = cn
            
            myData = sqlCmd.ExecuteReader

            Return myData
        Catch ex As Exception
            Return ex
        End Try
    End Function
   
End Module

Public Sub FillList()
With lvList
            .Clear()

            .View = View.Details
            .FullRowSelect = True
            .GridLines = True

            .Columns.Add(" "Employee Name", 200)
            .Columns.Add("Component", 200).TextAlign = HorizontalAlignment.Left
           
           

            Dim conn As New SqlConnection(My.Settings.MyConnectionString)

            If conn.State = ConnectionState.Open Then conn.Close()
            conn.Open()


            Dim cmd As New SqlCommand(query, conn)

            Dim PassParam As New SqlParameter(".......", .......) 'if there are parametrs

            cmd.Parameters.Add(PassParam)
           


            FillListView(lvList, cmd.ExecuteReader(CommandBehavior.CloseConnection))



        End With
 End Sub

  DDim query As String = "SELECT .............. FROM ........... WHERE ...... = @....... ORDER BY ......"


'then in your form Load or button click call the sub

Call FillList

Open in new window

0
 
DhaestCommented:
Code to fill the listview control in vb.net dynamically
http://www.daniweb.com/forums/thread49338.html
Dim rdGetData As SqlClient.SqlDataReader
Try
If ListView1.Items.Count > 0 Then
ListView1.Items.Clear()
End If
SQLCmd.CommandType = CommandType.Text
SQLCmd.CommandText = "SELECT * from table"
rdGetData = SQLCmd.ExecuteReader

Dim intCount As Decimal = 0
While rdGetData.Read
ListView1.Items.Add(Trim("FieldName")) 'col no. 1
ListView1.Items(CInt(intCount)).SubItems.Add(Trim(rdGetContactsInfo("FieldName"))) 'col no. 2
intCount = intCount + 1
End While
rdGetData.Close()
rdGetData = Nothing

Catch Exp As Exception
intNumError = Err.Number()
MsgBox("[ " & CStr(intNumError) + " ] " + Err.Description, MsgBoxStyle.Critical, " (Program Error)")
End Try

Open in new window

0
 
angel7170Author Commented:
Hi, I tried using like this, but it's giving an error message "Execute Reader
: Connection Property has not been initilalized.

Please help. Thank you


Dim connectionstring As String = "Data Source=SAEOP201\OVOPS; Password=P44df?x&PQ9Ofyzxh19u; User Id=ReportTableViewer"
        Dim intNumError As Integer
        Dim SQLCMD As SqlClient.SqlCommand
        Dim rdGetData As SqlClient.SqlDataReader
        Using Miconnection As New SqlClient.SqlCommand(connectionstring)
            Try
                If ListView1.Items.Count > 0 Then
                    ListView1.Items.Clear()
                End If
                SQLCMD = New SqlClient.SqlCommand("SELECT Full_name, Component from VW_CONTACTS")
                rdGetData = SQLCMD.ExecuteReader

                Dim intCount As Decimal = 0
                While rdGetData.Read
                    ListView1.Items.Add("Full_name") 'col no. 1
                    ListView1.Items.Add("Component") 'col no. 2
                    intCount = intCount + 1
                End While
                rdGetData.Close()
                rdGetData = Nothing

            Catch Exp As Exception
                intNumError = Err.Number()
                MsgBox("[ " & CStr(intNumError) + " ] " + Err.Description, MsgBoxStyle.Critical, " (Program Error)")
            End Try
        End Using
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
DhaestCommented:
You need to add your connection on your sqlCommand
SQLCMD = New SqlClient.SqlCommand("SELECT Full_name, Component from VW_CONTACTS", Miconnection )
0
 
angel7170Author Commented:
It's giving an error when I added the connection

Value of type 'System.Data.SQLClient.SQLCommand cannot be converted to 'System.Data.SQLClient.SQLConnection

Please help.
0
 
DhaestCommented:
Dim connectionstring As String = "Data Source=SAEOP201\OVOPS; Password=P44df?x&PQ9Ofyzxh19u; User Id=ReportTableViewer"

Dim conn As New SqlConnection(connString)
Dim cmdString As String = "SELECT Full_name, Component from VW_CONTACTS"
Dim cmd As New SqlCommand(cmdString, conn)

conn.Open()

Dim reader As SqlDataReader = cmd.ExecuteReader()

If ListView1.Items.Count > 0 Then
     ListView1.Items.Clear()
End If

While reader.Read()
    Dim str(2) As String
    Dim itm As ListViewItem
    str(0) = reader("Full_name").ToString()
    str(1) = reader("Component").ToString()
    itm = New ListViewItem(str)
    ListView1.Items.Add(itm)
End While

conn.Close()


0
 
angel7170Author Commented:
Nothing happens. I have placed this code in

Tabpage2_Click property...
0
 
John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
note...

The Public Sub FillList you must set it in your Form's Class (Form Code)
0
 
angel7170Author Commented:
Works awesome. Thank you very much jtoutou

0
 
angel7170Author Commented:
Thank you Dhaest for your assistance too..
0
 
John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
Glad i helped you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.