Why does this work?

[the other john]

ok, for a change I'm looking for why something "does" work.

I wanted to insert a record into an Access DB and then retrieve the
value that was just inserted. I came across this...

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

I tried this out and it seemed to work but what I noticed is that when
it printed out the value "before" the requery it already contained the
value I was looking for. So, for some reason, this works. why and
how?

Thanks!
'
'
rsStoryData.Add New

'rsStoryData("f ld_story_catago ry_ID") = Upload.Form("ca tagoryID")
rsStoryData("fl d_story_title") = Upload.Form("ti tle")
rsStoryData("fl d_story_dateCre ated") = now

rsStoryData.Upd ate

'retrieve story ID just created and place in variable. why this works
I don't know...
storyID = rsStoryData("PK _story_ID") 'this value is the "new" just
inserted ID value
'
'

[Bob Barrows [MVP]]

Re: Why does this work?

the other john wrote:

ok, for a change I'm looking for why something "does" work.
>
I wanted to insert a record into an Access DB and then retrieve the
value that was just inserted. I came across this...
>

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

>
I tried this out and it seemed to work but what I noticed is that when
it printed out the value "before" the requery it already contained the
value I was looking for. So, for some reason, this works. why and
how?
>
Thanks!
'
'
rsStoryData.Add New
>
'rsStoryData("f ld_story_catago ry_ID") = Upload.Form("ca tagoryID")
rsStoryData("fl d_story_title") = Upload.Form("ti tle")
rsStoryData("fl d_story_dateCre ated") = now
>
rsStoryData.Upd ate
>
'retrieve story ID just created and place in variable. why this works
I don't know...
storyID = rsStoryData("PK _story_ID") 'this value is the "new" just
inserted ID value
'
'

Hard to say, given that you haven't shown us your connection string or
the options used to open your recordset.

I will say that the example in the KB article
1. uses the obsolete ODBC driver
2. erroneously states that a server-side cursor cannot be used with Jet
3. because of the mistaken assumption in 2, uses a client-side cursor
which must be requeried in order to retrieve the value.

You seem to be using a server-side cursor, whose autoincr field is
automatically populated after the update method is executed.

http://www.aspfaq.com/show.asp?id=2174

--
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.

[the other john]

Re: Why does this work?

Sorry. Here's the full version...

Set objWriteConn = Server.CreateOb ject("ADODB.Con nection")
objWriteConn.Op en "Provider=Micro soft.Jet.OLEDB. 4.0; Data
Source=E:\InetP ub\Clients\xxxx x\cgi-bin\writing.mdb "
Set rsStoryData = Server.CreateOb ject("ADODB.Rec ordset")
storySQL = "SELECT * FROM tbl_stories;"
rsStoryData.Ope n storySQL, objWriteConn, adOpenStatic,
adLockOptimisti c, adCmdText

rsStoryData.Add New

'rsStoryData("f ld_story_catago ry_ID") = Upload.Form("ca tagoryID")
rsStoryData("fl d_story_title") = Upload.Form("ti tle")
rsStoryData("fl d_story_dateCre ated") = now
rsStoryData("fl d_story_body") = Upload.Form("st oryBody")
rsStoryData("fl d_story_quote1" ) = Upload.Form("qu ote1")
rsStoryData("fl d_story_quote2" ) = Upload.Form("qu ote2")
rsStoryData("fl d_story_quote3" ) = Upload.Form("qu ote3")
rsStoryData.Upd ate

'retrieve story ID just created and place in variable
storyID = rsStoryData("PK _story_ID")

rsStoryData.Clo se
Set rsStoryData = nothing


Bob Barrows [MVP] wrote:

the other john wrote:

ok, for a change I'm looking for why something "does" work.

I wanted to insert a record into an Access DB and then retrieve the
value that was just inserted. I came across this...

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

I tried this out and it seemed to work but what I noticed is that when
it printed out the value "before" the requery it already contained the
value I was looking for. So, for some reason, this works. why and
how?

Thanks!
'
'
rsStoryData.Add New

'rsStoryData("f ld_story_catago ry_ID") = Upload.Form("ca tagoryID")
rsStoryData("fl d_story_title") = Upload.Form("ti tle")
rsStoryData("fl d_story_dateCre ated") = now

rsStoryData.Upd ate

'retrieve story ID just created and place in variable. why this works
I don't know...
storyID = rsStoryData("PK _story_ID") 'this value is the "new" just
inserted ID value
'
'

Hard to say, given that you haven't shown us your connection string or
the options used to open your recordset.
>
I will say that the example in the KB article
1. uses the obsolete ODBC driver
2. erroneously states that a server-side cursor cannot be used with Jet
3. because of the mistaken assumption in 2, uses a client-side cursor
which must be requeried in order to retrieve the value.
>
You seem to be using a server-side cursor, whose autoincr field is
automatically populated after the update method is executed.
>

http://www.aspfaq.com/show.asp?id=2174

>
>
--
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.

[Bob Barrows [MVP]]

Re: Why does this work?

the other john wrote:

Sorry. Here's the full version...
>
Set objWriteConn = Server.CreateOb ject("ADODB.Con nection")
objWriteConn.Op en "Provider=Micro soft.Jet.OLEDB. 4.0; Data
Source=E:\InetP ub\Clients\xxxx x\cgi-bin\writing.mdb "
Set rsStoryData = Server.CreateOb ject("ADODB.Rec ordset")
storySQL = "SELECT * FROM tbl_stories;"
rsStoryData.Ope n storySQL, objWriteConn, adOpenStatic,
adLockOptimisti c, adCmdText

You see? You are using a server-side cursor which is the default. The writer
of that KB article was wrong when he said server-side cursors could not be
used with Jet ... or he was referring to an earlier version of Jet. You are
using Jet 4.0.

<snip>

rsStoryData.Upd ate
>
'retrieve story ID just created and place in variable
storyID = rsStoryData("PK _story_ID")
>

With server-side cursors, this field is automatically updated.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[the other john]

Re: Why does this work?

LOL, this is so cool! All this time I've been doing work arounds for
nothing! Have to research server-side cursors.

Thanks Bob!


Bob Barrows [MVP] wrote:

the other john wrote:

Sorry. Here's the full version...

Set objWriteConn = Server.CreateOb ject("ADODB.Con nection")
objWriteConn.Op en "Provider=Micro soft.Jet.OLEDB. 4.0; Data
Source=E:\InetP ub\Clients\xxxx x\cgi-bin\writing.mdb "
Set rsStoryData = Server.CreateOb ject("ADODB.Rec ordset")
storySQL = "SELECT * FROM tbl_stories;"
rsStoryData.Ope n storySQL, objWriteConn, adOpenStatic,
adLockOptimisti c, adCmdText

>
You see? You are using a server-side cursor which is the default. The writer
of that KB article was wrong when he said server-side cursors could not be
used with Jet ... or he was referring to an earlier version of Jet. You are
using Jet 4.0.
>
<snip>

rsStoryData.Upd ate

'retrieve story ID just created and place in variable
storyID = rsStoryData("PK _story_ID")

>
With server-side cursors, this field is automatically updated.
>
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"