Solved

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

Posted on 2010-09-21
11
2,909 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
I need help embedding an image as HTML in my vb.net application 3 68
Pagebreak issue while printing the aspx page 3 24
Variable Event ? 3 27
MailAddress in vb 4 15
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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

778 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