Compare 2 Tables - Return Rows VB 2008

Expand Post »

Hows it going everyone. Im trying to compare two tables, and display the rows that are not in both tables in a DataGrid. Im using Visual Basic 2008, and created a query in the TableAdapterManager, but when I try to call the query, it is not displaying the results. Shows an error to lessen constraints, but I disabled constraints. I would really appreciate any tips, Im still pretty new to VB. Heres what I have in the query.

VB.NET Syntax (Toggle Plain Text)
SELECT     Table1.Path, Table1.Filename, Table1.Type, Table1.[key], Table2.Path AS Expr1, Table2.Filename AS Expr2, Table2.Type AS Expr3
FROM         Table1, Table2
WHERE     (Table1.Path <> Table2.Path) OR
                      (Table1.Filename <> Table2.Filename)
SELECT     Table1.Path, Table1.Filename, Table1.Type, Table1.[key], Table2.Path AS Expr1, Table2.Filename AS Expr2, Table2.Type AS Expr3
FROM         Table1, Table2
WHERE     (Table1.Path <> Table2.Path) OR
                      (Table1.Filename <> Table2.Filename)

cellus205

Reputation Points: 10

Solved Threads: 3

Junior Poster in Training

Offline

57 posts
since May 2007

Permalink

Jun 3rd, 2008

Re: Compare 2 Tables - Return Rows VB 2008

Do you want to
1) step throgh all rows of Table1, and show the row when there is no row with the same path AND filename in Table 2
2) and then do the same vice versa?

If yes, and DBMS is MS SQL Server or Oracle I would use a union:

VB.NET Syntax (Toggle Plain Text)
/* paste this in MS Query Analyzer, use || instead of + for oracle */
DROP TABLE t1
DROP TABLE t2
CREATE TABLE t1(
sPath VARCHAR(20),
sFilename VARCHAR(10)
)
CREATE TABLE t2(
sPath VARCHAR(20),
sFilename VARCHAR(10)
)
INSERT INTO t1 VALUES ( 'P1', 'F1' )
INSERT INTO t1 VALUES ( 'P2', 'F2' )
INSERT INTO t2 VALUES ( 'P1', 'F1' )
INSERT INTO t2 VALUES ( 'P3', 'F3' )
 
SELECT sPath + sFilename FROM t1 WHERE spath + sfilename NOT IN 
   (SELECT spath + sfilename FROM t2) 
UNION ALL
SELECT sPath + sFilename FROM t2 WHERE spath + sfilename NOT IN 
   (SELECT spath + sfilename FROM t1)
 
 
------------------------------ 
P2F2
P3F3
(2 rows affected)
/* paste this in MS Query Analyzer, use || instead of + for oracle */
DROP TABLE t1
DROP TABLE t2
CREATE TABLE t1(
sPath VARCHAR(20),
sFilename VARCHAR(10)
)
CREATE TABLE t2(
sPath VARCHAR(20),
sFilename VARCHAR(10)
)
INSERT INTO t1 VALUES ( 'P1', 'F1' )
INSERT INTO t1 VALUES ( 'P2', 'F2' )
INSERT INTO t2 VALUES ( 'P1', 'F1' )
INSERT INTO t2 VALUES ( 'P3', 'F3' )

SELECT sPath + sFilename FROM t1 WHERE spath + sfilename NOT IN 
   (SELECT spath + sfilename FROM t2) 
UNION ALL
SELECT sPath + sFilename FROM t2 WHERE spath + sfilename NOT IN 
   (SELECT spath + sfilename FROM t1)


------------------------------ 
P2F2
P3F3
(2 rows affected)

dadelsen

Reputation Points: 25

Solved Threads: 5

Newbie Poster

Offline

22 posts
since Jun 2007

Permalink

Jun 3rd, 2008

Re: Compare 2 Tables - Return Rows VB 2008

Thanks a lot, that got it working for me. Just created the table with the query, databind, and there it was. Thanks again

cellus205

Reputation Points: 10

Solved Threads: 3

Junior Poster in Training

Offline

57 posts
since May 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: enter key

Next Thread in VB.NET Forum Timeline: Help StreamReader with DataGrid XML

DaniWeb Message

Cancel Changes

User Name
Password