Filling typed datatables from stored queries

[Rob Richardson]

Greetings!

My Access database has a table named Holdings which has a field named
Covered. It has a query named ActiveHoldings that retrieves all records
from the Holdings table for which Covered is false.

I have a typed dataset that is set up to handle the data the ActiveHoldings
query will return. To retrieve that data, I use the following lines of
code:

daAdapter.SelectCommand = New OleDbCommand("SELECT * FROM ActiveHoldings",
mDB)
daAdapter.Fill(m_Portfolio, "ActiveHoldings")

It seems strange to me that I have to use an SQL command to retrieve data
from my database, when I already have an SQL command stored in the database.
I tried using just "ActiveHoldings" for the first argument to the
OleDbCommand's constructor, but it didn't work. Is there a way to just pass
the name of the query to the database, instead of an entire SQL statement?

Also, when I first set this up, I was getting the entire contents of the
Holdings table. I changed the schema of the typed dataset to match the
ActiveHoldings query, but I forget to change the SQL statement in the
OleDbCommand constructor. Therefore, the adapter was retrieving more data
than the typed dataset was prepared to handle. The ActiveHoldings table
ended up with no records, but no exception was thrown. I believe there
would have been some error indication someplace. How should I have checked
for an error in that operation?

Thanks again!

Rob

 

[Miha Markic [MVP C#]]

Hi Rob,

Use
daAdapter.SelectCommand = New OleDbCommand("SELECT * FROM ActiveHoldings",
mDB)
daAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

 

[William Ryan [eMVP]]

AFAIK, if your query doesn't match a typed dataset it just ignores the
results..so that might be what's causing problem 2. Like Miha posted,
setting the commandtype should fix the first problem.

HTH,

Bill