First of all:
Double Quote Marks (“) have to be around an entire SQL Statement
If there is Limiter (non-numeric), like ‘where Fieldname=Peter’, where the limiter is not numeric, then you must put single quotes around it:
“Select * from Tablename where (Firstname)=’Peter’
This is an example of an EXACT SQL Statement. We are asking to return all instances of the name ‘Peter’.
Here’s where it gets tricky with variables. In our form, we are requesting a name, but whoever fills out the form might want to search for someone with a different name. So, since it can be different things to different people, we assign that section of the SQL statement a variable.
Let’s say you have designated a variable called FirstNameVar. Above where Peter starts is where the variable would go, but Peter is an exact search and a variable is not, so your SQL ending double quote goes directly after the single quote. This is where the EXACT SQL statement ends. At this point, you have:
“Select * from Tablename where (Firstname)='”
Notice, that directly after the single quote is a double quote, just as if it were the end, but as you know, it isn’t. Think of how a response.write statement is in ASP.Net when we have html that needs to be in the response.write statement:
Response.write “Here is the the name You wanted” & myVar
The exact HTML is separated from the ASP. Well, in SQL, the same thing is happening. You are separating the EXACT SQL statement from the Variables.
OK, what about the variable? Well, you add an ampersand and the variable name:
“Select * from Tablename where (Firstname)='” & FirstNameVar
But, we’re not finished yet, because, if you remember, the EXACT name must be surrounded by single quotes and the entire SQL statement must start & end with double quotes. Therefore, we must add the single quote, but also remember that it is still part of the EXACT SQL Syntax. And – in SQL, when you separate the EXACT SQL from the variables, each section of the EXACT SQL is enclosed within its own double quotes. The SQL statement, though, within the ASP.Net brackets is a separated text statement. The EXACT SQL is surrounded by quotes, where the variables aren’t. Remember, we are inside the ASP.Net brackets with this SQL statement and everything within double quotes is not considered ASP.Net code. So, we end up with:
“Select * from Tablename where (Firstname)='” & FirstNameVar & “‘”
If you wanted to change out the “=” for “like”, then you would have an exact SQL like:
“Select * from Tablename where (Firstname)=’Peter%’
where this would find all instances of Peter as well as Peterman, Peterovsky, etc.
“Select * from Tablename where (Firstname)=’%Peter%’
— which would return to you any name which had the letters Peter anywhere in it.
With Variables, that would give you something like this:
“Select * from Tablename where (Firstname)=’%” & FirstNameVar & “%'”
Notice And, of course, this is only the first part because we are dealing with Non-numeric/text type values only. There are no single quotes around numeric values in an SQL statement.
So, if you put the single quotes around data and the data type of the field is numeric, you will get an error – a datatype mismatch error. Naturally, you need to be knowledgeable about the data structure of your table so that, for the fields that have a numeric datatype, you can go back and remove the single quotes around that field in the SQL statement.
For instance, if your statement looks like this:
MySQL="Select * from data where age >'" & strAge & "'"
You will get an error on the Age field if, in your data structure, you have defined the Age field with a numeric datatype. You would need to change your statment to this:
MySQL="Select * from data where age >" & strAge
Notice that the single quote before and after strAge have been removed. This will not give you an error. Then, of course, this principle is even more exemplified when using an INSERT statement. It boils down to one fact :
- When you use single quotes, you are telling the database “the data I’m requesting (or INSERTING) is text”.
When you do not surround the data in your SQL statements with single quotes, you are telling the database “the data I’m requesting (or INSERTING) is numeric”.
So, as you can see it really pays to know your data!
Now – one Caveat – – Let’s say, you are inserting/updating a field where there is an apostrophe or single quote in the text itself. Since data is surrounded with single quotes, this would then cause a problem. It would actually end the text for that field at the point of the single quote/apostrophe. Then, the rest of the entire SQL statement is thrown off. To deal with this we can double the single quotes in the variable before it’s inserted into the database. By doing this, the database knows that two single quotes/apostrophes in a row mean that we actually want that character in the data. I normally recommend doing the replacement before the SQL statement so that the SQL statement is as clean and readable as possible. Here’s the basic VB.Net syntax for replacement:
MyVar = MyVar.Replace(“‘”, “””)
Don’t forget, though, to reverse the process when displaying the data from the database.In this tutorial, we’ve shown how to use single quotes and double quotes, with variables in an SQL statement and how to deal with single quotes/apostrophes with insert/update SQL statements. At this point, the rest is up to you!