Results 1 to 10 of 10
  1. #1

    Auto Increment MSSQL

    I am stuck and need the expertise of you folks. I am trying to have one of my rows inside a table to auto increment the default value that I would like it to start off as is: 0001 to 9999

    Should the data type for this row be an int or numeric?

    Any help would be greatly appreciated.

  2. #2
    I'm not sure that would work, and if it doesn't you can always use PHP to parse it like that.

    Try using varchar for that.

  3. #3
    Sadly this application is ASP based. And I don't want to muck around too much with it.

  4. #4
    Oh, sorry about that. I misread your title. Best of luck with that though.

  5. #5
    Join Date
    Mar 2004
    Location
    california
    Posts
    162
    If the column doesn't exists, just use this sql:
    alter table <table> add <column name> int identity(1,1) not null

    If it exists, just do:
    alter table <table> drop column <column name>

    Then use the alter table code shown above

  6. #6
    I will give that a try, thanks unlucky1

  7. #7
    Join Date
    Feb 2005
    Location
    Galveston, TX USA
    Posts
    115
    The data type of MSSQL identity field can be Int, BigInt and Decimal. In term of performance and storage, Int data type is better than others. You can get the difference from http://www.sqlteam.com/item.asp?ItemID=4123 .

  8. #8
    I think I have it working, but I need the values to be: 0001-9999

    When I add int identity(1,1) it starts at 1 and increments by one. What value should I use so it will use 0001?

    identity(-0001, 1) brings it back to 1 when I save.

    Thanks.

  9. #9
    Join Date
    Feb 2005
    Location
    Galveston, TX USA
    Posts
    115
    There is no easy way to achieve this.

    1) Use char or varchar data type as your key field, you need to automatically increment the value at your ASP code or use SQL Server triger.

    2) Use identity(1, 1) in SQL database. But you can always display "1" as "0001".

    3) Create another computed column and format your identity(1, 1) as "0001 - 9999".

    ComputedColumn = Right('0000' + Cast(IdentityColumn As varchar), 4).

  10. #10
    I'll give that a try, thanks. I am starting to loathe Accpac CRM more and more as the days go by.

Posting Permissions

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