Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with retreiving multiple countries to copy to another table

Posted on 2011-02-14
5
Medium Priority
?
258 Views
Last Modified: 2012-05-11
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.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
        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
0
Comment
Question by:vcharles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 34892838
Hi,

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

Open in new window

0
 

Author Comment

by:vcharles
ID: 34944070
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
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34944125
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()

Open in new window

0
 
LVL 13

Accepted Solution

by:
gamarrojgq earned 2000 total points
ID: 34944790
Hi,

Changet this line
dbcmd.CommandText = "Select COUNTRY From AOP5 WHERE SN = " & dSNTable.Rows(y).Item(0)

for this

dbcmd.CommandText = "Select DISTINCT COUNTRY From AOP5 WHERE SN = " & dSNTable.Rows(y).Item(0)

0
 

Author Closing Comment

by:vcharles
ID: 34944817
Thank You!
0

Featured Post

TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question