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

'rsStoryData("fld_story_catagory_ID") = Upload.Form("catagoryID")
rsStoryData("fld_story_title") = Upload.Form("title")
rsStoryData("fld_story_dateCreated") = now

rsStoryData.Update

'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]]

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

'rsStoryData("fld_story_catagory_ID") = Upload.Form("catagoryID")
rsStoryData("fld_story_title") = Upload.Form("title")
rsStoryData("fld_story_dateCreated") = now

rsStoryData.Update

'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]

Sorry. Here's the full version...

Set objWriteConn = Server.CreateObject("ADODB.Connection")
objWriteConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=E:\InetPub\Clients\xxxxx\cgi-bin\writing.mdb"
Set rsStoryData = Server.CreateObject("ADODB.Recordset")
storySQL = "SELECT * FROM tbl_stories;"
rsStoryData.Open storySQL, objWriteConn, adOpenStatic,
adLockOptimistic, adCmdText

rsStoryData.AddNew

'rsStoryData("fld_story_catagory_ID") = Upload.Form("catagoryID")
rsStoryData("fld_story_title") = Upload.Form("title")
rsStoryData("fld_story_dateCreated") = now
rsStoryData("fld_story_body") = Upload.Form("storyBody")
rsStoryData("fld_story_quote1") = Upload.Form("quote1")
rsStoryData("fld_story_quote2") = Upload.Form("quote2")
rsStoryData("fld_story_quote3") = Upload.Form("quote3")
rsStoryData.Update

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

rsStoryData.Close
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.AddNew

'rsStoryData("fld_story_catagory_ID") = Upload.Form("catagoryID")
rsStoryData("fld_story_title") = Upload.Form("title")
rsStoryData("fld_story_dateCreated") = now

rsStoryData.Update

'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]]

the other john wrote:

Sorry. Here's the full version...

Set objWriteConn = Server.CreateObject("ADODB.Connection")
objWriteConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=E:\InetPub\Clients\xxxxx\cgi-bin\writing.mdb"
Set rsStoryData = Server.CreateObject("ADODB.Recordset")
storySQL = "SELECT * FROM tbl_stories;"
rsStoryData.Open storySQL, objWriteConn, adOpenStatic,
adLockOptimistic, 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.Update

'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]

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.CreateObject("ADODB.Connection")
objWriteConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=E:\InetPub\Clients\xxxxx\cgi-bin\writing.mdb"
Set rsStoryData = Server.CreateObject("ADODB.Recordset")
storySQL = "SELECT * FROM tbl_stories;"
rsStoryData.Open storySQL, objWriteConn, adOpenStatic,
adLockOptimistic, 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.Update

'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"