Syntax Error in Recordset

[qiaojun]

There are four core objects in the form - txtUserName, txtPassword, cmdOK and cmdCancel. A database named LoginY2K.mdb is connected to this form. It contains a table named User which comprising UserName and Pasword two fields. Below is the coding:

option Explicit
public LoginSucceeded as Boolean
---------------------------------------------------------------
private Sub cmdCancel_Click()
'set the global var to false
'to denote a failed login
LoginSucceeded = false
End
End Sub
---------------------------------------------------------------
private Sub cmdOK_Click()
Dim conAdo as ADODB.Connection
Dim rstAdo as ADODB.Recordset

set conAdo = new ADODB.Connection
set rstAdo = new ADODB.Recordset
conAdo.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Login Security\LoginY2K.mdb;Persist Security Info=false"
rstAdo.Open "Select * From User Where UserName = '" & txtUserName.Text & "';", conAdo, adOpenStatic, adLockOptimistic, adCmdText 'error OCCURS HERE !!!

MsgBox rstAdo.RecordCount & "found." 'Just to test
'check for correct password
If txtPassword .txt= rstAdo.Fields("Password") then
'place code to here to pass the
'success to the calling sub
'setting a global var is the easiest
LoginSucceeded = true
MsgBox "U r here" ' Just to test
else
MsgBox "Invalid Password, try again!", , "Login"
txtPassword.SetFocus
SendKeys "{Home}+{End}"
End If
End Sub



But an error pops up saying," Run-time error '-2147217900 (8004e14); Syntax error in FROM clause. " after submitting the user name and password. (pls look at ERROR OCCURS HERE)

Can anyone help me???

[urs]

instead of cmdtext
try adcmdtable
i think this might work
best of luck

[Dae Jeon]

Instead of doing "Select ...." directly define
a string type:

Dim str1 as string

and use str1 as the parameter to the call.


dkj kaj hj sj

[michi]

What if you take of ";"?


===================
"Select * From User Where UserName = '" & txtUserName.Text & "'"
====================

Regards,

Michi

[d.paulson]

I think User is a reserved word so try putting square brackets around it.

rstAdo.Open "Select * From [User] Where UserName = '" & txtUserName.Text & "';", conAdo, adOpenStatic, adLockOptimistic, adCmdText

David Paulson

[ramayansunil]

Hi,
U change the name of the Table other than User.
ex: User1 and try.

Bye
sunil.

[vincentma]

Michi is right in this case!

[Cakkie]

User is a keyword in SQL, and therefore cannot be used as a table name. If you do use it as a table name, you must enclose it in [ ] like d.paulson said.

Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook