How to Merge data from table 2 to table 1 based on common field

[victor64]

mySQL_Statement = "INSERT INTO IDENTIFICATION_DATA(code) (SELECT RN FROM REFERENCE_DATA(RN) WHERE IDENTIFICATION_DATA.NIIN = REFERENCE_DATA.NIIN)"

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

 

[victor64]

Follow up to question asked

Hello again, I''m getting the following error on theobjDataAdapter.Fill(objDataSet, "SN") line.

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Below is the complete code.

Dim mySQL_Statement As String = ""
mySQL_Statement = "Update([identification_data]" & vbNewLine
mySQL_Statement = "INNER(Join)" & vbNewLine
mySQL_Statement = "reference_data" & vbNewLine
mySQL_Statement = "ON identification_data.niin = reference_data.niin " & vbNewLine
mySQL_Statement = "SET [identification_data].code = [reference_data].RN"

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

can you please help me fix this error

Thanks,

Victor

 

[MattP]

You've got a problem here:

Dim mySQL_Statement As String = "" 
mySQL_Statement = "Update([identification_data]" & vbNewLine 
mySQL_Statement = "INNER(Join)" & vbNewLine 
mySQL_Statement = "reference_data" & vbNewLine 
mySQL_Statement = "ON identification_data.niin = reference_data.niin " & vbNewLine 
mySQL_Statement = "SET [identification_data].code = [reference_data].RN"

Put a break in your code and check what's in your mySQL_Statement string after stepping through this section.

 

[victor64]

That's the exact code that I'm using, and it seems to work.

Thanks for yor reply.

Victor

 

[MattP]

That's the exact code that I'm using...

I know it is.

Try stepping through this section and seeing what the variable holds when you're done. You should find that it doesn't seem to work.