By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,657 Members | 751 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,657 IT Pros & Developers. It's quick & easy.

Why does this work?

P: n/a
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
'
'

Aug 7 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.
Aug 7 '06 #2

P: n/a
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.
Aug 8 '06 #3

P: n/a
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"
Aug 8 '06 #4

P: n/a
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"
Aug 8 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.