RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

Tip of the Day
Language: Active Server Pages (ASP)
Expertise: Beginner
Feb 25, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Formatting Your SQL Statement Correctly


I am trying to set up a recordset from the results of a form. On the ASP page, I have set a variable to the entry from the form but I want to enter this value in the search query, whatever it may be.

<% style= Request.Form("IPStyle") 
price = Request.Form("IPPrice")

<% IF price = "Cheap" THEN 
   Set RSFrames = Server.CreateObject("ADODB.Recordset")
   strSql = "SELECT * FROM Frames WHERE Type = style  AND Cost < 10" 
   RSFrames.Open  strSQL, cn

This code works except I can only make the 'Type = style' search for the word "style" or I generate an error.


In your code, "style" is a variable that contains the value of whatever was present in the form control: "IPStyle". Within your SQL statement, you want this value to be placed, and not the name of the variable.

Therefore, change your SQL statement from:

strSql = "SELECT * FROM Frames WHERE Type = style  AND Cost < 10" 


strSql = "SELECT * FROM Frames WHERE Type = " & style & " AND Cost < 10" 

If the field named 'Type' is a character field (string), then you need to enclose the value of the "style" variable within quotes. So, change your SQL statement to:

strSql = "SELECT * FROM Frames WHERE Type = '" & style & "' AND Cost < 10" 

If there is a potential for the value of the "style" variable itself to have a single quote within it (for example, "Ladies' Jackets"), then you need to be extra careful. So change your code to take care of that issue first, by replacing all occurrences of the single quotes to two single quotes:

style = Replace(style, "'", "''")
   strSql = "SELECT * FROM Frames WHERE Type = '" & style & "' AND Cost < 10" 

By the way, you should avoid using names like "style", and "price" for your variables. "Style" is a reserved word in HTML, and CSS (whether using JavaScript or VBScript). You will have trouble and will spend hours trying to figure out why your code is not working. Always use non-English-dictionary words for variable names. For example, simply change it to "strStyle".

DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date