Question How to use SQL commands with DataSets

Snowflake

Member
Joined
Sep 16, 2014
Messages
15
Programming Experience
Beginner
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,725
Location
Sydney, Australia
Programming Experience
10+
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

Member
Joined
Sep 16, 2014
Messages
15
Programming Experience
Beginner
Firstly, thanks for replying

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

VB.NET:
        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.
 
Top Bottom