Generating an Autonumber - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Generating an Autonumber

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-20 : 07:26:00

John writes "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.

Do you know what I am doing wrong, it is fine for generating the number the first time but after that get error.

Thanks."

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-05-20 : 07:40:07

WHY are you not using the INBUILT function AUTOnumber available to be defined on the SQL column?
You're reinventing a wheel.....save yourself the hassle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources