VB.NET Dealing with SQL Nulls and SqlString.Null

Question

I would like to test SQL output parameters for NULL values. What I have realised is that SQLString.NULL is not equal to DBNull.Value. My question is, is there a more general way of checking a parameter for null values?

Ideally I do not want to have to check each parameter based on their SQL type, e.g. integer parameters SqlTypes.SqlInt32.Null.

Here is a snippet of my code:

.ExecuteStoredProcedure("spGetClientDetails")

If CInt(.Parameters("@return_val").Value) = 0 Then

If myParams(1).SqlValue.Equals(SqlTypes.SqlString.Null) Or myParams(1).SqlValue.Equals(System.DBNull.Value) Then
    Claimants = ""
Else...

Andomar · Accepted Answer · 2013-05-20 10:18:10Z

2

If you use Value instead of SqlValue, you'll get regular .NET types instead of an SqlType. When the database returns NULL, Value will be set to DBNull.Value.

A concise way to check for DBNull.Value is TryCast:

Dim Claimants As String = TryCast(myParams(1).Value, String)

This will set Claimants to Nothing if myParams(1) is not a string. To set Claimants to an empty string instead, you can add If:

Dim Claimants As String = If(TryCast(myParams(1).Value, String), "")

answered May 20, 2013 at 10:18

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VB.NET Dealing with SQL Nulls and SqlString.Null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related