Insert records from table 2 to tsable 1

[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()

 

[Bernie]

Well, if I take your request literally, then you need to do the following.

1. Modify table to include a column/field for INTEREST.
2. Read all of the interest values for LAST=charles.
3. Concantinate all the values read in step 2 into a string, seperating them with commas.
4. Update the LAST-charles record in Table 1 to make the INTEREST field equal to the string made in Step 3.

There reason I'm not sure what you need it the placing of three interest values into one field. Is that what your assigenment really wanted?

Bernie

 

[victor64]

Bernie,

Yes, this is what I'm trying to do. The database is as follows:

table 1 and table 2 have a common field "NIIN"

I'm building a new column in table 1 to include all the records that matches the NIIN field
in table 2. For example table 2 has multiple matching NIIN, I need to append these records to the new column (sperated by a column).

Thanks,

Victor

 

[Bernie]

Good, the you are on your way. Let us know if you need any more help.

Bernie

 

[victor64]

How do I accomplish step 3?, my code below is not working.

Dim mySQL_Statement As String = "UPDATE identification_data " & vbNewLine & _
" INNER JOIN " & vbNewLine & _
" reference_number_data " & vbNewLine & _
" ON identification_data.niin " & _
" = reference_number_data.niin " & vbNewLine & _
" SET identification_data.code = " & _
" concat(identification_data.code,reference_number_data.rn) "

 

[Bernie]

I wouldn't do it in a SQL statement. I would either capture the records in a dataset and then loop through them to get all the values, or read them with a stream with a datareader and concantinate them in real time as I read them.

Bernie

 

[victor64]

Can you send a sample code?

Thanks,

Victor

 

[cjard]

I wouldn't do it in a SQL statement

I would

 

[Bernie]

I would

OK, then you answer his question.

 

[cjard]

Apologies; i take that back. Now that I've actually read the question in more depth and understood it to be an attempt to pivot table 2 into table 1, I'd go with your suggestion. Actually I wouldnt update the tables at all because it will cause no end of problems trying to work with the data. It should be left as is and pivoted whenever it is to be displayed

 

[victor64]

Hello,

I'm making some progrsss but getting the following error on line:cmd.ExecuteNonQuery
It looks like the "'" don't match, I couldn't figure it out.

Syntax error in string in query expression ''hockey,swimming,'.

Can you please help me fix this error.

Thanks,

Code:

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aopt2002orgorg.mdb;Persist Security Info=True;Jet OLEDB:Database Password=testaopupdate"

        Dim objConnection As New OleDb.OleDbConnection(ConnectionString)
        objConnection.Open()

        Dim da As New OleDb.OleDbDataAdapter("Select NIIN, code2 from IDENTIFICATION_DATA", objConnection)

        Dim dt1 As New DataTable

        da.Fill(dt1)

        da.Dispose()
        da = Nothing

        da = New OleDb.OleDbDataAdapter("Select NIIN, RN from REFERENCE_NUMBER_DATA", objConnection)

        Dim dt2 As New DataTable

        da.Fill(dt2)

        Dim dv2 As DataView

        dv2 = dt2.DefaultView

        Dim cmd As OleDbCommand

        Dim SQLStr As String

        For Each dr As DataRow In dt1.Rows

            dv2.RowFilter = "NIIN='" & dr.Item("NIIN") & "'"

            If dv2.Count > 0 Then

                SQLStr = "Update IDENTIFICATION_DATA Set code2='"

                For Each drv2 As DataRowView In dv2
                    SQLStr &= drv2.Item("rn") & ","
                Next

                'strip off trailing comma
                SQLStr = SQLStr.Substring(0, SQLStr.Length - 1)

                cmd = New OleDbCommand(SQLStr, objConnection)

                cmd.ExecuteNonQuery()

                cmd.Dispose()

                cmd = Nothing

            End If

        Next

Victor

 

[victor64]

This code is works.

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aopt2002orgorg.mdb;Persist Security Info=True;Jet OLEDB:Database Password=testaopupdate"

        Dim objConnection As New OleDb.OleDbConnection(ConnectionString)
        objConnection.Open()

        Dim da As New OleDb.OleDbDataAdapter("Select NIIN, code2 from IDENTIFICATION_DATA", objConnection)

        Dim dt1 As New DataTable

        da.Fill(dt1)

        da.Dispose()
        da = Nothing

        da = New OleDb.OleDbDataAdapter("Select NIIN, RN from REFERENCE_NUMBER_DATA", objConnection)

        Dim dt2 As New DataTable

        da.Fill(dt2)

        Dim dv2 As DataView

        dv2 = dt2.DefaultView

        Dim cmd As OleDbCommand

        Dim SQLStr As String

        For Each dr As DataRow In dt1.Rows

            '.RowFilter = "NIIN='" & dr.Item("NIIN") & "'"
            dv2.RowFilter = "NIIN='" & dr.Item("NIIN") & "'"

            If dv2.Count > 0 Then

                SQLStr = "Update IDENTIFICATION_DATA Set code2='"

                For Each drv2 As DataRowView In dv2
                    SQLStr &= drv2.Item("rn") & ","
                Next

                'strip off trailing comma
                SQLStr = SQLStr.Substring(0, SQLStr.Length - 1)

                'add closing apostrophe
                SQLStr &= "'"

                'add WHERE clause
                SQLStr &= " Where NIIN='" & dr.Item("NIIN") & "'"

                cmd = New OleDbCommand(SQLStr, objConnection)

                'MsgBox(SQLStr)

                cmd.ExecuteNonQuery()

                cmd.Dispose()

                cmd = Nothing

            End If
        Next

 

[cjard]

Please use code tags when posting code
Visual Basic .NET Forums - BB Code List

I tend not to bother reading posts that have badly formatted code in them

 

[victor64]

How is is badly formatted?, your suggestion would be appreciated.

Victor

 

[cjard]

I haven't the time to teach you how to use an internet forum, I'm afraid. Please use code tags in future