OleDbDataAdapter.Fill(DataTable)

  • Thread starter Thread starter Diana Mueller
  • Start date Start date
D

Diana Mueller

I'm working with ADO.NET for quite a while now (allthough usually within the
SqlClient namespace), but this has never happened before,
The project I'm currently working on has the requirement, that the user
should be able to work with a local access db
or the central sql server as datasource (user's choice). So we decided to
use OleDbConnections, -Commands, -Adapters etc.
so that switching between datasources will only require a change of the
connectionstring.

Now to my problem. I'm trying to fill a DataTable of my typed DataSet with
an OleDbDataAdapter using it's .Fill() function.
The problem is, no rows are added to the table. If I do an .ExecuteReader()
with the adapter's SelectCommand though, all rows that should be returned
are returned (also when executing the command in sql server's query
analyzer). What could be the problem here? I read in the
DbAdapter.Fill(DataTable) help, that
using sql batch commands can lead to problems with the OleDb data provider.
I am filling a temporary table before reading out of it in the command, so
could that be the source of trouble? Has someone an idea what I could do to
fix this?

Any help is (as allways) highly appreciated,
Diana
 
Diana,

Are you using command.parameters. The behaviour of that is different from
the behaviour in SQLClient. (It does not use the name however use the
sequence it is used in the connectionstring. Therefore you can use ? as
well)

I hope this helps,

Cor
 
Hi Diana,

Indeed, I think that that is your problem. Reading the MSDN helptext it says
(http://msdn.microsoft.com/library/d...rfsystemdatacommondbdataadapterclasstopic.asp)
....
Note When handling batch SQL statements that return multiple results, the
implementation of FillSchema for the .NET Framework Data Provider for OLE DB
retrieves schema information for only the first result. To retrieve schema
information for multiple results, use Fill with the MissingSchemaAction set
to AddWithKey.
....

Did you use the MissingSchemaAction?

Cheers,
Christiaan
 
Yes, I set the MissingSchemaAction of the OleDbAdapter to AddWithKey.
Unfortuantely that didn't change anything. I thought, maybe anyone has
encountered this problem before and knows what I have to do to fix this.
Thanks for your help Christiaan.
 
Yes the OleDbCommand uses one parameter. I don't think you understood my
problem though. The command executes fine. It returns a reader that reads
through all rows that I am expecting. Only when trying to use an
OleDbAdapter to fill a DataTable the problem (no rows being added to the
table) occurs.
Diana,

Are you using command.parameters. The behaviour of that is different from
the behaviour in SQLClient. (It does not use the name however use the
sequence it is used in the connectionstring. Therefore you can use ? as
well)

I hope this helps,
Click to expand...
Cor
 
Dianna,
Yes the OleDbCommand uses one parameter. I don't think you understood my
problem though. The command executes fine. It returns a reader that reads
through all rows that I am expecting. Only when trying to use an
OleDbAdapter to fill a DataTable the problem (no rows being added to the
table) occurs.
Click to expand...

Than show us those part of the code, now it seems for me a quiz without a
prize.

Cor
 
Cor,

the thing is, I don't think the problem lies in the code.
My problem is as follows:

OleDbCommand selectCommand = MyMagicFactory.Make();
OleDbAdapter adp = new OleDbAdapter(selectCommand);

adp.SelectCommand.Connection.Open();
OleDbDataReader reader = adp.SelectCommand.ExecuteReader();
int i = 0;
while (reader.Read())
i++;
reader.Close();
adp.SelectCommand.Connection.Close();

// i is now x, which is exactly what I was expecting;

// now for the problem...
MyTypedDataSet dts = new MyTypedDataSet();
int j = adp.Fill(dts.MyTable);
// j is 0 because no rows were added. :(
 
Diana,

In my opinion did you type this new.
It is not impossible that there is just a case or whatever wrong in your
code. Therefore this you could have copied from a website in my opinion and
shows us nothing to make it able to help you.

Cor
 
Hi Diana,

What if you use
int j = adp.Fill(dts);
instead of
int j = adp.Fill(dts.MyTable);

In case you made a typo in your MyTable definition, it should create a table
in the dts

HTH
Christiaan
 
This is strange. When I use the .Fill(DataSet) overload the DataTable is
filled nicely.
It is the DataTable I was trying to fill in the first place, not a newly
created one. I don't get it. My problem at hand it solved though, so thanks
a lot for the suggestions!
 
Diana,
It is the DataTable I was trying to fill in the first place, not a newly
created one. I don't get it. My problem at hand it solved though, so
thanks
a lot for the suggestions!
Click to expand...
Did you read what Cristian wrote, it has the same intention as my message.
Can it be that you made a typo in the name of the datatable or whatever.
This part is very case sensitive.

Cor
 
Cor,

I reproduced the defect I was describing in my test environment, where
actually used code pretty much like the one I posted. That is why I am so
sure
that there is nothing to be won from looking at the original lines.
Naturally (like you assumed), the code I posted was not taken from the real
application.

Anyways, my table is filled now, using Christiaan's suggestion of filling
the DataSet instead of the DataTable. Thanks for trying to help.
 
All the mapping is genrated code, so I don't think there's the chance of a
typo. Furthermore, I think a typo would've resulted in a newly created table
in my DataSet, which did not happen.

I think I'll dig deeper into that problem when I've got a little more time
and post my results here.
 
If Fill(DataSet) worked and Fill(DataTable) didn't then it sounds more like
multiple row returning results were being returned with the first one being
empty. Did Fill(DataSet) create multiple DataTable?
 
Back
Top