Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Unanswered: generate AccNo in SQL(select min AccNo from table where AccNo>300000000

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do yourself a favour and don't try to "re-use" old numbers, don't try to fillin those gaps

    you're only asking for a world of hurt

    your account numbers should be generated by an IDENTITY column, don't try anything more complicated
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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