locked
how to customize the AutoNumber? RRS feed

  • Question

  • User629360359 posted

    i have a primary key where its a autonumber, by default the database auto increase the integer by 1. 

    how to change the format to ABC1, ABC2, ABC3, ABC4 ... 

    (using MS access)

    thanks in advance :)

    Monday, June 6, 2011 9:43 AM

Answers

  • User1770881099 posted

     

    I do not believe that this is possible. The purpose of an identity is to allow the database to do the numbering for you. While it would be easy to code, having to split the string, increase the count, then concat the string back together on every row to figure out the highest number would be a lot of work.<o:p></o:p>

     <o:p></o:p>

    Check out this thread for customization with the identity column<o:p></o:p>

    http://office.microsoft.com/en-us/access-help/change-a-columns-identity-properties-adp-HP003088488.aspx<o:p></o:p>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 6, 2011 9:52 AM
  • User3866881 posted

    My addition:

    If you only want to show it on UI ---- I mean that only it's for appearance. You can do this:

    1) Set the column (let's say "Id" to Identified column stepped by 1 from 1).

    2) Bind in the TemplateField in the GridView like this:

    <asp:GridVeiw....>

       ...

       <asp:Template HeaderText="xxx">

                    <ItemTemplate>

                            <%#Eval("Id","ABC{0}")%>

                    </ItemTemplate>

       </asp:Template>

    </asp:GridView>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 7, 2011 9:47 PM
  • User1867929564 posted

    Table Name=Table1
    Two column namely col1 (pk text),col2 (text)
    where col1 will contain ABC1,ABC2........etc

    while insert in table1 i write query

    insert into table1(col1,col2)
    SELECT 'ABC' & max(replace(col1,'ABC',''))+1,'fgdf' from table1

     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 10, 2011 1:48 AM
  • User-821857111 posted

    As others have said, this is not possible. There are workarounds: you could just use string.Format for display purposes as Decker suggested, or you could add another column and generate your own ID value from the Autonumber that Access generates:

    string query = "Insert Into Categories (CategoryName) Values (?)";
    string query2 = "Select @@Identity";
    string query3 = "UPDATE Categories SET MyCustomId = ?";
    int ID;
    string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data ource=|DataDirectory|Northwind.mdb";
    using (OleDbConnection conn = new OleDbConnection(connect))
    {
      using (OleDbCommand cmd = new OleDbCommand(query, conn))
      {
        cmd.Parameters.AddWithValue("", Category.Text);
        conn.Open();
        cmd.ExecuteNonQuery();
        cmd.CommandText = query2;
        ID = (int)cmd.ExecuteScalar(); 
        cmd.Parameters.Clear();
        cmd.CommandText = query3;
        cmd.Parameters.AddWithValue("", "ABC" + ID);
        cmd.ExecuteNonQuery();
      }
    }



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 10, 2011 11:05 AM

All replies

  • User1770881099 posted

     

    I do not believe that this is possible. The purpose of an identity is to allow the database to do the numbering for you. While it would be easy to code, having to split the string, increase the count, then concat the string back together on every row to figure out the highest number would be a lot of work.<o:p></o:p>

     <o:p></o:p>

    Check out this thread for customization with the identity column<o:p></o:p>

    http://office.microsoft.com/en-us/access-help/change-a-columns-identity-properties-adp-HP003088488.aspx<o:p></o:p>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 6, 2011 9:52 AM
  • User3866881 posted

    My addition:

    If you only want to show it on UI ---- I mean that only it's for appearance. You can do this:

    1) Set the column (let's say "Id" to Identified column stepped by 1 from 1).

    2) Bind in the TemplateField in the GridView like this:

    <asp:GridVeiw....>

       ...

       <asp:Template HeaderText="xxx">

                    <ItemTemplate>

                            <%#Eval("Id","ABC{0}")%>

                    </ItemTemplate>

       </asp:Template>

    </asp:GridView>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 7, 2011 9:47 PM
  • User1867929564 posted

    Suppose I hv a table called Emp (id (autonumber),name (text),empid (text)) 
    Here i took id in order to test.
    I write  this query in mdb query window

    INSERT INTO emp (name,empid)
    Select 'gfhfgh', 'ABC'&max(id)+1 from emp

    It append record. 

    Wht is ur table design ? in place of 'gfhfgh' you can use your parameter variable.
    Thursday, June 9, 2011 6:59 AM
  • User1867929564 posted

    Table Name=Table1
    Two column namely col1 (pk text),col2 (text)
    where col1 will contain ABC1,ABC2........etc

    while insert in table1 i write query

    insert into table1(col1,col2)
    SELECT 'ABC' & max(replace(col1,'ABC',''))+1,'fgdf' from table1

     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 10, 2011 1:48 AM
  • User-821857111 posted

    As others have said, this is not possible. There are workarounds: you could just use string.Format for display purposes as Decker suggested, or you could add another column and generate your own ID value from the Autonumber that Access generates:

    string query = "Insert Into Categories (CategoryName) Values (?)";
    string query2 = "Select @@Identity";
    string query3 = "UPDATE Categories SET MyCustomId = ?";
    int ID;
    string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data ource=|DataDirectory|Northwind.mdb";
    using (OleDbConnection conn = new OleDbConnection(connect))
    {
      using (OleDbCommand cmd = new OleDbCommand(query, conn))
      {
        cmd.Parameters.AddWithValue("", Category.Text);
        conn.Open();
        cmd.ExecuteNonQuery();
        cmd.CommandText = query2;
        ID = (int)cmd.ExecuteScalar(); 
        cmd.Parameters.Clear();
        cmd.CommandText = query3;
        cmd.Parameters.AddWithValue("", "ABC" + ID);
        cmd.ExecuteNonQuery();
      }
    }



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 10, 2011 11:05 AM