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".
