Update

Hi,

I am not sure what I am doing wrong. (I am using MySQL)

My update syntax is not updating.

            <CFSET today = #DateFormat(Now(), "mm/dd/yyyy")#>
            <CFSET DaysToExpire = #DateDiff("d","#today#", "#UserAccess.AccExpireDate#")#>

      Update  UserTbl
      set UserLastLoginDate = '#today#'
      where UserID = #Trim(UserAccess.UserID)#

lulu50Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TempDBACommented:
I am not very familiar with mysql but we can check the query.
Why are you using the #. What is the significance of # here.
It must have to do something with # as the logic of the sql looks correct.
0
Peter KipropCommented:
# is used for dates in MS access and not mysql
0
Eric FlammOwnerCommented:
This is more of a ColdFusion question than a MySQL question. You're combing ColdFusion syntax (<CFSET> Tag) and MySQL, and I'm not sure exactly how they get along. Here's some info on the pound signs (from http://learn-coldfusion-tutorial.com/):
You may notice that no pound signs are used around the Now() function in the <cfset> tag, while there are pound signs around the DateFormat() function. The use of pound signs to indicate that a variable or expression should be evaluated can be a bit confusing. Here are the rules:
-Use pound signs when the expression to be evaluated is not within a ColdFusion tag (e.g, <cfoutput>#Now()#</cfoutput>.
-Use pound signs around expressions that are within a ColdFusion tag ONLY when the expression is also in quotes (e.g, <cfset greeting = "Hello #person#">).
-With nested functions, use pound signs only around the outermost function (e.g, <cfoutput>#DateFormat(Now())#</cfoutput>).

I don't think your ColdFusion syntax is correct - for example, DateDiff takes a dateobject (e.g., Now()) for its second argument, and you've entered a string variable (whose value might be "12/18/11"). I also can't tell whether CF will be able to evaluate UserAccess.AccExpireDate - I don't know if that's supposed to be a reference to a field in a dataset, some sort of namespace reference, or something else. I'm also not sure how you execute a MySQL command in ColdFusion - I assume you have to instantiate a Command Object of some kind, and execute it against an open connection to the database.

I think you should add this question to the ColdFusion zone.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

SidFishesCommented:
The simplest way around this - and frankly you MUST use the following procedure to prevent sql injection attacks

 set UserLastLoginDate = <cfqueryparam cfsqltype="cf_sql_date" value="#today#">


Have a read here

http://www.experts-exchange.com/Database/MySQL/Q_22479194.html
0
SidFishesCommented:
oh and to explain the non-security reason this works is that

This tag does the following:

    Allows the use of SQL bind parameters, which improves performance.
    Ensures that variable data matches the specified SQL type.
    Allows long text fields to be updated from a SQL statement.
    Escapes string variables in single-quotation marks.

http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_p-q_18.html

in other words -  cf does the work of deciding how to present the data to the database
0
SidFishesCommented:
and btw your CF code is correct ;)
0
Eric FlammOwnerCommented:
@SidFishes - doesn't the update query have to be wrapped in cfquery tag with a reference to the database connection?
Also, how does DateDiff work if you feed it a string (#DateFormat(Now())#) rather than a dateobject?
0
SidFishesCommented:
eflamm yes your correct there does need to be a cfquery (and the OP has been around in the CF TA's for quite a while so I figured that was just a copy paste thing)

but for the benefit of the paq db - the correct code would

<CFSET today = #DateFormat(Now(), "mm/dd/yyyy")#>
<CFSET DaysToExpire = #DateDiff("d","#today#", "#UserAccess.AccExpireDate#")#>
<cfquery name="myQry" datasource="someDSN">
      Update  UserTbl
      set UserLastLoginDate = <cfqueryparam cfsqltype="cf_sql_date" value="#today#">
      where UserID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Trim(UserAccess.UserID)#">
</cfquery>

note that i've enclosed userid in cfqueryparam as well


As for your Q about datadiff -  CF is a bit -loose- about how it deals with things, in fact all simple variables are treated as strings internally "For simple data, such as numbers and strings, the data type is unimportant until the variable is used in an expression or as a function argument." http://livedocs.adobe.com/coldfusion/8/htmldocs/Variables_05.html (you OOP folks are just cringing aren't you)

this is why using cfqueryparam is such a help - you don't need to manage the typing. In this case, pass it any kind of date like string and CF can format it for your db specific format.

Lulu50 -  I should mention however that while you code is not incorrect, it is more complex than it needs to be. There's no need to use DateFormat to insert to a database when using cfqueryparam - CF will do that for you. In CF land, Format functions are for -display- only. Use them for <cfoutput> but not for server side things.

Also -  you do not need to use ##'s within most <cf> tags.  <CFSET today = Now()> is correct-est Note that this does not apply when you are setting variable withing quotes within a cftag

including cfqueryparam and cfloop

ie: <cfqueryparam cfsqltype="cf_sql_date" value="#today#">

<cfloop index = "1" from="1" to="#x#">







0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lulu50Author Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.