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?

Improve company productivity with a Business Account.Sign Up

x
 
John (Yiannis) ToutountzoglouConnect With a Mentor Instructor 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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.