0

I have a table named users which has the following columns in it

User_id,user_name,user_pwd,First_Name,Middle_Name,Last_Name and user_type.

I have dataset named dst and created a table called user in the dataset. Now I want to populate listbox with user_Name, First_Name, Last_name of each and every row in the table user.

I am able to add one column value at a time but not getting how to add multiple column values of each row to listbox

Dim dt As DataTable = Dst.Tables("user")

For Each row As DataRow In dt.Rows
    lstUsers.Items.Add(row("User_Name"))
Next

Above code works perfectly but I also want to add First_name as well as last_name to the list box at the same time.

4
  • Are you trying to have 3 columns in the ListBox? Commented Mar 16, 2013 at 7:24
  • Not three columns only single column but that must have all the three column values from the table user Commented Mar 16, 2013 at 7:29
  • you want to add like this.? user_Name1, First_Name1, Last_name1,user_Name2, First_Name2, Last_name2 Commented Mar 16, 2013 at 8:03
  • 1
    Have you tried lstUsers.Items.Add(row("User_Name") + ", " + row("First_Name") + ", " + row("LastName")) or something similar? Note that I would suggest building the string first in case one of the columns in the DataRow is null. Commented Mar 16, 2013 at 8:32

4 Answers 4

1

Use same approach as you have, but put all values you want in one string.

Dim dt As DataTable = Dst.Tables("user")

For Each row As DataRow In dt.Rows
    Dim sItemTemp as String
    sItemTemp = String.Format("{0},{1},{2}", row("User_Name"), row("First_Name"), row("Last_Name"))
    lstUsers.Items.Add(sItemTemp)
Next

String.Format() function will call .ToString() on all parameters.

In this case if row(ColumnName) is NULL value then .ToString() return just empty string

Sign up to request clarification or add additional context in comments.

Comments

0

You have 2 choices:

  1. Using the ListBox:

To use the ListBox, set the font to one that is fixed width like courier new (so that the columns line up), and add the items like this:

For Each row As DataRow In dt.Rows
    lstUsers.Items.Add(RPAD(row("User_Name"),16) & RPAD(row("First_Name"),16) & RPAD(row("Last_Name"),16))
Next

The RPAD function is defined like this:

Function RPAD(a As Object, LENGTH As Object) As String
    Dim X As Object
    X = Len(a)
    If (X >= LENGTH) Then
        RPAD = a : Exit Function
    End If
    RPAD = a & Space(LENGTH - X)
End Function

Adjust the LENGTH argument as desired in your case. Add one more for at least one space. This solution is less than ideal because you have to hard-code the column widths.

  1. Use a DataGridView control instead of a ListBox. This is really the best option, and if you need, you can even have it behave like a ListBox by setting the option to select the full row and setting CellBorderStyle to SingleHorizontal. Define the columns in the designer, but no need to set the widths - the columns can auto-size, and I set that option in the code below. if you still prefer to set the widths, comment out the AutoSizeColumnsMode line.

The code to set up the grid and add the rows goes like this:

    g.Rows.Clear() ' some of the below options are also cleared, so we set them again
    g.AutoSizeColumnsMode = DataGridViewAutoSizeColumnMode.AllCells
    g.CellBorderStyle = DataGridViewCellBorderStyle.SingleHorizontal
    g.SelectionMode = DataGridViewSelectionMode.FullRowSelect
    g.AllowUserToAddRows = False
    g.AllowUserToDeleteRows = False
    g.AllowUserToOrderColumns = True
    For Each row As DataRow In dt.Rows
        g.Rows.Add(row("User_Name"), row("First_Name"), row("Last_Name"))
    Next

Comments

0

You might solved your problem by now but other users like me might have issue with it.
Above answers given worked for me even but I found a same answer in a simple way according to what I want..

cmd = New SqlCommand("select User_Name, First_Name, Last_Name from User")
  Dim dr As SqlDataReader = cmd.ExecuteReader(YourConnectionString)

  If dr.HasRows Then
        Do While dr.Read
                        lst.Items.Add(dr.Item(0).ToString & " " & dr.Item(1).ToString & " " & dr.Item(2).ToString)
        Loop
  End If

This worked for me, maybe wrong way but I found it simple :)

Comments

0

May I suggest you use a ListView control instead of Listbox?

If you make the switch, here's a sample subroutine you could use to fill it up with the data you said you want. Adapt it the way you like; there's much room for improvement but you get the general idea:

Public Sub FillUserListView(lstUsers As ListView, Dst As DataSet)

    Dim columnsWanted As List(Of String) = New List(Of String)({"User_Name", "First_Name", "Last_Name"})
    Dim dt As DataTable = Dst.Tables("user")
    Dim columns As Integer = 0
    Dim totalColumns = 0
    Dim rows As Integer = dt.Rows.Count

    'Set the column titles
    For Each column As DataColumn In dt.Columns
        If columnsWanted.Contains(column.ColumnName) Then
            lstUsers.Columns.Add(column.ColumnName)
            columns = columns + 1
        End If
        totalColumns = totalColumns + 1
    Next

    Dim rowObjects(columns - 1) As ListViewItem
    Dim actualColumn As Integer = 0

    'Load up the rows of actual data into the ListView
    For row = 0 To rows - 1
        For column = 0 To totalColumns - 1
            If columnsWanted.Contains(dt.Columns(column).ColumnName) Then

                If actualColumn = 0 Then
                    rowObjects(row) = New ListViewItem()
                    rowObjects(row).SubItems(actualColumn).Text = dt.Rows(row).Item(actualColumn)

                Else
                    rowObjects(row).SubItems.Add(dt.Rows(row).Item(actualColumn))
                End If

                lstUsers.Columns.Item(actualColumn).Width = -2  'Set auto-width

                actualColumn = actualColumn + 1
            End If
        Next

        lstUsers.Items.Add(rowObjects(row))
    Next

    lstUsers.View = View.Details 'Causes each item to appear on a separate line arranged in columns
End Sub

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.