ado recordset identity value not being returned

In your Debug.Print, did you want
  rsProjectInfo("ClientInfoID")
rather than
  rsProjectInfo("ProjectInfoID")
?

Mark

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

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!

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!

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!

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.

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<

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

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

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

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.

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

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<

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

I quit on trying to get the value back the way I wanted. The next best way was to use the @@IDENTITY.