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

END

Open in new window

ALTER 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

END

Open in new window

Carla RomereDirector of Information TechnologyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paulmc999Commented:
Can I suggest that you do not wish to update the audit table if the main table update fails for some reason, so I suggest, you call the audit procedure from the main procedure but only if the main table update succeeds.

You would use TRANSACT AND ROLLBACK statements in MSSQL, are you familiar with these?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GewgalaCommented:
You can execute a stored procedure from within a stored procedure, like this:

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

EXEC AER_PRODUCT_MASTER_LTD_AUDIT @Param1, @Param2, @Param3, @Param4, @etc...

END

Open in new window


The magic happens with this line:

EXEC AER_PRODUCT_MASTER_LTD_AUDIT @Param1, @Param2, @Param3, @Param4, @etc...

If all of your parameters to run the 2nd stored procedure are not available in the execution of the first, you can create them, like so:

declare @Param1 nvarchar(255)

set @Param1 = 'Some Static Value'

----OR-----

select @Param1 = someField from SomeTable

EXEC AER_PRODUCT_MASTER_LTD_AUDIT @Param1, @Param2, @Param3, @Param4, @etc...

Open in new window

0
Carla RomereDirector of Information TechnologyAuthor Commented:
Help me follow this logic. If I run the audit sp AFTER the first one, then my old values would be gone already wouldn't they?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Paulmc999Commented:
You have to keep the values in variables until both tables are updated.
0
GewgalaCommented:
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:

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

END

Open in new window


Hopefully that makes sense.
0
Carla RomereDirector of Information TechnologyAuthor Commented:
Okay - giving these suggestions a shot. I will update shortly as to whether I got it to work correctly or not.
0
GewgalaCommented:
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 values

Open in new window

0
Carla RomereDirector of Information TechnologyAuthor Commented:
Is there any reason this should NOT work as is written? I just combined the update statement with an insert statement right below it inside the same stored procedure (instead of using two different ones). I've also included my code behind.

When I click the update button on my aspx page, I'm getting this error:

String or binary data would be truncated.
The statement has been terminated.
Source Error:

Line 164:
Line 165:        conn.Open();
>>>Line 166:        int rows = command.ExecuteNonQuery();

I have looked at all the column definitions in the tables and they match and none of the data that I'm updating/inserting should be too long. Is there any way to tell on what field it's getting hung up?
ALTER PROCEDURE [dbo].[AER_UPDATE_PRODUCT_MASTER]

--NEW VALUES FROM WEBPAGE

@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 datetime

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

INSERT INTO [fin_dev].[dbo].[AER_PRODUCT_MASTER_LTD_CHANGES]
           ([EXISTING_PART_CODE]
           ,[OLD_PART_DESC_1]
           ,[NEW_PART_DESC_1]
           ,[OLD_PART_DESC_2]
           ,[NEW_PART_DESC_2]
           ,[OLD_UNIT_SALES]
           ,[NEW_UNIT_SALES]
           ,[OLD_PRODUCT_GROUP]
           ,[NEW_PRODUCT_GROUP]
           ,[OLD_PRODUCT_CLASS]
           ,[NEW_PRODUCT_CLASS]
           ,[OLD_PRODUCT_TYPE]
           ,[NEW_PRODUCT_TYPE]
           ,[OLD_SYS_PART_DETAIL]
           ,[NEW_SYS_PART_DETAIL]
           ,[NEW_SYS_MODIFY_USER]
           ,[NEW_SYS_MODIFIED_DATE])
     VALUES
           (@PART_CODE,
		  @OLD_PART_DESC_1,
		  @PART_DESC_1,
		  @OLD_PART_DESC_2,
		  @PART_DESC_2,
		  @OLD_UNIT_SALES,
		  @UNIT_SALES,
		  @OLD_PRODUCT_GROUP,
		  @PRODUCT_GROUP,
		  @OLD_PRODUCT_CLASS,
		  @PRODUCT_CLASS,
		  @OLD_PRODUCT_TYPE,
		  @OLD_SYS_PART_DETAIL,
		  @PRODUCT_TYPE,
		  @SYS_PART_DETAIL,
		  @SYS_MODIFY_USER,
		  GETDATE()
)

END

