Why does this work?

Discussion in 'ASP General' started by the other john, Aug 7, 2006.

  1. 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
    '
    '
    the other john, Aug 7, 2006
    #1
    1. Advertising

  2. 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], Aug 7, 2006
    #2
    1. Advertising

  3. 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.
    the other john, Aug 8, 2006
    #3
  4. 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"
    Bob Barrows [MVP], Aug 8, 2006
    #4
  5. 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"
    the other john, Aug 8, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. jblazi
    Replies:
    5
    Views:
    420
    jblazi
    Aug 16, 2004
  2. Horace Nunley

    why why why does function not work

    Horace Nunley, Sep 27, 2006, in forum: ASP .Net
    Replies:
    1
    Views:
    450
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
    Sep 27, 2006
  3. Mr. SweatyFinger

    why why why why why

    Mr. SweatyFinger, Nov 28, 2006, in forum: ASP .Net
    Replies:
    4
    Views:
    861
    Mark Rae
    Dec 21, 2006
  4. Mr. SweatyFinger
    Replies:
    2
    Views:
    1,761
    Smokey Grindel
    Dec 2, 2006
  5. Tarun
    Replies:
    5
    Views:
    382
    Tarun
    Jul 14, 2005
Loading...

Share This Page