VB List Boxes and Database Connectivity

[simonfoley]

Hi there

I'm studying Visual Basic (using Visual Studio 2008) as part of an all-round IT foundation degree...I must confess that programming is becoming my weak spot! Unlike the C++ I was taught last year, I'm not finding VB too easy...probably becasue I've never programmed this way before and I've missed a couple of lessons.

Anyway...

I'm writing a program that accesses an Access Database. The bit of it I'm having problems with at the moment concerns populating a list box with the contents of a table from the database.

What I want to know is how do I have the data in columns in the list box? The table that the data is coming from is 4 columns wide, and has 14 records in in total. At the moment, it fills the list box in one column....fields one underneath the other...

Here's my code:

Private Sub citydata_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim dataCity As New DataTable()
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=E:\globexdata\globex.mdb"
        Dim sqlString As String = "SELECT * FROM cities"
        Dim dataAdapt As New OleDb.OleDbDataAdapter(sqlString, connectionString)

        dataAdapt.Fill(dataCity)
        dataAdapt.Dispose()

        For r = 1 To dataCity.Rows.Count
            For c = 1 To dataCity.Columns.Count
                lstPopulation.Items.Add(dataCity(r - 1)(c - 1))
            Next c
        Next r

    End Sub

I hope someone can help! I may have a lot more questions over the next week...beyond the basics I'm finding VB hell!!

[simonfoley]

Here's an afterthought - should I populate an array with what I'm pulling in from the table first...then I can manipulate the array if need be?

[kvprajapati]

I think you want to show all records in listbox but the listbox support single column. You can switch to "ListView" if you want multiple columns.

ListBox1.MultiColumn = True
        ListBox1.HorizontalScrollbar = True
        ListBox1.ColumnWidth = 100
        ListBox1.FormattingEnabled = True
        
        For Each row As DataRow In dataCity.Rows
            ListBox1.Items.Add(String.Join(",", row.ItemArray.OfType(Of String).ToArray()))
        Next

[simonfoley]

Hi there

Thanks adatapost - that's NEARLY done the trick. However, the table I'm importing data from has 4 columns. Columns 1 & 2 are text, columns 3 & 4 are numeric. Your code only adds columns 1 & 2...and not 3 & 4.

So - what do I need to add to it to show all 4 columns?

[ShahanDev]

Hi!

Change the For Loop with this:

For Each row As DataRow In dataCity.Rows
            ListBox1.Items.Add(String.Concat(row.ItemArray))
        Next

Actually the for loop "adatapost" suggested will just take "string", so if you have integers in row it won't include it in array. So, you can use "String.Concat()" method for this purpose.

[ShahanDev]

Also, if you need "," delimited value(s) then replace For Loop like this:

For Each row As DataRow In dataCity.Rows
            ListBox1.Items.Add(Join(row.ItemArray, ","))
        Next

[simonfoley]

ShahanDev - thanks for that, but the items in the list box populate with no gap or comma between them. The previous loop, whist it did not include integers, did have a comma separator!

**EDIT** - just noticed your edit - thanks again!(it now works). I'm sure I'll have more questions in the future about the application I'm working one!