Incrementing Numbers automatically

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

SQL Server 2000 Forums

SQL Server Development (2000)

Incrementing Numbers automatically

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2005-05-18 : 11:34:09

I have a field in a table which is an integer and I want to be able to increment this by 1 everytime a new order is raised.
The number is of the form 2005/001 and I want to automatically increment this to 2005/002 and so on.
I know that you have to set the field as an identifier but due to the format of the number i.e 2005/001 it is causing problems.
I need the forward slash in for the format the user requested.

Does anyone know how this can be done?

SubPar_Coder
Starting Member

23 Posts

Posted - 2005-05-18 : 11:38:47

You can probably use something like this:

Create Procedure YouStoredProcedureName As
Set Identity_Insert YourDatabase.dbo.Bills ON

insert into Bills (BillNo) select Max(BillNo)+1 From Bills

Set Identity_Insert YourDatabase.dbo.Bills OFF

Jack of all trades, Master of none!

Sully
Starting Member

30 Posts

Posted - 2005-05-18 : 11:50:24

Macca,
How are you storing 2005/001 as an integer?

Stuck in neutral in a pedal to the metal world,
Sully

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-05-18 : 15:38:15

What table is this value in? In the Orders table? What does the table(s) look like (DDE)? Is 2005 the year?

HTH

=================================================================
The surest way to corrupt a youth is to instruct him to hold in higher esteem those who think alike than those who think differently. -Friedrich Nietzsche, philosopher (1844-1900)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-18 : 15:51:06

I think your after this
Set nocount on
Declare @newint int
Select @newint = Max(Cast(Substring(yourfield,6,10)as Int )) + 1

Select CAst (Year(getdate())as varchar(5)) + '/' + cast(@newint as VArchar(10)) as yourfield

This will not give you your 001 fill but it will work more than 999 times.

Jim
Users <> Logic

macca
Posting Yak Master

146 Posts

Posted - 2005-05-19 : 04:24:26

I want to set the column the same as AutoNumber in Access. Is there any facility in Sqlserver 2000 to do the same as Autonumber or something like that, but using the format 2005/001. 2005 is the year.

Any suggestions?

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-19 : 05:17:53

here this the solution to your problem:

paste it on your Query Analyzer
---------------- paste starts here ---------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetID]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetID]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

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([YourPrimaryKeyInYourTable]),1) from [ChangeThisToYourTableName] -- 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

-------------------- paste end here-----------

When inserting a new record you must call the function example :

insert into YourOrderTable(Field1, Field2, TheFieldThatNeedsFormatting)
values (value1, value2, select dbo.getID(getDate()))

that's it i hope that your problem is already solve..

Cursors are for those who doesn't know how to use SQL

raclede™

macca
Posting Yak Master

146 Posts

Posted - 2005-05-19 : 06:33:43

Raclede,

Thanks for the post but could you comment it as I don't understand some of it.

You say to call the function example, but the example is an insert, what I want is that when I create a new form, a field in the form will be populated automatically with the next available Order Number e.g. 05/002.

Will your above code do this?

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-19 : 06:43:28

Yup just the same just execute an sql command with select dbo.getID(getDate()) <--- this parameter and then it will return u the value...

in C#, Since we use Application Blocks this is how it looks like

StringBuilder sqlqry = new StringBuilder();
sqlqry.Append("select dbo.getID(getDate())");

dr = Sql400Accessor.ExecuteReader(constr.ToString(), sqlqry.ToString(),CommandType.Text, "");
if(dr.HasRows)
{
while(dr.Read())
{
YourTextBoxName.Text = dr[0].ToString().Trim();
}
else
YourTextBoxName.Text = "Cannot Get Details"
}

Cursors are for those who doesn't know how to use SQL

raclede™

macca
Posting Yak Master

146 Posts

Posted - 2005-05-19 : 07:12:34

