Cast from type 'DBNull' to type 'String' is not valid. when creating dataset

[troutbum]

I am creating a dataset by using a stored procedure. When I bind the
dataset to the datagrid I get the following Error "Cast from type
'DBNull' to type 'String' is not valid." I used a sqlreader to verify
that the field was returning "", as per my stored procedure
IsNull(field, '') as Field returned. All the other fields returned
are integers and have a values associated with them as well. I tried
to run the debugger to see if the dataset is empty, but it doesn't
appear to be. I also checked the datagrid columns and stored
procedure fields to also make sure they match. Thanks for any
direction.
Troutbum

 

[William Ryan eMVP]

Troutbum:

I'm kind of confused about the sequence of events. When you say "creating a
dataset by using a stored procedure" I'm assuming you are filling it and
it's already been created. However, where does the datareader come in?
What line of code is throwing the exceptiion something like
myGrid.DataSource = theDataSet?

Can you post the code snippet, it'd probably be easier to track down then.

 

[Kevin Yu [MSFT]]

Hi Troutbum,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you received an error message which
says "Cast from type 'DBNull' to type 'String' is not valid." when binding
the DataSet to a DataGrid. If there is any misunderstanding, please feel
free to let me know.

Based on my experience, this issue might be caused by many reasons. Please
try to set breakpoints in your code and step into the program to see if the
exception was thrown exactly when binding to the data source. Could you
please also paste some of your code, the database table definition and the
stored procedure here? It might be much easier for us to find the problem
with code, and we can deliver our assistance more quickly.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guinness Mann]

I used a sqlreader to verify
that the field was returning "", as per my stored procedure
IsNull(field, '') as Field returned.

Here's how I deal with that pesky "DBNull is not a string" problem:

public static string executeStrScalar(string sSQL)
{
string tmp = "";
object obj = new object(); <--- HERE

try
{
HERE ---> obj = SqlHelper.ExecuteScalar
(strCon, CommandType.Text, sSQL);
HERE ---> if (obj != null)
tmp = Convert.ToString(obj);
}
catch(Exception e)
{
string s = String.Format
( "AccessDistractors:getDistractorTextForId: "
+ "Unexpected database error {0}.\n"
+ "SQL = {1}."
, e.Message
, sSQL
);
throw new ApplicationException(s);
}
return tmp;
}

By setting the return from the DAAB equal to a plain old object, I get
the chance to check it for null and *then* convert it to a string.

-- Rick

 

[Kevin Yu [MSFT]]

Hi Troutbum,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[gpig]

sql datareader: do you read the data manually using the
"while sqldatareader.read" ? Then you should use "If NOT
sqldatareader.isDBNull(columnindex) THEN your variable =
sqldatareader.getstring(columnindex)