CF / SQL Server syntax error HELP

[Phil Powell]

I am not sure why this is producing a SQL Server related error, but w/o
having an instance of SQL Server on my machine to verify anything further,
can you all help me with this?

<!--- validate() --->
<cffunction name="validate" access="remote" returnType="numeric">
<cfargument name="username" required="yes" type="string" />
<cfargument name="password" required="yes" type="string" />

<cfquery name="validate" datasource="#request.dsn#">
SELECT userID
FROM User
WHERE username = '#UCase(username)#'
AND password = '#UCase(password)#'
</cfquery>

<cfif validate.RecordCount EQ 0>
<cfquery name="log" datasource="#request.dsn#">
UPDATE User
SET lastLoggedIn = #createOdbcDate(now())#
WHERE userId = #validate.userID#
</cfquery>
<cfreturn validate.userID />
<cfelse>
<cfreturn 0 />
</cfif>
</cffunction>

Sorry that's all I can honestly provide, other than the error being on the
line with <cfif validate.RecordCount>

Phil

[charliek]

On Tue, 14 Oct 2003 20:37:57 -0400, "Phil Powell" <so*****@erols.com>
wrote:

I am not sure why this is producing a SQL Server related error, but w/o
having an instance of SQL Server on my machine to verify anything further,
can you all help me with this?

<!--- validate() --->
<cffunction name="validate" access="remote" returnType="numeric">
<cfargument name="username" required="yes" type="string" />
<cfargument name="password" required="yes" type="string" />

<cfquery name="validate" datasource="#request.dsn#">
SELECT userID
FROM User
WHERE username = '#UCase(username)#'
AND password = '#UCase(password)#'
</cfquery>
OK you want to select the userid that has a username of #username# and
a password of #password#.

<cfif validate.RecordCount EQ 0>
Now if no user is found, update the users last login date. This code
will throw an error because validate.userID will not be defined.
Because you are trying to update a user only when no user is found.
It should not throw a database error but rather validate.userid is not
defined and that will throw an error. replace the above if statement
with the one below, and if a problem still remains please provide the
error messages given.

<cfif validate.recordCount GT 0>

and now every thing should work fine.
<cfquery name="log" datasource="#request.dsn#">
UPDATE User
SET lastLoggedIn = #createOdbcDate(now())#
WHERE userId = #validate.userID#
</cfquery>
<cfreturn validate.userID />
<cfelse>
<cfreturn 0 />
</cfif>
</cffunction>

Sorry that's all I can honestly provide, other than the error being on the
line with <cfif validate.RecordCount>

Phil

Also you can download a 120 trial of MS SQL server and if you are
running windows 2000+ you can run it.

Charliek

[Adam Haskell]

> Now if no user is found, update the users last login date. This code

will throw an error because validate.userID will not be defined.
Slight correction....Even if the query returns 0 resutlts validate.userID
will be defined it will just be blank.
The error from SQL is probably saying "Incorrect syntax near '='."
The cause of the problem is still the same...your if statement should be GT
or NEQ 0.
Side note bad form calling a table user....that is an SQL reserve word I
thought...

Adam H

"charliek" <ch*************@hotmail.com> wrote in message
news:g4********************************@4ax.com... On Tue, 14 Oct 2003 20:37:57 -0400, "Phil Powell" <so*****@erols.com>
wrote:

I am not sure why this is producing a SQL Server related error, but w/o
having an instance of SQL Server on my machine to verify anything further,can you all help me with this?

<!--- validate() --->
<cffunction name="validate" access="remote" returnType="numeric">
<cfargument name="username" required="yes" type="string" />
<cfargument name="password" required="yes" type="string" />

<cfquery name="validate" datasource="#request.dsn#">
SELECT userID
FROM User
WHERE username = '#UCase(username)#'
AND password = '#UCase(password)#'
</cfquery>

OK you want to select the userid that has a username of #username# and
a password of #password#.

<cfif validate.RecordCount EQ 0>

Now if no user is found, update the users last login date. This code
will throw an error because validate.userID will not be defined.
Because you are trying to update a user only when no user is found.
It should not throw a database error but rather validate.userid is not
defined and that will throw an error. replace the above if statement
with the one below, and if a problem still remains please provide the
error messages given.

<cfif validate.recordCount GT 0>

and now every thing should work fine.

<cfquery name="log" datasource="#request.dsn#">
UPDATE User
SET lastLoggedIn = #createOdbcDate(now())#
WHERE userId = #validate.userID#
</cfquery>
<cfreturn validate.userID />
<cfelse>
<cfreturn 0 />
</cfif>
</cffunction>

Sorry that's all I can honestly provide, other than the error being on theline with <cfif validate.RecordCount>

Phil

Also you can download a 120 trial of MS SQL server and if you are
running windows 2000+ you can run it.

Charliek