Avatar of Klainn
Klainn
 asked on

Update table query won't actually update the table.

What I am attempting to do is take 3 values passed from a url (GlobalID, Type, and StartDate) as I believe these should distinguish one row from another. The variables are passed to the getInfo query and I can verify that they are being passed as I output them below via getInfo.GlobalID, getInfo.Type, and getInfo.StartDate. When I process the form, the query to update the table and set the Status and Occurrence fields do not update and no error is thrown. It's probably something easy I've overlooked, but I can't see it.

Help!

Here are some variables being passed.. timeoff_admin.cfm?GlobalID=2037503&Type=Vacation&StartDate=11/01/2006

The error I get now (before the form will even load) is "Invalid data URL.GlobalID for CFSQLTYPE CF_SQL_INTEGER."

All the tables share GlobalID as the key and they are all data types of INT



<!--- Take values from Mypage and query database with them --->
<!--- End Take values from Mypage and query database with them --->
<!--- Check if form is submitted and process --->
<cfif IsDefined('form.timeoff')>
<cfquery datasource="csc">
UPDATE Timeoff
SET Status='#FORM.Status#', Occurrence='#FORM.Occurrence#'
WHERE GlobalID = '#FORM.GlobalID#'
AND Type = '#FORM.Type#'
AND StartDate = '#FORM.StartDate#'
</cfquery>
     <cfif #FORM.Status# IS "Denied">
          <cfmail to="#getInfo.Email#" from="Service Desk Supervisors" subject="Your #getInfo.Type# request for #getInfo.StartDate# to #getInfo.EndDate# has been #FORM.Status#" type="html">
          Your request has been #FORM.Status# for the following reasons:<br />
          #FORM.Reason#
          </cfmail>
     <cfelseif #FORM.Status# IS "Approved">
          <cfmail to="#getInfo.Email#" from="Service Desk Supervisors" subject="Your #getInfo.Type# request for #getInfo.StartDate# to #getInfo.EndDate# has been #FORM.Status#" type="html">
          Your request has been #FORM.Status# and is on the calendar.<br />
          </cfmail>
     </cfif>
<script language="javascript" type="text/javascript">
opener.window.location.reload();
self.close();
</script>
<cfelse>
<cfparam name="URL.GlobalID" default="1">
<cfparam name="URL.Type" default="1">
<cfparam name="URL.StartDate" default="1">
<cfquery datasource="csc" name="getInfo">
SELECT *
FROM Timeoff
WHERE GlobalID = <cfqueryparam value="URL.GlobalID" cfsqltype="cf_sql_integer">
AND Type = <cfqueryparam value="URL.Type">
AND StartDate = <cfqueryparam value="URL.StartDate">
</cfquery>
<cfoutput>
#getInfo.GlobalID#<br />
#getInfo.Type#<br />
#getInfo.StartDate#<br />
</cfoutput>
<form action="<cfoutput>#CGI.SCRIPT_NAME#</cfoutput>" method="post">
<select name="Status" size="1">
<option value="Approved">Approved</option>
<option value="Denied">Denied</option>
</select><br />
Occurrence: <input type="text" name="Occurrence" /><br />
If request is denied, submit reason:<br />
<textarea name="Reason" cols="80%" rows="10"></textarea>
<input type="hidden" name="GlobalID" value="#getInfo.GlobalID#" />
<input type="hidden" name="Type" value="#getInfo.Type#" />
<input type="hidden" name="StartDate" value="#getInfo.StartDate#" />
<input type="submit" name="timeoff" value="Approve / Deny Request" class="submitbutton" />
</form>
</cfif>
<!--- End Check if form is submitted and process --->
Web Servers

Avatar of undefined
Last Comment
Klainn

8/22/2022 - Mon
gdemaria


 Is startDate a column of type DateTime and does it contain the time as well as the date?

 If so, it will not match just the date.

 That is, if startDate contains  '12/31/2006 12:15 pm' it will not match '12/31/2006'

UPDATE Timeoff
SET Status='#FORM.Status#', Occurrence='#FORM.Occurrence#'
WHERE GlobalID = '#FORM.GlobalID#'
AND Type = '#FORM.Type#'


AND StartDate = '#FORM.StartDate#'

TRY...

