Question How to use SQL commands with DataSets

[Snowflake]

What I want to do is load two tables from MS Access into VB.NET and modify the data they contain with SQL statements

I have managed to copy the two tables into a dataset, but now I am stuck on how to run SQL commands on them

Someone did say that if your data is in a dataset you cannot use SQL statements, I don't know if that's true but any tips or suggestions would be appreciated

 

[jmcilhinney]

Someone did say that if your data is in a dataset you cannot use SQL statements, I don't know if that's true but any tips or suggestions would be appreciated

That is not true but exactly how untrue it is depends on exactly what you mean when you say "dataset".

I have managed to copy the two tables into a dataset, but now I am stuck on how to run SQL commands on them

What EXACTLY do you mean when you say "copy the two tables into a dataset"? The statement itself doesn't actually make sense but I know the sort of thing you're getting at. Do you mean that you used the Data Source wizard to generate a typed DataSet, including table adapters? If you have a typed DataSet then you use those table adapters to move data from the database to the DataSet and back again. If you're using an untyped DataSet then you use data adapters instead, which are similar but not the same. I can give you more specific instruction but I need to know which way to go first.

 

[Snowflake]

Firstly, thanks for replying

The following should give you some idea what I was trying to do

        Dim Conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Dpath
        Dim Rs1,Rs2 As New Recordset

        Rs1.Open("SELECT Location, Point, Area FROM Ta WHERE (Vsat Is Null);", Conn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic)
        Rs2.Open("SELECT Locat, Point, Area FROM Tb WHERE (Vsat Is Null);", Conn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic)

        Dim Da As New System.Data.OleDb.OleDbDataAdapter()
        DA.Fill(DSet, Rs1, "Ta")
        DA.Fill(DSet, Rs2, "Tb")

What main thing I wanted to do was insert Rs1,Rs2 into a new table based on the value of Area

There is no urgency to the question as I managed to work my way around the problem by running the SQL statements in Access before importing a single table into my program.

 

[jmcilhinney]

Why are you using Recordsets at all? Is there a particular reason that you're mixing ADO and ADO.NET rather than just using ADO.NET?