Inserting a Request.Form variable into a SQL statement

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

 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

 <%@ LANGUAGE = "VBSCRIPT"    %>

<% Option Explicit %>
 
<% If Request.Form("name") <> "" And Request.Form("extension") <> "" Then %> 

<%

    On Error Resume Next

    Dim MyConnection, sql, rsRecords, results
 
    sql = "SELECT DISTINCT name, extension FROM Employees WHERE name = " & Request.Form("name") 
 
    Set MyConnection = Server.CreateObject("ADODB.Connection")

    Set rsRecords = Server.CreateObject("ADODB.RecordSet")
 
    MyConnection.Open("employee")

    rsRecords.Open sql, MyConnection 

%>
 
<% If rsRecords.EOF Then %>

<H2> No records found </H2>

<%End If %>
 
<%
 
While Not rsRecords.EOF

results = rsRecords("name") & "&nbsp;&nbsp" & rsRecords("extension") & "<BR>"

Response.Write(results)

rsRecords.MoveNext

WEND
 
rsRecords.Close

MyConnection.Close

Set MyConnection = Nothing
 
%>
 
<%

Else

%>
 
<HTML>

<HEAD>

<TITLE>Database Connection</TITLE>

</HEAD>
 
<BODY onLoad = "document.searchDB.name.focus()">

<H3> Please Enter the Name and Extension You're Searching For: </H3>

<TABLE>

<FORM NAME = "searchDB"    METHOD = "post" ACTION = "ASPDBtest.asp">

<TR><TD ALIGN = "left"><B>Name</B>

<TD><INPUT TEXT = "text"  NAME = "name"  SIZE = "25">

<TR><TD ALIGN = "left"><B>Extension</B>

<TD><INPUT TEXT = "text"  NAME = "extension"  SIZE = "25">

<TR><TD COLSPAN = "2"    ALIGN = "left">

<TD><INPUT TYPE = "Submit"  VALUE = "Submit">

<TD><INPUT TYPE = "Reset"  VALUE = "Reset">

</FORM>

</TABLE>
 
</BODY>

</HTML>
 
<%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

Subscribe Post Reply

4938

jhardman

3,406

Expert 2GB

You say you have tried several combinations of quotes. The correct one should be:

Expand|Select|Wrap|Line Numbers

 sql = "SELECT DISTINCT name, extension FROM Employees WHERE name = '" & Request.Form("name") & "'"

'print sql in HTML comment for troubleshooting purposes

response.write "<!-- sql: " & sql & " -->" & vbNewLine

Have you tried this one?

Let me know if this works.

Jared

Apr 23 '07 #2

Arnold Schuur

If name is a char-field (which is likely) then the query must have single quotes ('):

Expand|Select|Wrap|Line Numbers

 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

 MyConnection.Open "Driver={Microsoft Access Driver (*.mdb)};" & _

                  "DBQ=nwind.mdb;" & _

                  "DefaultDir=C:\program files\devstudio\vb;" & _

                  "Uid=Admin;Pwd=;"

Apr 23 '07 #3

tnspc

You say you have tried several combinations of quotes. The correct one should be:

Expand|Select|Wrap|Line Numbers

sql = "SELECT DISTINCT name, extension FROM Employees WHERE name = '" & Request.Form("name") & "'"

'print sql in HTML comment for troubleshooting purposes

response.write "" & 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

Arnold Schuur

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

Select * from Employees where name like '%John%'

Apr 23 '07 #5

tnspc

Expand|Select|Wrap|Line Numbers

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

3,406

Expert 2GB

As Arnold said, it looks like you want the sql wildcard in this case. So try this:

Expand|Select|Wrap|Line Numbers

 sql = "SELECT DISTINCT name, extension FROM Employees WHERE name LIKE '%" & Request.Form("name") & "%'" 
 

Let me know how it goes.

Jared

Apr 23 '07 #7

tnspc

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

3,406

Expert 2GB

Replacing the ' with '' might work.

Expand|Select|Wrap|Line Numbers

sql = replace(sql, "'", "''")

Jared

Apr 24 '07 #9

Similar topics

Inserting data into a table

by: Geoff Wickens | last post by:

I am quite new to all this but am trying to create a database driven site. I have been able to use information from my sample database but I now want to be able to insert data into it. At present I...

ASP / Active Server Pages

Inserting Input data into a HTTP post.

by: Geoff | last post by:

I have been asked to add postal rates to a web page shopping cart checkout procedure. After contacting the post office I discovered that they used XML to receive postal rate requests and send back...

.NET Framework

Inserting data into Access form from SQL Stored Procedure

by: Josh Strickland | last post by:

I am attempting to create an Access database which uses forms to enter data. The issue I am having is returning the query results from the Stored Procedure back in to the Access Form. ...

Microsoft Access / VBA

inserting into MS access using ASP.NET/VB

by: Kevin | last post by:

Hello experts, I'm a newbee to ASP.NET. And I have been struggling solving this problem. But I can't do it. Can you please help me get over this wall? Below is the code and error message: <%@...

ASP.NET

Inserting a dynamic URL in to a MAILTO: tag

by: 35th Ave Media | last post by:

Hello, I have about 60+ pages that I need to insert a MAILTO: tag so people can email the page using their email client. The body of the message is going to be the URL of that page. Using ASP,...

ASP.NET

Error inserting into iSeries DB2 table with long name via SQL Server 2000 linked Server

by: Charles Wilt | last post by:

I have a IBM iSeries (aka AS-400) running v5r3 of OS/400 that I access via a linked server from SQL Server 2000. The following select works fine: select * from...

DB2 Database

Problems with Walkthrough: Editing and Inserting Data in Web Pages with the DetailsView Web Server Control"

by: tom c | last post by:

I am going through "Walkthrough: Editing and Inserting Data in Web Pages with the DetailsView Web Server Control" found at http://msdn2.microsoft.com/en-us/library/sdba1d59.aspx I am using...

ASP.NET

need more help on split, replace and inserting into a databse

by: MrHelpMe | last post by:

Hello again experts, I have successfully pulled data from an LDAP server and now what I want to do is drop the data into a database table. The following is my code that will insert the data but...

ASP / Active Server Pages

Problem inserting data to database

by: bseakgano | last post by:

I have developed a intranet . Using HTML , SQL and ASP . I have created a table with SQL is just fine . And design a form is just looks fine to me . But when I try to insert Data into the SQL I just...

ASP / Active Server Pages

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA