Results 1 to 26 of 26

Thread: [RESOLVED] Creating an Autonumber Field in SQL *RESOLVED*

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2007
    Location
    Middletown, CT
    Posts
    948

    Resolved [RESOLVED] Creating an Autonumber Field in SQL *RESOLVED*

    Hello,
    I am trying to create a database using SQL statements. (Such as CREATE TABLE, etc). I need to have a field be an autonumber primary key field. I can create the primary key using the ADD PRIMARY KEY command, but I need to be able to create an autonumber on that field. Anyone have a solution?
    Last edited by drag0n_45; Mar 4th, 2008 at 10:02 PM.

  2. #2
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Creating an Autonumber Field in SQL

    Sure....lots of solutions.

    However, they all are different depending on what database you are using?

  3. #3
    Addicted Member
    Join Date
    Oct 2006
    Location
    Chennai, India
    Posts
    198

    Re: Creating an Autonumber Field in SQL

    Code:
    CREATE SEQUENCE sq;
    CREATE TABLE t_test(
     id INTEGER PRIMARY KEY DEFAULT NEXT_VALUE OF sq,
     name VARCHAR(10)
     );
    Regards
    Srinivasan Baskaran
    India

  4. #4
    Addicted Member
    Join Date
    Oct 2006
    Location
    Chennai, India
    Posts
    198

    Re: Creating an Autonumber Field in SQL

    Can you refer the link

    http://sqlzoo.net/howto/source/z.dir.../i02create.xml

    for all major DB's
    Regards
    Srinivasan Baskaran
    India

  5. #5
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Creating an Autonumber Field in SQL

    Quote Originally Posted by cheenu_vasan
    Code:
    CREATE SEQUENCE sq;
    CREATE TABLE t_test(
     id INTEGER PRIMARY KEY DEFAULT NEXT_VALUE OF sq,
     name VARCHAR(10)
     );
    Where is your AutoNumber field?

  6. #6
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Creating an Autonumber Field in SQL

    That looks like an Oracle type (guessing with the sequence since SQL Server uses Identity). I would have done the ID with a trigger on Insert to get the next number from the sequence.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2007
    Location
    Middletown, CT
    Posts
    948

    Re: Creating an Autonumber Field in SQL

    hmm...here's the code to create the database....
    I'm using VB with DAO (i think)... Just your standard database and recordset objects. Using an access db.


    Code:
    strSQL = "CREATE TABLE Isotopes (Isotope varchar, A2Value varchar, RQValue char(6), ID int)"
        db.Execute strSQL
        'Create the primary key for the isotopes
        strSQL = "CREATE INDEX ID ON Isotopes(ID)"
        db.Execute strSQL
        
        'Create customer's table
        'Contains: The customers name as a variable length string(multiline req'd), license as a variable length string
            '   The contact information as a variable length string (multiline req'd)
            'The isotope list and percentages will be compared with an inner join and retrived via that method
        strSQL = "CREATE TABLE Customers (LocName varchar, Street varchar, CityState varchar, License varchar, ContactName varchar," _
                        & "ContactPhoneDay varchar, ContactPhoneNight varchar, ID int)"
        db.Execute strSQL
        'Make ID be the primary key
        strSQL = "ALTER TABLE Customers ADD PRIMARY KEY (ID)"
        db.Execute strSQL
        
        'Create Isotope Table (ID: Relates to customer ID, ISOIndex relates to the number that is the isotope)
        'This table has a key field that is ONLY for a primary key. This is made as a safety net for the customer edit
        'form. Without a primary autonumber key, the datagrid powered by adoisotope will fail.
        strSQL = "CREATE TABLE IsotopeList (Key int, ID INT, IsoIndex INT, IsoPercent VARCHAR)"
        db.Execute strSQL
        'Set to primary key - indexed w/ no duplicated
        '!!!-------------------------------------------------
    
        strSQL = "ALTER TABLE Isotopes ADD PRIMARY KEY (Key)"
        db.Execute strSQL

  8. #8
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Creating an Autonumber Field in SQL

    In Access, you set the field type as AutoNumber

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2007
    Location
    Middletown, CT
    Posts
    948

    Re: Creating an Autonumber Field in SQL

    I just tried Cheenu's link and it had some very very interesting information. To the best of my knowledge I'm using an Access database. I tried

    Code:
    strSQL = "CREATE TABLE IsotopeList (Key COUNTER, ID INT, IsoIndex INT, IsoPercent VARCHAR)"
        db.Execute strSQL
    and got a syntax error in my SQL statement from the db object when I tried to run that.

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2007
    Location
    Middletown, CT
    Posts
    948

    Re: Creating an Autonumber Field in SQL

    Hack, the program is not in access, but it is created from the database object in the dao library. The database can be opened in access, but it is used exclusively with my program. I know how to set the autonumber field in access, but I need my program to do it at run time.

  11. #11
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: Creating an Autonumber Field in SQL

    drag0n_45, you still did not say what database are you using ?

    In SQL Server you do it like this:
    Code:
    CREATE TABLE IsotopeList (
          Key INT PRIMARY KEY IDENTITY(1,1) NOT NULL
        , ID INT
        , IsoIndex INT
        , IsoPercent VARCHAR
    )

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2007
    Location
    Middletown, CT
    Posts
    948

    Re: Creating an Autonumber Field in SQL

    I'm sorry Michael. I'm not sure how to find this out. In the connection string of the ADODB control I specify Microsoft Jet 4.0. When I create the database I use {databasename}.CreateDatabase ({file name}) I'm not sure what this created by default.

    I've tried those commands but I get an error "Syntax error in field definition." from the database object.

  13. #13
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Creating an Autonumber Field in SQL

    Quote Originally Posted by drag0n_45
    Hack, the program is not in access,
    I don't care what the program is in. The language you are using is beyond definition of irrelevent. The point is you are using an Access database, and need to set the field as AutoNumber.

  14. #14
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: Creating an Autonumber Field in SQL

    If you are executing the sql statement via the Jet OLEDB provider the syntax would be the same as for SQL Server except the Identity key word must appear before the PRIMAY KEY key words. Also, place square brackets around the field named Key (because Key is a keyword). You would be better off changing the name of that field however.

    Code:
    CREATE TABLE IsotopeList (
          [Key] IDENTITY(1,1) PRIMARY KEY NOT NULL
        , ID INT
        , IsoIndex INT
        , IsoPercent VARCHAR
    )
    You don't need to include the (1,1) as it is the default.

  15. #15

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2007
    Location
    Middletown, CT
    Posts
    948

    Re: Creating an Autonumber Field in SQL

    That failed as well. I am only using the OLEDB provider to read the database, as I'm not sure quite how to use it. (I'm using it for things like datagrids, listboxes, etc and auto-editing of recordsets). When I create the database, I refer to a databse object that belongs to the DAO 3.6 object library. Please forgive me, I'm new to the database designing world and all the specifics in it. I don't know half the differences between ADO, DAO, Jet, Access, etc...

  16. #16
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: Creating an Autonumber Field in SQL

    If you are using DAO, then why do you want to create the DB using SQL ?

    You can use the DAO objects, like in this example:
    http://www.developerfusion.co.uk/show/3039/

  17. #17

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2007
    Location
    Middletown, CT
    Posts
    948

    Re: Creating an Autonumber Field in SQL

    because I'm a moron and don't know any better? LOL

    I was just intrigued by the idea of using SQL, as making tabledefs and all that seemed quite a bit more code. You'd recommend I do that instead of through SQL?

  18. #18
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Creating an Autonumber Field in SQL

    I've only had a quick look, but it seems like it would be better - you'll get autocomplete and help for it in VB, unlike with SQL statements.
    Quote Originally Posted by drag0n_45
    I don't know half the differences between ADO, DAO, Jet, Access, etc...
    In simple terms:

    Access is the kind of database you are using (assuming it is an .mdb file), and it holds the data. Alternatives would be SQL Server/Oracle/MySQL/etc.

    Jet is the name of the driver, which communicates between programs and the database. You are using the OLEDB version of it, which is good - it is newer and offers better features.

    DAO and ADO (and even RDO) are connection technologies, which allow your program to communicate with the driver. RDO is the oldest (very bad idea), DAO is also very old (bad idea - it has been recommended against for 10 years, even in the VB6 help for DAO), and ADO is the current generation.

  19. #19
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: Creating an Autonumber Field in SQL

    I just tried Cheenu's link and it had some very very interesting information. To the best of my knowledge I'm using an Access database. I tried
    Code:
    strSQL = "CREATE TABLE IsotopeList (Key COUNTER, ID INT, IsoIndex INT, IsoPercent VARCHAR)"
        db.Execute strSQL
    and got a syntax error in my SQL statement from the db object when I tried to run that.
    You may find using the DAO Objects easier but it is a lot more work.

    The syntax error with the above is that Key is a keyword. When you use a keyword as the name of a database object (tables, fields, queries) you must delimit them within square brackets in your sql statement.

    This code works when using the DAO engine.

    Code:
        Dim db As DAO.Database
        Dim strSQL As String
        
        Set db = DBEngine.OpenDatabase("m:\testing\nwind2.mdb")
        
        strSQL = "Create Table IsotopeList([Key] Counter PRIMARY KEY NOT NULL, ID INT, IsoIndex INT, IsoPercent VARCHAR(50))"
    
        db.Execute strSQL
        db.Close

  20. #20

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2007
    Location
    Middletown, CT
    Posts
    948

    Re: Creating an Autonumber Field in SQL

    i did. that too failed. i even tried renaming the field to FldKey

    I'm trying to make the database with data access objects now, but that is also a bit confusing. for instance, I can't find a data type that is an autonumber

  21. #21

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2007
    Location
    Middletown, CT
    Posts
    948

    Re: Creating an Autonumber Field in SQL

    Alright guys, I tried making the database through dao objects. I encounter the message "Invalid argument." when I try to append the table to the database. code as follows:
    vb Code:
    1. Dim db As dao.Database
    2.  
    3. Dim LocalInfo As dao.TableDef
    4. Dim Isotopes As dao.TableDef
    5. Dim IsotopeList As dao.TableDef
    6. Dim Customers As dao.TableDef
    7.  
    8. Dim IsotopeIndex As dao.Field
    9. Dim IsotopeListIndex As dao.Field
    10. Dim CustomerIndex As dao.Field
    11.  
    12.  
    13.  
    14. Set db = dao.CreateDatabase("data.mdb", dbLangGeneral)
    15.    
    16. Set LocalInfo = db.CreateTableDef("LocalInfo")
    17. Set Customers = db.CreateTableDef("Customers")
    18. Set IsotopeList = db.CreateTableDef("IsotopeList")
    19. Set Isotopes = db.CreateTableDef("Isotopes")
    20.  
    21.  
    22.  
    23. With LocalInfo
    24.     .Fields.Append .CreateField("LocName", dbText)
    25.     .Fields.Append .CreateField("Street", dbText)
    26.     .Fields.Append .CreateField("CityState", dbText)
    27.     .Fields.Append .CreateField("License", dbText)
    28.     .Fields.Append .CreateField("ContactName", dbText)
    29.     .Fields.Append .CreateField("ContactPhoneDay", dbText)
    30.     .Fields.Append .CreateField("ContactPhoneNight", dbText)
    31.     .Fields.Refresh
    32. End With
    33.  
    34. Set CustomerIndex = LocalInfo.CreateField("ID")
    35. CustomerIndex.Attributes = dbAutoIncrField
    36. With Customers
    37.     .Fields.Append CustomerIndex
    38.     .Fields.Append .CreateField("LocName", dbText)
    39.     .Fields.Append .CreateField("Street", dbText)
    40.     .Fields.Append .CreateField("CityState", dbText)
    41.     .Fields.Append .CreateField("License", dbText)
    42.     .Fields.Append .CreateField("ContactName", dbText)
    43.     .Fields.Append .CreateField("ContactPhoneDay", dbText)
    44.     .Fields.Append .CreateField("ContactPhoneNight", dbText)
    45.     .Fields.Refresh
    46. End With
    47.  
    48. Set IsotopeListIndex = IsotopeList.CreateField("PriKey")
    49. IsotopeListIndex.Attributes = dbAutoIncrField
    50. With IsotopeList
    51.     .Fields.Append IsotopeListIndex
    52.     .Fields.Append .CreateField("IsoIndex", dbInteger)
    53.     .Fields.Append .CreateField("ID", dbInteger)
    54.     .Fields.Append .CreateField("IsoPercent", dbSingle)
    55.     .Fields.Refresh
    56. End With
    57.  
    58. Set IsotopeIndex = Isotopes.CreateField("ID")
    59. IsotopeIndex.Attributes = dbAutoIncrField
    60. With Isotopes
    61.     .Fields.Append IsotopeIndex
    62.     .Fields.Append .CreateField("Isotope", dbText)
    63.     .Fields.Append .CreateField("A2Value", dbText)
    64.     .Fields.Append .CreateField("LQValue", dbText)
    65.     .Fields.Refresh
    66. End With
    67.  
    68. db.TableDefs.Append Customers
    69. db.TableDefs.Append LocalInfo
    70. db.TableDefs.Append Isotopes
    71. db.TableDefs.Append IsotopeList

  22. #22
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: Creating an Autonumber Field in SQL

    When you create the 3 autonumber fields specify the datatype.

    Set CustomerIndex = LocalInfo.CreateField("ID", dbLong)
    CustomerIndex.Attributes = dbAutoIncrField

  23. #23

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2007
    Location
    Middletown, CT
    Posts
    948

    Re: Creating an Autonumber Field in SQL

    Thanks bruce. Thank worked. Now I need to figure out how to make all of those autonumber fields a primary key. I try making a new index, but when I add the index to the collection i get the message: "Invalid field definition "ID" in definition of index or relationship."

    I've tried renaming the table that I link to the index, but that has no result.
    vb Code:
    1. Set CustomerIndex = Customers.CreateIndex("PrimaryKey")
    2. CustomerIndex.Primary = True
    3. CustomerIndex.Unique = True
    4.  
    5. Set tmpTable = Customers.CreateField("ID", dbLong)
    6. tmpTable.Attributes = dbAutoIncrField
    7.  
    8. CustomerIndex.Fields.Append tmpTable
    9. CustomerIndex.Fields.Refresh
    10.  
    11. With Customers
    12.     .Fields.Append .CreateField("LocName", dbText)
    13.     .Fields.Append .CreateField("Street", dbText)
    14.     .Fields.Append .CreateField("CityState", dbText)
    15.     .Fields.Append .CreateField("License", dbText)
    16.     .Fields.Append .CreateField("ContactName", dbText)
    17.     .Fields.Append .CreateField("ContactPhoneDay", dbText)
    18.     .Fields.Append .CreateField("ContactPhoneNight", dbText)
    19.     .Indexes.Append CustomerIndex
    20.     .Fields.Refresh
    21. End With
    22. db.TableDefs.Append Customers
    23. db.TableDefs.Refresh
    24. db.Close

  24. #24

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2007
    Location
    Middletown, CT
    Posts
    948

    Re: Creating an Autonumber Field in SQL

    Got it. Thanks everyone who helped. I'm sure I'll have more questions in the near future, like how to stop errors from automation controllers, but I think I might be able to handle that one on my own. On a closing note, does anyone have any good database tutorial references? Not necessarily for coding, but for ADO, DAO, Jet, etc so I could learn about all the differences and how they interact? Thanks!

  25. #25
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Re: Creating an Autonumber Field in SQL *RESOLVED*

    Thanks for letting us know that you have your answer. In the future you might find it easier by simply pulling down the Thread Tools menu and clicking the Mark Thread Resolved button. Also if someone has been particularly helpful, or even particularly unhelpful, you have the ability to affect a their forum "reputation" by rating their post.

  26. #26
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: [RESOLVED] Creating an Autonumber Field in SQL *RESOLVED*

    On a closing note, does anyone have any good database tutorial references? Not necessarily for coding, but for ADO, DAO, Jet, etc so I could learn about all the differences and how they interact?
    I'm not aware of any, and to be honest there isn't much you need to know about that, but to expand on my previous post:

    The database just stores the data, and the driver (in this case Jet) is what allows communication with it. Once you have chosen a database, it is rare to have a choice about which driver to use (except whether it is the OLEDB version or not - but you should always try to use that option as it is newer, and therefore likely to be improved and have better support).

    The combination of database and driver affects which syntax and features are supported for SQL statements (for example, Access/Jet allows IIF, but for SQL Server you use Case). There are standards for SQL statements, but they have a limited range, so there are lots of variations tacked on by each database system.


    The connection technology (ADO/DAO/RDO) is what you use in your program (like the DAO code in the last few posts), and sends all database requests to the driver, which then works with the database.

    In some cases, it can have a minor effect on SQL syntax (but not much), and generally only affects the code you write. ADO has the most features, but in this particular area (creating tables etc) DAO isn't far off, and unfortunately I don't have a link to a tutorial on the ADO equivalent.

    RDO and DAO are seriously out of date, and are not likely to work on 64bit versions of Windows (or any others which are released from now on), so generally are recommended against.


    If you want further clarification, it is probably best to start a new thread in our Database Development forum

Posting Permissions

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



Click Here to Expand Forum to Full Width