By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,221 Members | 517 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,221 IT Pros & Developers. It's quick & easy.

Inserting a Request.Form variable into a SQL statement

P: 30
I'm trying to access a Request.Form variable and plug it into an SQL statement, so that the statement will select just the particular data I need.

Here's the offending snippet of code:

Expand|Select|Wrap|Line Numbers
  1. sql = "SELECT DISTINCT name, extension FROM Employees WHERE name = " & Request.Form("name") 
I assumed that the problem was simply not having quotation marks in the right places, but I've tried many combinations of quotes, to no avail.

Here's the complete code (keep in mind that I'm still testing it, so it may not entirely make sense; first, I tried to get it to output something, now I'm at the point of trying to narrow down the output to just what I actually need).

Expand|Select|Wrap|Line Numbers
  1. <%@ LANGUAGE = "VBSCRIPT"    %>
  2. <% Option Explicit %>
  3.  
  4. <% If Request.Form("name") <> "" And Request.Form("extension") <> "" Then %> 
  5. <%
  6.     On Error Resume Next
  7.     Dim MyConnection, sql, rsRecords, results
  8.  
  9.     sql = "SELECT DISTINCT name, extension FROM Employees WHERE name = " & Request.Form("name") 
  10.  
  11.     Set MyConnection = Server.CreateObject("ADODB.Connection")
  12.     Set rsRecords = Server.CreateObject("ADODB.RecordSet")
  13.  
  14.     MyConnection.Open("employee")
  15.     rsRecords.Open sql, MyConnection 
  16. %>
  17.  
  18. <% If rsRecords.EOF Then %>
  19. <H2> No records found </H2>
  20. <%End If %>
  21.  
  22.  
  23. <%
  24.  
  25. While Not rsRecords.EOF
  26. results = rsRecords("name") & "&nbsp;&nbsp" & rsRecords("extension") & "<BR>"
  27. Response.Write(results)
  28. rsRecords.MoveNext
  29. WEND
  30.  
  31. rsRecords.Close
  32. MyConnection.Close
  33. Set MyConnection = Nothing
  34.  
  35. %>
  36.  
  37. <%
  38. Else
  39. %>
  40.  
  41. <HTML>
  42. <HEAD>
  43. <TITLE>Database Connection</TITLE>
  44. </HEAD>
  45.  
  46. <BODY onLoad = "document.searchDB.name.focus()">
  47. <H3> Please Enter the Name and Extension You're Searching For: </H3>
  48. <TABLE>
  49. <FORM NAME = "searchDB"    METHOD = "post" ACTION = "ASPDBtest.asp">
  50. <TR><TD ALIGN = "left"><B>Name</B>
  51. <TD><INPUT TEXT = "text"  NAME = "name"  SIZE = "25">
  52. <TR><TD ALIGN = "left"><B>Extension</B>
  53. <TD><INPUT TEXT = "text"  NAME = "extension"  SIZE = "25">
  54. <TR><TD COLSPAN = "2"    ALIGN = "left">
  55. <TD><INPUT TYPE = "Submit"  VALUE = "Submit">
  56. <TD><INPUT TYPE = "Reset"  VALUE = "Reset">
  57. </FORM>
  58. </TABLE>
  59.  
  60. </BODY>
  61. </HTML>
  62.  
  63. <%End If%> 
AGAIN, please understand that this is still developmental code. Nothing is worse than getting back info on the rest of the program, when all I need (for this post anyway) is how to make that sql statement execute properly! :o) Help is greatly appreciated.
Apr 23 '07 #1
Share this Question
Share on Google+
8 Replies

jhardman
Expert 2.5K+
P: 3,405
You say you have tried several combinations of quotes. The correct one should be:
Expand|Select|Wrap|Line Numbers
  1. sql = "SELECT DISTINCT name, extension FROM Employees WHERE name = '" & Request.Form("name") & "'"
  2. 'print sql in HTML comment for troubleshooting purposes
  3. response.write "<!-- sql: " & sql & " -->" & vbNewLine 
Have you tried this one?

Let me know if this works.

Jared
Apr 23 '07 #2

P: 36
If name is a char-field (which is likely) then the query must have single quotes ('):
Expand|Select|Wrap|Line Numbers
  1. sql = "SELECT DISTINCT name, extension FROM Employees WHERE name = '" & Request.Form("name") & "'" 
When you open the connection with conn.open you should supply the connectionstring. For example:

Expand|Select|Wrap|Line Numbers
  1. MyConnection.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
  2.                   "DBQ=nwind.mdb;" & _
  3.                   "DefaultDir=C:\program files\devstudio\vb;" & _
  4.                   "Uid=Admin;Pwd=;"
Apr 23 '07 #3

P: 30
You say you have tried several combinations of quotes. The correct one should be:
Expand|Select|Wrap|Line Numbers
  1. sql = "SELECT DISTINCT name, extension FROM Employees WHERE name = '" & Request.Form("name") & "'"
  2. 'print sql in HTML comment for troubleshooting purposes
  3. response.write "<!-- sql: " & sql & " -->" & vbNewLine 
Have you tried this one?

Let me know if this works.

Jared


It worked beautifully! One other quick question- what is the ASP version of a wildcard character, where the user could enter something close to the right name and get the person they're looking for?
Apr 23 '07 #4

P: 36
what is the ASP version of a wildcard character, where the user could enter something close to the right name and get the person they're looking for?
Expand|Select|Wrap|Line Numbers
  1. Select * from Employees where name like '%John%'
Apr 23 '07 #5

P: 30
Expand|Select|Wrap|Line Numbers
  1. Select * from Employees where name like '%John%'

Right, I know the % wildcard in SQL. How would I insert that into my previous code so that it would interpret properly inside the ASP delimiters?
Apr 23 '07 #6

jhardman
Expert 2.5K+
P: 3,405
As Arnold said, it looks like you want the sql wildcard in this case. So try this:

Expand|Select|Wrap|Line Numbers
  1. sql = "SELECT DISTINCT name, extension FROM Employees WHERE name LIKE '%" & Request.Form("name") & "%'" 
Let me know how it goes.

Jared
Apr 23 '07 #7

P: 30
The wildcard character did not work. Again, probably an issue with the placement of quotes. However, I did have one other question: how do I get the SQL statement to search correctly if the name has an apostrophe in the record (ex. Jane O' Hare), and then get it to display right? I know there's a way to do it with escape characters, but I'm blanking on how to implement it. Otherwise the search is executing fine.

p.s. If you have any more suggestions on the wildcard, please let me know
Apr 24 '07 #8

jhardman
Expert 2.5K+
P: 3,405
Replacing the ' with '' might work.
Expand|Select|Wrap|Line Numbers
  1. sql = replace(sql, "'", "''")
  2.  
Jared
Apr 24 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.