Cannot Determine New Record ID

R

Robin Lawrie

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.
 
B

Bob Barrows [MVP]

Robin said:
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.
 
B

Bob Barrows [MVP]

Robine said:
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
 
B

Bob Barrows [MVP]

Robine said:
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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top