cfquery problem with insert.

[cfsetNewbie]

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.

[Guest]

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

[Adam Cameron.]

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

[Dan_Bracuk]

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.

[cfsetNewbie]

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.

[Dan_Bracuk]

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.

[-__cfSearching__- · Accepted solution]

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?

[Guest]

-==cfSearching==- wrote:

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.ht ml

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

Thanks for the tip about the result attribute. I was unaware of it and that would come in handy with a project I'm working on.

[-__cfSearching__-]

that would come in handy with a project I'm working on.

It will come in handy here. So you do not get the wrong ID from your INSERT statement. With your current code, that is very likely.

What database are you using?

[Guest]

-==cfSearching==- wrote:

that would come in handy with a project I'm working on.

It will come in handy here. So you do not get the wrong ID from your INSERT statement. With your current code, that is very likely.

What database are you using?

@cfSearching -- I'm not the original poster. Sorry for the confusion.

[-__cfSearching__-]

@cfSearching -- I'm not the original poster. Sorry for the

confusion.

Ah, okay. I am responding via email and did not see the original post

[cfsetNewbie]

I'm sorry, was out of the office. Yes it is an access DB.

I did get it to work. I changed my tags from isDefined to this

<cfif Form.RecordID eq 0>

because I found, using isDefined, well it is defined, it was passing a 0 making my code think it was an update and not an insert. Once I changed this, and switched the order of the insert / update if statement, it worked like a charm.

Thank you for all the help!

[-__cfSearching__-]

There is still the issue of potentially retrieving the wrong ID from the INSERT.

[Dan_Bracuk]

The 1st post mentions that it's an access database.  If your question about the datatypes were referring to the data and time, at least two other database types have date only, time only, and date-time - redbrick and db2.

[-__cfSearching__-]

... at least two other database types have date only,

time only, and date-time - redbrick and db2.

Are those types valid for MS Access?