CODEWITHSUNDEEP
vb.netwinformslistbox
Matt
Matt

Reputation: 31

Display SQL Tabe in listbox

at the moment the results from the code below are shown as one list in a single column. How do I amend the code to show each field in a separate column? Many thanks.

Private Sub loadSelection()

'Add Params and run query
sql.addParam("@productName", "%" & productSearch.Text & "%")

sql.ExecQuery("SELECT itemName, category, itemID " &
          "FROM Inventory " &
          "WHERE product LIKE @productName;")

    'Report & abort on errors
    If sql.HasException(True) Then Exit Sub

    'loop rows and return users to the list
    For Each r As DataRow In sql.DBDataTable.Rows
         listBox1.Items.Add(r("itemName"))
         listBox1.Items.Add(r("category"))
         listBox1.Items.Add(r("itemID"))
    Next

End Sub

Upvotes: 0

Views: 67

Answers (2)

jmcilhinney
jmcilhinney

Reputation: 54477

You should not be using a ListBox or a ListView. You should be using a DataGridView control and binding via a BindingSource. Add both to your form in the designer and then do this:

myBindingSource.DataSource = sql.DBDataTable
myDataGridView.DataSource = myBindingSource

That will, by default, create the columns in the grid automatically at run time. If you need something other than the default behaviour, you can create the columns yourself at run time and set their DataPropertyName to make them bind to a specific data source column.

Also note that this creates a live relationship between the DataTable and the grid. That means that you must not repurpose that same DataTable for some other data while it's bound to that grid. I've seen others do just that when using a DAL like you appear to have created. I've many such DAL implemented with the best of intentions but the result is poor.

Upvotes: 2

Pete -S-
Pete -S-

Reputation: 562

Using List View

Here is one option with the list view control:

 Dim listItem As ListViewItem = Form.lvwListCtl.Items.Add(r("itemID"))

 listItem.SubItems.Add(r("itemName"))
 listItem.SubItems.Add(r("category"))

Set the View property to lvwListCtl.View = System.Windows.Forms.View.Details

Using the List Box

This is a little "hacky"; but, you could do something like this. Add one line, and format each field as fixed length, something like this:

listBox1.Items.Add(r("itemName").ToString.PadRight(100, " ") & r("category").ToString.PadRight(50, " ") & r("itemID").ToString.PadRight(10, " ")

Note: Either option you need to handle NULL values returned.

Upvotes: 1

Related Questions