How do I handle Conversion from type 'DBNull' to type 'String' is not valid

Question

I need some expect advice on how to handle the following:- I have a data field misc_text_2 that is of type varchar(25) and allows NULL. Now if I use the following syntax

<asp:Label ID="lblPrinter" runat="server"  Text='<%# iif(eval("misc_text_2") is dbnull.value, "", iif(eval("misc_text_2") like "NA", "None", iif(eval("misc_text_2") like "KP1", "Kitchen Printer 1", iif(eval("misc_text_2") like "KP2", "Kitchen Printer 2", iif(eval("misc_text_2") like "KP3", "Kitchen Printer 3", iif(eval("misc_text_2") like "BP1", "Bar Printer 1", iif(eval("misc_text_2") like "BP2", "Bar Printer 2", iif(eval("misc_text_2") like "BP3", "Bar Printer 3", Eval("misc_text_2")))))))))%>'></asp:Label>

I keep on getting an error Exception Details: System.InvalidCastException: Conversion from type 'DBNull' to type 'String' is not valid.

I know I'm missing something, but what...

Thanks in advance

Hereof

What language/environment is that? I'd guess ASP. Could you plese mention that and tag the question accordingly? – Flavorful

Answer 1

You could in your sql query use isNull(misc_text_2, '') to return en empty string instead of DBNull.

Dambro

Never return empty strings in the place of nulls, this reduces the portability of your dataset and is misrepresentative of the data model. If it's a null it's a null. Let the UI layer worry about how to display it. – Prussiate

That is your opinion. Returning an empty string solves his problem, right? – Dambro

"Empty is different than null". And this is not an opinion, is a fact. – Withershins

Answer 2

You must explicitly check for DBNull.Value and do the conversion yourself.

In other words, build a method that will do the conversion for you, taking DBNull.Value into account.

Erotica

Answer 3

Not answering your question, but: You should really create a code behind method that does the conversion. That will make the code easier to understand and debug, and will make it possible to reuse the code.

Readymade

Answer 4

Each time you use the Eval, you have to shim-in lazy evaluation, somehow. To do so, replace each instance of:

iif(eval("misc_text_2") like ...

with

iif(IsDbNull(eval("misc_text_2")) OrElse eval("misc_text_2") like ...

The OrElse will prevent any attempted conversion of a DbNull to boolean. However, from a more fundamental perspective, bang is most-correct. This should all be done code-behind, probably in the ItemDataBound (or RowDataBound) event handler.

Upon further reflection...

The O.P. might also make use of the Convert.ToString() method in his code, which will convert DBNulls to String.Empty.

Cusack

Convert.ToString() saved me! – Reamonn

Answer 5

Since we have a legacy database that was set up for MS-Dynamics (Solomon), our method of handling nulls is to convert them to null strings either in the ASP or VB.NET code. i.e.

Trim$(misc_text_2 & " ")

Gets rid of the problem for any version of VB.

Penetrate

You don't need the Trim if you use "" instead of " ". – Thoron

Still use trim because Dynamics uses only fixed size fields which are blank filled. – Penetrate

Answer 6

If you are using data set designer, the easiest way to get rid of this error is to change the properties of respected column.

NullValue exception to "Empty" instead of "Throw exception".

hope this will help you all.

Seamount

Answer 7

For convert DBNull or IsDBNull you can try this ways:

1. Convert DBNull to an empty string

   (DBNullObj).ToString

2. Create a DBNull Converter Function

   Public Function ConvertDBNullToString(DBNullObj As Object) as string

   if IsDBNull(DBNullObj) then

   return ""

   end if

   return DBNullObj

   End Function

Chur

Answer 8

As spiritUMTP suggested, if you are using the Dataset Designer, change the DataColumn.NullValue from "Throw exception" to either "empty" or "Nothing". I chose the latter, and it fixed the problem. I now just check for Nothing (If IsNothing(columnFax) then ...)

Glorygloryofthesnow