Cannot Determine New Record ID

Discussion in 'ASP General' started by Robin Lawrie, Jan 15, 2006.

  1. Robin Lawrie

    Robin Lawrie Guest

    Hi,

    Looking for some help here!

    I'm adding records to a SQL 2000 DB using the objRS.AddNew and objRS.Update
    methods.

    What I'd like to do is determine the new primary key value of the added
    record.

    I've tried the suggestions here :
    http://authors.aspalliance.com/remas/VFAQ/IdentityColumn/ and here:
    http://support.microsoft.com/default.aspx/kb/221931 but none of them work
    reliably. The Microsoft method worked a couple of times then kept reporting
    the same Primary Key ID back to me then reported an old Primary Key.

    I've spent a couple of hours on this which is ridiculous as this must be a
    common thing to do.

    Any suggestions / proven code very, very welcome!

    Thanks in advance,

    Robin.
     
    Robin Lawrie, Jan 15, 2006
    #1
    1. Advertising

  2. Robin Lawrie wrote:
    > Hi,
    >
    > Looking for some help here!
    >
    > I'm adding records to a SQL 2000 DB using the objRS.AddNew and
    > objRS.Update methods.


    Don't. Cursors( recordsets) are very inefficient when it comes to modifying
    data. They should only be used to retrieve read-only data in ASP
    applications.

    Use sql DML (Data Modification Language - INSERT, UPDATE and DELETE
    statements) in a stored procedure that uses an output parameter to return
    the identity value using the SCOPE_IDENTITY function.

    For example, consider this table:

    CREATE TABLE dbo.bookings
    (
    BookingID int IDENTITY,
    FromTime datetime NOT NULL,
    ToTime datetime NOT NULL,
    RoomID int NULL,
    CONSTRAINT PK_bookings
    PRIMARY KEY CLUSTERED (BookingID)
    ON
    [PRIMARY]
    )

    I would create a stored procedure as follows:
    CREATE PROCEDURE bookings_INS
    (
    @BookingID int output,
    @FromTime datetime,
    @ToTime datetime,
    @RoomID int
    )
    AS
    BEGIN
    INSERT INTO dbo.bookings ( FromTime, ToTime, RoomID)
    VALUES (@FromTime, @ToTime,@RoomID)
    SET @BookingID = SCOPE_IDENTITY()
    END

    To test this in Query Analyzer, you would do this:

    declare @id int
    exec bookings_INS @id output, '13:00', '14:00', 5
    select @id as [new id]

    To run this from ASP, you would get my free stored procedure code generator
    from
    http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear
    and use it to create the following code:

    dim newid
    Dim cnSQL
    cnSQL.Open "<your connection string>"
    Dim cmd, param

    Set cmd=server.CreateObject("ADODB.Command")
    With cmd
    .CommandType=adcmdstoredproc
    .CommandText = "bookings_INS"
    set .ActiveConnection=cnSQL
    set param = .createparameter("@RETURN_VALUE", adInteger, _
    adParamReturnValue, 0)
    .parameters.append param
    set param = .createparameter("@BookingID", adInteger, _
    adParamOutput)
    .parameters.append param
    set param = .createparameter("@FromTime", adDBTimeStamp, _
    adParamInput, 0, cdate(13:00))
    .parameters.append param
    set param = .createparameter("@ToTime", adDBTimeStamp, _
    adParamInput, 0, cdate(14:00))
    .parameters.append param
    set param = .createparameter("@RoomID", adInteger, _
    adParamInput, 0, 5)
    .parameters.append param
    .execute ,,adexecutenorecords

    newid= .Parameters("@BookingID").value

    end with


    HTH,
    Bob Barrows
    PS. if you wish to persist with the inefficient recordset method, then you
    are going to need to show us your code. We can't help you in a vacuum.

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

  3. Ray Costanzo MVP, Jan 16, 2006
    #3
  4. Robine wrote:
    > Hi Bob,
    >
    > Thanks for your excellent posting....I'm currently looking into Stored
    > Procedures and utilising them in my web application.
    >
    > I've got a further couple of questions if you don't mind!
    >
    > Firstly, the web application I'm building is for an estate agents.
    > Using your example, I am trying to update a table with information
    > about a property. The table has lot's of fields and I've come up with
    > this stored procedure to update it:
    >
    > CREATE PROCEDURE bookings_INS

    Nothing says it has to be called "bookings_INS" :)

    > Firstly, is this SP updating too many tables or can it cope with all
    > of these fields?


    no problem. Actually, this will be more efficient than sending the entire
    SQL statement across the wire (which is what will happen when you use a
    recordset to update the table.)
    >
    > Secondly, @entered_date is a datetime data type.
    >
    > Using the objrs.addnew method before, I could set this value using
    > objrs("entered_date") = Now() but I don't know how to use Now() to get
    > the current date and time in the @entered_date field in the new sp?


    You don't need to even pass the date to the procedure.
    In Transact-SQL (which is the "brand" of sql used by SQL Server), You can
    use either the GETDATE() function or the CURRENT_TIMESTAMP function. In the
    VALUES clause, just replace @entered_date with a call to either function:

    @number_bedrooms, @number_bathrooms, @main_image, CURRENT_TIMESTAMP)

    or

    @number_bedrooms, @number_bathrooms, @main_image, GETDATE())

    You can also set the default for the column to either function so you don't
    even have to mention the column in your INSERT statements.

    Bob Barrows


    --
    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], Jan 16, 2006
    #4
  5. Robine wrote:
    > Hi Bob,
    >
    > I've written my first stored procedure now, tested it using Query
    > Analyser as you suggested and have now implemented the ASP code into
    > my application after downloading your Stored Procedure Generator code
    > (which is very handy by the way!) and it's working which is excellent
    > - my first stored procedure :)
    >
    > I'm becoming a convert to them now after being a little scared of them
    > first of all as I didn't understand them. The next one I'll be writing
    > will be an update one and then a delete one....I think I know what to
    > do......
    >
    > Thanks very much again for your help and your excellent explanations
    > and advice......


    Excellent.
    We'll be here if you need any more help.

    Bob Barrows
    PS. You can modify that procedure to handle both inserts and updates:

    CREATE PROCEDURE bookings_INSUPD
    (
    @BookingID int=0 output, --assign default to parm
    @FromTime datetime,
    @ToTime datetime,
    @RoomID int
    )
    AS
    BEGIN
    IF @BookingID = 0
    BEGIN
    INSERT INTO dbo.bookings ( FromTime, ToTime, RoomID)
    VALUES (@FromTime, @ToTime,@RoomID)
    SET @BookingID = SCOPE_IDENTITY()
    END
    ELSE
    BEGIN
    UPDATE dbo.bookings
    SET FromTime= @FromTime,
    ToTime= @ToTime,
    RoomID= @RoomID
    WHERE BookingID = @BookingID
    END
    END

    This will allow you to use a single function in your vbscript code to handle
    both inserts and updates (you want to use adParmInputOutput for the
    @BookingID parameter)

    function InsUpdBookings (BookingID, FromTime, ToTime, RoomID)
    'run the procedure, then
    InsUpdBookings = newid
    end function

    The code that calls this function will know whether or not 0 was supplied
    for BookingID and will handle the result accordingly.

    --
    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], Jan 18, 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. André
    Replies:
    0
    Views:
    3,750
    André
    Jun 25, 2006
  2. Laszlo Nagy

    dbfpy - cannot store new record

    Laszlo Nagy, May 21, 2009, in forum: Python
    Replies:
    0
    Views:
    475
    Laszlo Nagy
    May 21, 2009
  3. 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:
    750
    Frits van Soldt
    Nov 17, 2005
  4. André
    Replies:
    0
    Views:
    445
    André
    Jun 25, 2006
  5. Maximus
    Replies:
    2
    Views:
    193
    Bob Barrows [MVP]
    Apr 12, 2007
Loading...

Share This Page