December 6, 2008 at 7:07 pm
Dear,
If have created a stored procedure to "empirically" (try and catch error until it works) insert some record by generating the key. The generated key will "violate" the primary key constraint until it works. (It is really the only way I found in order to reach my goals since I didn't want to use another table to handle the "next value". I have good reason for this.).
When a record is finally inserted, I want to retrieve the "key" in asp.net but the returned "key" is not the correct one.
Maybe with this code will be easier to understand my problem:
CREATE PROCEDURE dbo.sp_test(@in_parent nvarchar(255), @in_value nvarchar(255)) AS
BEGIN
DECLARE @str_padding VARCHAR(5), @int_counter int, @str_key nvarchar(255), @int_error int
SELECT @str_padding = '000'
SELECT @int_counter = 0
SELECT @int_error = 1 -- force 1 in order to start the loop
WHILE (@int_error <> 0) BEGIN -- loop until no more error => record correctly created
SELECT @int_counter = @int_counter +1
-- Let's generate the next key: format e.g. 001001, then 001002, 001003, ...
SELECT @str_key = @in_parent + RIGHT(@str_padding + CAST(@int_counter AS varchar(255)),3)
-- try to insert a record that might violate the primary key (set on keyName)
INSERT INTO tbl_myTable (keyName, valValue) VALUES (@str_key, @in_value);
-- return error code
SELECT @int_error = @@error
END
SELECT @str_key AS NewKey --- I want to return the key that was actually inserted
RETURN 0
END
In ASP.NET, when I try to retrieve the key that was actually inserted (via SqlDataReader), it returns the key that corresponds to the first attempt.
In other words, let's suppose @in_parent = "001001" and the key that worked was "001001004", the key I obtain in ASP.NET is "001001001" instead of "001001004"
How does this come and how to find out a solution?
In advance, many thanks
December 8, 2008 at 8:39 am
Why do the insert? Why not build the key before the insert like this:
ALTER PROCEDURE dbo.sp_test
(
@in_parent nvarchar(255),
@in_value nvarchar(255)
)
AS
BEGIN
DECLARE
@str_padding VARCHAR(5),
@int_counter int,
@str_key nvarchar(255),
@int_error INT
SELECT
@str_padding = '000'
SELECT
@int_counter = 1
SELECT @str_key = @in_parent + RIGHT(@str_padding +
CAST(@int_counter AS varchar(255)), 3)
WHILE EXISTS (Select 1 FROM tbl_MyTable WHERE keyName = @str_Key)
BEGIN
SELECT
@int_counter = @int_counter + 1
-- Let's generate the next key: format e.g. 001001, then 001002, 001003, ...
SELECT
@str_key = @in_parent + RIGHT(@str_padding +
CAST(@int_counter AS varchar(255)),
3)
End
-- insert a record that might violate the primary key (set on keyName)
INSERT INTO
tbl_myTable (keyName, valValue)
VALUES
(@str_key, @in_value) ;
SELECT
@str_key AS NewKey --- I want to return the key that was actually inserted
RETURN
ENd
The smiley is a closing parenthesis. The other thing you can do is make @str_key an output parameter then you can use ExecuteNonQuery with an output parameter to return the key. That is how I normally do it in asp.net.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 8, 2008 at 3:24 pm
Thank you for your answer.
Here follows additional information referring to your question...
1. Why inserting the record?
=> Since it might happen that several "users" would like to create a new item at the very same time, using your solution to check "WHILE EXISTS..." does not ensure that when the INSERT statement is actually run, the new key is actually correct (see concurrent requests).
=> This explains why I used the empirical mode.
2. Use the 'output' parameter...
=> I already tried to use it but it does not work (in this case) and I even cannot refer to the "output" parameter is my code. The drRequest collection does not "recognise" the parameter.
==> It seems that if an error is encountered in a Stored Procedure, @@error is set and any subsequent new assignments to @str_key in the WHILE loop is not "taken into consideration" for the output...
==> would you think there might be a bug in SQL Server 2000?
Regards,
Didier
December 8, 2008 at 3:36 pm
didier.boelens (12/8/2008)
Thank you for your answer.Here follows additional information referring to your question...
1. Why inserting the record?
=> Since it might happen that several "users" would like to create a new item at the very same time, using your solution to check "WHILE EXISTS..." does not ensure that when the INSERT statement is actually run, the new key is actually correct (see concurrent requests).
=> This explains why I used the empirical mode.
You can solve this with a lock hint or setting your isolation level.
2. Use the 'output' parameter...
=> I already tried to use it but it does not work (in this case) and I even cannot refer to the "output" parameter is my code. The drRequest collection does not "recognise" the parameter.
Without seeing the .NET code I can't really answer this one. If drRequest is a datareader then the output paramter would not be there because ExecuteNonQUery does not return a datareader. In order to use the output parameter your .NET code would have to be something like this (VB.NET):
Dim cn as New SQLConnection([connection string])
Dim cmd as New SQLCommand(cn)
Dim param as New SQLParameter()
Dim strKey as String
cmd.CommandType = StoredProcedure
cmd.CommandText = [ProcName]
param.Name = "@str_key"
param.SQLDbType = SQLDBType.nvarchar
param.Length = 255 ' this might be size
param.Direction = Output
cmd.Parameters.Add(param)
cmd.ExecuteNonQuery
strQuery = cmd.Parameters("@str_key")
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply