Incorrect SQL Syntax?!

ckudrna · June 5th, 2003, 01:31 PM

I am trying to make data from an access database apear when a user enters in a username. This username (on an HTML form) is turned into a variable, (varname). I then run an SQL Select statement that compares the username entered (stored into the varname variable) and brings up other fields of the same record. Here is the code.
<html>
<head>
<title>ASP Script</title>
</head>
<%
dim rs
dim varname
varname=Request.form("name")
set rs=server.createobject("ADODB.recordset")
sqltext="SELECT * FROM people"
sqltext=sqltext& "Where lastname=" &varname&";"
rs.open sqltext, "DSN=test"
%>
Your First Name is <%rs("firstname")%>
<body>
</body>
</html>

I keep getting this error,

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
/datatest/testname.asp, line 12

What is the correct way of referncing a string (text) variable in a SQL Select statement?!!

I have tried putting ' around the varname like this........

Where lastname='" &varname&"';"

Please email or post, thank you very much

sattedm · June 5th, 2003, 03:07 PM

Well, SQL is not my strong point but if the spacing in your code is the same as your post, you need to add a space between 'people' and 'where' in your SQL statement:

sqltext="SELECT * FROM people"
sqltext=sqltext& "Where lastname=" &varname&";"

The way it's written here you are creating a statement that reads:
"Select * FROM peopleWHERE lastname = "

Also, I believe that since the value of your varname variable is a string you need to include single quotes around that value in the SQL statement. Like this:
"Where lastname= '" & varname &"';"

Denise