To Quote or Not to Quote?
This is by far the largest issue when dealing with SQL. Depending on the datatype you are dealing with (such as a number or character string), you must interface the database differently. Don't worry for now if you don't understand the ColdFusion tags we're mentioning here; they will be explained later in this chapter.
Suppose you have two fields in a table called userID and password. userID is numeric and password is a character string. Consider the following code:
<CFSET userID = 12345> <CFSET password = "myPass"> <CFQUERY.... UPDATE users SET userID = #userID#, password = '#password#' </CFQUERY>
Pay close attention to the quotes when setting new variables. userID is defined as numeric so the value does not need to be quoted. However, because the password field is a character string (like text or memo in Microsoft Access), it must be enclosed in single quotes. Date formats must also be enclosed in quotes to be inserted or updated in a table.