data insert using input output stored procedure
Solution 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();
Solution 2
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);
Solution 3
Change the return variable to:
Select @RcptNo = SCOPE_IDENTITY()
It will return the identity number created for the inserted record within this procedure.
Rajiv
Updated on June 15, 2022Comments
-
Rajiv almost 2 years
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
-
Rajiv over 10 years"FirstName" refers to the parameter name in the Store Procedure?
-
Rajiv over 10 yearsI tried as you suggested. I am getting a SQL Exception Timeout eror.
-
Dmytro Rudenko over 10 yearschange 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
-
MLT over 10 years@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'