Creating an AutoNumber field from ADO?

[Josh McFarlane]

Hi,

I'm attempting to create a table via ADO and C++, but I can't seem to
find the type of field to make a proper autonumber.

After taking a look at the DataTypeEnum there doesn't seem to be
anything indicitive of an autonumber field.

Could anyone shed light on the field type I need to use for an
autonumber?

Thanks,
Josh McFarlane

[MrSmersh]

I assume that you want to do this for SQL Server?
CREATE TABLE "TableName" ("ColumnName" int NOT NULL IDENTITY (1, 1))

I know how it could be done for other databases than SQL Server, so if you
need tell.

[Josh McFarlane]

MrSmersh wrote:
> I assume that you want to do this for SQL Server?
> CREATE TABLE "TableName" ("ColumnName" int NOT NULL IDENTITY (1, 1))
>
> I know how it could be done for other databases than SQL Server, so if you
> need tell.

Actually, we're interfacing with the Jet engine to MS Access.

Currently, we're not using SQL statements for the table creation, that
sounds like a better idea than our current method.

What would the SQL through the Jet engine be to create an autonumber
int?

Thanks,
Josh McFarlane

[MrSmersh]

This is how the SQL statements could look (you find all the needed info in
MSDN help):

CREATE TABLE "TableName" ("ColumnName" int NOT NULL IDENTITY (1, 1))

ALTER TABLE "TableName" ADD "ColumnName" int NOT NULL IDENTITY (0, 1)

A short sample of how could you call them:

#include <afxole.h>
#include <dbdao.h>

CdbWorkspace ws;
CdbDatabase db;
... // Initialize ws, db, etc.

ws.BeginTrans(); // Start working.
db.Execute(_T("CREATE TABLE \"TableName\" (\"ColumnName\" int NOT NULL
IDENTITY (1, 1))"), dbFailOnError);
ws.CommitTrans(); // Accept changes.
ws.Close(); // Close the workspace.

About Identity property as used in CREATE TABLE and ALTER TABLE from MSDN help

IDENTITY
Specifies that the new column is an identity column. When a new row is added
to the table, SQL Server provides a unique, incremental value for the column.
Identity columns are commonly used in conjunction with PRIMARY KEY
constraints to serve as the unique row identifier for the table. The IDENTITY
property can be assigned to a tinyint, smallint, int, decimal(p,0), or
numeric(p,0) column. Only one identity column can be created per table. The
DEFAULT keyword and bound defaults cannot be used with an identity column.
Either both the seed and increment must be specified, or neither. If neither
are specified, the default is (1,1).

[Orimslala]

Hi,

The Sql statement to create an autonumber through the Jet Engine should
be something like :
CREATE TABLE TableName(ColumnName AutoIncrement PRIMARY KEY)
(am assuming you want to put a constraint on the field,hence the
Primary Key. If thats not the case just remove the Primary Key bit)
You might also want to have a look at the KB Article below, the code
shown is in VB,however it should give you an idea of how you could get
this implemented in C++.

http://support.microsoft.com/default.aspx?scid=kb;en-us;275252

Hope that helps

Orims