id of new record

I can't seem to get id of new record. What am I missing ?

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;"&_
"Data Source=" & Server.MapPath("/mydb.mdb")

set conn=server.createobject("adodb.connection")
conn.Open strCon

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorLocation = adUseClient
objRS.Open "myTable" , conn, adOpenKeyset, adLockOptimistic, adCmdTable
objRS.AddNew

' ...other code to assign values....

objRS.Update
num = objRS.Fields.Item("ID").Value '<--ID is autonumber field

objRS.Close
Set objRS = Nothing

'/debug/
showmsg "num=" & num : response.end

Re: id of new record

Jon Paal wrote:
> I can't seem to get id of new record. What am I missing ?
>
> strCon = "Provider=Microsoft.Jet.OLEDB.4.0;"&_
> "Data Source=" & Server.MapPath("/mydb.mdb")
>
> set conn=server.createobject("adodb.connection")
> conn.Open strCon
>
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objRS.CursorLocation = adUseClient

You need to use adUseServer to get the ID automatically updated.

> objRS.Open "myTable" , conn, adOpenKeyset, adLockOptimistic,

adOpenKeyset is ignored when requesting an adUseClient cursor location.
Only static cursors (adOpenStatic) are allowed when using adUseClient

> adCmdTable objRS.AddNew
>
> ' ...other code to assign values....
>
> objRS.Update
> num = objRS.Fields.Item("ID").Value '<--ID is autonumber field
>
> objRS.Close
> Set objRS = Nothing
>
> '/debug/
> showmsg "num=" & num : response.end

You should not be using a recordset to maintain data in ASP. Use SQL DML
(Data Modification Language - INSERT, UPDATE, DELETE) queries instead.
In ASP, recordsets should only be used to retrieve read-only data for
display purposes.
Here is some reading material:

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl




--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: id of new record

thanks,

MS uses recordsets, so should be ok...

I did find this too, it seems to work.

http://support.microsoft.com/default.aspx/kb/221931



"Bob Barrows [MVP]" wrote in message news:Oh1KDxUwGHA.976@TK2MSFTNGP05.phx.gbl...
> Jon Paal wrote:
>> I can't seem to get id of new record. What am I missing ?
>>
>> strCon = "Provider=Microsoft.Jet.OLEDB.4.0;"&_
>> "Data Source=" & Server.MapPath("/mydb.mdb")
>>
>> set conn=server.createobject("adodb.connection")
>> conn.Open strCon
>>
>> Set objRS = Server.CreateObject("ADODB.Recordset")
>> objRS.CursorLocation = adUseClient
>
> You need to use adUseServer to get the ID automatically updated.
>
>> objRS.Open "myTable" , conn, adOpenKeyset, adLockOptimistic,
>
> adOpenKeyset is ignored when requesting an adUseClient cursor location.
> Only static cursors (adOpenStatic) are allowed when using adUseClient
>
>> adCmdTable objRS.AddNew
>>
>> ' ...other code to assign values....
>>
>> objRS.Update
>> num = objRS.Fields.Item("ID").Value '<--ID is autonumber field
>>
>> objRS.Close
>> Set objRS = Nothing
>>
>> '/debug/
>> showmsg "num=" & num : response.end
>
> You should not be using a recordset to maintain data in ASP. Use SQL DML
> (Data Modification Language - INSERT, UPDATE, DELETE) queries instead.
> In ASP, recordsets should only be used to retrieve read-only data for
> display purposes.
> Here is some reading material:
>
> See here for a better, more secure way to execute your queries by using
> parameter markers:
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
> Personally, I prefer using stored procedures, or saved parameter queries
> as
> they are known in Access:
>
> Access:
> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>
>
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: id of new record

Jon Paal wrote:
> thanks,
>
> MS uses recordsets, so should be ok...
>
What do you mean by that statement? MS "uses" recordsets? Where are you
talking about them using them? Do you have any conception about the
difference between server-based applications like ASP and desktop
applications like Access and VB?

But, I'm happy you've got your thing working.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: id of new record

yes, I know the difference...
check the link I posted, you'll see they show "The sample uses VBScript in an Active Server Pages (ASP) page."


"Bob Barrows [MVP]" wrote in message news:uI07fyVwGHA.4872@TK2MSFTNGP02.phx.gbl...
> Jon Paal wrote:
>> thanks,
>>
>> MS uses recordsets, so should be ok...
>>
> What do you mean by that statement? MS "uses" recordsets? Where are you
> talking about them using them? Do you have any conception about the
> difference between server-based applications like ASP and desktop
> applications like Access and VB?
>
> But, I'm happy you've got your thing working.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: id of new record

Oh, I see. I didn't realize you were posting that link as an example of
"MS uses recordsets".

Unfortunately, the docs are full of samples that illustrate a point
being made but which simply should not be used in production code.
Unfortuantely, these sample will never be rewritten, and all we can do
is point out when the samples should not be used. In this instance, the
KB article was probably written before Jet 4.0 introduced the use of
"Select @@IDENTITY" to retrieve the last-generated autonumber value.
This article supersedes the one you cited:
http://support.microsoft.com/kb/232144/EN-US/

Jon Paal wrote:
> yes, I know the difference...
> check the link I posted, you'll see they show "The sample uses
> VBScript in an Active Server Pages (ASP) page."
>
>
> "Bob Barrows [MVP]" wrote in message
> news:uI07fyVwGHA.4872@TK2MSFTNGP02.phx.gbl...
>> Jon Paal wrote:
>>> thanks,
>>>
>>> MS uses recordsets, so should be ok...
>>>
>> What do you mean by that statement? MS "uses" recordsets? Where are
>> you talking about them using them? Do you have any conception about
>> the difference between server-based applications like ASP and desktop
>> applications like Access and VB?
>>
>> But, I'm happy you've got your thing working.
>>
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.