Show the Identity Field Using the addnew method

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Show the Identity Field Using the addnew method

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-24 : 07:49:56

Ryan writes "I have tried and tried... 15 hours now... to get the Identity field in my Sql 2000 db to show up on a confirmation page after the user submits the info. What happens is this page adds 1 records for every compnay selected and adds it to the database. It worked great in Access and the autonumber field was returned. But since my move to SQL Server 2000 i cannot get it to work.. I think i have tried every possible solution on the net that I could find.. gives no errors.. but does not give a number I am stumped....

Here is my code..

cst = "Provider=SQLOLEDB;Data Source=server;" & _
"Initial Catalog=db;Network=DBMSSOCN;" & _
"User Id=user;Password=pwd"
set objConn = CreateObject("ADODB.Connection")
objConn.open cst
' // Use of Network=DBMSSOCN is to avoid Named Pipes errors;
' // see Article #2082 for more details

DIM objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
Set ts_info = Server.CreateObject("ADODB.Recordset")

objRS.Open "customers", objConn, adOpenKeySet, adLockOptimistic, adCmdTable

Dim IDSS
IDSS = request.form("IDS")
IDArray = Split(IDSS,",")
Dim iItem

For Each iItem in IDArray
ts_info.Open "Select * From table WHERE ID=" & iItem & "", objConn, adLockOptimistic, adCmdTable
objRS.AddNew
**add my items**

objRS.Update
'Now, we must get the identity for the value we just inserted!
Dim rsIdentity 'a recordset to hold the identity value

'This line of code will get us the indentity value for the row
' we just inserted!!
Set rsIdentity = objConn.Execute("SELECT @@IDENTITY FROM customers")

'Read the current value:
Dim iCurrentValue
iCurrentValue = rsIdentity(0)

Response.Write ("Estimate Request #" & iCurrentValue & " sent successfully to:
" & ts_info("tree_service") & " 

")

ts_info.Close
Next
'JMail = Nothing
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
 <hr color="#6487DC">"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-03-24 : 08:49:36

Search here for @@identity used with STORED PROCEDURES....(which is the best SQL Coding practice anyway)...that will give you a roadmap as to how to solve the problem.

@@identiy is only available WITHIN the insert session....it is not a global variable waiting around for a 'SELECT' statement to be invoked at a later stage.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources