Solved

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

Posted on 2010-09-21
11
3,276 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

622 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