jonnyboyo
Posts: 15
|
| Posted: 05/07/2004, 3:20 AM |
|
I've been using codecharge for a while now and been successfully generating reference numbers in a second field everytime an article is submitted to the database. I'm now using CCS with mysql and I need a second field to generate numbers on an autoincrement basis. As two auto fields aren't allowed in mysql, I'm having a problem in placing the code in the right place in ccs to make it work. I've been using the UPDATE Articles SET id=LAST_INSERT_ID(id+1); code but I seem unable top place where it works.
It simply needs to generate a number evrytime an article is submitted and can be anything from 1 to a zillion.
I know there's a lot of clever CCS users out there, can anybody help?
|
 |
 |
peterr
Posts: 5971
|
| Posted: 05/07/2004, 8:54 AM |
|
Where have you placed your code for updating the Articles? I think that you could put it in the After Insert event and it should work. And how did you obtain "LAST_INSERT_ID"?
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Johnnyboyo
|
| Posted: 05/07/2004, 9:52 AM |
|
I've tried inserting the code into the After Insert Event and it almost works. It's inserting 1 and updating the last insert by 1. So a record with the id no 50 is 51 after the next insert and the new record is 1. I'm know I'm missing something from the code but I don't know what.
Here's what I'm using, this is pure guesswork as I don't write sql code.
Dim SQL
Dim Connection
Dim ErrorMessage
SQL = "UPDATE Articles SET id=LAST_INSERT_ID(id+1)"
Set Connection = New clsDBA4gzine
Connection.Open
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
Connection.Close
Set Connection = Nothing
On Error Goto 0
|
|
|
|
peterr
Posts: 5971
|
| Posted: 05/07/2004, 10:24 AM |
|
The LAST_INSERT_ID obtains the last inserted autoincremented value, but as you previously wrote your second key is not autonumeric. Therefore you may need to use some custom code that will lookup the largest id value in your table and then use it for the update.
For example
LastKey = CCDLookup(MAX(id"),"Articles","user_id="&Session("UserID")...
I'm not 100% sure though that I understood what you need.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
jonnboyo
|
| Posted: 05/07/2004, 10:34 AM |
|
I'll try this. Quite simply the database has an autoincrement field which is ArticleID and works fine. I don't want to use this as a reference number so I'm using a second field called id to generate a second sequential number. In access it works fine as I can set two auto fields so everytime something is added to the database it generates the two numbers.
The id field needs to get a permanent ref number for each article posted and be generated everytime.
I suppose at a push I could use the auto ArticleID field as a reference.
|
|
|
|
