korrea301
Posts: 3
|
| Posted: 05/20/2006, 2:41 PM |
|
I'm trying to use either of these to get the last id of a mysql database for inserting into another table, i'm using asp.
I'm trying to place code in the AfterInsert(Sender) function
nothing seems to work though.
Some things I've tried:
Dim LastID
Set Connection = New clsDBConnection2
Connection.Open
LastID = CCDLookup("id","Customers","Customers=mysql_insert_id()",Connection)
LastID = "SELECT mysql_insert_id() FROM Customers"
LastID = "Select LAST_INSERT_ID()"
Ive tried these in various different ways, I'm clueless though.
Anyone help??
|
 |
 |
Edd
Posts: 547
|
| Posted: 05/20/2006, 6:04 PM |
|
Try
LastID = CCDLookup("Max(id)","Customers","",Connection)
Not the best method in a multi-user environment unless to have something else from the form that can make the record selection unique.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
marcwolf
Posts: 361
|
| Posted: 05/20/2006, 11:41 PM |
|
This is always a difficult one - getting the last inserted ID in a universal environment.
One can use DB specific code but when you move to a different DB platform it all comes apart.
What we do is this.
We create an application variable called NexNum and increment it for each insert. i.e.
Function NextNumber()
Dim lngNextNum
Application.Lock
lngNextNum = clng(Application("NextNum")) + 1
if lngNextNum < 5000 then lngNextNum = 5000
if lngNextNum > 20000 then lngNextNum = 5000
Application("NextNum") = lngNextNum
Application.Unlock
NextNumber = lngNextNum
End Function
Now we use the Application.Lock so that only one person can update the number at a time.
Next in each table we have a field called Version_no.
Before any records are inserted we call the NextNumber to get the next number in sequence and put it into Version_no
After the record is inserted we can search for the record that has a version_no of NextNum and then retrieve the unique_ID - remebering at the same time to set the Version_no to 0
---------------------------------
We also use the Version_no to ensure that in a multi-user environment that records are not over-written. Each time a record is updated we increment the version_no by 1
So user A get record with Version_no of 4
user B get records with Version_no of 5
User A updates the records, and before saving checks that the version_no still equals 4, and saves it giving a Versions_no of 5
User B makes changes and then tries to save it. The system checks the version_no of the record and sees that is is different from the version_no in memory and so warns the user that the record has been changed by another user
Thus records details cannot be overwritten in a multiuser environment.
I hope this helps.
Dave
_________________
' Coding Coding Coding
Keep Those Keyboards Coding.
Raw Code!!!!!!!
|
|
|
|
Maybe
|
| Posted: 05/21/2006, 8:26 PM |
|
I use for MySQL and works for me:
SQL = "SELECT LAST_INSERT_ID() FROM your_table"
|
|
|
|
marcwolf
Posts: 361
|
| Posted: 05/21/2006, 11:06 PM |
|
Well Maybe
Here is a question.
Your customer decides to move from an external Linux hosting using MySql to an internal hosting using Small Business Server and SQLServer.
Of course you can install MySQl onto the server but if the object of the client is to upsize all of their MS-Access DB's to SQL server and integrate them too - you have problems.
Database specific commands are great.. Until you need to move platforms.
My solution also addresses another aspect too. Preventing data being overwritten by multiple users.
In a Web environment it is not feasonable to lock a reacords when it is read becuse one never knows when the client is going to send information back to the server. With our version_no control system one can check instantly wether a records has been changed and thus not overwriting updated information.
Of course the user of the current session will get a warning and will have to read/modify/update again but it means that any previous changes are preserved.
Hope this helps
Dave
_________________
' Coding Coding Coding
Keep Those Keyboards Coding.
Raw Code!!!!!!!
|
|
|
|
Maybe
|
| Posted: 05/22/2006, 4:03 PM |
|
Well marwolf
That’s all well and good, I was simply adding a comment that SELECT LAST_INSERT_ID() works for MySql which is WHAT the original thread starter was asking for. Yours will work too but it wasn’t what the thread starter was ASKING FOR.
Stops scathing head, and acknowledges the boss marcwolf 
|
|
|
|
WKempees
|
| Posted: 05/23/2006, 1:52 AM |
|
Did anyone (Maybe, Dave) notice that korrea301 was opening a new connection?
That is a funny move if you want to catch the last inserted ID, I think.
For MySQL (repect, dave)
LatstID = CCDLookup("last_insert_id()","Customers","",Your current
Connection)
should work ok, when put in the AfterInsert and using the CURRENT
connection.
Unfortunately i'm not into ASP so syntax might be off.
http://dev.mysql.com/doc/refman/4.1/en/getting-unique-id.html
If the original question had be different, the whole discussion could have
been more interesting.
Walter
"korrea301" <korrea301@forum.codecharge> schreef in bericht
news:6446f8d074b33f@news.codecharge.com...
> I'm trying to use either of these to get the last id of a mysql database
> for
> inserting into another table, i'm using asp.
>
> I'm trying to place code in the AfterInsert(Sender) function
> nothing seems to work though.
>
> Some things I've tried:
> Dim LastID
>
> Set Connection = New clsDBConnection2
> Connection.Open
>
> LastID =
> CCDLookup("id","Customers","Customers=mysql_insert_id()",Connection)
>
> LastID = "SELECT mysql_insert_id() FROM Customers"
>
> LastID = "Select LAST_INSERT_ID()"
>
> Ive tried these in various different ways, I'm clueless though.
>
> Anyone help??
>
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