Open in new window

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class updpm : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void btnShowpart_Click(object sender, EventArgs e)
    {
        FormView1.Visible = true;
        txtCurrpartcode.Text = txtPartcode.Text;
        btnShowmodify.Visible = true;
    }

    protected void btnShowmodify_Click(object sender, EventArgs e)
    {
        Panel1.Visible = true;
        FormView1.Visible = false;
        btnShowmodify.Visible = false;
        btnShowpart.Visible = false;
        lblSuccess.Visible = false;

        txtCurrUser.Text = User.Identity.Name.Split('\\')[1].ToUpper();

        //Old values

        TextBox Oldpartdesc1 = (TextBox)FormView1.FindControl("PART_DESC_1TextBox");
        txtOldpartdesc1.Text = Oldpartdesc1.Text;

        TextBox Oldpartdesc2 = (TextBox)FormView1.FindControl("PART_DESC_2TextBox");
        txtOldpartdesc2.Text = Oldpartdesc2.Text;

        TextBox Oldunitsales = (TextBox)FormView1.FindControl("UNIT_SALESTextBox");
        txtOldunitsales.Text = Oldunitsales.Text;

        TextBox Oldproductgroup = (TextBox)FormView1.FindControl("PRODUCT_GROUPTextBox");
        txtOldproductgroup.Text = Oldproductgroup.Text;

        TextBox Oldproductclass = (TextBox)FormView1.FindControl("PRODUCT_CLASSTextBox");
        txtOldproductclass.Text = Oldproductclass.Text;

        TextBox Oldproducttype = (TextBox)FormView1.FindControl("PRODUCT_TYPETextBox");
        txtOldproducttype.Text = Oldproducttype.Text;

        TextBox Oldsyspartdetail = (TextBox)FormView1.FindControl("SYS_PART_DETAILTextBox");
        txtOldsyspartdetail.Text = Oldsyspartdetail.Text;

        //New values
        
        TextBox Newpartdesc1 = (TextBox)FormView1.FindControl("PART_DESC_1TextBox");
        txtNewpartdesc1.Text = Newpartdesc1.Text;

        TextBox Newpartdesc2 = (TextBox)FormView1.FindControl("PART_DESC_2TextBox");
        txtNewpartdesc2.Text = Newpartdesc2.Text;

        TextBox Newunitsales = (TextBox)FormView1.FindControl("UNIT_SALESTextBox");
        ddlUnitsales.SelectedValue = Newunitsales.Text;

        TextBox Newproductgroup = (TextBox)FormView1.FindControl("PRODUCT_GROUPTextBox");
        ddlProductgroup.SelectedValue = Newproductgroup.Text;

        TextBox Newproductclass = (TextBox)FormView1.FindControl("PRODUCT_CLASSTextBox");
        ddlProductclass.SelectedValue = Newproductclass.Text;

        TextBox Newproducttype = (TextBox)FormView1.FindControl("PRODUCT_TYPETextBox");
        ddlProducttype.SelectedValue = Newproducttype.Text;

        TextBox Newsyspartdetail = (TextBox)FormView1.FindControl("SYS_PART_DETAILTextBox");
        txtNewsyspartdetail.Text = Newsyspartdetail.Text;
    }

    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection("Server=sdwinsql03v; database=fin_dev; User Id=intranet; Password=intranet");
        SqlCommand command = new SqlCommand("AER_UPDATE_PRODUCT_MASTER", conn);
        command.CommandType = CommandType.StoredProcedure;

        //Old Values

        SqlParameter myOldPART_CODEPrm = new SqlParameter("@PART_CODE", SqlDbType.VarChar);
        myOldPART_CODEPrm.Value = txtCurrpartcode.Text;

        SqlParameter myOldPARTDESC1Prm = new SqlParameter("@OLD_PART_DESC_1", SqlDbType.VarChar);
        myOldPARTDESC1Prm.Value = txtOldpartdesc1.Text;

        SqlParameter myOldPARTDESC2Prm = new SqlParameter("@OLD_PART_DESC_2", SqlDbType.VarChar);
        myOldPARTDESC2Prm.Value = txtOldpartdesc2.Text;

        SqlParameter myOldUNITSALESPrm = new SqlParameter("@OLD_UNIT_SALES", SqlDbType.VarChar);
        myOldUNITSALESPrm.Value = txtOldunitsales.Text;

        SqlParameter myOldPRODUCTGROUPPrm = new SqlParameter("@OLD_PRODUCT_GROUP", SqlDbType.VarChar);
        myOldPRODUCTGROUPPrm.Value = txtOldproductgroup.Text;

        SqlParameter myOldPRODUCTCLASSPrm = new SqlParameter("@OLD_PRODUCT_CLASS", SqlDbType.VarChar);
        myOldPRODUCTCLASSPrm.Value = txtOldproductclass.Text;

        SqlParameter myOldPRODUCTTYPEPrm = new SqlParameter("@OLD_PRODUCT_TYPE", SqlDbType.VarChar);
        myOldPRODUCTTYPEPrm.Value = txtOldproducttype.Text;

        SqlParameter myOldSYSPARTDETAILPrm = new SqlParameter("@OLD_SYS_PART_DETAIL", SqlDbType.Text);
        myOldSYSPARTDETAILPrm.Value = txtOldsyspartdetail.Text;

        //New Values

        SqlParameter myPART_CODEPrm = new SqlParameter("@PART_CODE", SqlDbType.VarChar);
        myPART_CODEPrm.Value = txtCurrpartcode.Text;

        SqlParameter myPARTDESC1Prm = new SqlParameter("@PART_DESC_1", SqlDbType.VarChar);
        myPARTDESC1Prm.Value = txtNewpartdesc1.Text;

        SqlParameter myPARTDESC2Prm = new SqlParameter("@PART_DESC_2", SqlDbType.VarChar);
        myPARTDESC2Prm.Value = txtNewpartdesc2.Text;

        SqlParameter myUNITSALESPrm = new SqlParameter("@UNIT_SALES", SqlDbType.VarChar);
        myUNITSALESPrm.Value = ddlUnitsales.SelectedValue;

        SqlParameter myPRODUCTGROUPPrm = new SqlParameter("@PRODUCT_GROUP", SqlDbType.VarChar);
        myPRODUCTGROUPPrm.Value = ddlProductgroup.SelectedValue;

        SqlParameter myPRODUCTCLASSPrm = new SqlParameter("@PRODUCT_CLASS", SqlDbType.VarChar);
        myPRODUCTCLASSPrm.Value = ddlProductclass.SelectedValue;

        SqlParameter myPRODUCTTYPEPrm = new SqlParameter("@PRODUCT_TYPE", SqlDbType.VarChar);
        myPRODUCTTYPEPrm.Value = ddlProducttype.SelectedValue;

        SqlParameter mySYSPARTDETAILPrm = new SqlParameter("@SYS_PART_DETAIL", SqlDbType.Text);
        mySYSPARTDETAILPrm.Value = txtNewsyspartdetail.Text;

        command.Parameters.Add("@SYS_MODIFY_USER", SqlDbType.Char).Value = User.Identity.Name.Split('\\')[1].ToUpper();

        SqlParameter myNEWSYSMODIFIEDDATEPrm = new SqlParameter("@NEW_SYS_MOFIFIED_DATE", SqlDbType.DateTime);

        command.Parameters.Add(myOldPART_CODEPrm);
        command.Parameters.Add(myOldPARTDESC1Prm);
        command.Parameters.Add(myOldPARTDESC2Prm);
        command.Parameters.Add(myOldUNITSALESPrm);
        command.Parameters.Add(myOldPRODUCTGROUPPrm);
        command.Parameters.Add(myOldPRODUCTCLASSPrm);
        command.Parameters.Add(myOldPRODUCTTYPEPrm);
        command.Parameters.Add(myOldSYSPARTDETAILPrm);

        command.Parameters.Add(myPART_CODEPrm);
        command.Parameters.Add(myPARTDESC1Prm);
        command.Parameters.Add(myPARTDESC2Prm);
        command.Parameters.Add(myUNITSALESPrm);
        command.Parameters.Add(myPRODUCTGROUPPrm);
        command.Parameters.Add(myPRODUCTCLASSPrm);
        command.Parameters.Add(myPRODUCTTYPEPrm);
        command.Parameters.Add(mySYSPARTDETAILPrm);
        command.Parameters.Add(myNEWSYSMODIFIEDDATEPrm);

        conn.Open(); 
        int rows = command.ExecuteNonQuery();
        conn.Close();

        FormView1.DataBind();
        Panel1.Visible = false;
        FormView1.Visible = true;
        lblSuccess.Visible = true;
        btnShowmodify.Visible = true;
        btnShowpart.Visible = true;
    }
}

