Victor Charles
asked on
Help with retreiving multiple countries to copy to another table
Hello,
I'm using the code below to retreive countries from COUNTRY column in AOP5 and copy it to Column CA2 in AOP6A, problem is I need to retreive multiple countries in the following format: BEL,CAN,FRA,USA and copy it to CA2 in AOP6A. Is there a way to retreive multiple countries for matching IDs between both table before I copy them to CA2?
For example to code below only retreives one country:
Set CA2='" & dTable.Rows(i).Item(1) & "'
I need to set CA2 = BEL,CAN,FRA etc..
Dim ConnectionString As String = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=|DataDirectory|\aop t2002org.m db;Persist Security Info=True;Jet OLEDB:Database Password=testaopupdate"
Dim dbcon As New OleDbConnection(Connection String)
dbcon.Open()
Dim dbcmd As New OleDbCommand
dbcmd.Connection = dbcon
dbcmd.CommandText = "Select SN, COUNTRY From AOP5"
Dim dTable As New DataTable
Dim dbadp As New OleDbDataAdapter(dbcmd)
dbadp.Fill(dTable)
For i As Integer = 0 To dTable.Rows.Count - 1
dbcmd.CommandText = "Update AOP6A Set CA2='" & dTable.Rows(i).Item(1) & "' Where SN='" & dTable.Rows(i).Item(0) & "'"
dbcmd.ExecuteNonQuery()
Next
dTable.Dispose()
dbcmd.Dispose()
dbcon.Close()
dbcon.Dispose()
AOP5 Table
SN COUNTRY
10411 BEL
10411 CAN
10411 FRA
AOP6A Should include (AOP6A AND AOP5 are linked by the SN Field)
SN CA2
10411 BEL,CAN,FRA
Thanks,
Victor
I'm using the code below to retreive countries from COUNTRY column in AOP5 and copy it to Column CA2 in AOP6A, problem is I need to retreive multiple countries in the following format: BEL,CAN,FRA,USA and copy it to CA2 in AOP6A. Is there a way to retreive multiple countries for matching IDs between both table before I copy them to CA2?
For example to code below only retreives one country:
Set CA2='" & dTable.Rows(i).Item(1) & "'
I need to set CA2 = BEL,CAN,FRA etc..
Dim ConnectionString As String = "Provider=Microsoft.Jet.OL
Dim dbcon As New OleDbConnection(Connection
dbcon.Open()
Dim dbcmd As New OleDbCommand
dbcmd.Connection = dbcon
dbcmd.CommandText = "Select SN, COUNTRY From AOP5"
Dim dTable As New DataTable
Dim dbadp As New OleDbDataAdapter(dbcmd)
dbadp.Fill(dTable)
For i As Integer = 0 To dTable.Rows.Count - 1
dbcmd.CommandText = "Update AOP6A Set CA2='" & dTable.Rows(i).Item(1) & "' Where SN='" & dTable.Rows(i).Item(0) & "'"
dbcmd.ExecuteNonQuery()
Next
dTable.Dispose()
dbcmd.Dispose()
dbcon.Close()
dbcon.Dispose()
AOP5 Table
SN COUNTRY
10411 BEL
10411 CAN
10411 FRA
AOP6A Should include (AOP6A AND AOP5 are linked by the SN Field)
SN CA2
10411 BEL,CAN,FRA
Thanks,
Victor
ASKER
Hello,
I'm sorry for the late reply, it works but I'm getting duplicates in the same string/row. For example: BEL, FRA.FRA,USA,DNK,DNK. Is there a way to avoid duplicate countries in the string?
Thankis,
Victor
I'm sorry for the late reply, it works but I'm getting duplicates in the same string/row. For example: BEL, FRA.FRA,USA,DNK,DNK. Is there a way to avoid duplicate countries in the string?
Thankis,
Victor
Use DISTINCT for Country also.
Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aopt2002org.mdb;Persist Security Info=True;Jet OLEDB:Database Password=testaopupdate"
Dim dbcon As New OleDbConnection(ConnectionString)
dbcon.Open()
Dim dbcmd As New OleDbCommand
dbcmd.Connection = dbcon
'TO GET THE SN LIST
dbcmd.CommandText = "Select Distinct SN From AOP5"
Dim dSNTable as New DataTable
Dim dbadpSN As New OleDbDataAdapter(dbcmd)
dbadp.Fill(dSNTable)
Dim strUpdate As String
'FOR EACH SN CREATE AN STRING WITH THE COUNTRIES
For y As Integer = 0 To dSNTable.Rows.Count -1
'GET THE COUNTRIES FOR THE SN
Dim dTable As New DataTable
dbcmd.CommandText = "Select DISTINCT COUNTRY From AOP5 WHERE SN = " & dSNTable.Rows(y).Item(0)
Dim dbadp As New OleDbDataAdapter(dbcmd)
dbadp.Fill(dTable)
strUpdate = ""
'CREATE THE STRING WITH ALL THE COUNTRIES FOR THE SN LIKE BEL,CAN,FRA ETC.
For i As Integer = 0 To dTable.Rows.Count - 1
strUpdate &= dTable.Rows(i).Item(0) & ","
Next
'REMOVE THE LAST COMMA IF THERE WAS COUNTRIES FOR THE SN
if strUpdate.Length > 0 Then
strUpdate = strUpdate.Substring(0, strUpdate.Length - 1)
end if
'UPDATE AOP6A TABLE
dbcmd.CommandText = "Update AOP6A Set CA2='" & strUpdate & "' Where SN='" & dSNTable.Rows(y).Item(0) & "'"
dbcmd.ExecuteNonQuery()
Next
dTable.Dispose()
dbcmd.Dispose()
dbcon.Close()
dbcon.Dispose()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank You!
You can try to build your Update string for each SN, and after that run your UPDATE statement, try the attached code, it should works, and will give you an idea of how to do it.
Hope it helps
Open in new window