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

[Dan_Bracuk]

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.

[KathyJoS]

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!

[cf_dev2]

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>

[Sankalan]

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

[Sankalan]

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

[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

[cf_dev2]

> I know you're probably not yet running CF8, but here's a possible reason to upgrade:

Now that's a great feature!

[KathyJoS]

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

[KathyJoS · Accepted solution]

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

[Newsgroup_User]

now that is absolutely ridiculous! why would the extra pair of " be
needed there???
--

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