AUTONUMBER SCRIPT

[kingjjx]

Hi, can somebody help me. I want a short script that will automatically generate a number. Basically like autonumber since I can not make my primary key datatype=autonumber so I thought doing this short script would be a good way to input a primary key and I couLd just set datatype od my primary key to text/num.

Please someone help me. I have no idea how to write this script.
Thank you
-jon

 

[Silvano]

if possible, set datatype of the field to number;
now, to find out what is the highest value you already have in the database use this:

cfquery name="queryName"...
SELECT Max(primKeyFieldName) AS oldKey
FROM tableName
...

then you can simply add 1 to get newKey value that will be unique:

cfset newKey = queryName.oldKey + 1
Sylvano
dsylvano@hotmail.com

 

[kingjjx]

Where in my script do i add these:

cfquery name="queryName"...
SELECT Max(primKeyFieldName) AS oldKey
FROM tableName
...
DO I PUT A </CFQUERY> AFTER THIS ONE????

cfset newKey = queryName.oldKey + 1 <<-WHERE DO I PUT THIS ?

THANK YOU
IM JUST A NEWBIE . THANKS FOR ALL YOUR HELP

 

[kingjjx]

WHERE DO I PUT THIS IN THE SCRIPT ???? ( I GOT THE 1ST PART ALREADY)

cfset newKey = queryName.oldKey + 1

 

[Silvano]

I am assuming that you have table in the database with some values in it; one of the field is numeric datatype and it will be used as primary key; primary key cannot have duplicate values, therefore, we must create unique value; to do that we first create a query to find out what is the highest value in the primare key field; I am asking for the highest because I need only one unique return;

<cfquery name=&quot;queryName&quot; datasource=&quot;dbName&quot;>
SELECT Max(primKeyFieldName) AS oldKey
FROM tableName
</cfquery>

now we are creating absolutly new value:

<cfset newKey = queryName.oldKey + 1>

the newKey is unique number and it does not exist in the primary key field; when iserting new data in the table use that value to create new row

INSERT INTO tableName (primaryKey, userName...)
VALUES (#newKey#, '#userName#'... )

Sylvano
dsylvano@hotmail.com

 

[kingjjx]

HEY, IT GAVE ME THIS ERROR MESSAGE ::: ANY HELP ?


Error Diagnostic Information

An error occurred while evaluating the expression:

newkey = Dynorun.oldkey + 1

Error near line 14, column 7.
--------------------------------------------------------------------------------

Cannot convert to number.

Please, check the ColdFusion manual for the allowed conversions between data types

 

[Silvano]

it means that the data type of the oldkey field of the Dynorun query is text; see if you can open data base and change it to number Sylvano
dsylvano@hotmail.com

 

[kingjjx]

HEY, I FIXED THE PROBLEM ABOUVE .. BUT NOW . .WHEN I CLICK SEND .. IT GIVES ME THIS ERROR ::: ANY HELP ???

Error Occurred While Processing Request
Error Diagnostic Information

An error occurred while evaluating the expression:

#newkey#

Error near line 9, column 23.
------------------------------------------------------------

Error resolving parameter NEWKEY

**************************************
************************************
I THINK IT IS BECAUSE IF THIS

INSERT INTO tableName (primaryKey, userName...)
VALUES (#newKey#, '#userName#'... )

 

[Silvano]

it means that newkey variable is not defined while you are trying to access it in the cfquery; make sure that you have defuned it with:

<cfset newKey = queryName.oldKey + 1>

BEFORE trying to use it in the:

INSERT INTO tableName (primaryKey, userName...)
VALUES (#newKey#, '#userName#'... )

Sylvano
dsylvano@hotmail.com

 

[kingjjx]

HEY .. THANKS A LOT FOR ALL YOUR HELP
FINALLY FIXED.