getting ident_current into a vb variable

Posted on 2004-11-08
Medium Priority
Last Modified: 2013-12-25
I have an insert statement that I have run through adodb.recordset.
now I am trying to get the primary key out of the database using Scope_identity() function .
I do not know how to call the value of it to vb.
I thought I could do selct statement
Select IDENT_CURRENT('orders')

intOrderID = rs.Fields("IDENT_CURRENT").Value

but does not seem to work? any ideas?
Question by:shayne23d
  • 6
  • 4
LVL 12

Expert Comment

ID: 12529672
If you're adding the record via the ADO recordset Update method, then after you call Update, the record just added becomes the current record.  So you can get its primary key by simply setting the appropriately-typed variable equal to it, i.e.,
rs("field1") = "SomeValue"
rs("field2") = "SomeOtherValue"
SomeVariable = rs("PrimaryKeyField")

Thus, there is no need to try to call directly to SQL to get the ID of the just-added record.
LVL 12

Expert Comment

ID: 12529680
Of course, if you're working in VB .Net, you will need all the .Value properties since .Net doesn't support default collections/properties.

Author Comment

ID: 12535492
here is the call that I am using for the database.
Dim cn As ADODB.Connection
   Dim cmd As ADODB.Command
   Set cn = New ADODB.Connection
   Set cmd = New ADODB.Command
   cmd.ActiveConnection = cn
   cmd.CommandText = strSqlInsert
   cmd.CommandType = adCmdText
   Set rs = New ADODB.Recordset
   rs.Open cmd, , adOpenKeyset, adLockOptimistic

if I then call  variable = rs("Order_ID").value  I get an error

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 12

Expert Comment

ID: 12535757
Oh, I see what you're asking now.  So your strSqlInsert is something like
"INSERT INTO myTable .... "
and so you want to run it and find out the value of the last ID generated by that INSERT command.

I thought you were updating the recordset directly as described above.

I have two ideas:
1.  You could immediate open another recordset by EXECUTEing a COMMAND against your connection with command text = "SELECT IDENT_CURRENT('TableName')" then read the value of NewRS(0).  
2.  You could do something like DMax("IDFieldName","TableName") which will retrieve the maximum value in the field.

Either of these methods might NOT return what you expect in a high-volume environment:  what happens if someone else's changes append the instant between yours and you call to get the last ID?  You'll get someone else's last inserted record id.

Without changing to the method I referred to earlier, where you retrieve your new records then insert them into the new recordset one at a time, I'm not sure you to get the lastest primary key generated by your specific process with absolute certainty.
LVL 12

Expert Comment

ID: 12535768
My suggestion 1 above assumes you're working against a MS SQL server:  IDENT_CURRENT is a SQL-specific funciton.

Author Comment

ID: 12535876
I think I have come up with a solution
strSqlInsert = "DECLARE @id as int  insert into orders (numb_Pages,print_Type, page_Layout, bleed_Option, paper_type, proof, ins, amount, doc_ID, customer_ID) Values('" & intNumbPages & "','" & strPrintType & "', '" & strPageLayout & "', '" & blnBleedOption & "', '" & strPaperType & "' , '" & strProof & "', '" & strInsert & "', '" & intAmount & "', '" & intDocID & "', '" & strCustID & "'" & ") select  * from orders where Order_ID = @id"

let me know if you think that will work.
after getting your feedback i will accept it as the answer


Author Comment

ID: 12535884
I mean I will accept your comments as the answer and award the points to you

LVL 12

Accepted Solution

pique_tech earned 1200 total points
ID: 12536748
I don't think that will do what you want--the syntax doesn't look like it will work on a SQL connection.

However, it seems that you're inserting one record at a time.  Why not take advantage of the following variation on the Recordset.AddNew syntax.  You can basically call the .AddNew method and pass a list of field names as an array and a list of values as an array, and ADO knows to put the values into the fields.  Then you're guaranteed to get the ID generated for that particular record.  It also eliminates the need for the Command object.  You could even eliminate the Connection object unless you need an explicit Connection object.  Let me know if this helps--I think if you clean it up (remove all the commented out stuff I left just to show you what I did), it'll look a lot better and will be easier to maintain.

Dim Order_ID As Long
Dim rs As ADODB.RecordSet
'Dim cn As ADODB.Connection                             ----> you won't need the explicit coonnection object with this suggestion
'Dim cmd As ADODB.Command                            ----> you won't need the command object with this suggestion
'Set cn = New ADODB.Connection                        ----> you won't need the explicit coonnection object with this suggestion
'cn.CONNECTIONSTRING = CONNECTIONSTRING  ----> you won't need the explicit coonnection object with this suggestion
'cn.Open                                                           ----> you won't need the explicit coonnection object with this suggestion
'Set cmd = New ADODB.Command                      ----> you won't need the command object with this suggestion                
'cmd.ActiveConnection = cn                                ----> you won't need the command object with this suggestion
'cmd.CommandText = strSqlInsert                      ----> you won't need the command object with this suggestion
'cmd.CommandType = adCmdText                     ----> you won't need the command object with this suggestion
Set rs = New ADODB.Recordset
rs.Open "TheTableName", CONNECTIONSTRING , adOpenKeyset, adLockOptimistic, adCmdTable
rs.AddNew Array ("numb_Pages","print_Type", "page_Layout", "bleed_Option", "paper_type", "proof", "ins", "amount", "doc_ID", "customer_ID"), Array(intNumbPages,strPrintType, strPageLayout, blnBleedOption, strPaperType, strProof, strInsert, intAmount, intDocID, strCustID)
Order_ID = rs("Order_ID")

'Don't forget to clean up somewhere later in the procedure:
Set rs = Nothing

Author Comment

ID: 12536878
Very nice. Great suggestion. I have increased the point value for the added help
LVL 12

Expert Comment

ID: 12536948
Thanks!  Feel free to add comments here if you need anything more with this issue.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month14 days, 5 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question