Open in new window

0
Carla RomereDirector of Information TechnologyAuthor Commented:
Okay - it IS performing the update procedure correctly - so it's something with the Insert statement.
0
GewgalaCommented:
This is usually an error when your parameters are not long enough for the data you are assigning to them, specifically these ones:

@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 datetime

Open in new window


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?
0
GewgalaCommented:
"none of the data that I'm updating/inserting should be too long"

Sounds like you need to verify each value you are passing in that it has a length acceptable to it's variable definition in the stored procedure.

Also, in your c# codebehind, make sure that you're adding your parameters in order as they are defined in the stored procedure, and that you are not mixing a value up when adding them to the SqlCommand.  If you are accidentally adding a 25 character string to the index position of where a 6 character string is being looked for then you'll get that error...
0
Carla RomereDirector of Information TechnologyAuthor Commented:
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?
0
Paulmc999Commented:
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.
0
Carla RomereDirector of Information TechnologyAuthor Commented:
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.
0
Paulmc999Commented:
BTW I mean t that you run your SP in SQL SERVER with test data, NOT from your web page
0
Paulmc999Commented:
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
0
GewgalaCommented:
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....
0
Paulmc999Commented:
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.
0
Carla RomereDirector of Information TechnologyAuthor Commented:
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.
0
Paulmc999Commented:
well we will see what the moderator thinks.
0
GewgalaCommented:
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.
0
Carla RomereDirector of Information TechnologyAuthor Commented:
If there was an error on my part, then I will modify the points if that is what a moderator suggests.
0
Paulmc999Commented:
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.
0
GewgalaCommented:
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.
0
Paulmc999Commented:
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.
0
Paulmc999Commented:
I accept that
0
Paulmc999Commented:
I accept that
0
GewgalaCommented:
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.
0
Paulmc999Commented:
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.
0
Paulmc999Commented:
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!!!!!!!!!!
0
GewgalaCommented:
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.
0
Paulmc999Commented:
OK no more comments from me, could the moderator please decide what was the answer here and who gave the answer. Thanks.
0
GewgalaCommented:
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).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.