Last Row Got Stuck

Hello,

I have a small table to manage orders in my company. When a new order is entered, the script makes use of the last row of the table to find out the last order, increments it and creates the new order number. The problem is, a few days ago the last row got stuck. New insertions to the table all got the same order number and are placed above the last row. Anybody has any idea what's going on?hey
do you have multiple concurrent applciations accesing the table?
any reason you are doing the incremting 'manually' instead of using an auto-inc int?
des|||Hello Des,

Below is the protion of the ASP code that assigns the ordernumber. It is in the form "TPD-mmm-nnn" where mmm is the mont (like 002 for Feb) and nnn is the order number (like 001,002,etc). The if clause tries to reset the order number back to 001 if the month starts anew.

...
set rs = Server.CreateObject("ADODB.recordset")
rs.CursorType = 3
selectstr="SELECT * FROM konuttekliflist"
rs.open selectstr, conn
rs.movelast
tekno=rs.fields("teklifno")
tekay=mid(tekno,5,3)
teklifindex=right("00" & right(tekno,4)+1,3)
monthnow=right("00" & month(now()),3)
if tekay=monthnow then
teklifno1="TPD-" & monthnow & "-" & teklifindex
else
teklifno1="TPD-" & monthnow & "-001"
end if
sql="INSERT INTO konuttekliflist ("
...

And yes, the application is used concurrently by 4 people.

Thanks to your interest and help.|||So ID is generated with datenow...are the four apps runnning on sep machines to db server & eachother? are all their dates/times
in perfect sync? otherwise this could cause problems.

If two orders come in at the same time, they could potentially conflict?

i see you select all into a recordset - this could cause locks that conflict with your other apps. why not select(max) substring(mmm)+subsrting(nnn) into local variable, instead of iterating through recordset?
How does it cross over the year?

how about generating the ID in sql with a computed column, using its own date and increment, then at least you dont have four different things competing for the next value? ie.gen id's centrally

des

Last row ?

Hello Team

How can i get a value from a column in the last row

for ex: select orderno from orders .......... but the last row ?

Thanks lot

Greetings,

SQL Server has no concept of last row, or first row or middle row for that matter.

To determine the last row you need to know by what criteria. If your Order Number column is sequential or your table contains a datetime column then you can get a close approximation by using TOP and ORDER BY.

SELECT TOP 1 OrderNo FROM Orders ORDER BY OrderDate DESC

If there is no column in the table from which you can determine the correct ordering then you're stuck.

--

Regards,
Neale NOON

|||

There is one more work around available to get the last record..

if your table has 3 columns,

declare @.col1 as varchar(100);
declare @.col2 as varchar(100);
declare @.col3 as varchar(100);

Select @.Co1l=Col1,
@.Col2=Col2,
@.Col3=Col3
From
Tablename;

Select @.Col1 as Col1, @.Col2 as Col2, @.Col3 as Col3;

So here you need not to use any order by clause. It always fet the last row.

|||If you are really just looking for the last row, reverse your sort order and use SELECT TOP 1 ... That will get you the first row in the reversed order, ie last row.

Last insert id

Hi

I am trying to import several master detail records from files to ms sql
server.
I have orders file and order_items file that has several rows for each
order.
If I insert programmatically these records how can find out which order ID
was the last inserted, so that I can attach the subsesquent row items to a
proper order.

I am quite new to ms sql server. I have used mysql a lot and there I could
use mysql_insert_id to find out the last autoincremented filed number.
I am looking for a similar method for ms sql server 2000.

TIA
George"George Hill" <ghill@.NOSPAM.com> wrote in message
news:LUf8b.5984$ZB4.5409@.reader1.news.jippii.net.. .
> Hi
> I am trying to import several master detail records from files to ms sql
> server.
> I have orders file and order_items file that has several rows for each
> order.
> If I insert programmatically these records how can find out which order ID
> was the last inserted, so that I can attach the subsesquent row items to a
> proper order.
> I am quite new to ms sql server. I have used mysql a lot and there I could
> use mysql_insert_id to find out the last autoincremented filed number.
> I am looking for a similar method for ms sql server 2000.
> TIA
> George

Assuming that you're using an IDENTITY column to generate the IDs, then the
scope_identity() function will give the last ID inserted. There are also
ident_current() and @.@.identity - see Books Online for an explanation - but
scope_identity() is probably the one you want.

Simon