Solved

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

Posted on 2010-09-21
11
3,053 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:angel7170
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 33724978
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
 

Author Comment

by:angel7170
ID: 33725292
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 33725318
You need to add your connection on your sqlCommand
SQLCMD = New SqlClient.SqlCommand("SELECT Full_name, Component from VW_CONTACTS", Miconnection )
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:angel7170
ID: 33725362
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 33725433
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
 

Author Comment

by:angel7170
ID: 33725595
Nothing happens. I have placed this code in

Tabpage2_Click property...
0
 
LVL 18

Accepted Solution

by:
John (Yiannis) Toutountzoglou earned 500 total points
ID: 33725606
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
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33725728
note...

The Public Sub FillList you must set it in your Form's Class (Form Code)
0
 

Author Closing Comment

by:angel7170
ID: 33725947
Works awesome. Thank you very much jtoutou

0
 

Author Comment

by:angel7170
ID: 33725955
Thank you Dhaest for your assistance too..
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33725961
Glad i helped you
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB.NET 2008 - SQL Timeout 9 50
"Emulate" TAB key when press Enter Key 3 67
get combo value in class 5 23
DataGridView / get bound table name? 8 36
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question