First INSERT with autonumber

Results 1 to 5 of 5

Thread: First INSERT with autonumber

  1. #1
    Reggie Guest

    Default First INSERT with autonumber

    Every time I create a new database setup, I have to write some code for the special case of the first INSERT. Am I doing something totally unnecessary? When I do an INSERT, I use the MAX function to get the highest autonumber then add 1 to it, but if it is null I just set it equal to 1.

  2. #2
    Join Date
    Dec 1969

    Default RE: First INSERT with autonumber

    Your going to run into problems using the MAX(Field) + 1 method. What if 3 users all try to insert at the same time? Try using an IDENTITY Field (MS SQL Server) or a Autonember Field(MS Access).<BR><BR>

  3. #3
    Reggie Guest

    Default RE: First INSERT with autonumber

    I&#039ve been using ASP for over a year now, but I must be missing something very basic. I am using an autonumber field, but when I am inserting my insert statement looks like this:<BR><BR>SQL_query = "INSERT INTO TableName Values (MyAutonumber, &#039Something&#039, &#039Something else&#039)"<BR><BR>The system I set this up for is very low volume; only about 15 people use it so the method I have used hasn&#039t caused any problems with collisions (that I&#039ve been notified of). How should I be INSERTing? Is there another method of getting the next number or should the autonumber take care of that. And if so, how do I INSERT and get it to go into the next available slot?<BR><BR>Thank you.

  4. #4
    KPW Guest

    Default RE: First INSERT with autonumber

    You should not have to try to insert the AutoNumber value -- Access will do this for you. I think you are running into this problem because your table has 3 columns, so you are assuming you must insert 3 values. However, if you specify the column names of the last 2 columns in your SQL statement, Access will automatically insert the correct AutoNumber value for you.<BR><BR>Let&#039s assume your table has the following 3 columns:<BR><BR>iAutoNumber<BR>sColumn1<BR>sColumn 2<BR><BR>Your SQL might look something like this:<BR><BR>SQL_query = "INSERT INTO tMyTable (sColumn1, sColumn2) VALUES (&#039Something&#039, &#039Something else&#039)"<BR><BR>HTH

  5. #5
    Reggie Guest

    Default RE: First INSERT with autonumber

    Oh, now I understand. It&#039ll make my SQL statements longer, but it&#039ll make sure there are no collisions and prevent me from having to check to find the max value.<BR><BR>Thanks.

Posting Permissions

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