I'm converting a lot of FOXPRO/FOXWEB files to ASP.NET with VB.NET. The underlying database will remain FOXPRO (for now).
Given two tables in two different databases that share a common field. Say the first table is "table1" and the second is "table2" and the name of the common field is "cid"
Say I have used oledb to extract an oledb table from table1 called "interestingrows" using a line of code something like:
adapter.Fill(MyDataSet, "interestingrows")
I would like to say something like
sql = "DELETE FROM interestingrows WHERE cid in (SELECT cid FROM TABLE2)"
... except the sql cmd is being sent to my adapter which is connect to a foxpro db and "interestingrows" is an oledb table.
It would be very nice, though not absolutely necessary, if the solution will work when we eventually decide to switch underlying database to MS SQL (or whatever customer decides on).
Based on your comment feedback, you might want to create a SECONDARY "Connection" that points to the common root folder to both tables... Otherwise I expect your query would fail by trying to gain access one level UP from where the current connection is... It might fail you on that. So, with a new rooted connection path, your query can reference the paths and tables in one query... something like...
C:\Somewhere\CommonPath\
C:\Somewhere\CommonPath\PrimaryData\
C:\Somewhere\CommonPaht\AlternateData\
and your current connection is going to
C:\Somewhere\CommonPath\PrimaryData\
create a SECONDARY connection pointing to
C:\Somewhere\CommonPath\
Then your query can be something like
delete from PrimaryData\Table1
where cid in ( select cid from AlternateData\Table2 );