How can I write these to my database as Null instead of Nothing

Question

Hi Everyone,

How can I change the following code to write to the database null instead of empty strings?

Public Sub SetChangeRequest(ByVal referenceLeaseID As Integer, _
                                  ByVal referenceCustomerID As Integer, _
                                  Optional ByVal custUnitNum As Object = Nothing, _
                                  Optional ByVal driverFirstName As Object = Nothing, _
                                  Optional ByVal driverLastName As Object = Nothing, _
                                  Optional ByVal driverEmail As Object = Nothing, _
                                  Optional ByVal plateNumber As Object = Nothing, _
                                  Optional ByVal plateProvince As Object = Nothing, _
                                  Optional ByVal usageProvince As Object = Nothing, _
                                  Optional ByVal invoiceGroups As Object = Nothing)
    mcmd = New SqlCommand
    mcmd.CommandType = CommandType.Text
    mcmd.Connection = mcn
    mcmd.CommandText = "IF EXISTS (SELECT * FROM ChangeRequest WHERE ReferenceLeaseID = " & referenceLeaseID & ")" & vbNewLine & _
                        "DELETE FROM ChangeRequest WHERE ReferenceLeaseID = " & referenceLeaseID & vbNewLine & _
                        "INSERT INTO ChangeRequest (ReferenceLeaseID, ReferenceCustomerID, CustomerUnitNum, DriverFirstName, DriverLastName, DriverEmail, PlateNumber, PlateProvince, UsageProvince, InvGroupID)" & vbNewLine & _
                        "VALUES ('" & referenceLeaseID & "', '" & referenceCustomerID & "', '" & custUnitNum & "', '" & driverFirstName & "', '" & driverLastName & "', '" & driverEmail & "', '" & plateNumber & "', '" & plateProvince & "', '" & usageProvince & "', '" & invoiceGroups & "')"
    mcn.Open()
    mcmd.ExecuteScalar()
    mcn.Close()
End Sub

Cheers,

Mike

Answer 1

You can use DBNull.Value. http://msdn.microsoft.com/en-us/library/system.dbnull.value.aspx

Answer 2

There's this thing called DBNull.Value. That's what you want instead of Nothing.

To test if a DB value is NULL, use the function IsDBNull.

Answer 3

They way you're constructing your query is inefficient, hard to read, error prone, and worst of all open to SQL injection attacks. You should use SQL parameters:

mcmd.CommandText = "IF EXISTS (SELECT * FROM ChangeRequest WHERE ReferenceLeaseID = @referenceLeaseID") " _
                   "DELETE FROM ChangeRequest WHERE ReferenceLeaseID = @referenceLeaseID " & _
                   "INSERT INTO ChangeRequest (ReferenceLeaseID, ReferenceCustomerID, CustomerUnitNum, DriverFirstName, DriverLastName, DriverEmail, PlateNumber, PlateProvince, UsageProvince, InvGroupID) " & _
                   "VALUES (@referenceLeaseID, @referenceCustomerID, @custUnitNum, @driverFirstName, @driverLastName, @driverEmail, @plateNumber, @plateProvince, @usageProvince, @invoiceGroups)"

You can specify values for parameters like:

mcmd.Parameters.Add("@parameterName", If(ParameterValue Is Nothing, DBNull.Value, ParameterValue))

Answer 4

Use a parameterized query instead of putting the values in SQL. Besides fixing the nulls issue, it will also protect against SQL injection.

