How to concantenate rows from ms access using vb.net 2008 express

[Nwanati]

Good day,

Can any one please help me to concantenate rows from my ms access database using vb.net. I have a table in ms access where there are contacts of clients, so now i want to seleect only mobile numbers of all clients and then concantenate them seperated by deliminated commas. I realy new to this and i'm not realy sure of how to acchieve this. Any solution that will give me all the mobile numbers into a single row/line seperated each number with a deliminated comma. I have tried different methode but it gives my endless errors. Amongst other things i tried to bind the mobile numbers in to a listbox so that i can loop through and insert all the number in the textbox seperating them with a deliminated commas....that does not work it says "Operator '+' is not defined for type 'DataRowView' and string ";".

This is the code i have used:
Dim address As String
' Dim n As Integer
For i As Integer = 1 To Mobile_PhoneListBox.Items.Count - 1 Step 1
Mobile_PhoneListBox.SetSelected(i, True)
address = address + Mobile_PhoneListBox.Items(i) + ";"
Next i

AddressTextBox.Text = Mobile_PhoneListBox.Items(0) + ";" + address
All i need is to concatenate the rows in mobile column and seperate them by ";", please help?


Thanks in advance

 

[jebenson]

How are you connecting to the database?


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Nwanati]

Thanks for your response!

This is how i connect to the database. I created a listbox from my form and bind it with the relevant column using the data aource i created through vb.net 2008 data source wizard. Since i have access to the database i dont have any problem changing the way i connect to it, as long i get what i'm looking for and that is to concatenate the rows separating them by delimineted commas.

When i use a listbox with items (unbound from database)...the items that i maunualy enter after creating the listbox my code above can do the work but on data bound listbox it never works...

please help....any possible solution will be hihly valued and appreciated

 

[jebenson]

I would not use the data source wizard; I prefer to do all of my database coding "by hand".

Dim conn As OleDbConnection
Dim da As OleDbDataAdapter
Dim dt As DataTable
Dim SQLStr As String
Dim MobileNumbers As String

conn = New OleDbConnection("<connection string goes here>")

SQLStr = "Select * from <tablename> where <criteria to get what you want>"

da = New OleDbDataAdapter(SQLStr, conn)

dt = New DataTable

da.Fill(dt)

For Each dr As DataRow In dt.Rows
    MobileNumbers &= dr.Item("<FieldNameHere>")
Next

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!