?
Solved

Executing Two Stored Procedures in asp.net

Posted on 2011-10-11
35
Medium Priority
?
455 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:Carla Romere
  • 14
  • 11
  • 8
33 Comments
 
LVL 2

Accepted Solution

by:
Paulmc999 earned 668 total points
ID: 36950340
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
 
LVL 7

Assisted Solution

by:Gewgala
Gewgala earned 1332 total points
ID: 36950476
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
 

Author Comment

by:Carla Romere
ID: 36950641
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:Paulmc999
ID: 36950660
You have to keep the values in variables until both tables are updated.
0
 
LVL 7

Expert Comment

by:Gewgala
ID: 36950693
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
 

Author Comment

by:Carla Romere
ID: 36950704
Okay - giving these suggestions a shot. I will update shortly as to whether I got it to work correctly or not.
0
 
LVL 7

Expert Comment

by:Gewgala
ID: 36950708
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
 

Assisted Solution

by:Carla Romere
Carla Romere earned 0 total points
ID: 36951126
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
 

Author Comment

by:Carla Romere
ID: 36951206
Okay - it IS performing the update procedure correctly - so it's something with the Insert statement.
0
 
LVL 7

Expert Comment

by:Gewgala
ID: 36951227
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
 
LVL 7

Assisted Solution

by:Gewgala
Gewgala earned 1332 total points
ID: 36951254
"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
 

Author Comment

by:Carla Romere
ID: 36951278
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
 
LVL 2

Expert Comment

by:Paulmc999
ID: 36951295
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
 

Author Comment

by:Carla Romere
ID: 36951302
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
 
LVL 2

Expert Comment

by:Paulmc999
ID: 36951310
BTW I mean t that you run your SP in SQL SERVER with test data, NOT from your web page
0
 
LVL 2

Expert Comment

by:Paulmc999
ID: 36951349
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
 
LVL 7

Expert Comment

by:Gewgala
ID: 36951363
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
 
LVL 2

Expert Comment

by:Paulmc999
ID: 36951388
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
 

Author Comment

by:Carla Romere
ID: 36951406
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
 
LVL 2

Expert Comment

by:Paulmc999
ID: 36951415
well we will see what the moderator thinks.
0
 
LVL 7

Expert Comment

by:Gewgala
ID: 36951495
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
 

Author Comment

by:Carla Romere
ID: 36951613
If there was an error on my part, then I will modify the points if that is what a moderator suggests.
0
 
LVL 2

Expert Comment

by:Paulmc999
ID: 36951665
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
 
LVL 7

Expert Comment

by:Gewgala
ID: 36951866
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
 
LVL 2

Expert Comment

by:Paulmc999
ID: 36951910
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
 
LVL 2

Expert Comment

by:Paulmc999
ID: 37010988
I accept that
0
 
LVL 2

Expert Comment

by:Paulmc999
ID: 37010989
I accept that
0
 
LVL 7

Expert Comment

by:Gewgala
ID: 37011479
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
 
LVL 2

Expert Comment

by:Paulmc999
ID: 37011517
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
 
LVL 2

Expert Comment

by:Paulmc999
ID: 37011530
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
 
LVL 7

Expert Comment

by:Gewgala
ID: 37011647
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
 
LVL 2

Expert Comment

by:Paulmc999
ID: 37011652
OK no more comments from me, could the moderator please decide what was the answer here and who gave the answer. Thanks.
0
 
LVL 7

Expert Comment

by:Gewgala
ID: 37012260
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question