error '80040e14' Syntex error in From clause

alisonchase · January 2nd, 2005, 10:04 AM

Microsoft JET Database Engine error '80040e14'
Syntax error in FROM clause.
/Check1.asp, line 53

This is line 53 but I can't see what might be wrong.
<P><a href="login.htm">Try again.</a>

Previous version had this as

<P><a href="studentservice/login.htm">Try again.</a>

with studentservice being the DSN but neither seemed to work.
Any help appreciated.
Alison

Imar · January 2nd, 2005, 10:21 AM

It looks like you have an incorrect SQL statement.

I looked at your other post, and saw something like:

SELECT username, pwd

There is no FROM clause wiith a table name in there. A simple SELECT statement should look like this:

SELECT Column1, Column2, FROM TableName [WHERE SomeColumn = SomeValue]

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Sabotage by Beastie Boys (Track 6 from the album: Ill Communication) What's This?

alisonchase · January 2nd, 2005, 10:31 AM

Imar / I'm making it really hard for you to help me by giving you incomplete code... here it is. I'm so new at this that everything is difficult but I've made it more so because my course books use older versions of everthing as the hands on examples. PWS instead of IIS; notepad/freeware instead of FP2003, etc. I've typed the examples I believe accurately and have been working through the changes required by the newer versions. The database is called student.mdb; the DNS is studentservice.

I appreciate your help and will promise to pay my debt off by helping other newbies. /Alison

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Class Registration: Check Username & Password</title>
<meta name="Microsoft Theme" content="sandston 1011">
<meta name="Microsoft Border" content="tb, default">
</head>
<body><span style="font-size: p">
<%
dim myconnection
dim rsTitleList
dim connectstring
dim sqlString
dim username
dim password

connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("student.mdb")
set myconnection = Server.CreateObject("ADODB.Connection")
set rsTitleList = Server.CreateObject("ADODB.Recordset")

myconnection.open connectString
username = Request.Form("username")
password = Request.Form("password")

sqlString = "Select * from user where username = '" & username & "'"
set rsTitleList = myconnection.Execute(sqlString)

if (rsTitleList.bof) and (rsTitleList.eof) then
response.write("Sorry, invalid username. Please try again.")
%>

</span>

Return to <A HREF="Login.htm">Login Page</A>
<% elseif rsTitleList("password")=password then %>
<h1>University Student Information</h1>[list]

<LI><H3><a href="request.htm"> Class Registration Information</a></H3></li>
<LI><h3> Student Grade Book</h3></li>
<LI><h3> Bills and Payments</h3></li>
</UL>
Return to <a href="Login.htm">Login Page</a>

<% else %>
We're sorry. We cannot process your request.
<p>Login Failed. Wrong username or password.
<P><a href="login.htm">Try again.</a>
<% End if %>

</body></html>

Imar · January 2nd, 2005, 10:42 AM

Do you have a second page that posts to this page? If so, does that page have a text box called username?

Take a look at this:

Code:

username = Request.Form("username")
...
sqlString = "Select * from user where username = '" & username & "'"

You build up a Sql statement by getting the user name from the Request.Form collection. Does that ever get a correct value?

Then again, this shouldn't cause the problem, because when the text box is empty or does not exist, you end up with something like :

Select * from user where username = ''

which should work.

The problem could be caused by the table name: user. That is actually a reserved keyword (http://support.microsoft.com/kb/q109312/)

Either rename the table, or enclose it in brackets:

Select * from [user] where username = '" & username & "'"

(Renaming is a better solution, IMHO)

To diagnose the problem, write out the Sql statement before you execute it, and then paste the statement in the Access query designer so you can test it out. To write out the statement, modify your code so it looks like this (the bold parts are new):

Code:

sqlString = "Select * from user where username = '" & username & "'"
Response.Write("SQL is " & sqlString & "<br />")
Response.End()
set rsTitleList = myconnection.Execute(sqlString)

If all this doesn't help, can you post the code for the entire page, including the stuff with the <form> and <input> tags?

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Into My Arms by Nick Cave & the Bad Seeds (Track 1 from the album: The Boatman's Call) What's This?

alisonchase · January 2nd, 2005, 10:47 AM

The login page code is as follows. Thanks/Alison

<html>
<form Name = "Request" Action = "Check1.asp" method = "POST">
<p>Please enter your username and password to access the database:</p>
<table border="0" width="290">
<tr><td align = "left" width="99"> Username:</td>
    <td> <input type="text" name="username" size="25"></td>
    </tr>
    <td align = "left" width="99"> Password:</td>
    <td> <input type="password" name="password" size="25"></td>
    </tr>
    </table>
    <p> <input type = "submit" value = "Submit">
    <input type = "reset" value = "Reset Form"> </p>

</form>
</body>
</html>

alisonchase · January 2nd, 2005, 10:56 AM

You were right!! I had two errors - the first was that I named the table users - not user and then when I chose user as the table name, it was reserved.

The code now works and I greatly appreciate your help.
Alison