Insert data into SQL Server db - find out the record number

[D. Leger]

Hello all,

I'm using the following code to add records to a SQL Server Express database
table using an INSERT statement in the SQL string.

Set cnt = CreateObject("ADODB.Connection")
With cnt
.ConnectionString = connString
.Open
.Execute sqlString
.Close
End With

The table I add data to has an auto-increment Record_No field (Identity
Increment). It is also the primary key. How do I determine this record_no? I
need it to subsequently add data to another table.

Thanks

 

[Tore]

I am not sure I fully understood your problem, but you can get the maximum
record_no

Dim RS as new ADODB.recodset
cnt.commandType = adcmdtext
cnt.commandText = "Select max(Record_No) from Tablename"
set RS = cnt.execute
if RS.EOF then
'Nothing was returned
else
msgbox(RS(0)) 'returns first item in first row of recordset. You will only
have one row
End IF

 

[D. Leger]

Thank you Tore for answering my question.

The line "cnt.commandType = adcmdtext" generated an error but your code put
me on the right track.

I modified my code as follows:

Set cnt = CreateObject("ADODB.Connection")

sqlString = "INSERT INTO ..."
sqlString2 = "SELECT MAX(Protein_No) FROM IDENTIFIED_PROTEIN"
With cnt
.ConnectionString = connString
.Open
.Execute sqlString
End With

Set RS = cnt.Execute(sqlString2)

If RS.EOF Then
'Nothing was returned
Else
MsgBox (RS(0)) 'returns first item in first row of recordset. You
will only have one row
End If

cnt.Close

 

[Tore]

About the error you experienced with cnt.commandType = adcmdtext.

May be you should add some library to your code. If it works ok then don't
bother, but if you need some more ado functionality do the following:

Go to your visual basic code window. Click on the Tools command in the menu
bar. Select "references" and add "Microsoft Active X Data Object". Usually
you should select the most recent edition. This will add some new
possibilities to your code and should eliminate the error you mentioned.

If you send your excel workbook to someone, they need to have all the
references you have added available on their pc, or things may fail.

Tore

 

[D. Leger]

Ok, thanks for the suggestion and thank you once again for taking the time to
answer my post.

Dan

 

[Tim Williams]

If this is for >1 user and you might have simultaneous inserts then this is
not the best approach: how do you know it's "your" RecordNo you're getting
back ?

SQL Server has "@@IDENTITY" for this:
http://msdn.microsoft.com/en-us/library/ms187342.aspx

Tim

 

[Tore]

You are right, you don't know it is "your" record, you only know it is the
last one at the time of the select statement. If you need to make sure that
it is "your" record you will have do insert as well as to find the last
incremented value within a stored procedure on the server. I prefer to use
"ident_current" in stead of @@identity. @@Identity seems to give you the
latest identity insert in the database, no matter what table in the database
that had a record inserted.

Tore