VB.Net IIf function in adding SqlParameter

Question

I am rewriting this long INSERT statement and parameters used to look like this:

    cmd.Parameters.AddWithValue("@Website", General.fnSQLNullValues(tWebsite.Text))

Where General.fnSQLNullValues is this:

        Public Shared Function fnSQLNullValues(ByVal sValue As Object, Optional ByVal Len As Integer = 999999) As Object
            If sValue Is Nothing Then Return DBNull.Value
            fnSQLNullValues = IIf(sValue.ToString.Length = 0, DBNull.Value, Left(sValue.ToString(), Len))
        End Function

I don't like this at all , and it seems to be alot of code all to do just this,

       cmd.Parameters.AddWithValue("@Website" , If(tWebsite.Text , DBNull.Value))

from my understanding , that one line of code there DBNull.Value will replace tWebsite.Text as the value if tWebsite.Text is null or not accepted and it seems to me to do the same thing as the other function in General. Is this correct and is one way any better then the other?

Also , I get Warning : "Cannot Infer Common Type; Object Assumed" from the second way , but it seems to me that the first way was using a generic object anyways , so I do not know if I should just ignore this warning

Please update the title/question/tag as *this really has nothing to do with ADO/SQL* and everything to do with `Iff` vs `If` (coalescing). (However, it should be quite possible to use `null` instead of `DBNull.Value`, which can be "silently cast" to the type in cases involving reference or Nullable types.) — , Jul 06 '12 at 22:28

score 5 · Accepted Answer · answered Jul 06 '12 at 22:34

Strings are a little tricky when working with database parameters in code. If you have an empty string, it will pass an empty string value for insertion. However, if you set you set your string to a value of Nothing, it will insert a NULL value into the database. Otherwise it will set an empty string.

Ideally, you'd have some kind of business layer on top of your data layer that will check your tWebsite.Text value and either pass in Nothing or the Text value to your function that sets up the parameters. This is slightly different that your first code example above. That way your data layer only has to execute the following command:

cmd.Parameters.AddWithValue("@Website" , valueWebsite))

...and no hassle is necessary.

For other data types (such as integers, decimals, etc.), check out the Nullable Types and work with them. They make things really easy to work with. By default they initialize to Nothing, so if you don't assign it a value (from your input boxes) the value stays Nothing and will do a proper NULL insert into the database.

say @Website is expecting a float , I could do Dim website As Nullable(of float) - then put website in there? — Scott Selby, Jul 06 '12 at 22:50
You could, but I think it'd blow up when you try to put say google.com in a Float? haha — Ryan Ternier, Jul 06 '12 at 22:51
@ScottSelby Correct. Generally my logic goes: Dim website As Nullable(Of Float)? If Not String.IsNullOrEmpty(tWebsite.Text) website = Float.Parse(tWebSite.Text) EndIF — Dillie-O, Jul 06 '12 at 22:54

VB.Net IIf function in adding SqlParameter

1 Answers1