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

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

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

"LunaNera" <(E-Mail Removed)> wrote in message
news:3E15BCE6-2C6B-4701-A856-(E-Mail Removed)...
> 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

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" wrote:

> 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
>
>
>

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/de...classtopic.asp

I hope that gives an idea?

Cor

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
"LunaNera" <(E-Mail Removed)> wrote in message
news:00280C3D-0A94-4AF8-9ED4-(E-Mail Removed)...
> So you're telling me to use an Excel file as a proper datasource and query
> that...
> it sounds interesting
>
> BUT
>
> unfortunately, the data that I want to execute the query on is in multiple
> Excel files. That's why I wanted to stick all the data from the various
> sources into my Typed Dataset and then do a query on that.
>
> Surely the best thing to do is to have a proper underlying DB;
> I would then:
> 1. Import the DB into my Dataset;
> 2. Import my Excel data into the Dataset therfore applying the necessary
> constraints;
> 3. Save the whole thing back into the Database;
> 4. Execute the query on the Database.
>
> This, unfortunately for me, is quite a paradigm shift and will require
lots
> of rewriting
>
>
> "Jared" wrote:
>
> > 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
> >
> > "LunaNera" <(E-Mail Removed)> wrote in message
> > news:3E15BCE6-2C6B-4701-A856-(E-Mail Removed)...
> > > 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
> >
> >
> >