Solved

OleDbDataAdapter.fill fails on MS Access Null column

Posted on 2006-06-12
11
829 Views
Last Modified: 2008-01-09
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.OLEDB.4.0;Data Source=" + mdbFile + ";";
OleDbConnection cnnAccesss = new OleDbConnection(MDBconnectionString);

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);
0
Comment
Question by:mmaslar
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 11

Expert Comment

by:Agarici
ID: 16894009
try setting dsmdb.enableconstraints = false;

and please add the error message you get...

hth,
A.
0
 

Author Comment

by:mmaslar
ID: 16899063
> try setting dsmdb.enableconstraints = false;
I tried; no difference.

>please add the error message you get...
'system.data.oledb.oledbexception' 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
0
 
LVL 11

Accepted Solution

by:
Expert1701 earned 300 total points
ID: 16899935
I created an access database with a table named "Client" and nullable columns, "FirstName", "LastName", and "MI".  Your code worked without any problems, so here are a few checks/tests:

  Confirm that the MI column is actually named "MI".

  Try: string SQLselect = "SELECT Client.FirstName as first_name, Client.LastName as last_name, Client.[MI] as mi_initial FROM Client";

  Try: string SQLselect = "SELECT Client.* FROM Client";

Let us know how you make out.
0
 

Author Comment

by:mmaslar
ID: 16908131

>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

0
 
LVL 11

Expert Comment

by:Expert1701
ID: 16908195
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" ?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:mmaslar
ID: 16908575
>  Client.MiddleInitial
I renamed the field, and tried that... same error @ the same location.

Thanks,
Mark
0
 
LVL 11

Expert Comment

by:Expert1701
ID: 16921267
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.
0
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 100 total points
ID: 16921412
try this:  It makes use of the Access NZ function, to replace the Null values in the MI field with "" (a zero-length string)

string SQLselect = "SELECT Client.FirstName as first_name, Client.LastName as last_name, NZ(Client.[MI], '') as mi_initial FROM Client";

AW
0
 

Author Comment

by:mmaslar
ID: 16930892
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
0
 
LVL 11

Expert Comment

by:Expert1701
ID: 16930925
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.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16931534
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now