Skip to main content
J
jawmusic
Inspiring
February 11, 2013
Question

Problem looping insert query

  • February 11, 2013
  • 2 replies
  • 1164 views

I'm working on inserting a recordset up Google Calendar entries into my site database. I have a form set up that allows me to insert new entries into the data table, but when I try to loop the insert query over the Google recordset, I get nothing (no errors, but no entries added to database).

Here's my form, which both displays the values from the Google query and populates fields (mostly hidden) with those values to submit to the Insert query. There's also a line that checks to make sure the event hasn't been canceled (canceled events are still listed, the start and end times are just set to null values):

<form method="post" action="<cfoutput>#CurrentPage#</cfoutput>">

<table cellpadding="2" border="1">

<cfset thisRow = 0>

<cfloop query="band_events">

<cfif IsDefined("starttime") AND IsDefined("endtime") AND starttime NEQ "" AND endtime NEQ "">

<cfset thisRow = thisRow + 1>

<cfoutput>

<tr>

  <td>#thisRow#</td>

  <td><input name="update#thisRow#" type="checkbox" checked="checked" /></td>

  <td><input name="calendar#thisRow#" type="hidden" value="Band" />Band</td>

  <td><input name="creator#thisRow#" type="hidden" value="#authoremail#" />#authoremail#</td>

  <td><input name="starttime#thisRow#" type="hidden" value="#starttime#" />#starttime#</td>

  <td><input name="endtime#thisRow#" type="hidden" value="#endtime#" />#endtime#</td>

  <td><input name="title#thisRow#" type="hidden" value="#title#" />#title#</td>

  <td><input name="content#thisRow#" type="hidden" value="#content#" />#content#</td>

  <td><input name="location#thisRow#" type="hidden" value="#where#" />#where#</td>

  <td><input name="ID#thisRow#" type="hidden" value="#ID#" />#ID#</td>

  <td><input name="published#thisRow#" type="hidden" value="#ISOToDateTime(published)#" />#ISOToDateTime(published)#</td>

  <td><input name="updated#thisRow#" type="hidden" value="#ISOToDateTime(updated)#" />#ISOToDateTime(updated)#</td>

</tr>

</cfoutput>

</cfif>

</cfloop>

</table>

<input type="hidden" name="totalRows" value="#row#" />

<input type="submit" name="submit" value="Submit" />

<input type="hidden" name="update_events" value="update_events" />

</form>

Here's the action script. There's some extra code in there to convert some of the date formats Google uses, and to account for the differences in time zones between the Google values and local time:

<cfif StructKeyExists(form,"update_events")>

  <cfset loopCount = form["totalRows"]>

  <cfloop index="row" from="1" to="#loopCount#">

    <cfset thisCalendar = form["calendar" & row]>

    <cfset thisCreator = form["creator" & row]>

    <cfset thisStarttime = form["starttime" & row]>

    <cfset thisEndtime = form["endtime" & row]>

    <cfif TimeFormat(thisStarttime,"hh:mm tt") EQ "12:00 AM" AND TimeFormat(thisEndtime,"hh:mm tt") EQ "12:00 AM">

      <cfset thisAllDay = -1>

    <cfelse>

      <cfset thisAllDay = 0>

      <cfset thisStarttime = DateAdd("h",5,thisStarttime)>

      <cfset thisEndtime = DateAdd("h",5,thisEndtime)>

    </cfif>

    <cfset thisTitle = form["title" & row]>

    <cfset thisContent = form["content" & row]>

    <cfset thisLocation = form["location" & row]>

    <cfset thisID = form["ID" & row]>

    <cfset thisPublished = form["published" & row]>

    <cfset thisUpdated = form["updated" & row]>

    <cfquery datasource="ctband">

      INSERT INTO Events (Calendar, Created_By, All_Day, Event_Start, Event_End, Title, Content, Location, ID, Published, Updated) Values (

      <cfqueryparam value="#thisCalendar#" cfsqltype="cf_sql_varchar">,

      <cfqueryparam value="#thisCreator#" cfsqltype="cf_sql_varchar">,

      <cfqueryparam value="#thisAllDay#" cfsqltype="cf_sql_integer">,

      <cfqueryparam value="#thisStarttime#" cfsqltype="cf_sql_timestamp">,

      <cfqueryparam value="#thisEndtime#" cfsqltype="cf_sql_timestamp">,

      <cfqueryparam value="#thisTitle#" cfsqltype="cf_sql_varchar">,

      <cfqueryparam value="#thisContent#" cfsqltype="cf_sql_clob">,

      <cfqueryparam value="#thisLocation#" cfsqltype="cf_sql_varchar">,

      <cfqueryparam value="#thisID#" cfsqltype="cf_sql_varchar">,

      <cfqueryparam value="#thisPublished#" cfsqltype="cf_sql_timestamp">,

      <cfqueryparam value="#thisUpdated#" cfsqltype="cf_sql_timestamp">)

    </cfquery>

  </cfloop>

