14 Replies Latest reply on Apr 30, 2010 5:48 PM by -==cfSearching==-

    cfquery problem with insert.

    cfsetNewbie Level 1

      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.

        • 1. Re: cfquery problem with insert.
          mdarchives

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

          • 2. Re: cfquery problem with insert.
            Adam Cameron. Level 5

            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-7f ae.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

            • 3. Re: cfquery problem with insert.
              Dan Bracuk Level 5

              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.

              • 4. Re: cfquery problem with insert.
                cfsetNewbie Level 1

                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.

                • 5. Re: cfquery problem with insert.
                  Dan Bracuk Level 5

                  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.

                  • 6. Re: cfquery problem with insert.
                    -==cfSearching==- Level 4

                    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?

                    • 7. Re: cfquery problem with insert.
                      mdarchives Level 1

                      -==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.

                      • 8. Re: cfquery problem with insert.
                        -==cfSearching==- Level 4

                        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?

                        • 9. Re: cfquery problem with insert.
                          Dan Bracuk Level 5

                          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.

                          • 10. Re: cfquery problem with insert.
                            mdarchives Level 1

                            -==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.

                            • 11. Re: cfquery problem with insert.
                              -==cfSearching==- Level 4

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

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

                               

                              Are those types valid for MS Access?

                              • 12. Re: cfquery problem with insert.
                                -==cfSearching==- Level 4

                                @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

                                • 13. Re: cfquery problem with insert.
                                  cfsetNewbie Level 1

                                  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!

                                  • 14. Re: cfquery problem with insert.
                                    -==cfSearching==- Level 4

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