?
Solved

ado recordset identity value not being returned

Posted on 2003-03-03
16
Medium Priority
?
298 Views
Last Modified: 2008-01-09
If I run the following code in VB I get the value of the Identity back in the recordset.

If I run the equivalent code in ASP, the value comes back empty.

Private Sub Command1_Click()

sSQL = "SELECT * FROM cpd_ProjectInfo WHERE 0=1"

Set rsProjectInfo = CreateObject("ADODB.RecordSet")
Set Conn = CreateObject("ADODB.Connection")
rsProjectInfo.CursorLocation = adUseClient

rsProjectInfo.Open sSQL, "User ID=iuser;password='xxxxxx';Data Source=IntranetDev", adOpenStatic, adLockBatchOptimistic

rsProjectInfo.AddNew

rsProjectInfo("ClientInfoID") = 1 '(ClientInfoID)
rsProjectInfo("BusinessSolution") = "test"

rsProjectInfo.UpdateBatch

Debug.Print rsProjectInfo("ProjectInfoID")

End Sub
0
Comment
Question by:alaplume
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +3
16 Comments
 
LVL 2

Expert Comment

by:stiemark
ID: 8059980
In your Debug.Print, did you want
  rsProjectInfo("ClientInfoID")
rather than
  rsProjectInfo("ProjectInfoID")
?

Mark
0
 
LVL 2

Expert Comment

by:kraig
ID: 8059991
Try explicitly stating the fields you want to select in your sql statement.  In this case, use:

SELECT ClientInfoID, BusinessSolution
FROM cpd_ProjectInfo
WHERE ClienInfoID=-1
0
 
LVL 1

Author Comment

by:alaplume
ID: 8060025
stiemark: rsProjectInfo("ProjectInfoID") is the identity field. The other fields don't really matter.

kraig: I want to know why the exact same code doesn't work in ASP vs straight VB!
0
Industry Leaders: 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 1

Author Comment

by:alaplume
ID: 8060028
stiemark: rsProjectInfo("ProjectInfoID") is the identity field. The other fields don't really matter.

kraig: I want to know why the exact same code doesn't work in ASP vs straight VB!
0
 
LVL 1

Author Comment

by:alaplume
ID: 8060065
stiemark: rsProjectInfo("ProjectInfoID") is the identity field. The other fields don't really matter.

kraig: I want to know why the exact same code doesn't work in ASP vs straight VB!
0
 
LVL 2

Expert Comment

by:kraig
ID: 8060072
Don't ask me.

I didn't make up asp, I just try to make it work for what I need it to do.

I've had this issue before, but don't remember exactly how I got around it...

Good luck.
0
 
LVL 10

Expert Comment

by:apollois
ID: 8060109
alaplume,

>>>rsProjectInfo.Open sSQL, "User ID=iuser;password='xxxxxx';Data Source=IntranetDev", adOpenStatic, adLockBatchOptimistic<<<

this connection string won't work in ASP.  You need something like:

===================================================
strConn = "Provider=SQLOLEDB.1;" _
          & "Server=SQLServerMachineNameOrIP;" _
            & "Database=YourDBName;"_
            & "uid=UserName;" _
          & "pwd=Password;"

SET Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConn

Set rsProjectInfo = CreateObject("ADODB.RecordSet")
rsProjectInfo.CursorLocation = adUseClient

rsProjectInfo.Open sSQL, Conn, adOpenStatic, adLockBatchOptimistic
======================================================

If this doesn't help, please post your ASP code.

Best Regards,
>apollois<
0
 
LVL 1

Author Comment

by:alaplume
ID: 8060150
apollois: assume the connection string is ok.

sSQL = "SELECT * FROM cpd_ProjectInfo WHERE 0=1"

Set rsProjectInfo = Server.CreateObject("ADODB.RecordSet")

rsProjectInfo.CursorLocation = adUseClient

rsProjectInfo.Open sSQL, DBConnString, adOpenStatic,
adLockBatchOptimistic

rsProjectInfo.AddNew()

rsProjectInfo("ClientInfoID") = 1
rsProjectInfo("BusinessSolution") = "test"

rsProjectInfo.UpdateBatch()

