Auto Increment Fields in sql

[Rashid]

hi all,
i am trying to figure out how to use auto increment in sql. i know about
indentity fields. but each time i insert records the indentiy field is
blank.
i read that you can use select max command. but how and when to you call
this command ie i suppose before the updates but how do i store the value to
a variable and have the variable stored to the tables?
thanks in advance
rashid

[pr]

"Rashid" <m...@pacific.net.sg> wrote in message
news:bjmt59$rbm$1...@reader01.singnet.com.sg...

Use, or write, a number generator. Using max() will crash
your program as soon as it is multi-user.

PR

[John]

Ah, how do you know the Identity field is blank? Try the following:

Create the Table:

CREATE TABLE dbo.ReportSources (
rs_id int IDENTITY (1, 1) NOT NULL ,
rs_name [varchar] (50) NULL
)
GO

Yes you said you know about Identity values, but you shouldn't need to
use the MAX() SQL function, here's how I use it.

Add a new value and get the Identity of the row just inserted:

with MyDataModule.ReportQuery do
begin
try
Screen.Cursor := crSQLWait;
Close;
SQL.Clear;
SQL.Add('INSERT INTO ReportSources VALUES ("'+strMyNewReport+'")');
SQL.Add('SELECT @@IDENTITY AS [New Identity]');
Open;
iLastIdentity := FieldByName('New Identity').AsInteger;
Close;
Screen.Cursor := crDefault;

etc, etc.

If you are inserting into more than one table set a local SQL variable
to the @@identity and insert into the next table. @@Identity is of
course global to your Database, not to each table so if your operation
is lengthy and you collect the @@identity later its not guaranteed to
be your @@identity value unless you use a SQL Transaction in you SQL
text.

Multiple Table INSERT:

with MyDataModule.ReportQuery do
begin
try
Screen.Cursor := crSQLWait;
Close;
SQL.Clear;
SQL.Add('BEGIN TRAN NEW_RECORD');
SQL.Add(' DECLARE @NewReport int, @NewSomethingElse int');
SQL.Add(' INSERT INTO ReportSources VALUES
("'+strMyNewReport+'")');
SQL.Add(' SELECT @NewReport = @@IDENTITY');
SQL.Add(' INSERT INTO SomethingElse VALUES
("'+strMyNewSomething+'")');
SQL.Add(' SELECT @NewSomethingElse = @@IDENTITY');
SQL.Add('IF (@@ERROR<>0)');
SQL.Add('BEGIN');
SQL.Add(' ROLLBACK TRAN NEW_RECORD');
SQL.Add(' SELECT [New Report]=0, [New Something Else]=0');
SQL.Add('END');
SQL.Add('ELSE');
SQL.Add('BEGIN');
SQL.Add(' COMMIT TRAN NEW_RECORD');
SQL.Add(' SELECT @NewReport AS [New Report],');
SQL.Add(' @NewSomethingElse AS [New Something Else]');
SQL.Add('END');
Open;
iLastReport := FieldByName('New Report').AsInteger;
iLastSomething := FieldByName('New Something Else').AsInteger;
Close;
Screen.Cursor := crDefault;

etc, etc.

"Rashid" <m...@pacific.net.sg> wrote in message news:<bjmt59$rbm$1...@reader01.singnet.com.sg>...

[John]

Opps, forgot to add the list of SQL fields to be inserted in my
examples. It just doesn't matter how many times you check there's
always a typo somewhere!

Add a new value and get the Identity of the row just inserted:

with MyDataModule.ReportQuery do
begin
try
Screen.Cursor := crSQLWait;
Close;
SQL.Clear;

SQL.Add('INSERT INTO ReportSources (rs_name)');
SQL.Add(' VALUES ("'+strMyNewReport+'")');

SQL.Add('SELECT @@IDENTITY AS [New Identity]');
Open;
iLastIdentity := FieldByName('New Identity').AsInteger;
Close;
Screen.Cursor := crDefault;

Obviously the multiple INSERT would follow in a similar way.

"pr" <sh...@netactive.co.za> wrote in message news:<3f5fc...@news1.mweb.co.za>...