Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

Formatting Your SQL Statement Correctly

Question:

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.

Answer:

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" 

to:

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.

 

 

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