Recalling a record insert

Discussion in 'ASP General' started by the other john, Jul 14, 2006.

  1. I want to insert a record into a DB and then immediately recall what
    the ID is. I have heard that there is a way to do this with SQL Server
    (although I haven't done it yet). Unfortunately this is for Access.

    I've done some workarounds for this in the past but want to know if
    there are better ways. What I've done in the past is to recall the
    file name or something from the FORM collection against the DB
    (assuming this file is unique). Using aspupload, if the overwrite
    attribute is set for FALSE I can use that as a way to better ensure the
    file name is unique as it renames the file if it is not unique. The
    problem is I need to to this without aspupload now.

    I had thought of recalling the last record ID from the DB on the FORM
    side and putting it in a hidden field but this seems risky. Any
    suggestions?

    Thanks again!
     
    the other john, Jul 14, 2006
    #1
    1. Advertising

  2. There's a section about doing this in Access here:
    http://www.aspfaq.com/show.asp?id=2174

    Ray at work

    "the other john" <> wrote in message
    news:...
    >I want to insert a record into a DB and then immediately recall what
    > the ID is. I have heard that there is a way to do this with SQL Server
    > (although I haven't done it yet). Unfortunately this is for Access.
    >
    > I've done some workarounds for this in the past but want to know if
    > there are better ways. What I've done in the past is to recall the
    > file name or something from the FORM collection against the DB
    > (assuming this file is unique). Using aspupload, if the overwrite
    > attribute is set for FALSE I can use that as a way to better ensure the
    > file name is unique as it renames the file if it is not unique. The
    > problem is I need to to this without aspupload now.
    >
    > I had thought of recalling the last record ID from the DB on the FORM
    > side and putting it in a hidden field but this seems risky. Any
    > suggestions?
    >
    > Thanks again!
    >
     
    Ray Costanzo [MVP], Jul 14, 2006
    #2
    1. Advertising

  3. Excellent, thanks!


    Ray Costanzo [MVP] wrote:
    > There's a section about doing this in Access here:
    > http://www.aspfaq.com/show.asp?id=2174
    >
    > Ray at work
    >
    > "the other john" <> wrote in message
    > news:...
    > >I want to insert a record into a DB and then immediately recall what
    > > the ID is. I have heard that there is a way to do this with SQL Server
    > > (although I haven't done it yet). Unfortunately this is for Access.
    > >
    > > I've done some workarounds for this in the past but want to know if
    > > there are better ways. What I've done in the past is to recall the
    > > file name or something from the FORM collection against the DB
    > > (assuming this file is unique). Using aspupload, if the overwrite
    > > attribute is set for FALSE I can use that as a way to better ensure the
    > > file name is unique as it renames the file if it is not unique. The
    > > problem is I need to to this without aspupload now.
    > >
    > > I had thought of recalling the last record ID from the DB on the FORM
    > > side and putting it in a hidden field but this seems risky. Any
    > > suggestions?
    > >
    > > Thanks again!
    > >
     
    the other john, Jul 14, 2006
    #3
  4. That article needs to be rewritten: the example given will raise an error
    due to the inability of Jet to perform batched queries. This example:
    <%
    fakeValue = 5
    set conn = CreateObject("ADODB.Connection")
    conn.open "<conn string>"
    sql = "INSERT someTable(IntColumn) values(" & fakeValue & ")" & _
    VBCrLf & " SELECT @@IDENTITY"
    set rs = conn.execute(sql)
    response.write "New ID was " & rs(0)
    rs.close: set rs = nothing
    conn.close: set conn = nothing
    %>

    needs to be rewritten as:

    <%
    fakeValue = 5
    set conn = CreateObject("ADODB.Connection")
    conn.open "<conn string>"
    sql = "INSERT someTable(IntColumn) values(" & fakeValue & ")"
    conn.execute sql,,129
    sql = " SELECT @@IDENTITY"
    set rs = conn.execute(sql,,1)
    response.write "New ID was " & rs(0)
    rs.close: set rs = nothing
    conn.close: set conn = nothing
    %>



    the other john wrote:
    > Excellent, thanks!
    >
    >
    > Ray Costanzo [MVP] wrote:
    >> There's a section about doing this in Access here:
    >> http://www.aspfaq.com/show.asp?id=2174
    >>
    >> Ray at work
    >>
    >> "the other john" <> wrote in message
    >> news:...
    >>> I want to insert a record into a DB and then immediately recall what
    >>> the ID is. I have heard that there is a way to do this with SQL
    >>> Server (although I haven't done it yet). Unfortunately this is for
    >>> Access.
    >>>
    >>> I've done some workarounds for this in the past but want to know if
    >>> there are better ways. What I've done in the past is to recall the
    >>> file name or something from the FORM collection against the DB
    >>> (assuming this file is unique). Using aspupload, if the overwrite
    >>> attribute is set for FALSE I can use that as a way to better ensure
    >>> the file name is unique as it renames the file if it is not unique.
    >>> The problem is I need to to this without aspupload now.
    >>>
    >>> I had thought of recalling the last record ID from the DB on the
    >>> FORM side and putting it in a hidden field but this seems risky.
    >>> Any suggestions?
    >>>
    >>> Thanks again!


    --
    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], Jul 14, 2006
    #4
  5. Hi Bob,

    could you explain these parts? I don't understand the number
    references, sorry.

    > conn.execute sql,,129


    > set rs = conn.execute(sql,,1)


    could you be explicit with these so I know what's happening here?

    Thanks again!


    Bob Barrows [MVP] wrote:
    > That article needs to be rewritten: the example given will raise an error
    > due to the inability of Jet to perform batched queries. This example:
    > <%
    > fakeValue = 5
    > set conn = CreateObject("ADODB.Connection")
    > conn.open "<conn string>"
    > sql = "INSERT someTable(IntColumn) values(" & fakeValue & ")" & _
    > VBCrLf & " SELECT @@IDENTITY"
    > set rs = conn.execute(sql)
    > response.write "New ID was " & rs(0)
    > rs.close: set rs = nothing
    > conn.close: set conn = nothing
    > %>
    >
    > needs to be rewritten as:
    >
    > <%
    > fakeValue = 5
    > set conn = CreateObject("ADODB.Connection")
    > conn.open "<conn string>"
    > sql = "INSERT someTable(IntColumn) values(" & fakeValue & ")"
    > conn.execute sql,,129
    > sql = " SELECT @@IDENTITY"
    > set rs = conn.execute(sql,,1)
    > response.write "New ID was " & rs(0)
    > rs.close: set rs = nothing
    > conn.close: set conn = nothing
    > %>
    >
    >
    >
    > the other john wrote:
    > > Excellent, thanks!
    > >
    > >
    > > Ray Costanzo [MVP] wrote:
    > >> There's a section about doing this in Access here:
    > >> http://www.aspfaq.com/show.asp?id=2174
    > >>
    > >> Ray at work
    > >>
    > >> "the other john" <> wrote in message
    > >> news:...
    > >>> I want to insert a record into a DB and then immediately recall what
    > >>> the ID is. I have heard that there is a way to do this with SQL
    > >>> Server (although I haven't done it yet). Unfortunately this is for
    > >>> Access.
    > >>>
    > >>> I've done some workarounds for this in the past but want to know if
    > >>> there are better ways. What I've done in the past is to recall the
    > >>> file name or something from the FORM collection against the DB
    > >>> (assuming this file is unique). Using aspupload, if the overwrite
    > >>> attribute is set for FALSE I can use that as a way to better ensure
    > >>> the file name is unique as it renames the file if it is not unique.
    > >>> The problem is I need to to this without aspupload now.
    > >>>
    > >>> I had thought of recalling the last record ID from the DB on the
    > >>> FORM side and putting it in a hidden field but this seems risky.
    > >>> Any suggestions?
    > >>>
    > >>> Thanks again!

    >
    > --
    > 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, Jul 14, 2006
    #5
  6. the other john wrote:
    > Hi Bob,
    >
    > could you explain these parts? I don't understand the number
    > references, sorry.


    The documentation for all MS technologies, including ADO, can be found here:
    http://msdn.microsoft.com/library

    The ADO documentation can be found by drilling into the "Win32 and COM
    Development" node to "Data Access and Storage" to "Microsoft "ActiveX Data
    Objects (ADO)" to "ADO Programmers Reference" to "ADO API Reference".

    >
    >> conn.execute sql,,129


    Specifically, in this case, you are looking for the
    Execute Method (Connection object) -
    http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthcnnexecute.asp
    CommandTypeEnum
    (http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstcommandtypeenum.asp)
    and ExecuteOptionEnum
    http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstexecuteoptionenum.asp

    129 is the combination of two constants: adCmdText (always tell ADO what the
    Command Type is - don't make it guess) and adExecuteNoRecords. The latter
    constant tells ADO not to create a recordset because your query is not
    returning records. It is always a good idea to tell it this when executing
    action queries that return no records.

    >
    >> set rs = conn.execute(sql,,1)

    In this case, you should be reading the Open Method (ADO Recordset)
    documentation -
    http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthrstopen.asp
    The 1 is adCmdText. Again, don't make ADO guess what the Command type is.


    --
    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], Jul 14, 2006
    #6
    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. =?Utf-8?B?SnVzdGlu?=

    Retrieving Record Key while creating the record.

    =?Utf-8?B?SnVzdGlu?=, Oct 4, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    656
    =?Utf-8?B?SnVzdGlu?=
    Oct 5, 2004
  2. André
    Replies:
    0
    Views:
    3,771
    André
    Jun 25, 2006
  3. MRW
    Replies:
    0
    Views:
    393
  4. Frits van Soldt

    How to insert a new record in detailsview when there are no record

    Frits van Soldt, Nov 16, 2005, in forum: ASP .Net Web Controls
    Replies:
    2
    Views:
    810
    Frits van Soldt
    Nov 17, 2005
  5. Guest
    Replies:
    1
    Views:
    282
    Alex G
    Jan 9, 2004
Loading...

Share This Page