wait til I get this staright Raclede.
I just run the above code in query analyzer changing any parts to the names for my table and column names.
What does that code ran in the Query analyzer do to the table?
Then each time I raise a new record the Page Load event should contain this code:
insert into YourOrderTable(Field1, Field2, TheFieldThatNeedsFormatting)
values (value1, value2, select dbo.getID(getDate()))
and this should do the job.
Reason I put in Page Load is due to when the form is opened I want the Order number to be in there for the user without them having to do anythin.

So your code should work then?

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-19 : 07:22:01

it doesn't do anything.. Its just a simple function that will return the formatting based on
theCurrYear + "/" + max(id) .. it is what you needed right... you are the one to make decision on when you should call the function.

Cursors are for those who doesn't know how to use SQL

raclede™

macca
Posting Yak Master

146 Posts

Posted - 2005-05-19 : 10:47:05

Raclede,

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.

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-19 : 21:01:22

since you are using web forms, and multiple users are accessing at a time, using that method will cause you some problem later on.. since the function only returns the ordernumber with format and doesn't do anything to the table.

Let take for instance the ordernumber in the database is 100 then when you do that method above when one user access the page it will return 2005/101 right? what about the second user, it will get the same 2005/101 since the ordernumber is not yet incremented.

What can you do is just like this...

1) When the page load call a stored procedure that will inserts a dummy record at your order table and returns the ordernumber. (see the SP Below)
2) in you ASP page just do the formatting

object [] arrReturn = null;
string SP_NAME = "GetIDProc";
string RetID;
SqlParameter[] parameters = { SqlAccessor.SqlParameterBuilder("@RetOrderID", SqlDbType.VarChar, 10, ParameterDirection.Output),
};
int iRow = SqlAccessor.ExecuteNonQuery(connectionString,
SqlAccessor.SqlCommandBuilder(new SqlCommand(SP_NAME), parameters),
CommandType.StoredProcedure, out arrReturn);
RetID = (string) (arrReturn[0].Equals(System.DBNull.Value) ? 0 : arrReturn[0]);

if (iRow > 0)
{
string myFormat;
DateTime d = new DateTime();
OrderNum.Text = d.Year + "/" + RetID;
}
else
{
OrderNum.Text = "Cannot Get Details";
}

3) When the user saves the order just Updated the OrderTable using the RetID variable, like this
update Orders set column1= value1, column2 = value2 where OrderID = RetID --- variable you have above;

The SP:

CREATE PROCEDURE GetIDProc
(
@RetID varchar(10) OUT
)

AS
BEGIN
DECLARE @orderid int
---- just to insert a dummy records
Insert into Orders (anyField) values (someValue)
-- gets the orderid inserted
select @orderid = @@identity
-- add 00 or 0 to header
set @RetID = ( CASE
WHEN @orderid < 10 THEN '00'
WHEN @orderid < 100 THEN '0'
ELSE ''
END ) + convert(varchar(5),@orderid)
RETURN
END
GO

4) if the users cancels the order you can do 2 things either delete the record or update it and mark it with "CANCELLED"

Cursors are for those who doesn't know how to use SQL

raclede™

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-19 : 21:03:45

the code will have some error forgot this:

OrderNum.Text = d.Year.ToString() + "/" + RetID;

Cursors are for those who doesn't know how to use SQL

raclede™

macca
Posting Yak Master

146 Posts

Posted - 2005-05-23 : 04:43:25

Raclede,

I have tried your code and am getting error messages with this SqlAccessor keyword you are using. I am getting errors saying that SqlAccessor is not declared.
What did you declare SqlAccessor as I am unsure what to declare it as?

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-23 : 05:46:23

sorry SqlAccessor -- is our Application DataBlocks,try using the SqlCommand and SqlConnection instead..

"Wisdom is knowing what to do next, skill is knowing how to do it, and virtue is doing it. "

"The questions you ask consistently will create either enervation or enjoyment, indignation or inspiration, misery or magic. Ask the questions that will uplift your spirit and push you along the path of human excellence. "

K.I.S.S - Keep it simple stupid

raclede™

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources