Use IF() Null Coalesce in SQL Parameters

Question

I am trying to use the If() Operator to coalesce values in a SQL Parameter but I am having trouble figured it out.

Dim First as String = Nothing
First = FirstName.Text
Using conn As New SqlConnection()
'Code omitted
.AddWithValue("@FirstName", If(First, DBNull.Value))

Basically, if First = Nothing, the Parameter should be formatted like:

.AddWithValue("@FirstName", DBNull.Value)

If FirstName.Text is not Nothing or Null then it would basically be like:

.AddWithValue("@FirstName", First)

Anyone have any pointers or have any suggestions?

I understand that First is carrying a value of "", I am just not sure how to program around that. That's why I tried = Nothing.

This is currently being written like:

If employeeName <> "last,first" Then
    cmd.Parameters.AddWithValue("@EMPL_NM", employeeName)
Else
    cmd.Parameters.AddWithValue("@EMPL_NM", DBNull.Value)
End If

Which I'm trying to reduce to

.AddWithValue("@FirstName", If(First, DBNull.Value))

Tim Schmelter · Accepted Answer · 2014-03-21 20:22:50Z

1

DBNull.Value and String are different types. The null-coalescing operator doesn't like that.

You could cast it to Object to make it compile, although i would prefer your If-Else:

cmd.Parameters.AddWithValue("@FirstName", If(First, DirectCast(DBNull.Value, Object)))

answered Mar 21, 2014 at 20:22

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

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

2 Comments

Mark C. Over a year ago

Damnet. I thought it would have something to do with that. Thanks! Do you think the If/Else is more efficient?

Tim Schmelter Over a year ago

@Newbie: it's not more efficient but it's clear and also enables to write additional code, for example if you want to handle the case that First is Nothing (message-box, log etc).

Collectives™ on Stack Overflow

Use IF() Null Coalesce in SQL Parameters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related