Kevin Sta. Ana Kevin Sta. Ana - 2 years ago 64
SQL Question

Passing null value from textbox to sql query

I am new with I am having problems passing a null value from a textbox to the sql query string. this is my current code:

Dim sqlstatement as string

If generic_jobTxt.Text == '' Then
generic_jobTxt = DBNull.Value
End If

sqlstatement = "Insert into Job_db (generic_job) values('"+generic_jobTxt+"')"

how can i pass a null value to the sql string so that when i run the sql i get a null value in the generic_job column. Thank you!

Answer Source

First, never concatenate strings to build your sql query, instead use parameterized queries. Otherwise you're open for sql injection and other issues. On that way you also don't need to fiddle around with apostrophes. But you should use the correct types.

Presuming you're using SQL-Server and the column type is varchar:

Dim sqlstatement = "Insert into Job_db (generic_job) values(@generic_job)"

Using con As New SqlConnection("connection-string")
    Using insertCommand = New SqlCommand(sqlstatement, con)
        Dim sqlParam = New SqlParameter("@generic_job", SqlDbType.VarChar)
        Dim jobTxt As String = generic_jobTxt.Text.Trim()
        sqlParam.Value = If(String.IsNullOrEmpty(jobTxt), Nothing, jobTxt)
        Dim inserted As Int32 = insertCommand.ExecuteNonQuery()
    End Using
End Using
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download