9 Replies Latest reply on Aug 7, 2015 4:42 AM by skoot3d4

    Error Executing Database Query. Incorrect integer value

    skoot3d4

      I am experiencing an error trying to run the following ColdFusion code:

       

      <cfif IsDefined('FORM.add_button.y')>

      <cfquery datasource="#sDSN#">

          INSERT INTO lcf_event(event_id,

                                event_date_time,

                                title,

                                description,

                                type,

                                user_id)     

         VALUES('#CreateUUID()#',

               #CreateODBCDateTime(CreateDateTime(FORM.year, FORM.month, FORM.day, FORM.hour, FORM.minute, 0))#,

               '#FORM.title#',

               '#FORM.description#',

               '#FORM.type#',

               '#GetAuthUser()#')

      </cfquery>

      <cflocation url="event_calendar.cfm" />

       

      I am using CF11 has the function CreateODBCDateTime has been changed in CF11? I read a post about this function not working in CF8 and CF9 but working in CF7!.

       

      I appreciate if someone can shed some light on this.

       

      Thank you in advance.

        • 1. Re: Error Executing Database Query. Incorrect integer value
          nic_tunney Adobe Community Professional

          Can you include your stack trace?  Are you sure it is failing on the CreateODBCDateTime()?  Was it the function that threw an error or the query?  Also, I'd recommend using <cfqueryparam />.  Not only does this keep you safer from SQL injection, but it will help you correctly format your parameters (cfsqltype).

          • 2. Re: Error Executing Database Query. Incorrect integer value
            haxtbh Level 4

            What are the datatypes of your fields?

             

            I notice you use GetAuthUser, which usually returns a string. You are inserting this into user_id, but is this field an integer?

            • 3. Re: Error Executing Database Query. Incorrect integer value
              skoot3d4 Level 1

              Thank you for your reply.

              I am almost certain it must be a query issue.  But I read somewhere on the web the CreateODBCDateTime() function is not converting the data correctly for the SQL to process.  I let you review the code below and maybe you notice something I might have missed!

               

              <cfif IsUserInRole('basic')>

                  <cflocation url="event_calendar.cfm" />

              </cfif>

               

              <cfparam name="URL.year" default="#Year(Now())#" />

              <cfparam name="URL.month" default="#Month(Now())#" />

              <cfparam name="URL.day" default="#Day(Now())#"/ >

               

              <cfset nHour = 12 />

              <cfset nMinute = 0 />

              <cfset sTitle = "" />

              <cfset sDescription = "" />

              <cfset sType = "public" />

               

              <cfif IsDefined('FORM.event_id')>

                  <cfquery datasource="#sDSN#" name="qEvent">

                     

                      SELECT *

                      FROM lcf_event

                      WHERE event_id = '#FORM.event_id#'

                  </cfquery>

                 

                  <cfset URL.year = Year(qEvent.event_date_time) />

                  <cfset URL.month = Month(qEvent.event_date_time) />

                  <cfset URL.day = Day(qEvent.event_date_time) />

                  <cfset nHour = Hour(qEvent.event_date_time) />

                  <cfset nMinute = Minute(qEvent.event_date_time) />

                  <cfset sTitle = qEvent.title />

                  <cfset sDescription = qEvent.description />

                  <cfset sType = qEvent.type />

               

              </cfif>

               

              <cfinclude template="../header.cfm" />

               

              <cfif IsDefined('FORM.add_button.y')>

              <cfquery datasource="#sDSN#">

                  INSERT INTO lcf_event(event_id,

                                        event_date_time,

                                        title,

                                        description,

                                        type,

                                        user_id)     

                 VALUES('#CreateUUID()#',

                       #CreateODBCDateTime(CreateDateTime(FORM.year, FORM.month, FORM.day, FORM.hour, FORM.minute, 0))#,

                       '#FORM.title#',

                       '#FORM.description#',

                       '#FORM.type#',

                       '#GetAuthUser()#')

              </cfquery>

              <cflocation url="event_calendar.cfm" />

              <cfelseif IsDefined('FORM.update_button.y')>

                  <cfquery datasource="#sDSN#">

                  UPDATE lcf_event

                  SET event_date_time = #CreateODBCDateTime(CreateDateTime(FORM.year, FORM.month, FORM.day, FORM.hour, FORM.minute, 0))#,

                  title = '#FORM.title#',

                  description = '#FORM.description#',

                  type = '#FORM.type#'

                  WHERE event_id = '#FORM.event_id#'

                  </cfquery>

                  <cflocation url="event_calendar.cfm" />

              <cfelse>

              <cfoutput>

                  <form action="#CGI.SCRIPT_NAME#" method="post">

                    <table>

                      <tr>

                        <td>

                          <img src="/sitecoldfusion/images/spacer.gif" height="15" />

                        </td>

                      </tr>

                      <tr>

                        <td class="ltturq">Title:</td>

                      </tr>

                      <tr>

                        <td class="ltturq">

                          <input type="text" name="title" class="field" value="#sTitle#" />

                        </td>

                      </tr>

                      <tr>

                        <td>

                          <img src="/sitecoldfusion/images/spacer.gif" height="15" />

                        </td>

                      </tr>

                      <tr>

                        <td class="ltturq">Description:</td>

                      </tr>

                      <tr>

                        <td class="ltturq">

                          <textarea name="description" cols="30" rows="4">#sDescription#</textarea>

                        </td>

                      </tr>

                      <tr>

                        <td>

                          <img src="/sitecoldfusion/images/spacer.gif" height="15" />

                        </td>

                      </tr>

                      <tr>

                        <td class="ltturq">Type:</td>

                      </tr>

                      <tr>

                        <td>

                          <select name="type">

                         

                              <option value="public"<cfif sType EQ "public"> select</cfif>>public</option>

                              <option value="private"<cfif sType EQ "private"> select</cfif>>private</option>

                          </select>

                        </td>

                      </tr>

                      <tr>

                        <td>

                          <img src="/sitecoldfusion/images/spacer.gif" height="15" />

                        </td>

                      </tr>

                      <tr>

                        <td class="ltturq">Date:</td>

                      </tr>

                      <tr>

                        <td class="ltturq">

                          <select name="year">

                              <cfset nEndYear = URL.year + 4 />

                              <cfloop from="#URL.year#" to="#nEndYear#" index="i">

                                  <option value="#i#"<cfif i EQ URL.year> selected</cfif>>#i#</option>

                              </cfloop>

                          </select>

                          <select name="month">

                              <cfloop from="1" to="12" index="i">

                                  <option value="#i#"<cfif i EQ URL.month> selected</cfif>>#MonthAsString(i)#</option>

                              </cfloop>

                          </select>

                          <select name="day">

                              <cfloop from="1" to="31" index="i">

                                  <option value="#i#"<cfif i EQ URL.day> selected</cfif>>#i#</option>

                              </cfloop>

                          </select>

                        </td>

                      </tr>

                      <tr>

                        <td>

                          <img src="/sitecoldfusion/images/spacer.gif" height="15" />

                        </td>

                      </tr>

                      <tr>

                        <td class="ltturq">Time:</td>

                      </tr>

                      <tr>

                        <td class="ltturq">

                          <select name="hour">

                              <cfloop from="0" to="23" index="i">

                              <cfif i EQ 0>

                                <cfset sHour = "12 AM" />

                                <cfelseif i EQ 12>

                                <cfset sHour = "12 PM" />

                                <cfelseif i GT 12>

                                <cfset sHour = (i- 12) & "PM" />

                                <cfelse>

                                    <cfset sHour = i & "AM" />

                               </cfif>

                               <option value="#i#"<cfif i EQ nHour> selected</cfif>>#sHour#</option>

                              </cfloop>

                          </select>

                          <select name="minute">

                              <cfloop from="0" to="59" index="i">

                              <option value="#i#"<cfif i EQ nMinute> selected</cfif>>#i#</option>

                              </cfloop>

                         </select>

                        </td>

                      </tr>

                      <tr>

                        <td>

                          <img src="/sitecoldfusion/images/spacer.gif" height="15" />

                        </td>

                      </tr>

                      <tr>

                        <td>

                        <cfif IsDefined('FORM.event_id')>

                            <input type="hidden" name="event_id" value="#FORM.event_id#" />

                            <input type="image" name="update_button" src="../images/update.gif" />

                        <cfelse>

                          <input type="image" name="add_button" src="../images/addEvent.gif" />

                        </cfif>

                        </td>

                      </tr>

                    </table>

                  </form>

              </cfoutput>

              </cfif>

              <cfinclude template="../footer.cfm" />

              • 4. Re: Error Executing Database Query. Incorrect integer value
                skoot3d4 Level 1

                Hi haxtbh,

                Your assumption is on the money, GetAuthUser  returns a string and I think there is an issue with the data conversion to integer.

                 

                I hope you guy can suggest a way to over come this .

                • 5. Re: Error Executing Database Query. Incorrect integer value
                  nic_tunney Adobe Community Professional

                  I'd imagine since getAuthUser() returns a string representing the username, change the datatype of the user_id field to varchar.  Either that or retrieve the user_id wherever you keep that and insert it instead.

                  • 6. Re: Error Executing Database Query. Incorrect integer value
                    haxtbh Level 4

                    Ideally you will want to the user ID to insert into the database if you are using it to reference another table with the user in. You will need to do a query of some kind using the GetAuthUser value to get the userID and then store that in the database when you do the insert. You could store the ID in a variable when you do the cflogin as well, to use later.

                     

                    If you just want the actual string returned by GetAuthUser then you will need to change the database type on the field from int to varchar.

                    • 7. Re: Error Executing Database Query. Incorrect integer value
                      Steve Sommers Level 4

                      I would like to emphasize what nic originally stated, use cfqueryparam -- "Not only does this keep you safer from SQL injection, but it will help you correctly format your parameters (cfsqltype)."

                       

                      As it stands right now, from the browser using one of various plug-ins, set the value of FORM.type to "x', 1) /*" and see what happens. Or worse, set FORM.type to: "x', 1) delete lcf_event /*". The syntax may be a hair off but I suggest you make a database backup first.

                      • 8. Re: Error Executing Database Query. Incorrect integer value
                        skoot3d4 Level 1

                        Thank you for that will give that a try.

                        • 9. Re: Error Executing Database Query. Incorrect integer value
                          skoot3d4 Level 1

                          Thank you for the reminder.  I have noted nic_tunney's recommendation.  I will implement that in the revised code.