getting ident_current into a vb variable

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Of course, if you're working in VB .Net, you will need all the .Value properties since .Net doesn't support default collections/properties.
shayne23dAuthor Commented:
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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.
My suggestion 1 above assumes you're working against a MS SQL server:  IDENT_CURRENT is a SQL-specific funciton.
shayne23dAuthor Commented:
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

shayne23dAuthor Commented:
I mean I will accept your comments as the answer and award the points to you

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shayne23dAuthor Commented:
Very nice. Great suggestion. I have increased the point value for the added help
Thanks!  Feel free to add comments here if you need anything more with this issue.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.