How to use the output parameter insert stored procedure in ASP.NET

Question

I have stored procedure as follows

ALTER PROCEDURE [dbo].[sp1]
    @mat NVARCHAR(1000) = NULL,
    @party NVARCHAR(1000) = NULL,
    @place NVARCHAR(1000) = NULL,
    @truk NVARCHAR(1000) = NULL,
    @qty NUMERIC(10) = NULL,
    @ptm NUMERIC(10) = NULL,
    @mop NVARCHAR(100) = NULL,
    @tos NVARCHAR(100) = NULL,
    @driver NVARCHAR(100) = NULL,
    @date1 DATE = NULL
AS
BEGIN
    DECLARE @sql NVARCHAR(4000); 
    DECLARE @params NVARCHAR(4000); 
    DECLARE @rate NUMERIC(10); 

    SET @sql ='select  @rate = ['+@mat+']   from tblcos'+ ' where [Name] = @party' 
    set @params = '@party nvarchar (1000), @rate NVARCHAR(10) OUTPUT'  
    exec sp_executesql @sql, @params,@party= @party,@rate = @rate OUTPUT

    INSERT INTO tblsls([Party], [Place], [truk], [Material], [Qty], rate, [Amount], [Payment], [Balance], [MOP], [TOS], [driver], [Date]) 
        SELECT 
            @party, @place, @truk, @mat, @rate, @qty, (@qty * @rate),
            @ptm, (@qty * @rate - @ptm), @mop, @tos, @driver, @date1
END

My aspx.cs file

 protected void Button1_Click(object sender, EventArgs e)
 {
     SqlConnection con = new SqlConnection (@"
     Data Source = ABC - PC; Initial Catalog = VRA; Integrated Security = True");

     SqlCommand cmd = new SqlCommand("sp1", con);
     cmd.CommandType = CommandType.StoredProcedure;

     cmd.Parameters.AddWithValue("Date", Calendar1.SelectedDate);
     cmd.Parameters.AddWithValue("party", Ddprt.SelectedValue);
     cmd.Parameters.AddWithValue("Place", tbpls.Text);
     cmd.Parameters.AddWithValue("Truk", Tbtru.Text);
     cmd.Parameters.AddWithValue("Material", Ddmat.SelectedValue);
     cmd.Parameters.AddWithValue("Qty", Tbqty.Text);
     **cmd.Parameters.AddWithValue("Rate",         );**
     **cmd.Parameters.AddWithValue("Amount",          );**
     cmd.Parameters.AddWithValue("Payment", Tbptm.Text);
     **cmd.Parameters.AddWithValue("Balance",            );**
     cmd.Parameters.AddWithValue("MOP", Ddmop.SelectedValue);
     cmd.Parameters.AddWithValue("TOS", Ddtos.SelectedValue);
     cmd.Parameters.AddWithValue("driver", Tbdri.Text);

     con.Open();

     int k = cmd.ExecuteNonQuery();

     if (k != 0)
     {
         Lbmsg.Text = "Record inserted successfully into the database";
         Lbmsg.ForeColor = System.Drawing.Color.CornflowerBlue;
     }

     con.Close();
}

How to make Rate, Amount and Balance to take the value from the stored procedure sp1 and update the value?

Possible duplicate of [Using stored procedure output parameters in C#](https://stackoverflow.com/questions/10905782/using-stored-procedure-output-parameters-in-c-sharp) — gsharp, Dec 28 '18 at 18:29
I don't understand the question. What is value when you say "...and update the value"? Any why are you using `sp_executesql` when you could just `SELECT` the values directly? — Crowcoder, Dec 28 '18 at 18:38
yes i am using sp_executesql , let me know how to take the value directly — Vinay Aparanji, Dec 28 '18 at 18:42
in store procedure i am supplying values for 10 parameter and procedure will update values for 13 columns , i want to do the same in asp.net web app also — Vinay Aparanji, Dec 28 '18 at 18:48
@VinayAparanji Then call the procedure from the web app..? Like you are doing..? — Jimenemex, Dec 28 '18 at 18:49
but what should i feed *cmd.Parameters.AddWithValue("Rate", );** **cmd.Parameters.AddWithValue("Amount", );** — Vinay Aparanji, Dec 28 '18 at 18:51
**cmd.Parameters.AddWithValue("Balance", );** can you kindly let me know — Vinay Aparanji, Dec 28 '18 at 18:51

score 1 · Answer 1 · answered Dec 28 '18 at 18:56

Create another procedure that accepts Rate, Amount, and Balance as parameters and call that one instead from your web app. That way you don't ruin the functionality of the existing sp1, but still have what you need.

I don't know much about syntax in sql, but I think it looks something like this. Somebody please correct if wrong.

ALTER PROCEDURE [dbo].[sp1_fromWeb]
    @mat NVARCHAR(1000) = NULL,
    @party NVARCHAR(1000) = NULL,
    @place nvarchar(1000)=null,
    @truk nvarchar (1000)=null,
    @qty numeric (10)=null,
    @ptm numeric(10)=null,
    @mop nvarchar(100)=null,
    @tos nvarchar(100)=null,
    @driver nvarchar(100)=null,
    @date1 date = null,
    @rate numeric(10),
    @amount numeric(10),
    @balance numeric(10)
AS
BEGIN
    DECLARE @sql NVARCHAR(4000); 
    DECLARE @params NVARCHAR(4000); 

    SET @sql ='select  @rate = ['+@mat+']   from tblcos'+ ' where [Name] = @party' 
    set @params = '@party nvarchar (1000), @rate NVARCHAR(10) OUTPUT'  
    exec sp_executesql @sql, @params,@party= @party,@rate = @rate OUTPUT

    INSERT INTO tblsls([Party],[Place],[truk], [Material],[Qty], rate,[Amount],[Payment],[Balance],[MOP],[TOS],[driver],[Date]) 
        SELECT @party, @place, @truk, @mat, @qty, @rate, @amount, @ptm, @balance, @mop, @tos, @driver, @date1
END

its wont work, i am want to know the the cmd.paramerters .addwithvalues part — Vinay Aparanji, Dec 29 '18 at 02:30

How to use the output parameter insert stored procedure in ASP.NET

1 Answers1