Executing Two Stored Procedures in asp.net
I have a page that performs an update to a table in our ERP system. We are trying to come up with a process where certain people have certain fields they may update without giving them access to the entire table. I got that working correctly. Now I've been tasked with creating and updating an "audit table" that updates anytime someone updates the main table. The fields they want are the current fields' values as well as the new fields' values with a userid and time stamp as to when it was updated. I have a stored procedure created for the initial update (included below). Then I have the stored procedure written to update the audit table (also included below).
I need to fire those both off with a single button click in an asp.net page. I have a formview that displays the current values and a table outside the formview that is automatically populated with the current values shown in the formview. I can't update from there though because of limitations in our ERP system.
What is the best way to accomplish this goal? I'd hoped there was a way to fire them both off from the single click on the asp.net page, but so far have not been able to make that work. I can post the asp.net code if needed.
I need to fire those both off with a single button click in an asp.net page. I have a formview that displays the current values and a table outside the formview that is automatically populated with the current values shown in the formview. I can't update from there though because of limitations in our ERP system.
What is the best way to accomplish this goal? I'd hoped there was a way to fire them both off from the single click on the asp.net page, but so far have not been able to make that work. I can post the asp.net code if needed.
ALTER PROCEDURE [dbo].[AER_UPDATE_PRODUCT_MASTER]
@PART_CODE VARCHAR(15),
@PART_DESC_1 VARCHAR(25),
@PART_DESC_2 VARCHAR(25),
@UNIT_SALES VARCHAR(4),
@PRODUCT_GROUP VARCHAR(6),
@PRODUCT_CLASS VARCHAR(6),
@PRODUCT_TYPE VARCHAR(6),
@SYS_PART_DETAIL TEXT,
@SYS_MODIFY_USER VARCHAR(12)
AS
BEGIN
UPDATE dbo.PRODUCT_MASTER
SET PART_DESC_1=@PART_DESC_1,
PART_DESC_2=@PART_DESC_2,
UNIT_SALES=@UNIT_SALES,
PRODUCT_GROUP=@PRODUCT_GROUP,
PRODUCT_CLASS=@PRODUCT_CLASS,
PRODUCT_TYPE=@PRODUCT_TYPE,
SYS_PART_DETAIL=@SYS_PART_DETAIL,
SYS_MODIFIED_DATE=GETDATE(),
SYS_MODIFY_USER=@SYS_MODIFY_USER
WHERE PART_CODE=@PART_CODE
ENDALTER PROCEDURE [dbo].[AER_PRODUCT_MASTER_LTD_AUDIT]
@EXISTING_PART_CODE varchar(15),
@OLD_PART_DESC_1 varchar(25),
@NEW_PART_DESC_1 varchar(25),
@OLD_PART_DESC_2 varchar(25),
@NEW_PART_DESC_2 varchar(25),
@OLD_UNIT_SALES varchar(4),
@NEW_UNIT_SALES varchar(4),
@OLD_PRODUCT_GROUP varchar(6),
@NEW_PRODUCT_GROUP varchar(6),
@OLD_PRODUCT_CLASS varchar(6),
@NEW_PRODUCT_CLASS varchar(6),
@OLD_PRODUCT_TYPE varchar(6),
@NEW_PRODUCT_TYPE varchar(6),
@OLD_SYS_PART_DETAIL text,
@NEW_SYS_PART_DETAIL text,
@NEW_SYS_MODIFY_USER varchar(12),
@NEW_SYS_MODIFIED_DATE datetime
AS
BEGIN
SET NOCOUNT ON;
UPDATE [fin_dev].[dbo].[PRODUCT_MASTER_LTD_CHANGES]
SET [EXISTING_PART_CODE] = @EXISTING_PART_CODE,
[OLD_PART_DESC_1] = @OLD_PART_DESC_1,
[NEW_PART_DESC_1] = @NEW_PART_DESC_1,
[OLD_PART_DESC_2] = @OLD_PART_DESC_2,
[NEW_PART_DESC_2] = @NEW_PART_DESC_2,
[OLD_UNIT_SALES] = @OLD_UNIT_SALES,
[NEW_UNIT_SALES] = @NEW_UNIT_SALES,
[OLD_PRODUCT_GROUP] = @OLD_PRODUCT_GROUP,
[NEW_PRODUCT_GROUP] = @NEW_PRODUCT_GROUP,
[OLD_PRODUCT_CLASS] = @OLD_PRODUCT_CLASS,
[NEW_PRODUCT_CLASS] = @NEW_PRODUCT_CLASS,
[OLD_PRODUCT_TYPE] = @OLD_PRODUCT_TYPE,
[NEW_PRODUCT_TYPE] = @NEW_PRODUCT_TYPE,
[OLD_SYS_PART_DETAIL] = @OLD_SYS_PART_DETAIL,
[NEW_SYS_PART_DETAIL] = @NEW_SYS_PART_DETAIL,
[NEW_SYS_MODIFY_USER] = @NEW_SYS_MODIFY_USER,
[NEW_SYS_MODIFIED_DATE] = @NEW_SYS_MODIFIED_DATE
WHERE EXISTING_PART_CODE=@EXISTING_PART_CODE
ENDASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You have to keep the values in variables until both tables are updated.
I missed that part of the question - you need the OLD field values prior to the update as well as the new field values? If that's the case, then what you need to do is preserve them into variables before the update is run, and then use them as arguments along with the new values when you execute the second stored procedure, something like this should work:
Hopefully that makes sense.
ALTER PROCEDURE [dbo].[AER_UPDATE_PRODUCT_MASTER]
@PART_CODE VARCHAR(15),
@PART_DESC_1 VARCHAR(25),
@PART_DESC_2 VARCHAR(25),
@UNIT_SALES VARCHAR(4),
@PRODUCT_GROUP VARCHAR(6),
@PRODUCT_CLASS VARCHAR(6),
@PRODUCT_TYPE VARCHAR(6),
@SYS_PART_DETAIL TEXT,
@SYS_MODIFY_USER VARCHAR(12)
AS
BEGIN
--declare variables to store old values
declare @partDesc1 varchar(25)
select @oldPartDesc1 = PART_DESC_1 from PRODUCT_MASTER where PART_CODE=@PART_CODE
select @oldPartDesc2 = PART_DESC_2 from PRODUCT_MASTER where PART_CODE=@PART_CODE
-- ...etc, so on with the rest of your variables that you need to use to preserve the old values
-- once you have all your variables set up to preserve old values, go ahead and run your update
UPDATE dbo.PRODUCT_MASTER
SET PART_DESC_1=@PART_DESC_1,
PART_DESC_2=@PART_DESC_2,
UNIT_SALES=@UNIT_SALES,
PRODUCT_GROUP=@PRODUCT_GROUP,
PRODUCT_CLASS=@PRODUCT_CLASS,
PRODUCT_TYPE=@PRODUCT_TYPE,
SYS_PART_DETAIL=@SYS_PART_DETAIL,
SYS_MODIFIED_DATE=GETDATE(),
SYS_MODIFY_USER=@SYS_MODIFY_USER
WHERE PART_CODE=@PART_CODE
-- now use the variables defined above that are holding your old values and pass them in to your 2nd stored procedure call with your new parameter values
EXEC AER_PRODUCT_MASTER_LTD_AUDIT @oldPartDesc1, @oldPartDesc2, @PART_DESC_1, @PART_DESC_2
ENDHopefully that makes sense.
ASKER
Okay - giving these suggestions a shot. I will update shortly as to whether I got it to work correctly or not.
Whoops, don't forget to have a declare line for each variable you are defining. My example above has a declare statement for the first but not for the second. It should be like this:
--declare variables to store old values
declare @oldPartDesc1 varchar(25)
declare @oldPartDesc2 varchar(25)
-- ...etc, so on with the rest of your variables to define that you will use
--assign variables defined above
select @oldPartDesc1 = PART_DESC_1 from PRODUCT_MASTER where PART_CODE=@PART_CODE
select @oldPartDesc2 = PART_DESC_2 from PRODUCT_MASTER where PART_CODE=@PART_CODE
-- ...etc, so on with the rest of your variables that you need to use to preserve the old valuesSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay - it IS performing the update procedure correctly - so it's something with the Insert statement.
This is usually an error when your parameters are not long enough for the data you are assigning to them, specifically these ones:
Are you sure that the varchar(25) and varchar(6) and varchar(4) that I'm seeing are the correct lengths and that you're not trying to pass in a value that would exceed this restriction?
@PART_CODE VARCHAR(15),
@PART_DESC_1 VARCHAR(25),
@PART_DESC_2 VARCHAR(25),
@UNIT_SALES VARCHAR(4),
@PRODUCT_GROUP VARCHAR(6),
@PRODUCT_CLASS VARCHAR(6),
@PRODUCT_TYPE VARCHAR(6),
@SYS_PART_DETAIL TEXT,
@SYS_MODIFY_USER VARCHAR(12),
--OLD VALUES FROM FORMVIEW
@OLD_PART_DESC_1 varchar(25),
@OLD_PART_DESC_2 varchar(25),
@OLD_UNIT_SALES varchar(4),
@OLD_PRODUCT_GROUP varchar(6),
@OLD_PRODUCT_CLASS varchar(6),
@OLD_PRODUCT_TYPE varchar(6),
@OLD_SYS_PART_DETAIL text,
@NEW_SYS_MODIFIED_DATE datetimeAre you sure that the varchar(25) and varchar(6) and varchar(4) that I'm seeing are the correct lengths and that you're not trying to pass in a value that would exceed this restriction?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is correct. I have the textboxes "maxlength" property set to these numbers and I have the dropdowns databound to only allowable values. I also have verified that they match the table definition in the sql table. I do have a primary key int autoincrement in the table, but I don't need to define that in the insert statement though, correct?
Can i suggest as a test you just make all VARCHARS 100 to see if the problem goes away, if it doesn't it is some other problem and then I would suggest you use a profiller to see what is going back and forth to the SQL server. If you need help with this, tell us what version of SQL Server you are using? If it is the Express version there are some free profilers available.
ASKER
Okay, I had two fields reversed in the stored procedure. Geesh. Thanks for the heads up on that one! I'd looked at this until I was blue in the face.
BTW I mean t that you run your SP in SQL SERVER with test data, NOT from your web page
I object to all the points going to Gewgala, you did exactly as I suggested and I suggested it first!!!!!!!!!
AND i tell you that you need to put all this in a TRANSACT.. ROLLBACK statement
AND i tell you that you need to put all this in a TRANSACT.. ROLLBACK statement
The problem was a mismatch in the parameters being passed in vs. the parameter definitions in the stored procedure. That was the first time that was suggested to check....
Gewgala that was the answer to a problem that arose from the implementation of the solution IT WAS NOT the answer to the original question and you know that. I have asked a moderator to look at this.
ASKER
Paulmc999, I didn't implement either of your suggestions. I combined them into one stored procedure instead of two (my idea) and Gewgala suggested I check the order of my fields in the stored procedure and that was the problem. I had two fields reversed.
well we will see what the moderator thinks.
Well, the second post in this thread was an answer to the original question with an example in it, followed up by a clarifying post later on... yeah, guess we'll see what a moderator thinks. I personally don't think there was any wrong doing here.
ASKER
If there was an error on my part, then I will modify the points if that is what a moderator suggests.
Well it is clear to me that the solution to your problem was that you call one SP which was my suggestion. Whether that SP called another SP or not is irrelevant, it does not change the solution.
I see your point. But, if we want to get technical, the solution that @Hers2keep went with was to do away with 2 procedures entirely and to consolidate them both into one. You could arguably say that you implied this with your first post of "call the audit procedure from the main one" but you didn't not come out and say that, you were still saying for him to work with two but to call them differently. He went with a different approach that was suggested by neither one of us. If there's any change to the way the answers were decided, he could reassign the answer as his own post where he showed code that consolidated them both into one stored procedure, and then mark the current answer as the assisted solution since that answer worked out a kink with the approach that he went with.
Nope hers2keep went with the solution of calling one SP from asp.net - my solution.
I would have been happy if the points had been shared but I will not accept what has been done. i really cannot see how the moderator will not see how unfair this has been too.
I would have been happy if the points had been shared but I will not accept what has been done. i really cannot see how the moderator will not see how unfair this has been too.
I accept that
I accept that
I'm ok with that, as long as the 500 points are distributed evenly between all the answers. I think that it is a little unfair to quality that the proposed change be made since my posts were much more in depth with examples. One quick little blurb that doesn't bother going into depth or giving code examples is definitely not the same caliber of answers that took the time to explain and to teach (which is the whole point of this site) rather than write something quick so that it happens to appear before any other post just so it gets credit as "the answer".
As long as the points get distributed evenly so that quality gets rewarded more than haste then I'm ok with it.
As long as the points get distributed evenly so that quality gets rewarded more than haste then I'm ok with it.
Well i am no longer happy because i think expanding on someone elses answer without more information being asked for or necessary seems to be a way of hijacking points, so NOW before I agree I would like to know exactly how many points are being awarded to each person.
AND BTW the question was not how to write a SP it was how to call two SPs at the same time in the same event!!!!!!!! The answer did not need CODE samples at that stage!!!!!!!!!!
Well the author made the comment that he didn't use any of your solutions. Obviously more depth was required. The real objective is to help the author, any amount of depth is necessary until they can walk away with a solution they understand. The author is the one that determines how much depth it's required, not you.
OK no more comments from me, could the moderator please decide what was the answer here and who gave the answer. Thanks.
I'm not saying that your original post wasn't the answer. I'm fine with that being marked as the answer, just as long as the points get distributed evenly across all 3 (your first comment, and my other 2 that the moderator listed as assists).
ASKER