473,700 Members | 2,823 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Cannot Determine New Record ID

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.
Jan 15 '06 #1
6 2203
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.Crea teObject("ADODB .Command")
With cmd
.CommandType=ad cmdstoredproc
.CommandText = "bookings_I NS"
set .ActiveConnecti on=cnSQL
set param = .createparamete r("@RETURN_VALU E", adInteger, _
adParamReturnVa lue, 0)
.parameters.app end param
set param = .createparamete r("@BookingID ", adInteger, _
adParamOutput)
.parameters.app end param
set param = .createparamete r("@FromTime" , adDBTimeStamp, _
adParamInput, 0, cdate(13:00))
.parameters.app end param
set param = .createparamete r("@ToTime", adDBTimeStamp, _
adParamInput, 0, cdate(14:00))
.parameters.app end param
set param = .createparamete r("@RoomID", adInteger, _
adParamInput, 0, 5)
.parameters.app end param
.execute ,,adexecutenore cords

newid= .Parameters("@B ookingID").valu e

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"
Jan 15 '06 #2
Try the suggestions here. http://www.aspfaq.com/show.asp?id=2174

Ray at home
Jan 16 '06 #3
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_addre ss1 varchar(100),
@property_addre ss2 varchar(100),
@property_addre ss3 varchar(100),
@property_town varchar(100),
@property_count y varchar(100),
@county_index int,
@property_postc ode varchar(8),
@price int,
@property_type varchar(50),
@property_type_ index int,
@tenure varchar(50),
@tenure_index int,
@property_descr iption varchar(700),
@number_recepti onrooms int,
@number_bedroom s int,
@number_bathroo ms int,
@main_image varchar(100),
@entered_date datetime
)
AS
BEGIN
INSERT INTO hk_property (user_id, property_addres s1, property_addres s2,
property_addres s3, property_town, property_county , county_index,
property_postco de, price, property_type, property_type_i ndex, tenure,
tenure_index, property_descri ption, number_receptio nrooms,
number_bedrooms , number_bathroom s, main_image, entered_date)
VALUES (@user_id, @property_addre ss1, @property_addre ss2,
@property_addre ss3, @property_town, @property_count y, @county_index,
@property_postc ode, @price, @property_type, @property_type_ index,
@tenure, @tenure_index, @property_descr iption, @number_recepti onrooms,
@number_bedroom s, @number_bathroo ms, @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 ***
Jan 16 '06 #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_I NS" :-)
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_TIMESTA MP function. In the
VALUES clause, just replace @entered_date with a call to either function:

@number_bedroom s, @number_bathroo ms, @main_image, CURRENT_TIMESTA MP)

or

@number_bedroom s, @number_bathroo ms, @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.
Jan 16 '06 #5
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 ***
Jan 18 '06 #6
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 adParmInputOutp ut 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"
Jan 18 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2242
by: Hennie de Nooijer | last post by:
Hi, This is a diffcult issue to explain. I hope to make my problem clear to you. SITUATION I'm building A SLA Query for a customer. This customer has an awkward way to determine the SLA results ;-) Depending on a category which is stored in a headertable (Requests) a field and logic is determined how to get a proper Close_Date. This Close_date can be the closedate of the request. It is also possible that the close_date is a certain...
5
6010
by: David C. Barber | last post by:
I'm trying to determine which user has locked a given record from VB6. I know I can use sp_lock and sp_who, and match up the data to determine which users have locked records in my database, however I haven't seen a way to match the specific user to the specific record. What am I missing here? Thanks! *David*
4
6450
by: Phillip J. Allen | last post by:
Hi all, I have a table with an “autonumber” primary key field that also acts as a foreign key in 2 other tables. I would like to programmatically add a new record to the first table and the other 2 child tables. The reason for doing this is to copy one complete record (parent and child table records) into a new record so the user can make a few modifications. So how do I determine what will be the next auto-generated number for my...
5
1527
by: MLH | last post by:
Anyone remember how to determine a particular record's record number (Access 2.0 table question)?
6
2003
by: Opie | last post by:
What would be a more efficient way for me to determine if a record in an SQL DB table exists? Right now, I have a try/catch like this: try { if(checkcom.ExecuteScalar().ToString()==tbBillNumber.Text) { .... } }
1
11306
by: chaticathe | last post by:
I am trying to determine the record number of a record in Oracle Forms so I can navigate to that record. I am using the code below but it only gives me the record number of the record I am currently displaying. declare temp_record number(40); char_record varchar2(40); Begin temp_record := 0; if :control.barcode_query is null then message('You must select a barcode.'); raise form_trigger_failure;
10
12705
by: MLH | last post by:
Suppose, in a multi-user environment, you have append query SQL in a VBA procedure that looks like INSERT INTO MyTable... and the next line reads MyVar=DMax("","MyTable... You can never be certain that MyVar will be set to the key-field value that was created when the Append query ran. Now, there are other ways to do it - I know - that will ensure you 'nab' the correct record. But I was wondering
1
5346
by: mark_aok | last post by:
Hi all, I have a situation where I need to determine a specific table's primary key, and then output it. I have tried the Database Object, and the Record Object, but I've had no luck. Ideally there would be something like,
3
9517
by: mmckinnon | last post by:
Hi All, This is really basic but I'm having trouble finding a straight forward way to do this. I have an Access application where the for is in spreadsheet mode. When a user leaves a control there is a series of calculations and the parent and child forms are refreshed. The cursor goes back up to the first control in the first record after refreshed and I bring it back to the original record using a bookmark. My problem is that I need...
0
8709
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8638
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9058
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8909
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6555
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5894
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3081
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2371
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2018
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.