Cannot Determine New Record ID

[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...dentityColumn/ 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.

[Bob Barrows [MVP]]

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/ind...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"

[Ray Costanzo MVP]

Try the suggestions here. http://www.aspfaq.com/show.asp?id=2174

Ray at home

[Robine]

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
(
@property_id int output,
@user_id int,
@property_address1 varchar(100),
@property_address2 varchar(100),
@property_address3 varchar(100),
@property_town varchar(100),
@property_county varchar(100),
@county_index int,
@property_postcode varchar(8),
@price int,
@property_type varchar(50),
@property_type_index int,
@tenure varchar(50),
@tenure_index int,
@property_description varchar(700),
@number_receptionrooms int,
@number_bedrooms int,
@number_bathrooms int,
@main_image varchar(100),
@entered_date datetime
)
AS
BEGIN
INSERT INTO hk_property (user_id, property_address1, property_address2,
property_address3, property_town, property_county, county_index,
property_postcode, price, property_type, property_type_index, tenure,
tenure_index, property_description, number_receptionrooms,
number_bedrooms, number_bathrooms, main_image, entered_date)
VALUES (@user_id, @property_address1, @property_address2,
@property_address3, @property_town, @property_county, @county_index,
@property_postcode, @price, @property_type, @property_type_index,
@tenure, @tenure_index, @property_description, @number_receptionrooms,
@number_bedrooms, @number_bathrooms, @main_image, @entered_date)
SET @property_id = SCOPE_IDENTITY()
END

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

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?

Many thanks again for your help Bob!
*** Sent via Developersdex http://www.developersdex.com ***

[Bob Barrows [MVP]]

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.

[Robine]

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

*** Sent via Developersdex http://www.developersdex.com ***

[Bob Barrows [MVP]]

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"