How do I extract the last inserted record. 2

[wniatbms]

Good day.

Let's say in a table there is a column "OrderInLine". On a form I want to display the very last number inserted in that column so the user (me) can input the next number.

I'm using asp and an MS Access 2000 DBase.

Thank you in advance.

William.

 

[FesterSXS]

If the table has an "id" column that autoincrements then you could do it like this:

strSQL = "SELECT TOP 1 OrderInLine FROM myTable ORDER BY id DESC;"

Tony

http://www.SpiraxSarco.com

 

[wniatbms]

Thank you so much Tony.

I have something else to ask.

This column "OrderInLine" decides the order of items on a menu. So 1 will be at the top and say, 85 will be last. Now, let's say I want to insert an item in the middle, 44. How do I get the numbers to recalculate to fit in a new number instead of me having to retype 45 to 86? Is it even possible?

Thank you so much again.

William

 

[shaddow]

I think that the best way of getting last id inserted it's

sql="select top 1 * from table"
rs.Open sql,objCOnn,3,3
rs.AddNew
...
rs.Update
lastID=rs("mytable_id")

Do you have something like this for your question...
1,2... 43,44,45... 85
and you want to replace or add a new 44 position(and then the oher ones will increase with 1 like 44,45(44)...85,86(85))?
or that position it's a new one(it wasnt taked before)

________
George, M
Searches(faq333-4906),Carts(faq333-4911)

 

[Tarwn]

It actually wouldn't be to hard to insert a new value with an order value in the middle of existing ones. 2 sql statements, one to shift everything tat needs to be shifted, and one to add the new value in:

'assuming there is a connection string called conn_str
'   also assuming the new order value is in Request.Form("newOrder")
Dim conn_obj, newOrder

'grab the newOrder value
newOrder = Request.Form("newOrder")

'create and open the connection
Set conn_obj = Server.Createobject("ADODB.Connection")
conn_obj.Open conn_str

'shift everything down to make room for the new value
conn_obj.Execute "UPDATE MyTable SET OrderInLine = OrderInLine + 1 WHERE OrderInLine >= " & newOrder

'insert new record
conn_obj.Execute "INSERT INTO MyTable(OrderInLine,otherField, anotherField, etc) VALUES(" & newOrder & ",'" & Request.Form("otherField") & "',etc)"

'clean up
conn_obj.Close
Set conn_obj = Nothing

So basically all you have to tell it is to increment evey OrderInLine value that is greater than or equal to your new one, then insert your new record. No muss no fuss

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/