October 8, 2006 at 11:59 am
I have created a stored procedure to insert data into table, and return the scope_identity(). The variables of the SP are defaulted to NULL:
...
@CategoryID INT = NULL,
@MainID INT = NULL,
...
After reading some articles on the Mirosoft site, it states to get this return statement in ASP, I need to connect using adodb parameters. However, I am stuck when creating the ASP page when I have no value to pass to the stored procedure.
adocmd.Parameters.Append adocmd.CreateParameter("IDCategory", adInteger, adParamInput, Request.Form("cboCategory"))
adocmd.Parameters.Append adocmd.CreateParameter("IDMain", adInteger, adParamInput, Request.Form("cboMainID"))
On other parameters referring to blank text fields, it works great, but fails on when the cbo does not have a value.
I apologize for posting this question on this forum, but I was wondering if it has to do with my stored procedure. Thanks...
October 8, 2006 at 4:02 pm
Don't you really care about duplicates in your database?
What if set of values you are trying to insert already exists in database?
I would suggest NEVER USE @@IDENTITY OR SCOPE_IDENTITY() to return ID for just inserted values.
Unless you can proof it's right place to use it.
But it's really rear occassion. As for me I used it once in last 2 years.
Moreover I removed these functions from about 50 of most critical SP developed by others, and it increased overall performance of application dramatically. I mean 5 times faster is not dramatically.
As for parameters you must convert string values in TextBoxes to integer values in order to supply it as INT parameter to SP.
Create a function to check if value in text box is valid and convert empty string to NULL or whatever you want to use as a replacement for NULL.
Then you may use it like this (not sure about exact syntax, sorry):
adocmd.Parameters.Append adocmd.CreateParameter("IDCategory", adInteger, adParamInput, MyConvFunction(Request.Form("cboCategory")))
_____________
Code for TallyGenerator
October 9, 2006 at 9:51 am
Sergiy is correct -
Either use some sort of conversion, or check the value of the cbo - if it is empty/blank pass zero (0) that is an integer and will satisfy the parameter
October 9, 2006 at 11:29 am
Thank you for your response... What would be the best method then to get the primary key (which is based upon a column called ID IDENTITY (1,1))? The reason behind this methodology is that I have a form to create a primary record, and get the ID number, which will be passed to a details form where data can be entered into other tables on a one to many basis.
I have a Check Constraint on the PRIMARY/FOREIGN relationship, so I need the ID number.
October 9, 2006 at 2:07 pm
You must have some "natural key", unique combination of fields which distinguish different entities.
Then use something like this:
INSERT INTO TABLE
....
WHERE NOT EXISTS (SELECT ...FROM TABLE
WHERE )
SELECT @BackID = ID FROM TABLE
WHERE
_____________
Code for TallyGenerator
October 9, 2006 at 9:47 pm
Sergiy,
I am trying to follow the logic here, and understanding your insert statement. The only columns that are unique in this table are the ID int Identity (1,1) and EventName varchar(50). In this case, I am not sure how a index on multiple columns would work, or referring to multiple columns. The rest of the columns in this table are are foreign keys (ints).
So here is my stab at what your wrote... the stored procedure would check to see:
IF EXISTS (Select EventName from Main)
BEGIN
RETURN
END
INSERT TABLEMAIN (... ... ... ... ...)
And instead of returning SCOPE_INDENTITY, return a recordset containing the ID created (Select @BackID = ID from Main where EventName = @name)
October 9, 2006 at 11:01 pm
Almost there.
1) You must have unique index on EventName in TABLEMAIN.
2) Try to avoid "IF" in SQL. Use "WHERE NOT EXISTS".
It's not a problem in this case, but don't take bad habits.
INSERT INTO TABLEMAIN
(..., EventName, ...)
..., @name, ....
WHERE NOT EXISTS (SELECT 1 FROM TABLEMAIN WHERE EventName = @name)
SELECT @BackID = ID FROM TABLE
WHERE EventName = @name
_____________
Code for TallyGenerator
October 10, 2006 at 9:37 pm
Thank you for your comments... I will code the procedure tonight, and take into account the IF statements and usage of the SCOPE_IDENTITY. I guess that is what I get for looking at Microsoft for examples out of the 2072 (Programming SQL Server) course.
October 10, 2006 at 9:54 pm
When using the WHERE NOT EXIST clause in the VALUES of the insert statement, does it need to be inside the () of the values, or outside. The SP is not compiling:
Server: Msg 156, Level 15, State 1, Procedure usp_NewEvent, Line 30
Incorrect syntax near the keyword 'WHERE'.
October 10, 2006 at 11:16 pm
OK,
3) Don't ever use VALUES.
SELECT will do the same but it's more capable and more flexible.
In your case it allows to use WHERE NOT EXISTS.
It also allows to retrieve identity values from lookup tables in single statement.
And so on.
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply