• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1098
  • Last Modified:

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

0
Phil Catterall
Asked:
Phil Catterall
  • 2
1 Solution
 
Gyanendra SinghArchitectCommented:
I guess your dr is null .. first check that ...
0
 
Anurag ThakurTechnical ManagerCommented:
i tried your code and did not find any exceptions
in one case i had a empty cell and in other case it had value
in case of empty cell dbnull returned true and it did not throw any error and in case of value i got the value back
0
 
Phil CatterallAuthor Commented:
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.
0
 
Phil CatterallAuthor Commented:
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 :-) )
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now