Inserting multiple records from one table to a single record in a second table

[victor64]

Hello,

I have two tables with matching fields LAST:

TABLE 1 (Records)

LAST HOBBY
charles Soccer


TABLE 2 (Records)

LAST INTEREST
charles swimming
charles basketball
charles football

I would like to have the following in TABLE 1 (Records)

LAST HOBBY INTEREST
charles Soccer basketball, football, swimming


Below is my code that inserts the records for matching records, how do I modify it to achieve what I am trying to do?

Thanks.

Code:

Dim mySQL_Statement As String = "UPDATE table1 " & vbNewLine & _
" INNER JOIN " & vbNewLine & _
" table2 " & vbNewLine & _
" ON table1.last " & _
" = table2.last " & vbNewLine & _
" SET table1 interest = " & _
" table2.interest "


Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aopt2002orgorg.mdb;Persist Security Info=True;Jet OLEDBatabase Password=testaopupdate"
Dim objConnection As New OleDb.OleDbConnection(ConnectionString)

'data adapter
Dim objDataAdapter As New OleDb.OleDbDataAdapter(mySQL_Statement, objConnection)

'dataset object
Dim objDataSet As New DataSet
'fill dataset
objConnection.Open()
objDataAdapter.Fill(objDataSet, "SN")
objConnection.Close()

 

[jmcilhinney]

Please post in the most appropriate forum for the topic of your thread, not just the first one you come to. The VS forums are for IDE questions, not code questions. The General forums are for questions that don't fit into one of the specific categories. Moved.

 

[cjard]

Now that youre here, would you mind removing (ask a moderator) your cross posted thread? Also read this Visual Basic .NET Forums - BB Code List about code tags, and finally, take a look at the difference between UPDATE and INSERT. You keep saying you want to insert data, but youre writing an update query