Database - How can I put the value of a variable/control into my SQL statement?

There are times when you want to create an SQL statement based on user input, or some code in your program. To do this you need to add the value to the rest of the SQL string that you create.

Before we get onto the details: it is strongly recommended that you validate the value of your variable/control before creating your SQL, such as ensuring any numeric fields do not contain text. Failure to do so is likely to lead to errors.


Another note is that this method is inherently unsafe - it is a much better idea to use Parameters instead. For an explanation of why (and links to code examples), see the FAQ article Why should I use Parameters instead of putting values into my SQL string?


Assuming that your SQL statement (without the value of the variable/control) is like this:

VB Code:

Dim sSQL as String
  sSQL = "SELECT Field1, Field2 FROM table1 WHERE Field3 = 1 ORDER BY Field1"

you can set the value (in this case the Field3 = 1) like this:

VB Code:

Dim sSQL as String
  'using a variable:
  sSQL = "SELECT Field1, Field2 FROM table1 WHERE Field3 = " & myVariable & " ORDER BY Field1"
  'OR, using the value of a control:
  sSQL = "SELECT Field1, Field2 FROM table1 WHERE Field3 = " & Text1.Text & " ORDER BY Field1"

If Field3 is a text or char field, you need to add single quotes around the text like this:

VB Code:

Dim sSQL as String
  'without a variable/control:
  sSQL = "SELECT Field1, Field2 FROM table1 WHERE Field3 = 'my text' ORDER BY Field1"
 
  'using a variable:
  sSQL = "SELECT Field1, Field2 FROM table1 WHERE Field3 = '" & myVariable & "' ORDER BY Field1"
  'OR, using the value of a control:
  sSQL = "SELECT Field1, Field2 FROM table1 WHERE Field3 = '" & Text1.Text & "' ORDER BY Field1"

Note that if the variable contains any ' characters the SQL statement will have errors, to correct this see this thread. (it is recommended that you always follow the method given there when you are building an SQL statement based on user input, even if you are not expecting ' characters).


.