9 Replies Latest reply on Mar 25, 2009 6:41 AM by CFmonger

    Problem inserting a record. Please help

    CFmonger
      I can't get my insert statement to work properly. I keep looking it over and it is written correctly, but it doesn't fire properly and doesn't throw and error. it just doesn't do anything.

      I am attaching my code for the 2 pages. page 1 is my form, this form is to do both update an existing record, and insert a new record. Right now, on this page, it handles the existing record and edit fine, even updates it properly on the action page. But the new record is not posting. The 2nd page I am posting is my action query. I don't think the problem is there, I think it is in my form.

      Can anyone please help me get this to work properly?

      Thank you.
        • 1. Re: Problem inserting a record. Please help
          The ScareCrow Level 1
          The insert code will never be executed.

          On your form page you have a cfparam that declares the url.RecordId with a default of zero.
          So the form field will always exist, thus in the action page you are trying to update a record with id of zero.

          Ken
          • 2. Re: Problem inserting a record. Please help
            CFmonger Level 1
            Ok, I took that out, now my action page is saying the my recordID is not defined, but it is.

            This is what I am using now:

            <cfset admin = "False">

            <cfparam name="variables.ID" type="integer" default="#url.RecordID#">
            <cfparam name="variables.Fname" default="">
            <cfparam name="variables.Lname" default="">
            <cfparam name="variables.userName" default="">
            <cfparam name="variables.password" default="">
            <cfparam name="variables.email" default="">
            <cfparam name="variables.admin" default="">

            <cfif url.RecordID GT 0>
            <cfquery name="useRec" datasource="#APPLICATION.dataSource#">
            SELECT user.Fname, user.Lname, user.userName, user.password, user.email, user.admin, user.ID
            FROM user
            WHERE user.ID =<cfqueryparam value="#URL.RecordID#" cfsqltype="cf_sql_integer">
            </cfquery>

            <cfif useRec.RecordCount EQ 1>
            <cfset variables.RecordID = useRec.ID>
            <cfset variables.Fname = useRec.Fname>
            <cfset variables.Lname = useRec.Lname>
            <cfset variables.userName = useRec.userName>
            <cfset variables.password = useRec.password>
            <cfset variables.email = useRec.email>
            <cfset variables.admin = useRec.admin>
            </cfif>
            </cfif>
            <head>
            <body>
            <cfoutput>
            <cfform action="action.cfm" method="post" name="useRec" id="useRec" enctype="multipart/form-data">
            <input type="hidden" name="variables.ID" value="#url.RecordID#">
            <input type="text" name="Fname" value="#variables.Fname#" maxLength="150">
            <!--- more inputs and submit buttons here --->
            </cfform></cfoutput>
            </body>

            this is my action page now:

            <cfif not IsDefined("FORM.admin")>
            <cfset IsAdmin= 0>
            <cfelse>
            <cfset IsAdmin= 1>
            </cfif>
            <cfif url.RecordID eq 0>
            <cfquery datasource="#APPLICATION.dataSource#" dbtype="ODBC">
            INSERT INTO user
            (Fname, Lname, userName, password, email, admin)
            VALUES (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Fname#">,
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Lname#">,
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.userName#">,
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.password#">,
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.email#">,
            <cfqueryparam value="#IsAdmin#" CFSQLType="CF_SQL_INTEGER">)
            </cfquery>
            <cflocation url="indexUser.cfm">
            <cfelse>
            <cfquery datasource="#APPLICATION.dataSource#" dbtype="ODBC">
            UPDATE user
            SET user.Fname=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Fname#">,
            user.Lname=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Lname#">,
            user.userName=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.userName#">,
            user.password=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.password#">,
            user.email=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.email#">,
            user.admin =<cfqueryparam value="#IsAdmin#" CFSQLType="CF_SQL_INTEGER">
            WHERE ID =<cfqueryparam value="#form.RecordID#" cfsqlType="CF_SQL_INTEGER">
            </cfquery>
            <cflocation url="indexUser.cfm?RecordID=#Form.RecordID#" addtoken="no">
            </cfif>

            I had it working... but then I made some other changes and it made things work better, until this issue. here is my error:

            Element RECORDID is undefined in URL.
            The error occurred on line 17 action.cfm.

            What am I doing wrong. This is driving me crazy, Not like it is my first form I wrote, I am missing something and can't figure it out.
            • 3. Re: Problem inserting a record. Please help
              Daverms Level 3
              Hi,

              Are you actually passing the recordid from the initial page?..
              • 4. Re: Problem inserting a record. Please help
                CFmonger Level 1
                yes, this is my first page form button that sends you to my edit page:

                <cfform action="Action.cfm" name="myform" method="post">
                <cfinput type="hidden" name="RecordID" value="#ID#">
                <!--- this is a submit button only --->
                </cfform>
                • 5. Re: Problem inserting a record. Please help
                  Daverms Level 3
                  Hi,

                  Does your URL contain the "RECORDID" string anywhere after "?" (or) "&" symbols?..

                  • 6. Re: Problem inserting a record. Please help
                    CFmonger Level 1
                    no, I was passing it through form variables, but if I use <cfif isDefined ("form.RecordID")> it still says that isn't defined.
                    • 7. Re: Problem inserting a record. Please help
                      Dan Bracuk Level 5
                      what happens when you cfdump your form? Is there a recordid?
                      • 8. Re: Problem inserting a record. Please help
                        CFmonger Level 1
                        I found it, I dumped a couple of different variables, and with the changes I made, I got form.RecordID to pass. Had to step away and think for a little while.

                        Thank you for the help.
                        • 9. Re: Problem inserting a record. Please help
                          CFmonger Level 1
                          I found it, I dumped a couple of different variables, and with the changes I made, I got form.RecordID to pass. Had to step away and think for a little while.

                          Thank you for the help.