Link to home
Start Free TrialLog in
Avatar of Phil Catterall
Phil Catterall

asked on

Using IsDBNull method of OleDBDataReader throws an error

I am using an OleDbDataReader to retrieve information from an Excel Spreadsheet. When I attempt to test a column for a null value using the IsDBNull method, I get an error "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
The connection for viewing your linked Microsoft Excel worksheet was lost.".
I cannot see what I am doing wrong, having compared my code to lost of examples. Can anyone advise or help me troubleshoot further
OleDbDataReader dr;
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\Uploads\Shelters.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
OleDbCommand cmd = new OleDbCommand("Select route from [AA$]", conn);
dr = cmd.ExecuteReader();
string route;
while (dr.Read())
{
       if (dr.IsDBNull(0)) //This line produces the error
       {
          route = "";
       }
       else
       {
          route = dr.GetString(0);
       }
}
dr.Close();
conn.Close();

Open in new window

Avatar of Gyanendra Singh
Gyanendra Singh
Flag of India image

I guess your dr is null .. first check that ...
ASKER CERTIFIED SOLUTION
Avatar of Anurag Thakur
Anurag Thakur
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Phil Catterall
Phil Catterall

ASKER

Well how weird. Having read it worked for you, I did a bit more research on the net, and found another extended property called IMEX, which I added as IMEX=1, re-tried and it worked. Then I removed it and it still works. I haven't changed anything !! Code still open, haven't reloaded, no reboot or anything.
Anyway, thanks for your input.
Well how weird. Having read it worked for an expert, I did a bit more research on the net, and found another extended property called IMEX, which I added as IMEX=1, re-tried and it worked. Then I removed the IMEX=1 and it still works! I haven't changed anything !! IDE/Code still open, haven't reloaded, no reboot or anything.
Anyway, thanks all for the input.- I will put this one down to an undocumented Microsoft feature (strange, haven't seen any of those before :-) )