Syntax error (missing operator)

[tekyge]

Here the code I am using to verfy a user name and password but I keep getting this error. Any help?

Code
sql = "SELECT * FROM [Clients] WHERE [Clients].[ClientID]=" &Trim(Request.Form("accountid"))& " AND [Clients].[Password]=" &Trim(Request.Form("'password'"))
rs.open sql, conn

Error
Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression '[Clients].[ClientID]= AND [Clients].[Password]='.

/cs/test/login.asp, line 11

 

[DotNetGnat]

that error implies that your form variables are not getting passed and they are empty...but as it is there is a mistake in query...i am sure password field is a string and you need single quotes around it...so try it...BUT MAKE SURE YOU PASS THE FORM VARIABLES CORRECTLY>>>

sql = "SELECT * FROM [Clients] WHERE [Clients].[ClientID]='" &Trim(Request.Form("accountid"))& "' AND [Clients].[Password]='" &Trim(Request.Form("'password'"))&"' "

if the ClientID is a number then you can remove the single quotes around it...

-DNG

 

[tekyge]

Thanks Dot

now I am getting this error

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

/cs/test/login.asp, line 11

 

[DotNetGnat]

first thing to note...when you get any error in your sql statement in your asp page, always it is a better idea to do a

response.write yoursql

to see the actual querystring that is being passed.

second thing...

if your field is a number then you dont need single quotes

if your field is a string then you need single quotes...

so change your query accordingly...

-DNG

 

[tekyge]

The password field is a text field, and I have the code below working but it keeps redirecting me to the error page even we I know the correct password and id is entered.

dim conn, rs, sql, intTelephone, intMobile
set rs = Server.CreateObject ("ADODB.Recordset")
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Data\Data.mdb;" & _
"Jet OLEDB:System Database=C:\Data\Scur.mdw", _
"xxxx", "xxxxxxxx"

sql = "SELECT * FROM [Clients] WHERE [Clients].[ClientID]=" &Trim(Request.Form("accountid"))& " AND [Clients].[Password]='" &Trim(Request.Form("'password'"))&"' "
rs.open sql, conn
'--

'This code is to check what we got back from the database:
IF rs.EOF THEN
response.redirect("/test/login/error.asp")
else
session("myvar1")=rs("ClientID")
session("myvar2")=rs("Contact")
session("myvar3")=rs("Company")
END IF
rs.close
response.redirect("/test/login/index.asp")

 

[DotNetGnat]

after this line

rs.open sql, conn

put this line...

response.write sql


then execute the page again...and see what is getting printed...

take that sql query and try it in access database..see if it works...make sure that there are no typos...no additional spaces...

-DNG

 

[tekyge]

ok got it to work if you change the field value in the database to a number and remove single quotes. However I need it to be a text field so letter and numbers can be used. Any help?

 

[tekyge]

Got it below is the code and it works Thanks for your help.

dim conn, rs, sql, intTelephone, intMobile
set rs = Server.CreateObject ("ADODB.Recordset")
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Data\Data.mdb;" & _
"Jet OLEDB:System Database=C:\Data\Scur.mdw", _
"xxx", "xxxxxx"

sql = "SELECT * FROM [Clients] WHERE [Clients].[ClientID]=" &Trim(Request.Form("accountid"))& " AND [Clients].[Password]='" &Trim(Request.Form("password"))&"' "
rs.open sql, conn
'--

'This code is to check what we got back from the database:
IF rs.EOF THEN
Response.Redirect "/test/login/error.asp"
Response.End
else
session("myvar1")=rs("ClientID")
session("myvar2")=rs("Contact")
session("myvar3")=rs("Company")
END IF
rs.close
Response.Redirect "/test/login/index.asp"
Response.End

 

[DotNetGnat]

no problem...glad to help...

-DNG