data insert using input output stored procedure

Question

I am creating a web application using ASP.net C#. I have a booking form and I need to insert data into a table using a Stored Procedure. The table has several columns, out of which second column is a computed column. The Stored Procedure is set up to insert the data and fetch the value from the second column after insert. Below is the code for Stored Procedure:

    Create Procedure sp_InsertCashPooja
@FirstName varchar(100),
@LastName varchar(100),
@TelNo bigint,
@Star char(50),
@Rasi char(50),
@Gothram char(50),
@PDMID int,
@PayMode bit,
@PujaName char(50),
@DonateAmt decimal(19,2),
@RcptNo varchar(25) output

as

Begin

SET NOCOUNT ON;

BEGIN TRY

BEGIN TRANSACTION

    if @PujaName != 'DONATION'
    Begin

        INSERT INTO PoojaDetails (FirstName, LastName, TelNo, Star, Rasi, Gothram, PoojaDietyMasterID, PayMode) values (@FirstName,@LastName,@TelNo,@Star,@Rasi,@Gothram,@PDMID,@PayMode)

    End

    if @PujaName = 'DONATION'
    Begin

        DECLARE @isDonate int = 0;

        INSERT INTO PoojaDetails (FirstName, LastName, TelNo, Star, Rasi, Gothram, PoojaDietyMasterID, PayMode, isDonate, DonateAmount) values (@FirstName,@LastName,@TelNo,@Star,@Rasi,@Gothram,@PDMID,@PayMode, @isDonate, @DonateAmt)

    End

    Select @RcptNo = max(ReceiptNo) from PoojaDetails
    Return @RcptNo
COMMIT TRANSACTION

END TRY

BEGIN CATCH
    IF (@@TRANCOUNT > 0)

    ROLLBACK TRANSACTION

END CATCH

SET NOCOUNT OFF;


End

I would like to insert data on the click of a button: I was able to figure out the below code....

 protected void btnSave_Click(object sender, EventArgs e)
        {


            frmFirstName = txtFirstName.Text.Trim().ToUpper();
            frmLastName = txtLastName.Text.Trim().ToUpper();
            frmPhoneNo = Convert.ToInt32(txtPhoneNo.Text.Trim());
            frmNakshatra = Convert.ToString(cmbNakshatra.SelectedItem).Trim();
            frmRasi = Convert.ToString(cmbRasi.SelectedItem).Trim();
            frmGothram = Convert.ToString(cmbGothram.SelectedItem).Trim();
            frmPujaName = Convert.ToString(cmbPujaName.SelectedItem).Trim();
using (SqlConnection connection = new SqlConnection())
        {
            if (frmPayMode == "Cash")
            {
                if (frmPujaName == "DONATION")
                {
                    SqlDataAdapter CashAdapter = new SqlDataAdapter();

                    CashAdapter.InsertCommand = new SqlCommand("sp_InsertCashPooja", connection);
                    CashAdapter.InsertCommand.CommandType = CommandType.StoredProcedure;

Please help.... I want to capture the returning RcptNo and later intend to call another ASPX page and pass the value using a Query String.

Thanks

score 1 · Accepted Answer · answered Nov 18 '13 at 15:57

1

Use simple SqlCommand for calling your SP

connection.Open();
var cmd = new SqlCommand("sp_InsertCashPooja", connection);
cmd.Parameters.AddWithValue("FirstName", frmFirstName);
// Add all the others parameters in same way
var id = (int)cmd.ExecuteScalar();
connection.Close();

answered Nov 18 '13 at 15:57

Dmytro Rudenko

2,524
2
13
22

"FirstName" refers to the parameter name in the Store Procedure? – Rajiv Nov 18 '13 at 16:56
I tried as you suggested. I am getting a SQL Exception Timeout eror. – Rajiv Nov 18 '13 at 17:03
change Select @ RcptNo = max(ReceiptNo) from PoojaDetails to the Select @RcptNo = SCOPE_IDENTITY() as has been proposed by . And also you may increase timeout value by cmd.CommandTimeout = number_of_seconds – Dmytro Rudenko Nov 18 '13 at 17:37
@Rajiv: the problem may be that you are returning an output parameter, you need to either specify param.Direction = ParameterDirection.ReturnValue for that one or get the value from your procedure with a 'select' rather than 'return' – MLT Nov 27 '13 at 09:23

MLT · Answer 2 · 2013-11-18T16:10:36.070

1

Change the return variable to:

Select @RcptNo = SCOPE_IDENTITY()

It will return the identity number created for the inserted record within this procedure.

edited Nov 18 '13 at 16:10

answered Nov 18 '13 at 16:04

MLT

524
7
19

score 1 · Answer 3 · answered Nov 19 '13 at 09:28

use sql parameter..

connection = ConfigurationManager.AppSettings["mycon"];

        SqlParameter[] para = new SqlParameter[2];
        para[0] = new SqlParameter("@stored procedure column name", string name);
        para[1] = new SqlParameter("@stored procedure column name", string name);

data insert using input output stored procedure

3 Answers3

Linked