Generate AccNo in SQl

Question

Post reply

Generate AccNo in SQl

Jeev Oberoy

Valued Member

Points: 65
More actions
September 17, 2010 at 7:49 pm

#229407

hi all...,
i create a registration form and it'll generate AccounNo for user after they click "SAVE" button.
my database AccountNo start from 300000000
it's generate ID after >300000000
if i register a person it will register to 300000001
let's say i have AccountNo-
300000001
300000002
300000004
300000005
i dun have "300000003"
i want to get that Account No to my registration...
when i click "SAVE" it's have to search the minimum Account No from database and add user...
how to do this..can anyone modify this code to read min AccountNo from database..
here's the code that i have using :
try
{
connection.Open();
#region Generating a newAccNo
SqlCommand newAccNoCommand = connection.CreateCommand();
newAccNoCommand.CommandText = "SELECT TOP 1 AccNo FROM Debtor ORDER BY AccNo DESC";
newAccNoCommand.CommandType = CommandType.Text;
int minCurrentAccNo = 0;
try
{
if (!int.TryParse(newAccNoCommand.ExecuteScalar().ToS tring(), out minCurrentAccNo))
{
minCurrentAccNo = defaultAccNo;
}
}
catch
{
minCurrentAccNo = defaultAccNo;
}
int newAccNo = minCurrentAccNo + 1;
newAccNoCommand.Dispose();
#endregion

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

I would suggest reading the following

http://www.sqlservercentral.com/articles/Datetime+Manipulation/61822/

AND the comments on the article describing some different approaches.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

bitbucket-25253 (9/17/2010)
I would suggest reading the following
http://www.sqlservercentral.com/articles/Datetime+Manipulation/61822/
AND the comments on the article describing some different approaches.

Oh my, no. Sorry Ron.

@Jeev... you're just asking for a computational world of hurt by trying to "fill in" all the missing account numbers that you may end up with. Just use an IDENTITY column and don't worry if a few account numbers end up missing because of people doing rollbacks or pressing the "Back Button" or what have you. Seriously... filling in missing account numbers is one of the worst things you could to to an SQL Server database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)