Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: 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?

  2. #2
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102
    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

  3. #3
    Join Date
    Feb 2004
    Posts
    2
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102
    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

Posting Permissions

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