Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 5th, 2003, 01:31 PM
Registered User
Join Date: Jun 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Incorrect SQL Syntax?!

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.
<title>ASP Script</title>
dim rs
dim varname
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")%>

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
Old June 5th, 2003, 03:07 PM
Registered User
Join Date: Jun 2003
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

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 &"';"


Similar Threads
Thread Thread Starter Forum Replies Last Post
Incorrect syntax near '.' meraj ADO.NET 6 February 27th, 2012 01:01 AM
Incorrect syntax near '!' sinapra Classic ASP Databases 8 August 25th, 2004 02:15 AM
Incorrect syntax near '`' Hez Classic ASP Databases 3 August 5th, 2004 08:13 AM
Incorrect syntax...... Adam H-W Classic ASP Databases 8 June 21st, 2004 11:14 AM
Incorrect SQL Syntax??! ckudrna Access ASP 1 June 5th, 2003 01:37 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.