Public Sub SetChangeRequest(ByVal referenceLeaseID As Integer, _
                                  ByVal referenceCustomerID As Integer, _
                                  Optional ByVal custUnitNum As Object = Nothing, _
                                  Optional ByVal driverFirstName As Object = Nothing, _
                                  Optional ByVal driverLastName As Object = Nothing, _
                                  Optional ByVal driverEmail As Object = Nothing, _
                                  Optional ByVal plateNumber As Object = Nothing, _
                                  Optional ByVal plateProvince As Object = Nothing, _
                                  Optional ByVal usageProvince As Object = Nothing, _
                                  Optional ByVal invoiceGroups As Object = Nothing)
    mcmd = New SqlCommand
    mcmd.CommandType = CommandType.Text
    mcmd.Connection = mcn
    mcmd.CommandText = "IF EXISTS (SELECT * FROM ChangeRequest WHERE ReferenceLeaseID = " & referenceLeaseID & ")" & vbNewLine & _
                        "DELETE FROM ChangeRequest WHERE ReferenceLeaseID = " & referenceLeaseID & vbNewLine & _
                        "INSERT INTO ChangeRequest (ReferenceLeaseID, ReferenceCustomerID, CustomerUnitNum, DriverFirstName, DriverLastName, DriverEmail, PlateNumber, PlateProvince, UsageProvince, InvGroupID)" & vbNewLine & _
                        "VALUES (@ReferenceLeaseID, @ReferenceCustomerID, @CustomerUnitNum, @DriverFirstName, @DriverLastName, @DriverEmail, @PlateNumber, @PlateProvince, @UsageProvince, @InvGroupID)"
    mcmd.Parameters.AddWithValue("ReferenceLeaseID", referenceLeaseID)
    mcmd.Parameters.AddWithValue("ReferenceCustomerID", referenceCustomerID )
    mcmd.Parameters.AddWithValue("CustomerUnitNum", custUnitNum)
    mcmd.Parameters.AddWithValue("DriverFirstName", driverFirstName)
    mcmd.Parameters.AddWithValue("DriverLastName", driverLastName)
    mcmd.Parameters.AddWithValue("DriverEmail", driverEmail)
    mcmd.Parameters.AddWithValue("PlateNumber", plateNumber)
    mcmd.Parameters.AddWithValue("PlateProvince", plateProvince)
    mcmd.Parameters.AddWithValue("UsageProvince", usageProvince)
    mcmd.Parameters.AddWithValue("InvGroupID", invoiceGroups)
    mcn.Open()
    mcmd.ExecuteScalar()
    mcn.Close()
End Sub

Answer 5

Biggest thing I'd suggest is you use a parameterised sql statement as you're opening up yourself to sql injection.

e.g. command.CommandText = "INSERT SomeTable (SomeField) VALUES (@SomeField)";

And then add the named parameters to the command using command.Parameters.Add

For values you want to store as NULL, set the parameter value to DBNull.Value.

Answer 6

If you really, really wanted to, you make the query you're constructing work by doing something like the following:

"VALUES (" & IIf(referenceLeaseID Is Nothing, "NULL", "'" & referenceLeaseID & "'"))

...but please, please don't do that. The code is ugly, hard to maintain, and allows for all kinds of unpleasantness like SQL injection.

Instead, use a parameterized query -- the entire process is pretty well explained in this MSDN article.

After the first pass, your code will then look something like this:

Dim cmd = New SqlCommand(".... VALUES (@p0)")
cmd.Parameters.Add(New SqlParameter("@p0", referenceLeaseID))

...which is still not pretty: the long multi-line SQL queries will continue to clutter your code.

To fix that issue as well, move your query into a stored procedure. This has the huge advantage of being able to compose your SQL in an editor that is intended for this purpose, instead of kludging it up in code. If you use a recent version of SQL Server Studio, or of course the indispensable SQL Prompt utility, you'll even have some nice Intellisense support.

The stored procedure will be pretty much identical to your current query, with some additional parameter declarations:

CREATE PROCEDURE UpdateDriverRecord
    @referenceLeaseID NVARCHAR(42)
BEGIN
    ...VALUES(referenceLeaseID = @referenceLeaseID)
END

Your final code will the look something like:

Dim cmd = New SqlCommand("UpdateDriverRecord")
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@referenceLeaseID", SqlDbType.NVarChar, 42)

This may seem like a lot of changes to work around a simple issue, but your code will be much more secure, readable and maintainable.