Syntax error in FROM clause

however, access seems fine with it

[chris_f]

I am running an ASP script. I am getting the error message:

Syntax error in FROM clause

on the line:

ChkUserNameRS.Open ChkUserNameSQLstr

Here is the snipet of code:

Set ChkUserNameRS = Server.CreateObject("ADODB.Recordset")
Set ChkUserNameRS.ActiveConnection = my_Conn
ChkUserNameSQLstr = "SELECT Count(Username) as UsernameCount FROM User WHERE Username='" & FrmUser & "'"
ChkUserNameRS.Open ChkUserNameSQLstr

---

I have used a response.write to see the full query the code tries to run. I have copied and paste this into access and it runs fine. I'm completely lost.

Any help is appreciated.
Chris.

[Dreamquick]

Might be related, might not, but "user" is also the name of a system keyword in SQL Server so the fact that you are using it the way you are might be the problem.

Instead try it like this to ensure that object names are recognised as objects rather than keywords etc;

ChkUserNameSQLstr = "SELECT COUNT( [Username] ) as UsernameCount FROM [User] WHERE [Username]='" & FrmUser & "'"

- Tony

[chris_f]

Dreamquick,

Thanks for the response. I have thought of that. Access does not use user as a keyword. If it did, the query would fail when I ran it in access. I have also used user before with no problems

Chris

[chris_f]

I stand corrected.

Sorry Dreamquick, you were right. I have renamed the table and it works fine now. Thanks.

I remember ASP has it's own set of keywords and as well as Access.

Chris

[txbakers]

I had the same problem with a field called "date" which worked fine in Access for a while, then didn't.
You have to watch keywords and probable key words......

[Giacomo]

Yeah, not long ago i experienced weird errors from using a column named "Language" in the WHERE clause of a SELECT query. And "Language" is not even listed in Access's reserved keywords!

Since I just couldn't rename the db field, I had to modify my query to use an "absolute" field name, like this:

WHERE [TableName]![Language]

instead of

WHERE Language

I nearly went nuts to find the cause of the error, but the square brackets saved my day.

[TheDave]

I use [brackets] whether my word is "user" or "christmas" :) Better safer than sorry, I have been caught like you guys in the frustration of "why wont it work!" ;)

[bcc1234]

If you are using aggregate expressions such as count() min() max() etc.. you must group your results.

I don't know asp nor ever use access, but I'm pretty sure what you need is:

select blah, count(blah2) from blah3 where blah4=blah5 group by blah;

If you are in fact trying to count the groups of blah.

<edit>sp</edit>
<edit>sp2, boy do I feel sleepy</sp>