Solved

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

Posted on 2010-09-21
11
2,722 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
Comment Utility
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
Comment Utility
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
Comment Utility
You need to add your connection on your sqlCommand
SQLCMD = New SqlClient.SqlCommand("SELECT Full_name, Component from VW_CONTACTS", Miconnection )
0
 

Author Comment

by:angel7170
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:angel7170
Comment Utility
Nothing happens. I have placed this code in

Tabpage2_Click property...
0
 
LVL 18

Accepted Solution

by:
John (Yiannis) Toutountzoglou earned 500 total points
Comment Utility
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
Comment Utility
note...

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

Author Closing Comment

by:angel7170
Comment Utility
Works awesome. Thank you very much jtoutou

0
 

Author Comment

by:angel7170
Comment Utility
Thank you Dhaest for your assistance too..
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
Glad i helped you
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now