Return Empty String as NULL

Question

I have a listbox select and I want when the user selects null for the empty string it produces to pull the nulls from the SQL table.

Here's what I have now. Blank strings return nothing because there are no empty fields in the table.

SELECT * FROM dbo.Table WHERE ID = " & TextBox2.Text & " and And Field1 IN (" & Msg1 & ")

How do I code that?

Answer 1

Use an If statement. When your textbox is empty, have the SQL string contain "ID is null" instead of appending the textbox's value.

If (TextBox1.Text = "") Then
   ' use Is Null in your sql statement 
Else
   ' use the textbox text value in your sql statement
End If

(Assuming you're talking about the textbox and not whatever Msg1 is.)

Answer 2

 dim sql
 If (TextBox2.Text = null) Then
      sql = "SELECT * FROM dbo.Table WHERE ID is null and And Field1 IN (" & Msg1 & ")"
 Else
     sql = "SELECT * FROM dbo.Table WHERE ID = " & TextBox2.Text & " and And Field1 IN (" & Msg1 & ")"
 End If

See @John Saunders comment, you are risking sql injections. When passing paremeter to an sql query, be sure to use parameters, and not concatenating strings.

Answer 3

SELECT * FROM dbo.Table WHERE ID = " & TextBox2.Text & 
" And Field1 " & IIF(Trim(Msg1) = "", "IS NULL", "IN (" & Msg1 & ")")

Yes, it is crude.
One should not write query in this style.

EDIT: Corrected. Please check.