Return Identity Col value thru code 2

[sjulian]

I am using VB6 ADO to add a record to a MSSQL 2000 table that has an Identity field. Once the record is added I need to know the value of that Identity to add a record to another table. This is a multi-user db and I have found instances where the time stamp is the same on two records.
I have read the threads on @@IDENTITY but they all involve using a stored procedure to add the new record. I prefer not to do that.
Is there no way to get the IDENTITY value of a newly added record except through @@IDENTITY or am I missing something.

 

[gmmastros]

First of all, do not use @@IDENTITY with Microsoft SQL Server. Instead, use Scope_Identity(). Please see: thread222-1434291

You stated, "stored procedure... I prefer not to do that."

Can you please explain why you prefer to not use stored procedures? Stored Procedures are cool. Very cool, actually.

If your primary reason for avoiding Stored Procedures is because you don't know how to create them and/or call them, that's fine. I (or others) will help you with that.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

This is the simplest example I could some up with.

First, create a test table in your database.

[COLOR=blue]Create[/color] [COLOR=blue]Table[/color] TestIdentity(Id [COLOR=blue]Int[/color] [COLOR=blue]Identity[/color](1,1), Data [COLOR=blue]VarChar[/color](100))

Then, in VB:

Private Sub Command1_Click()
    
    Dim DB As ADODB.Connection
    Dim RS As ADODB.Recordset
    
    Set DB = New ADODB.Connection
    DB.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=[!]YourDatabaseNameHere[/!];Data Source=[!]YourServerNameHere[/!]"
    Call DB.Open
    
    Set RS = DB.Execute("SET NOCOUNT ON Insert Into TestIdentity(Data) Values('Blah') Select Scope_Identity() As Id")
    MsgBox RS("Id")
    RS.Close
    DB.Close
    
End Sub

The important part here is....

SET NOCOUNT ON

Without this line, you will not be able to get the value.

Notice that this is really 3 different statements. To make this be a stored procedure....

Create Procedure InsertTestIdentity
  @Data VarChar(100)
As
SET NOCOUNT ON

Insert Into TestIdentity(Data) Values(@Data)

Select Scope_Identity() As Id

Then, you could call this stored procedure from VB like this...

Private Sub Command1_Click()
    
    Dim DB As ADODB.Connection
    Dim RS As ADODB.Recordset
    
    Set DB = New ADODB.Connection
    DB.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=[!]YourDatabaseNameHere[/!];Data Source=[!]YourServerNameHere[/!]"
    Call DB.Open
    
    Set RS = DB.Execute("InsertTestIdentity 'Blah'")

    MsgBox RS("Id")
    RS.Close
    DB.Close
    
End Sub

To remove the test table from your database...

[COLOR=blue]Drop[/color] [COLOR=blue]Table[/color] TestIdentity

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fredericofonseca]

first of all read thread183-1353336 to know why you should not use @@IDENTITY


And after reading it try the following.

SQL
CREATE TABLE dbo.my_tbl	(
	id int IDENTITY(1,1) NOT NULL,
	t1 char(10) NULL
	)  ON [PRIMARY]

VB
Private Sub test()
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open ("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=db_name;Data Source=server_name")

conn.Execute ("insert into my_tbl (t1) values ('a')")
Dim s As Long
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set rs = conn.Execute("select scope_identity()")
s = rs.Fields(0).Value
End Sub

"s" will contain the value of the identity column you want

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[sjulian]

I ended up using gmmastros' method and it is working.
Thanks a 1,000,000!