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

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

 

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

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.

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.

 

[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!