Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
I have two tables A and B shown.
Table A
HoleNo Depth_from Depth_to
A1 1 2
A1 3 4
A1 5 6
 
Table B
HoleNo Depth_from Depth_to
A1
A1
A1
 
Now How do I update table B so that it looks like table A
Note that no changes must be made to table A.
I have tried to assign id's to table B so that I do the update based on the unique id's, but since I am not to make any changes to table A I am finding it very difficult.
 
this is my code
I first save all HoleNo into B from A
sqlSTR = "SELECT * FROM A"
ExecuteSQLQuery(sqlSTR) 'call function to execute the query
For Each row1 As DataRow In sqlDT.Rows
 
sqlSTR = "INSERT INTO B (HoleNo) VALUES ('" & row1(0) & "')"
ExecuteSQLQuerylocal(sqlSTR) 'call function to execute the query
 
Next
 
So after inserting all HoleNo then I try to update the rest of the fields with this code
 
sqlSTR = "SELECT * FROM A"
ExecuteSQLQuery(sqlSTR) 'call function to execute the query
For Each row1 As DataRow In sqlDT.Rows
 
sqlSTR = "UPDATE B SET Depth_from ='" & row(0) & "' WHERE HoleNo ='" & row(0) & "'"
ExecuteSQLQuerylocal(sqlSTR)
 
Next
Posted 27-Feb-13 21:06pm
Edited 27-Feb-13 23:17pm
v2
Comments
Prakash Thirumoorthy at 28-Feb-13 3:14am
   
where is your query which was u tried?
rosoftghana at 28-Feb-13 5:03am
   
I first save all HoleNo into B from A
sqlSTR = "SELECT * FROM A"
ExecuteSQLQuery(sqlSTR) 'call function to execute the query
For Each row1 As DataRow In sqlDT.Rows
 
sqlSTR = "INSERT INTO (B) VALUES ('" & row1(0) & "')"
ExecuteSQLQuerylocal(sqlSTR) 'call function to execute the query
 
Next
 
then I update table B using this
sqlSTR = "SELECT * FROM A"
ExecuteSQLQuery(sqlSTR) 'call function to execute the query
For Each row1 As DataRow In sqlDT.Rows
 
sqlSTR = "UPDATE B SET Depth_from='" & row1("Depth_from") & "' WHERE HOLE_NUMBER='" & row(0) & "'"
ExecuteSQLQuerylocal(sqlSTR) 'call function to execute the query
 
Next
rosoftghana at 28-Feb-13 5:08am
   
I first save all HoleNo into B from A
sqlSTR = "SELECT * FROM A"
ExecuteSQLQuery(sqlSTR) 'call function to execute the query
For Each row1 As DataRow In sqlDT.Rows
 
sqlSTR = "INSERT INTO B (HoleNo) VALUES ('" & row1(0) & "')"
ExecuteSQLQuerylocal(sqlSTR) 'call function to execute the query
 
Next
 
So after inserting all HoleNo then I try to update the rest of the fields with this code
 
sqlSTR = "SELECT * FROM A"
ExecuteSQLQuery(sqlSTR) 'call function to execute the query
For Each row1 As DataRow In sqlDT.Rows
 
sqlSTR = "UPDATE B SET Depth_from ='" & row(0) & "' WHERE HoleNo ='" & row(0) & "'"
ExecuteSQLQuerylocal(sqlSTR)
 
Next

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Delete Table B from Database
then Create Table B from Table A
 
Use Sql Query
     DROP TABLE B
GO
     SELECT * INTO B From A
GO
  Permalink  
Comments
rosoftghana at 28-Feb-13 5:17am
   
I first save all HoleNo into B from A
sqlSTR = "SELECT * FROM A"
ExecuteSQLQuery(sqlSTR) 'call function to execute the query
For Each row1 As DataRow In sqlDT.Rows
 
sqlSTR = "INSERT INTO B (HoleNo) VALUES ('" & row1(0) & "')"
ExecuteSQLQuerylocal(sqlSTR) 'call function to execute the query
 
Next
 
So after inserting all HoleNo then I try to update the rest of the fields with this code
 
sqlSTR = "SELECT * FROM A"
ExecuteSQLQuery(sqlSTR) 'call function to execute the query
For Each row1 As DataRow In sqlDT.Rows
 
sqlSTR = "UPDATE B SET Depth_from ='" & row(0) & "' WHERE HoleNo ='" & row(0) & "'"
ExecuteSQLQuerylocal(sqlSTR)
 
Next

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 474
1 Gihan Liyanage 338
2 ChauhanAjay 180
3 Sergey Alexandrovich Kryukov 163
4 Vinay Mistry 160
0 Sergey Alexandrovich Kryukov 9,011
1 OriginalGriff 7,941
2 CPallini 2,603
3 Richard MacCutchan 2,121
4 Abhinav S 1,928


Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 28 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100