Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Jul 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting max(value) immediately after Insert (Access)

    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?


  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    welcome here!

    maybe some code ...
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    New to the CF scene
    Join Date
    Jul 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code

    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •