10 Replies Latest reply on Aug 12, 2007 6:50 PM by Newsgroup_User

    get next id

    KathyJoS

      Hi there,

      When inserting a new record I want to assign the next property_id from the property.property_id table. The query:

      <cfquery name="GetNewID" datasource="#application.DSN_Name#">
      SELECT top 1 property_id
      FROM property
      ORDER BY property_id desc
      </cfquery>

      When I test it works swell.

      However the error appears on the following line of code:
      <cflocation url="add_property_confirm.cfm?property_id=#GetNewID.property_id#">

      The error states that property_id is not defined in GetNewID.

      The property_id table is set for auto number and ident and using SQL 2000.

      I have no clue where to start to troubleshoot this - as an interesting quirk this code was working for years, the client pulled the site down, then we reinstated it - nothing changed in the database or the code....a mystery to me.

      Thanks in advance,

      Kathy
        • 1. Re: get next id
          Dan Bracuk Level 5
          Try changing this
          <cfquery name="GetNewID" datasource="#application.DSN_Name#">
          SELECT top 1 property_id
          FROM property
          ORDER BY property_id desc
          </cfquery>

          to this
          <cfquery name="GetNewID" datasource="#application.DSN_Name#">
          SELECT max(property_id) property_id
          FROM property
          </cfquery>

          Also, for this code
          <cflocation url="add_property_confirm.cfm?property_id=#GetNewID.property_id#">

          make sure there is no whitespace.
          • 2. Re: get next id
            KathyJoS Level 1
            Hi Dan - thanks for the quick response! I have changed the code, ensured there is no white space in :

            <cflocation url="add_property_confirm.cfm?property_id=#GetNewID.property_id#"> - but still receive an error on this string of code. Any other suggestions?

            Thanks again!
            • 3. Re: get next id
              cf_dev2 Level 1
              Dump the cfquery. Does it contain a value?

              <cfdump var="#GetNewID#">

              Are you trying to get the new id from an insert statement? If so you should use scope_identity()

              <cfquery name="GetNewID" ...>
              SET NOCOUNT ON
              INSERT INTO YourTable (....Columns...)
              VALUES (......)
              SELECT SCOPE_IDENTITY() AS Property_ID
              </cfquery>

              • 4. Re: get next id
                Sankalan Level 1
                What error did you get this time after making changes suggested by Dan?
                Is it the same error this time?

                If so then just dump <cfdump var="#GetNewID#"> and see is property_id is there or not.

                Thanks
                • 5. Re: get next id
                  Sankalan Level 1
                  One more thing.

                  I think SCOPE_IDENTITY() as suggested by cf_dev2 will be safe to use if your application is accessed by multiple users.

                  Say for example: in between your INSERT and SELECT top 1 / SELECT max(property_id) statement, if some other user inserts one record into property table then your select query will pick the property_id for the other user.

                  So I think you need to give a thought as per your code logic.

                  Thanks
                  • 6. Re: get next id
                    rich.leach
                    I know you're probably not yet running CF8, but here's a possible reason to upgrade:

                    http://www.forta.com/blog/index.cfm/2007/7/6/ColdFusion-8-Can-Return-Identity-Values

                    Rich
                    • 7. Re: get next id
                      cf_dev2 Level 1
                      > I know you're probably not yet running CF8, but here's a possible reason to upgrade:

                      Now that's a great feature!
                      • 8. Re: get next id
                        KathyJoS Level 1
                        Hi to all -

                        First thank you for all your suggestions - I've implemented all of them and still receive the original error, and a new one, Variable GetNewID is undefined.

                        I have no clue where to go from here - as you can tell I'm new to CF! Again - it WAS working and now it's not.

                        Kathy
                        • 9. Re: get next id
                          KathyJoS Level 1
                          Hey - I figured it out!!!!

                          The piece of code: <cflocation url="add_property_confirm.cfm?property_id=#GetNewID.property_id#">


                          Needed double quotes: <cflocation url="add_property_confirm.cfm?property_id=#"GetNewID.property_id"#">

                          It's now working as it should. What a very small error that created a very large problem.

                          Thanks to everyone for your input - I truly appreciate the time and thought you gave my situation.

                          Take care,

                          Kathy
                          • 10. Re: get next id
                            Level 7
                            now that is absolutely ridiculous! why would the extra pair of " be
                            needed there???
                            --

                            Azadi Saryev
                            Sabai-dee.com
                            http://www.sabai-dee.com