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="num eric">
<cfargument name="username" required="yes" type="string" />
<cfargument name="password" required="yes" type="string" />

<cfquery name="validate" datasource="#re quest.dsn#">
SELECT userID
FROM User
WHERE username = '#UCase(usernam e)#'
AND password = '#UCase(passwor d)#'
</cfquery>

<cfif validate.Record Count EQ 0>
<cfquery name="log" datasource="#re quest.dsn#">
UPDATE User
SET lastLoggedIn = #createOdbcDate (now())#
WHERE userId = #validate.userI D#
</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.Record Count>

Phil

[charliek]

Re: CF / SQL Server syntax error HELP

On Tue, 14 Oct 2003 20:37:57 -0400, "Phil Powell" <soazine@erols. com>
wrote:
[color=blue]
>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="num eric">
> <cfargument name="username" required="yes" type="string" />
> <cfargument name="password" required="yes" type="string" />
>
> <cfquery name="validate" datasource="#re quest.dsn#">
> SELECT userID
> FROM User
> WHERE username = '#UCase(usernam e)#'
> AND password = '#UCase(passwor d)#'
> </cfquery>[/color]

OK you want to select the userid that has a username of #username# and
a password of #password#.
[color=blue]
>
> <cfif validate.Record Count EQ 0>[/color]

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.record Count GT 0>

and now every thing should work fine.
[color=blue]
> <cfquery name="log" datasource="#re quest.dsn#">
> UPDATE User
> SET lastLoggedIn = #createOdbcDate (now())#
> WHERE userId = #validate.userI D#
> </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.Record Count>
>
>Phil
>[/color]

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]

Re: CF / SQL Server syntax error HELP

> Now if no user is found, update the users last login date. This code[color=blue]
> will throw an error because validate.userID will not be defined.[/color]

Slight correction....E ven 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" <charlie_knudse n@hotmail.com> wrote in message
news:g4hpov4h8u auvqr91q1par5fu 4g5fqeeto@4ax.c om...[color=blue]
> On Tue, 14 Oct 2003 20:37:57 -0400, "Phil Powell" <soazine@erols. com>
> wrote:
>[color=green]
> >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[/color][/color]
further,[color=blue][color=green]
> >can you all help me with this?
> >
> ><!--- validate() --->
> > <cffunction name="validate" access="remote" returnType="num eric">
> > <cfargument name="username" required="yes" type="string" />
> > <cfargument name="password" required="yes" type="string" />
> >
> > <cfquery name="validate" datasource="#re quest.dsn#">
> > SELECT userID
> > FROM User
> > WHERE username = '#UCase(usernam e)#'
> > AND password = '#UCase(passwor d)#'
> > </cfquery>[/color]
>
> OK you want to select the userid that has a username of #username# and
> a password of #password#.
>[color=green]
> >
> > <cfif validate.Record Count EQ 0>[/color]
>
> 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.record Count GT 0>
>
> and now every thing should work fine.
>[color=green]
> > <cfquery name="log" datasource="#re quest.dsn#">
> > UPDATE User
> > SET lastLoggedIn = #createOdbcDate (now())#
> > WHERE userId = #validate.userI D#
> > </cfquery>
> > <cfreturn validate.userID />
> > <cfelse>
> > <cfreturn 0 />
> > </cfif>
> > </cffunction>
> >
> >Sorry that's all I can honestly provide, other than the error being on[/color][/color]
the[color=blue][color=green]
> >line with <cfif validate.Record Count>
> >
> >Phil
> >[/color]
>
> Also you can download a 120 trial of MS SQL server and if you are
> running windows 2000+ you can run it.
>
> Charliek[/color]