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

[vc1881]

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

 

[vc1881]

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

 

[huggyboy]

the way you have written it the string mySQL_Statement ends up with 'SET [identification_data].code = [reference_data].RN' in it - try mySQL_Statement += instead of = when you are building the sql line

 

[vc1881]

Hello,

I got it to work with the following SQL:

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)

 

[huggyboy]

well done - as an aside i wasnt aware that the vbnewline was needed in an sql string - however of course at least 1 space is - makes it easier to read in debug mode i suppose