</cfif>

I'm thinking there must be something pretty obvious I've screwed up, but I can't for the life of me find it. Any help would be appreciated!

JAW

    This topic has been closed for replies.

    2 replies

    BreakawayPaul
    BreakawayPaul
    Inspiring
    February 11, 2013

    A couple of things.

    First, I see this:

    <cfset thisRow = thisRow + 1>

    Which is you counting the number of rows, I assume.  Then I see this:

    <input type="hidden" name="totalRows" value="#row#" />

    But I don't see where #row# has been set.

    Then I see this:

    form["totalRows"]

    When I'm used to seeing this:

    FORM.totalRows

    I've never seen the form["name"] before.  Does that actually work?

    J
    jawmusicAuthor
    Inspiring
    February 11, 2013

    First, I see this:

    <cfset thisRow = thisRow + 1>

    Which is you counting the number of rows, I assume.  Then I see this:

    <input type="hidden" name="totalRows" value="#row#" />

    But I don't see where #row# has been set.

    You are correct. That was a combination of a couple different attempts at this and I used the wrong variable name on that line. Alas, correcting that didn't solve the problem.

    Then I see this:

    form["totalRows"]

    When I'm used to seeing this:

    FORM.totalRows

    I've never seen the form["name"] before.  Does that actually work?

    I read somewhere that that's a more effective way of factoring the variable in the name of the form field as an alternative to Evaluate("form.field#row#"). I think it was somewhere on Ray Camden's blog, but it was a while ago. The only reason I used it there was for consistency. That  spot could easily have been FORM.totalRows, but the others would have to be either Evaluate("FORM.calendar#row#") or form["calendar" & row]. Again, I'm learning as I go, though, so I may have used it incorrectly. It's not throwing errors though, it's just reloading a blank page, as if it's submitting the form, but not actually processing the query. That's the confusing part to me.

    BreakawayPaul
    BreakawayPaul
    Inspiring
    February 11, 2013

    So when you load the page initially, you get the form, right?  But when you click "Submit" you get a blank page back and the query hasn't run?

    Try wrapping the query in a try/catch:

    <cftry>

    <cfquery...

    </cfquery>

    <cfcatch><cfoutput>#cfcatch.message#</cfoutput></cfcatch>

    </cftry>

    See if itthrows an error.

    D
    Dan_Bracuk
    Inspiring
    February 11, 2013

    I've been drinking, so I had to count thrice.  All three times I counted 11 values and 10 fields.

    J
    jawmusicAuthor
    Inspiring
    February 11, 2013

    Yes, but the 11th value (the value going into the All_Day column) is calculated based on two of the other fields (starttime and endtime), not directly inputted from the form. I can't see any reason why that shouldn't work the way it's set up, but I'm learning as I go, so there may be something I'm missing.

    And I've been drinking too...what else would I be doing while I'm snowed in in MA? ;-)

    Thanks for the reply!

    JAW

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices