2. ASPRunnerPro General questions
  3. Auto Number a Non Key Field?
This topic is locked

Auto Number a Non Key Field?

 		6/4/2007 11:25:11 AM
ASPRunnerPro General questions
S
 stealthwifi author

Hello,
I have a SQL Express DB set up and need one of the fields to be an auto number, in SQL I have the Identiy set for the primary key so that is the auto number field.
This other field that needs to be autonumber is not a unique Identifier at all, some of our cutomers use the field others don't.

The purpose is to help the data entry people not enter duplicate values.

In SQL it does not allow me to set multiple Identity's so I figured in AspRunner I would have to add some code to the After Update Event.

Am I going in the right direction with this or way off?
Thanks in advance!
Cheers,

Stealth-
Sergey Kornilov admin 6/4/2007

Yes, you can use BeforeAdd/BeforeEdit events for this purpose.
S
 		stealthwifi author 6/7/2007

Would something like:
[codebox]Function BeforeAdd(dict)

Field1 = rs.Field1 + 1

[/codebox]
be close?
I have been looking for a few days now and am having trouble coming up with the code to auto increment so the values dont repeat.
Thanks again for all your help!
Cheers,

Stealth-
Sergey Kornilov admin 6/7/2007

In BeforeAdd event you need to execute a SQL query that returns the max existing value in this column and add 1 to it.

set rstmp = Server.CreateObject("ADODB.Recordset")

rstmp.open "select max(field1)+1 from " & strTableName, dbConnection

dict("Field1") = rstmp(0)

rstmp.close : set rstmp = nothing
S
 		stealthwifi author 6/8/2007

Absolutly perfect!
<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=18686&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />
Cheers,

Stealth-