sql insert order

[xue]

thanks hblackorby for previous question.
I found the problem that sql table may insert every record above the first record or insert every record below the first record. it seems changes upon me restart my computer. that is very bad for my code since I always need to retrieve the last record for some assignment. but the inserting each record above the first record is going to ruin everything. if you know there is some way that I can enforce sql table to insert record one after each other from top to down order, can you tell me? if not, can you tell me if there is a way that I can assign unique identifers on ASP code, so I don't have to relie on sql table and retrieve it from sql table after insertion? thank you very much.

 

[vasah20]

If you are running SQL Server, then the problem is easily alleviated using the @@IDENTITY variable. What that will do is pull the primary key of the record you last inserted, allowing you to do what you want with it. of course, if you are going to do more DB interaction, then you should consider writing a stored proc to handle this all.

If you are using MS Access, unfortunately there's really no unique identifiers in ASP code, well at least none that I'm aware of.

My question is this, though, you are saying that inserting above the other records is going to ruin your table layout. Well, why don't you create a primary key on your table, and have it set to autoincrement. Since the primary key is autoincrementing, you never have to worry about where the record gets inserted. The newest record will always be the largest number in the primary key field. Then you can control the output using the ORDER BY clause. Even if you are using Access forms or some other application to access the Database, you can still add the ORDER BY conditional, and that should take care of listing it chronologically.

If this doesn't seem feasible (eg: you anticipate more records than a Long can handle), then have you considered creating a timestamp column, then inserting the timestamp along with the rest of the fields, again allowing you to sort by date and/or time.

Just some thoughts on the subject.
good luck

 

[Guest_imported]

I am using MS SQL SERVER. insert everything above the last record is going to ruin my code is because that I alway need the last record to assign to the variable CartID. CartID is going to be a cookie that assign to the customer once he add to cart something. I assume that records are going to be added one after another, so the last one is going to be the lastest new customer's cart id(which is the id field in cart table), and then loop through the whole thing to get the last record and assign that one to the lastest customer's cart cookie(cartID). but wierd is, sometimes after a while, the sql record start to insert differently. the lastest record is jumped to the beginning of the record instead of the bottom where it should be, and leaving me assigning the another's customer's cookie(this id field is at the bottom now) to the coming new customers. so he see something in his shopping cart that he did not add. I checked identifer on id field and it is increment one by one. the id field is also the primary key. I don't known if the problem occurs when the table has too many record in it, then it starts to insert at the top. last time when it happened, it has only about 30 records. if that is the case, can you give me a solution. what can I do with @@IDENTITY ? or how can I write store procedure on enforcing the order? I thought about putting a time field on the cart table, but what if 2 different people(new customers) add to shopping cart at the exact same time? your help is despitely needed. thank you very much.