Execute Query on Dataset

[Guest]

Hello, this is my first post in this newsgroup. Easy question:

I have a Dataset which I populate by reading tables from Excel (i.e. I don't
have an underlying Database).

I would like to query this Dataset and get a new Datatable as result. I need
my query to select only certain coloumns from certain datatables (i.e. I want
my sql statement to include Inner Joins).

I've looked at Dataview but it won't let you use Inner Joins or even select
only certain columns...

How to proceed????

Hope this doesn't sound too stupid

 

[Cor Ligthert]

LunaNera,

A dataset is a dataset not a database.

You can use
datatable.select (what are not SQL selects)
and
dataview.rowfilter(which uses the same expressions as above)
and
jus loop in a lot of way through your tables in the dataset
and
a lot more

I hope this helps?

Cor

 

[Jared]

The dataview object represents a specific view of a table, which will not
produce the results you are looking for.
Consider using a select statement on the datasource itself. I don't use many
xls datasources so you may have to to a litte reasearch on how to nest the
joins, but, the following example worked fine for me.

Dim dt As New DataTable("Results")
Dim xlsConn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=Northwind.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
Dim SelectStatement As String = "Select CompanyName, Orders.OrderID from
Customers INNER JOIN Orders on Customers.CustomerID = Orders.CustomerID"
Dim da As New OleDb.OleDbDataAdapter(SelectStatement, xlsConn)
Try
da.Fill(dt)
Catch ex As Exception

End Try

 

[Guest]

I am not very clear on why a dataset is a dataset and not a database :\ My
view was that a Dataset is a representation of a database but it exists only
in memory and not on disk.

So basically I cannot do what I want to do

myDataTable.Select(FilterExpression As String) acts only on a single
DataTable (the object that calls the Select function), so does
myDataview.RowFilter

BUT

If I create a real DB from my Dataset? And then query that DB to obtain the
Datatable that I wanted in the first place?

If this is possible it seems very stupid to me that the same thing can't be
done without having to save the Dataset into a DB on disk.

My god, I am such a n00b

 

[Cor Ligthert]

Luna,

A dataset on disk is nothing more than a txt file with some fields what we
call tags in it.

There is no way to extend it in the middle or things like that.

However I although i mention some things I did not point you on the
datarelation which can probably be a substitute for your Join.

http://msdn.microsoft.com/library/d...tml/frlrfsystemdatadatarelationclasstopic.asp

I hope that gives an idea?

Cor

 

[W.G. Ryan eMVP]

Can you pull a DataTable from each of your datasources so you have an local
copy of a table corresponding to each real object and bind them with a
DataRelation object? If I'm understanding your problem correctly, this
should do it for you.

let me know if not.

Cheers,

Bill