-
Mar 4th, 2008, 09:48 AM
#1
Thread Starter
Fanatic Member
[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.
-
Mar 4th, 2008, 09:56 AM
#2
Re: Creating an Autonumber Field in SQL
Sure....lots of solutions.
However, they all are different depending on what database you are using?
-
Mar 4th, 2008, 09:57 AM
#3
Addicted Member
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
-
Mar 4th, 2008, 09:59 AM
#4
Addicted Member
Re: Creating an Autonumber Field in SQL
Regards
Srinivasan Baskaran
India
-
Mar 4th, 2008, 10:00 AM
#5
Re: Creating an Autonumber Field in SQL
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?
-
Mar 4th, 2008, 10:15 AM
#6
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
-
Mar 4th, 2008, 10:21 AM
#7
Thread Starter
Fanatic Member
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
-
Mar 4th, 2008, 10:29 AM
#8
Re: Creating an Autonumber Field in SQL
In Access, you set the field type as AutoNumber
-
Mar 4th, 2008, 11:04 AM
#9
Thread Starter
Fanatic Member
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.
-
Mar 4th, 2008, 11:07 AM
#10
Thread Starter
Fanatic Member
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.
-
Mar 4th, 2008, 11:42 AM
#11
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
)
-
Mar 4th, 2008, 01:13 PM
#12
Thread Starter
Fanatic Member
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.
-
Mar 4th, 2008, 01:16 PM
#13
Re: Creating an Autonumber Field in SQL
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.
-
Mar 4th, 2008, 01:22 PM
#14
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.
-
Mar 4th, 2008, 03:33 PM
#15
Thread Starter
Fanatic Member
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...
-
Mar 4th, 2008, 03:41 PM
#16
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/
-
Mar 4th, 2008, 04:07 PM
#17
Thread Starter
Fanatic Member
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?
-
Mar 4th, 2008, 04:17 PM
#18
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.
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.
-
Mar 4th, 2008, 05:11 PM
#19
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
-
Mar 4th, 2008, 05:32 PM
#20
Thread Starter
Fanatic Member
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
-
Mar 4th, 2008, 06:53 PM
#21
Thread Starter
Fanatic Member
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:
Dim db As dao.Database Dim LocalInfo As dao.TableDef Dim Isotopes As dao.TableDef Dim IsotopeList As dao.TableDef Dim Customers As dao.TableDef Dim IsotopeIndex As dao.Field Dim IsotopeListIndex As dao.Field Dim CustomerIndex As dao.Field Set db = dao.CreateDatabase("data.mdb", dbLangGeneral) Set LocalInfo = db.CreateTableDef("LocalInfo") Set Customers = db.CreateTableDef("Customers") Set IsotopeList = db.CreateTableDef("IsotopeList") Set Isotopes = db.CreateTableDef("Isotopes") With LocalInfo .Fields.Append .CreateField("LocName", dbText) .Fields.Append .CreateField("Street", dbText) .Fields.Append .CreateField("CityState", dbText) .Fields.Append .CreateField("License", dbText) .Fields.Append .CreateField("ContactName", dbText) .Fields.Append .CreateField("ContactPhoneDay", dbText) .Fields.Append .CreateField("ContactPhoneNight", dbText) .Fields.Refresh End With Set CustomerIndex = LocalInfo.CreateField("ID") CustomerIndex.Attributes = dbAutoIncrField With Customers .Fields.Append CustomerIndex .Fields.Append .CreateField("LocName", dbText) .Fields.Append .CreateField("Street", dbText) .Fields.Append .CreateField("CityState", dbText) .Fields.Append .CreateField("License", dbText) .Fields.Append .CreateField("ContactName", dbText) .Fields.Append .CreateField("ContactPhoneDay", dbText) .Fields.Append .CreateField("ContactPhoneNight", dbText) .Fields.Refresh End With Set IsotopeListIndex = IsotopeList.CreateField("PriKey") IsotopeListIndex.Attributes = dbAutoIncrField With IsotopeList .Fields.Append IsotopeListIndex .Fields.Append .CreateField("IsoIndex", dbInteger) .Fields.Append .CreateField("ID", dbInteger) .Fields.Append .CreateField("IsoPercent", dbSingle) .Fields.Refresh End With Set IsotopeIndex = Isotopes.CreateField("ID") IsotopeIndex.Attributes = dbAutoIncrField With Isotopes .Fields.Append IsotopeIndex .Fields.Append .CreateField("Isotope", dbText) .Fields.Append .CreateField("A2Value", dbText) .Fields.Append .CreateField("LQValue", dbText) .Fields.Refresh End With db.TableDefs.Append Customers db.TableDefs.Append LocalInfo db.TableDefs.Append Isotopes db.TableDefs.Append IsotopeList
-
Mar 4th, 2008, 07:15 PM
#22
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
-
Mar 4th, 2008, 08:12 PM
#23
Thread Starter
Fanatic Member
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:
Set CustomerIndex = Customers.CreateIndex("PrimaryKey") CustomerIndex.Primary = True CustomerIndex.Unique = True Set tmpTable = Customers.CreateField("ID", dbLong) tmpTable.Attributes = dbAutoIncrField CustomerIndex.Fields.Append tmpTable CustomerIndex.Fields.Refresh With Customers .Fields.Append .CreateField("LocName", dbText) .Fields.Append .CreateField("Street", dbText) .Fields.Append .CreateField("CityState", dbText) .Fields.Append .CreateField("License", dbText) .Fields.Append .CreateField("ContactName", dbText) .Fields.Append .CreateField("ContactPhoneDay", dbText) .Fields.Append .CreateField("ContactPhoneNight", dbText) .Indexes.Append CustomerIndex .Fields.Refresh End With db.TableDefs.Append Customers db.TableDefs.Refresh db.Close
-
Mar 4th, 2008, 10:02 PM
#24
Thread Starter
Fanatic Member
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!
-
Mar 4th, 2008, 10:38 PM
#25
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.
-
Mar 5th, 2008, 01:03 PM
#26
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|