Skip to main content
C
cfsetNewbie
Known Participant
April 29, 2010
Answered

cfquery problem with insert.

  • April 29, 2010
  • 3 replies
  • 3425 views

Hello;

I'm having problems with my insert query for adding a new record. It's not throwing any errors, I dump all the variables from the from that feeds it and their there.. but it's not adding the record to the database. IT IS an access db, not my choice.. clients. Maybe that's the problem? (just kidding) Can someone help me figure out why this code won't add a new record. I'm posting my insert query, if you need more I can supply it. But i think it's a problem with this code.

<cfset EventDate = '#DateFormat(form.edit1,"mm/dd/yyyy")#'>
<cfquery result="yourResults" datasource="#APPLICATION.dataSource#" dbtype="ODBC">
INSERT INTO events
(title, eventDate, eventTime, location, contact, phone, fax, email, URL, sponsor, Body)
VALUES (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.title#">,
        <cfqueryparam cfsqltype="cf_sql_date" value="#EventDate#">,
        <cfqueryparam cfsqltype="cf_sql_time" value="#form.eventTime#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.location#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.contact#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.fax#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.email#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.URL#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.sponsor#">,
  <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.PDSeditor#">)
</cfquery>
<cfquery name="qFetchID" datasource="#APPLICATION.dataSource#">
        SELECT id
        FROM events
        WHERE title = '#form.title#'
  </cfquery>

<cflocation url="events_RecordView.cfm?id=#qFetchID.id#">

It all looks right to me.. I even tried making it throw errors to maybe trip up the real problem and it's not working. Obviously.. I'm missing something.

This topic has been closed for replies.
Correct answer -__cfSearching__-

You are using parsedatetime correctly, but not often enough.  It's also appropriate for time fields.

You have another potential logic error.  After you do your insert, your select query could return more than one row.  That being the case, the id you pass to the next page might not be the one you wanted.


You have another potential logic error.  After you do your

insert, your select query could return more than one row. 

That being the case, the id you pass to the next page might

not be the one you wanted.

In CF8+, you can use the result attribute to return the value of automatically generated ID's from simple INSERT statements.

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html

Also, I am wondering about your cfsqltypes. What database are you using?

3 replies

D
Dan_Bracuk
Inspiring
April 30, 2010

Do you have debugging turned on?  Does it show the sql actually running?

Are you using the select query to check to see if the record was inserted?  Just for fun, do a select count(*) before and after and see if the results change.  Also, comment out the cflocation tag so you don't leave the page while you are figuring this out.

Not related to your problem, but you are doing something bad, even though it works.  DateFormat produces a string, but you want a date object.  Coldfusion has a parsedatetime function that will make your page more robust.

C
cfsetNewbieAuthor
Known Participant
April 30, 2010

I think I figured out what the problem is.
When adding a new record, my ID is 0. I don't think my tags are picking it up properly, and making the difference between update, and insert.This is how this part is set off, is there a better way to specify edit, from insert?

<cfif isDefined("Form.RecordID")>

<cfquery datasource="#APPLICATION.dataSource#" dbtype="ODBC">
UPDATE events
SET events.title=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.title#">,

<!--- all my other tables are here --->

events.Body=<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.PDSeditor#">
WHERE ID = <cfqueryparam value="#form.RecordID#" cfsqlType="CF_SQL_INTEGER">
</cfquery>
<cflocation url="events_RecordView.cfm?ID=#Form.RecordID#">

<cfelse>

<cfquery result="yourResults" datasource="#APPLICATION.dataSource#" dbtype="ODBC">
INSERT INTO events
(title, eventDate, eventTime, location, contact, phone, fax, email, URL, sponsor, Body)
VALUES (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.title#">,
        <cfqueryparam cfsqltype="cf_sql_date" value="#ParseDateTime(form.edit1)#">,
        <cfqueryparam cfsqltype="cf_sql_time" value="#form.eventTime#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.location#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.contact#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.fax#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.email#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.URL#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.sponsor#">,
  <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.PDSeditor#">)
</cfquery>
<cfquery name="qFetchID" datasource="#APPLICATION.dataSource#">
        SELECT id
        FROM events
        WHERE title = '#form.title#'
  </cfquery>

<cflocation url="events_RecordView.cfm?id=#qFetchID.id#">
</cfif>

I also read the live docs on ParseDateTime, am I using it correctly? Nice idea, thank you.

D
Dan_Bracuk
Inspiring
April 30, 2010

You are using parsedatetime correctly, but not often enough.  It's also appropriate for time fields.

You have another potential logic error.  After you do your insert, your select query could return more than one row.  That being the case, the id you pass to the next page might not be the one you wanted.

A
Adam Cameron.
Inspiring
April 29, 2010

I dunno if this is relevant but, from the docs:

History

[...]

ColdFusion MX:


Deprecated [...] all values of the dbtype attribute except query. They do not work, and might cause an error, in releases later than ColdFusion 5.

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fae.html

(So Adobe have kinda misunderstood what "deprecated" means here, but you get the point).

Also - and not related - as someone else alluded to, you should pass dates to your DB driver as dates, not as strings.

And you should parameterise your SELECT query too.  Although I suspect that one is just for debuggging purposes.  Still: it's a good habit to get into to just automatically always param dynamic values in your SQL statements.

--

Adam

A
Anonymous
April 29, 2010

Just a hunch, but try using CreateODBCDate() for the event date.

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