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

[vc1881]

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

 

[jebenson]

I don't think you can do this with a single SQL statement. I think you will need to select everything from table1, loop through those records and select the appropriate records from table2 for each table1 record.

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)

Dim da As New OleDb.OleDbDataAdapter("Select LAST, HOBBY from Table1", objConnection)

dim dt1 As New DataTable

da.Fill(dt1)

da.Diapose
da = Nothing

da = New OleDb.OleDbDataAdapter("Select LAST, INTEREST from Table1", 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 = "LAST='" & dr.Item("LAST") & "'"

If dr2.Count>0

SQLStr = "Update Table1 Set INTEREST='"

For Each drv2 As DataRowView In dv2
SQLStr &= drv2.Item("INTEREST") & ","
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

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[vc1881]

Thank you, I will try it and get back to you.

Victor

 

[vc1881]

Hello,

I'm 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 OLEDBatabase 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

 

[Andrzejek]

You have the 'beginning' comma, but not the 'end' one;

SQLStr = "Update IDENTIFICATION_DATA Set code2 = [red]'[/red]"

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

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

[blue]SQLStr += "[red]'[/red]"[/blue]

And I think you still need a WHERE part of your SQLstr

Have fun.

---- Andy

 

[vc1881]

I added: SQLStr += " WHERE IDENTIFICATION_DATA.NIIN = REFERENCE_NUMBER_DATA'"

but I'm still getting the following error, the code field is definitely large enough to capture the data, any idea what's causing the error?.

Error: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

Victor

 

[jebenson]

Oops, sorry I was rushed when I made my earlier post. Here's the correct code to build the SQL:

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

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

'add closing apostrophe
SQLStr &= "'"

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

I would like to point out that this update will change all records for a particular last name, so if there are multiple "Smith" records, they will all get changed. I suggest using some unique identifier in the WHERE clause.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Andrzejek]

How are your fields (code2 and rn) defined?

Look at your SQLStr before executing, you may find out that it looks something like this:

Update IDENTIFICATION_DATA Set code2 = [COLOR=red yellow]'basketball                            , football                              , swimming                              '[/color]

You may want to Trim the fields before you Update.

Have fun.

---- Andy

 

[vc1881]

it's working ok Thanks a million. How do we avoid duplicate hobies from matching field to be included in the IDENTIFICATION_DATA table?

Thanks,

Victor

 

[vc1881]

Hello again,

I tried the same code to transfer data from a diferent table, but I am getting the following error:

"No value given for one or more required parameters", at the cmd.ExecuteNonQuery() line, given my code can you help me determine the cause of this error.

Thanks,

Code:

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)
objConnection.Open()

'Dim da As New OleDb.OleDbDataAdapter("Select NIIN, code3 from IDENTIFICATION_DATA", objConnection)
Dim da As New OleDb.OleDbDataAdapter("Select NIIN, SN 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)
da = New OleDb.OleDbDataAdapter("Select NSN, SN from AOP5", 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 = "NSN='" & dr.Item("NIIN") & "'"

If dv2.Count > 0 Then

SQLStr = "Update IDENTIFICATION_DATA Set SN='"

For Each drv2 As DataRowView In dv2
SQLStr &= drv2.Item("SN") & ","
Next
MsgBox(SQLStr)
'strip off trailing comma
SQLStr = SQLStr.Substring(0, SQLStr.Length - 1)

'add closing apostrophe
SQLStr &= "'"
MsgBox(SQLStr)
'add WHERE clause
SQLStr &= " Where NSN='" & dr.Item("NIIN") & "'"
MsgBox(SQLStr)
cmd = New OleDbCommand(SQLStr, objConnection)

MsgBox(SQLStr)

cmd.ExecuteNonQuery()

cmd.Dispose()

cmd = Nothing

 

[Andrzejek]

What does your MsgBox(SQLStr) say at the point of the error?

It would be better for you to have your SQLStr displayed in Debug - you can copy it and paste it here.

Have fun.

---- Andy

 

[jebenson]

Is the code failing immediately, or does it run for a bit before failing?

Could there be an apostrophe in any of the fields being transferred?



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[vc1881]

Hello,

I'm getting the followwing error at the cmd.ExecuteNonQuery() line:

No value given for one or more required parameters.

I can't see what's causing the error.

Thhanks,

Victor