response.write("<br>" & isEmpty(rsProjectInfo("ProjectInfoID")))
response.write(":ProjectInfoID<br>")

End Function
0
 
LVL 10

Expert Comment

by:apollois
ID: 8060189
Replace
rsProjectInfo.UpdateBatch()


WITH
rsProjectInfo.Update



IF NOT rsProjectInfo.EOF THEN
   response.write "<BR>New ProjectInfoID is: " & rsProjectInfo("ProjectInfoID") & "<BR>"
ELSE
  Response.write "<BR> Unable to ADD new Record<BR>"
END IF


This will work if you connection string is good, and you have established a connection to the DB, and provided that "ProjectInfoID" is an IDENTITY field
0
 
LVL 10

Expert Comment

by:MaxOvrdrv2
ID: 8060306
here is what your code should be in ASP:
<%
sSQL = "SELECT * FROM cpd_ProjectInfo WHERE 0=1"

Set rsProjectInfo = Server.CreateObject("ADODB.RecordSet")
Set Conn = Server.CreateObject("ADODB.Connection")
rsProjectInfo.ActiveConnection=Conn

rsProjectInfo.Open sSQL, "User ID=iuser;password='xxxxxx';Data Source=IntranetDev", adOpenStatic, adLockBatchOptimistic

if rsProjectInfo.EOF=False AND rsProjectInfo.BOF=False then
       rsProjectInfo.MoveLast
end if

rsProjectInfo.AddNew

rsProjectInfo("ClientInfoID") = 1 '(ClientInfoID)
rsProjectInfo("BusinessSolution") = "test"

rsProjectInfo.Update

rsProjectInfo.Close
rsProjectInfo.Open "SELECT * FROM  cpd_ProjectInfo WHERE CLientInfoID=1"

response.write rsProjectInfo("ProjectInfoID")
%>

Hope this helps

MaxOvrdrv2
0
 
LVL 3

Expert Comment

by:jarw
ID: 8061606
If you can use server-side cursors (like I do) you can try changing the CursorLocation property to "adUseServer" and the cursor type to "adOpenKeyset" (from "adOpenStatic").

Hope this helps.
0
 
LVL 10

Accepted Solution

by:
apollois earned 400 total points
ID: 8061643
Max,

>>>rsProjectInfo.Open "SELECT * FROM  cpd_ProjectInfo WHERE CLientInfoID=1"<<<

I'm sorry, but this is a bad idea.  You are assuming that clientInfoID is unique!  It may or may not be.

There are two well-established techniques for obtaining the primary key of a newly inserted record that uses autonumber/IDENTITY for the primary key:
1.  Use INSERT followed by SELECT @@IDENTITY
2.  Use Recordset.AddNew followed by Recordset.update

Best Regards,
>apollois<
0
 
LVL 10

Expert Comment

by:MaxOvrdrv2
ID: 8064144
apollo... well... from the way he is adding his record (.addnew ("ClientInfo")=1 .update)...and since all he wants is a way to see how it works in ASP... i assumed that checking for 1 would be the best idea... and i mean... as far as i see.. the @@identity only works on MS SQL databases...

anyways...

my code still works... :)

MaxOvrdrv2
0
 
LVL 10

Expert Comment

by:apollois
ID: 8066244
MaxOvrdrv2,

I didn't intend to start a debate here, but your code will only work if the ClientInifo is a unique field.

Both Oracle and MySQL have techniques to get the PK of the record just inserted.

IAC, using ADO with Recordset.Update should work for any DB.  This ensures that you are getting the correct PK without having do issue any additional SQL statements.

Best Regards,
>apollois<
0
 
LVL 10

Expert Comment

by:MaxOvrdrv2
ID: 8066767
i know... and i agree... i was just providing an example using the same code that he posted originally... which... upon looking at it... was simply a test... to see if he could retreive the right record... later on... he could easily replace the 1 with a dynamic variable/value...

but yes... i do agree with you... there are many ways to get the last entered record in a DB... i was just basing myself on his example...

i didn't mean to be mean or anything... i was just defending my basis... :)

MaxOvrdrv2
0
 
LVL 1

Author Comment

by:alaplume
ID: 8067203
I quit on trying to get the value back the way I wanted. The next best way was to use the @@IDENTITY.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses

752 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