...

View Full Version : Selecting max(value) immediately after Insert (Access)



riotgrrl
07-20-2004, 09:20 PM
If someone could explain this to me, words could not express my appreciation!

I have a page, which, depending on the information submitted, inserts that info into 1 of 3 tables. When the page reloads, I write out that information to the screen. All rows are linked by an ID number.

I do a select immediately after the insert to get all the rows (across the 3 tables) belonging to that ID number.

The problem is, it does not return the most recently inserted record, so I am always 1 behind. For instance, I have just inserted record 5, and it returns 1 to 4, even though I can see record 5 sitting there in table.

Could someone tell me why this is?

:confused:

raf
07-21-2004, 10:10 AM
welcome here!

maybe some code ...

riotgrrl
07-21-2004, 06:22 PM
set insertTextBlock = Server.CreateObject("ADODB.Command")
insertTextBlock.ActiveConnection = MM_Equine_STRING
insertTextBlock.CommandText = "INSERT INTO Stallion_Text (profile_seq, object_rank, text_block) VALUES (" & request.querystring("seq") & ", " & objectRank & ", '" & trim(replace(request.form("textBlock"), "'", "''")) & "') "
insertTextBlock.CommandType = 1
insertTextBlock.CommandTimeout = 0
insertTextBlock.Prepared = true
insertTextBlock.Execute()

Dim getText
Dim getText_numRows

Set getText = Server.CreateObject("ADODB.Recordset")
getText.ActiveConnection = MM_Equine_STRING
getText.Source = "SELECT object_rank, text_block from Stallion_Text where profile_seq = " & request.querystring("seq") & " ORDER BY object_rank;"

getText.CursorType = 0
getText.CursorLocation = 2
getText.LockType = 1
getText.Open()

getText_numRows = 0

There are actually 3 select statements, which pull all info on that profile_seq, then I have an algorithm which displays the info out of all 3 records sets by walking through them based on object_rank... as I said, the problem is that the select statement does not retrieve the most recently added record. So if records 1 -5 already exist, and I have just inserted 6, it will only show 1 - 5.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum