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


  • 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

    • Upravený Nwanati 6. března 2012 6:35
    6. března 2012 6:30


  • If I got you right, using a Listbox (or whatever you have now) is only an attempt to concatenate the numbers; it's not that you need the Listbox anyway, right?

    As you were already able to fill a control with the items, you must already have a datatabase connection. From this point, the following code should work:

       Private Shared Function GetAllMobileNumbers(ByVal con As OleDbConnection) As List(Of String)
          Dim cmd As New OleDbCommand("select mobile_number from contacts where mobile_number is not null", con)
          Dim dr As OleDbDataReader
          Dim sb As New System.Text.StringBuilder
          Dim result As New List(Of String)
          dr = cmd.ExecuteReader
          Do While dr.Read
    dr.Close() con.Close() Return result End Function


          Dim Numbers = GetAllMobileNumbers(con).ToArray
          Dim total = String.Join(";", Numbers)

    ..where con is the OleDbConnecton.

    Of course you must modify the table and column name.

    Variable 'total' now holds all mobile numbers in one String.


    6. března 2012 12:42