access database sql problem

[tuneman202002]

I was trying to create and edit page for my database, selecting stuff using this sql statement drawing the correct information from the querystring with the appended primary key id:

sqlNames="SELECT * FROM alumniList WHERE alid= & request.querystring("alid")

But I get:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/users/anderson/pub/asp/duthea/Alumedit.asp, line 65

sqlNames="SELECT * FROM alumniList WHERE alid= & request.querystring("alid")
--^

for everything I try, and the wierd thing is that one time when I ran the update it actually updated the database and still threw this message

Any suggestion as to what is wrong?

[Demaestro]

The error you are seeing is telling you that the statement has an unexpected end.

If you look at your string you are using double quotes to build it. Which means the start and end of the statement is determined by double quotes.

When you added the request.querystring("alid") the first double quote in that ended your string

Lets look at the output of your string to illustrate the problem. Since the double quotes signify the start and end of the string.

So the output of:
sqlNames="SELECT * FROM alumniList WHERE alid= & request.querystring("alid")

Is....
SELECT * FROM alumniList WHERE alid= & request.querystring(

What you need to do is use single quotes within your string so as to not terminate it.

The below should work or at least give you a new error.

sqlNames="SELECT * FROM alumniList WHERE alid= & request.querystring('alid')"

[tuneman202002]

when I change it to your suqqestion I get this:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'alid= & request.querystring('alid')'.

Although with it like this it does show the top part of my page so it's a step in the right direction

[syber]

Try:

sqlNames="SELECT * FROM alumniList WHERE alid=" & request.querystring("alid")

What you are trying to do is create a string in sqlNames that looks like this:

SELECT * FROM alumniList WHERE alid=1234

This is assuming that alid is numeric, if not then it must have single quotes around the value:

sqlNames="SELECT * FROM alumniList WHERE alid='" & request.querystring("alid") & "'"

Which should produce:
SELECT * FROM alumniList WHERE alid='abc4'

[tuneman202002]

that fixed it so I can see the entirety of my page but when I change something and click to submit it it gives the same error as before

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'alid= & request.querystring('alid')'.

But it update the database if I click the link back to the master page.

[syber]

request.querystring needs double quotes

'alid= & request.querystring("alid")'

[tuneman202002]

I had misread the error it wasn't the same it was

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'alid='

[syber]

Do you have an Update statement for the submit? It should look something like:

UPDATE alumniList
SET <column> = <value>
WHERE alid=1234

[tuneman202002]

I have an update that looks like this

sql="select * from alumniList where alid=" & request.form("theKey")
rs.open sql, conn

rs("alFirst")=request.form("alFirst")
rs("alLast")=request.form("alLast")
rs("alAddress")=request.form("alAddress")
rs("alCity")=request.form("alCity")
rs("alState")=request.form("alState")
rs("alZip")=request.form("alZip")
rs("alEmail")=request.form("alEmail")
rs("alPhone")=request.form("alPhone")

rs.update
rs.close
set rs=nothing
conn.close
set conn=nothing
response.redirect("AlumEdit.asp")

in my processing page, but not in the page that I send my form from, which is where all the errors are listed as coming from

[syber]

The update looks ok. Are you sure there is a value for "theKey"?

[tuneman202002]

yea I defined it in a hidden field in my form with the value being equal to the name for the primary key

[syber]

Sorry, not able to tell the problem from here. I would need to see the line of code where you are getting the ODBC error.

[tuneman202002]

ok well I'll see if I can look through my information at home and then may post again thank you for all your help