AND   dateDiff(D,StartDate , #createODBCdate(form.stateDate)# ) = 0

Klainn

ASKER
StartDate is just a date, no time involved. The error I'm getting off of my above code now is: "[Macromedia][SQLServer JDBC Driver][SQLServer]Syntax error converting the varchar value 'URL.GlobalID' to a column of data type int." This I'm confused by as the GlobalID value is an INT everywhere in the database but I can't get it to leave the URL.GlobalID variable as INT.
gdemaria


Remove the quotes from around the global ID

UPDATE Timeoff
SET Status='#FORM.Status#', Occurrence='#FORM.Occurrence#'
WHERE GlobalID = '#FORM.GlobalID#'
                         ^^^  Remove quotes which make it a string, this would appear as   = '123'  
AND Type = '#FORM.Type#'
AND StartDate = '#FORM.StartDate#'




UPDATE Timeoff
SET Status='#FORM.Status#'
    , Occurrence='#FORM.Occurrence#'
WHERE GlobalID = #val(FORM.GlobalID)#
AND Type = '#FORM.Type#'
<Cfif IsDate(FORM.StartDate)>
AND StartDate = #createODBCdate(FORM.StartDate)#
<cfelse>  AND 1 = 2  --- pull no records
</cfif>


will form.startDate always have a value?
This CFIF statement will ensure that its a date before placing in the where clause
<Cfif IsDate(FORM.StartDate)>
AND StartDate = #createODBCdate(FORM.StartDate)#
<cfelse>   <!---- what to do it its not a date?  Pull No Records or Pull All ? ---->
AND 1 = 2  --- pull no records
</cfif>
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Klainn

ASKER
Thanks for replying.

I made the change as above:

UPDATE Timeoff
SET Status='#FORM.Status#', Occurrence='#FORM.Occurrence#'
WHERE GlobalID = #val(FORM.GlobalID)#
AND Type = '#FORM.Type#'
AND StartDate = '#FORM.StartDate#'

I've made all the URL.GlobalID variables #val(URL.GlobalID)# in the form and now when I click on the link, this page pops up as it should. I've got it displaying the URL Variables as it should and they look fine. When I click the submit button the form processes, closes the window, and refreshes the parent window like it should but does not update the Status or Occurrence portions of the database. It doesn't change anything in the db.

The code as it stands now:

<cfset title = "Time Off Admin">
<cfinclude template="/unisys/includes/header_style.cfm">
<!--- Take values from Mypage and query database with them --->
<!--- End Take values from Mypage and query database with them --->
<!--- Check if form is submitted and process --->
<cfif IsDefined('form.timeoff')>
            <cfquery datasource="csc">
            UPDATE Timeoff
            SET Status='#FORM.Status#', Occurrence='#FORM.Occurrence#'
            WHERE GlobalID = #val(FORM.GlobalID)#
            AND Type = '#FORM.Type#'
            AND StartDate = '#FORM.StartDate#'
            </cfquery>
                              <!---<cfif #FORM.Status# IS "Denied">
                                    <cfmail to="#getInfo.Email#" from="Service Desk Supervisors" subject="Your #getInfo.Type# request for #getInfo.StartDate# to #getInfo.EndDate# has been #FORM.Status#" type="html">
                                    Your request has been #FORM.Status# for the following reasons:<br />
                                    #FORM.Reason#
                                    </cfmail>
                              <cfelseif #FORM.Status# IS "Approved">
                                    <cfmail to="#getInfo.Email#" from="Service Desk Supervisors" subject="Your #getInfo.Type# request for #getInfo.StartDate# to #getInfo.EndDate# has been #FORM.Status#" type="html">
                                    Your request has been #FORM.Status# and is on the calendar.<br />
                                    </cfmail>
                              </cfif>--->
                        <script language="javascript" type="text/javascript">
                        opener.window.location.reload();
                        self.close();
                        </script>
<cfelse>
            <cfquery datasource="csc" name="getInfo">
            SELECT *
            FROM Timeoff
            WHERE GlobalID = #val(URL.GlobalID)#
            AND Type = '#URL.Type#'
            AND StartDate = '#URL.StartDate#'
            </cfquery>
                        <cfoutput>
                        #URL.GlobalID#<br />
                        #URL.Type#<br />
                        #URL.StartDate#<br />
                        </cfoutput>
                        <form action="<cfoutput>#CGI.SCRIPT_NAME#</cfoutput>" method="post">
                        <select name="Status" size="1">
                        <option value="Approved">Approved</option>
                        <option value="Denied">Denied</option>
                        </select><br />
                        Occurrence: <input type="text" name="Occurrence" /><br />
                        If request is denied, submit reason:<br />
                        <textarea name="Reason" cols="80%" rows="10"></textarea>
                        <input type="hidden" name="GlobalID" value="#val(URL.GlobalID)#" />
                        <input type="hidden" name="Type" value="#URL.Type#" />
                        <input type="hidden" name="StartDate" value="#URL.StartDate#" />
                        <input type="submit" name="timeoff" value="Approve / Deny Request" class="submitbutton" />
                        </form>
</cfif>
<!--- End Check if form is submitted and process --->
gdemaria


 Turn on your coldfusion debugging view and see what the Update statement looks like as it is passed to the database.

 You can cut and paste the update statement directly into SQL server's query analyer page to see what happens when you run it without the whole coldfusion page.

  You can also cut and paste that update statement in here so we can see it...

Klainn

ASKER
Here is the query from when the variables are passed:

getInfo (Datasource=csc, Time=0ms, Records=1) in C:\CFusionMX7\wwwroot\supervisor\timeoff_admin.cfm @ 12:16:28.028

            SELECT *
            FROM Timeoff
            WHERE GlobalID = 2037503
            AND Type = 'Vacation'
            AND StartDate = '11/01/2006'
            

Here is the query from when the form is processed:

 (Datasource=csc, Time=0ms, Records=0) in C:\CFusionMX7\wwwroot\supervisor\timeoff_admin.cfm @ 12:17:52.052

            UPDATE Timeoff
            SET Status='Approved', Occurrence=''
            WHERE GlobalID = 0
            AND Type = '#URL.Type#'
            AND StartDate = '#URL.StartDate#'

I do see the problem, but I don't rightly see the solution. It's losing the variables somehow after the form is processed. Any thoughts?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
gdemaria

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Klainn

ASKER
I can't tell you how embarrassed I am that I missed something like that! But I'm glad to know it wasn't something more serious. Points Awarded!