AutoNumber
-
I have a asp.net form which is for orders with a field for numbers in it.
What I want is when a user raises a new order the Order number field will be populated automatically with the next order number. I want this order number to be incremented on the database table. The order number is of the form 05/001 and when the next order is raised it will be 05/002. 05 is the year.
Anyone any ideas how to do this.
Thanks.
-
I have actually succeeded in getting some of this working, here is the code, it is not working entirely as well as it should:
I have the following as asp.net code in page load:
If Not Page.IsPostBack Then
Dim Cmd As New SqlCommand("select dbo.getID(getDate())", cn)
cn.Open()
Dim dr As SqlDataReader = Cmd.ExecuteReader()
'dr = Sql400Accessor.ExecuteReader(constr.ToString(), sqlqry.ToString(), CommandType.Text, "")
If (dr.HasRows) Then
Do While dr.Read
OrderNum.Text = dr(0).ToString().Trim()
Loop
Else
OrderNum.Text = "Cannot Get Details"
End If
dr.Close()
cn.Close()
cn = Nothing
End If
And the following is the function:
CREATE FUNCTION GetID (
@currDate datetime -- cannot execute getdate in UDF
)
RETURNS VARCHAR(10) AS
BEGIN
Declare @retID varchar(10) --- id to be returned
Declare @maxID int
Declare @newint int
-- get the max orderID
Select @newint = COALESCE( max([OrderID]),1) from [Orders] -- if table has no ID
set @retID = CAST (Year(@currDate)as varchar(5)) +
-- add filler of "00" to start of string if new int < 9
(CASE WHEN @newint < 10 THEN '/' + '00' + CAST(@newint as VARCHAR)
-- add filler of "0" to start of string if new int < 99
WHEN @newint < 100 THEN '/' + '0' + CAST(@newint as VARCHAR)
ELSE '/' + CAST(@newint AS VARCHAR)
END)
RETURN @retID
END
My Table is called Orders and the column is OrderID and I am writing the number that is generated to this column, but I am also reading the values from there to increment.
When I go to generate a second record I keep getting the error: Cannot Get Details.
Does anyone know what I am doing wrong, it is fine for generating the number the first time but after that I get the error.
Thanks.
-
I do something similiar (format YYYYnnnnn) but it is done in a VB6 App.
Basicly I have a table with one record that has the last number used. I read in the number, check to see if it is the same year. If its the same year, I add one to the number and save it back to the table. If not, I use the new year and make it YYYY00001.
There can be a problem if you have a lot of people adding rows at the same time. Only read it in when you are ready to store it.
-
Your approach will work fine when you are the only person testing it, when you get the bugs out. But the nature of ASP apps is that you may have two people placing an order at the same time, so they will both get identical order numbers.
The simplest approach to use would be to commit the order number when you generate it, so that it cannot be used again. You could have a small table that tracks the last order number issued, incrementing it every time a request for a number is made. You'll want to use locks on that table during the critical part, generating and saving the number, so there are no concurrency issues.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply