Paul
|
| Posted: 01/08/2002, 2:15 AM |
|
I know this is a simple question, i am using ASP with mySQL:
On insert i want to check the highest current vacancy_id then add 1 to this to make a new vacancy_id for the inserted record.
Thanks in advance
|
|
|
 |
Nicole
|
| Posted: 01/08/2002, 3:23 AM |
|
Paul,
MySQL allow you mark fields as 'auto_increment' (like autonumber in Access). In this case when the new record is inserted you don't need to build the new inserted value manually.
|
|
|
|
Nicole
|
| Posted: 01/08/2002, 3:23 AM |
|
Paul,
MySQL allow you mark fields as 'auto_increment' (like autonumber in Access). In this case when the new record is inserted you don't need to build the new inserted value manually.
|
|
|
|
Paul
|
| Posted: 01/08/2002, 3:28 AM |
|
MySQL does indeed include an auto increment feature, but I would like to do this manually by select the highest vacancy_id value in the table and then adding 1 to it.
How would this be done... potentially this could lead to more flexibility in the future...
Thanks in advance
|
|
|
|
Nicole
|
| Posted: 01/08/2002, 4:25 AM |
|
Paul,
once you add primary key field to the record form, mark it as 'Key' field and set its type to Hidden, CC expects it to be autonumber and doesn't include it to generated sql statement. That's why you should add custom code to BeforeExecute event in order to get the new value of primary key and modify sql query. E.g.:
select case sAction
case "insert"
max_id = dLookUp("table_name", "max(primary_key_name)", "1=1")
next_id = max_id+1
sSQL = "insert into table_name primary_key_field, field1, field2 values ("& ToSQL(next_id, "Number") &", "& ToSQL(fldfield1, "Number") &", "& ToSQL(fldfield2, "Text") &")"
end select
|
|
|
|
|
