mmaslar
asked on
OleDbDataAdapter.fill fails on MS Access Null column
The code below reads an MS Access database & fills a data adapter. The daMdb.Fill(dsMdb) fails, however, when it encounters the MI column -- chich is all numms at this time. I have several columns which allow nulls; how to read into a dataset without failing?
Thanks!
Mark
string MDBconnectionString = @"Provider=Microsoft.Jet.O LEDB.4.0;D ata Source=" + mdbFile + ";";
OleDbConnection cnnAccesss = new OleDbConnection(MDBconnect ionString) ;
string SQLselect = "SELECT Client.FirstName as first_name, " +
"Client.LastName as last_name, " +
"Client.MI as mi_initial " +
"FROM Client";
OleDbDataAdapter daMdb = new OleDbDataAdapter(SQLselect , cnnAccesss);
DataSet dsMdb = new DataSet();
daMdb.Fill(dsMdb);
Thanks!
Mark
string MDBconnectionString = @"Provider=Microsoft.Jet.O
OleDbConnection cnnAccesss = new OleDbConnection(MDBconnect
string SQLselect = "SELECT Client.FirstName as first_name, " +
"Client.LastName as last_name, " +
"Client.MI as mi_initial " +
"FROM Client";
OleDbDataAdapter daMdb = new OleDbDataAdapter(SQLselect
DataSet dsMdb = new DataSet();
daMdb.Fill(dsMdb);
ASKER
> try setting dsmdb.enableconstraints = false;
I tried; no difference.
>please add the error message you get...
'system.data.oledb.oledbex ception' occurred in system.data.dll
This error occurs when Fill is invoked. The orrur does *not* occur if I omit the Middle Initial field from the Select string. All Middle Initial values are currently null, and the entire program works fine if that field is omitted. (But I need that field...)
Thanks,
Mark
I tried; no difference.
>please add the error message you get...
'system.data.oledb.oledbex
This error occurs when Fill is invoked. The orrur does *not* occur if I omit the Middle Initial field from the Select string. All Middle Initial values are currently null, and the entire program works fine if that field is omitted. (But I need that field...)
Thanks,
Mark
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>Confirm that the MI column is actually named "MI".
Yes, this SQL string was generated by Access' Query tool:
SELECT Client.ClientId, Client.FirstName, Client.LastName, Client.MI
FROM Client;
> Client.[MI] as mi_initial FROM Client";
This fails on the Fill.
>Try: string SQLselect = "SELECT Client.* FROM Client";
This succeeds on the fill. Interesting. However, I subsequently generate an XML file. The client's specification states that MI is a "required" field, so I was planning on generating an empty XML node for MI, since they haven't populated it w/ data yet. Select omits the MI field on XML export; probably because there's no data. Also, there are many fields that I do not wish to select, so Select * is not ideal.
Thanks,
Mark
Would you be able, only for the purposes of testing, to rename the "MI" field to something else ("MiddleInitial" for example) and try filling the dataset with "SELECT Client.ClientId, Client.FirstName, Client.LastName, Client.MiddleInitial FROM Client" ?
ASKER
> Client.MiddleInitial
I renamed the field, and tried that... same error @ the same location.
Thanks,
Mark
I renamed the field, and tried that... same error @ the same location.
Thanks,
Mark
Mark, this is a very strange problem. Does "SELECT MI FROM Client;" fail? If so, the next thing I would do is create a new database with an identical table (but create table manually, do not import from the existing file) and run the query against that.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Expert1701 & Arthur! As it turns out... I was an idiot. :-(
>Confirm that the MI column is actually named "MI".
The client has provided iterative copies of the database over the past few months. With Access, I was looking at the latest version. But with my program, a Config file was pointing to a different folder w/ an older database. And that older database didn't have the MI field.
>makes use of the Access NZ function
Nice tip; thanks.
Regards,
Mark
>Confirm that the MI column is actually named "MI".
The client has provided iterative copies of the database over the past few months. With Access, I was looking at the latest version. But with my program, a Config file was pointing to a different folder w/ an older database. And that older database didn't have the MI field.
>makes use of the Access NZ function
Nice tip; thanks.
Regards,
Mark
mmaslar, that is great news. Thanks for letting us know what the problem was... I think we have all been there! And thank you, Arthur_Wood, for your response to this question.
glad to be of assistance.
perhaps you should exercise some 'version control' so that you knw what fields ARE or ARE NOT included in which version, and also to assure that you are looking at the RIGHT version.
AW
perhaps you should exercise some 'version control' so that you knw what fields ARE or ARE NOT included in which version, and also to assure that you are looking at the RIGHT version.
AW
and please add the error message you get...
hth,
A.