Sometimes send a null value to SQL Server

[BukHix]

I am sending values to my database from textboxes that are usualy all required texboxes. In one case I have a textbox that is optional but my Insert Into String looks something like the one listed below. The database requires that if there is no value in the textbox the field must be a null. How can I send a Null Value to my sql server in the cases that I need to do so?

With cmd
    .CommandText = "Insert Into TimeGR (" & _
            "EntryPerson, " & _
                "Job," & _
                "JobName," & _
                "WeekEnding," & _
                "Employee," & _
                "FirstName," & _
                "LastName," & _
                "RateTime," & _
                ") Values('" & _
                strEntryPerson & "','" & _
                strJobNumber & "','" & _
                strJobName & "','" & _
                strWeekEnd & "','" & _
                strEmp & "','" & _
                strFname & "','" & _
                strLname & "','" & _
                strTime & "','" & _
                ")"
    .ExecuteNonQuery()
        trn.Commit()
End With

I know that I could use an IF then along with two seperate Insert Strings but I am thinking there is probably a better way/

[Memnoch1207]

Well you could
A) depending on your database (if you are using SQL Server) create a stored procedure that checks if the value is empty ("") or not

CREATE PROCEDURE stp_MyProcedure
   @Value1 varchar(50),
   @Value2 varchar(50
AS
INSERT INTO MyTable VALUES(
   @Value1,
   Case @Value2
      When '' Then Null
      Else @Value2
   End)

B) Create a procedure that accepts all of your parameters and allows the parameter that might be null as optional

Public Sub InsertValues(ByVal Value1 As String, ByVal Optional Value2 = Null)
   'Run your insert, it will either insert the value passed or it will insert Null
End Sub

Or
C) you could just write a simple If...Then..Else statment that checks the value prior to sending it to the database

If(Len(Value2.Text) = 0) Then
   Value2 = Null
Else
   Value2 = Value2.Text
End If

[BukHix]

I originaly tried option C but I get some squigly lines along with this error:

'Null' is not declared. 'Null' constant is no longer supported; use 'System.DBNull' instead.

System.DBNull provides a similar error. Option A is above my current abilites and I think B will probably give me the same error as above.

[nemaroller]

Your best bet is to check for zero-length strings at the SQL server.

[hellswraith]

You can only set DbNull.Value to a object that can accept it. A string can't accept a DbNull.Value.

A parameter object can though...

Dim param as New SqlParameter("@SomeValue", DbNull.Value)



So, to fill this out, you can do something like this:
Dim param as SqlParameter
If Value2.Text.Length > 0 Then
param = New SqlParameter("@SomeValue", Value2.Text)
Else
param = New SqlParameter("@SomeValue", DbNull.Value)
End If


You should be using stored procedures with parameters. Better, safer, and more descriptive. You can use the Data Access block from MS also to make things easier on you.