How to store data in 2 different tables at single button click event?

Question

Hi, Can we store data like this in 2 different tables? if yes plzz guide

Krishna Garad · Answer

You missed the opening and closing paranthesis for parameters write like bellow Create Procedure Sp_SpName (--Which you missed @para1, @para2 )--Missing in your case AS Begin --Operation End

Nethra R S · Answer

I get an error when i try to save data in 2 tables using stored procedure Stored Procedure is as below: USE [ERMS-WEB] GO /****** Object: StoredProcedure [dbo].[sp_SaveItemDetails] Script Date: 09/13/2011 09:22:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc [dbo].[sp_SaveItemDetails] @ItemCode varchar(150), @ItemName varchar(150), @ItemGroup smallint, @ItemType smallint, @ItemCategory smallint, @Unit smallint, @MinOrderingLevel decimal(10,2), @SellorBuy varchar(20), @SellingPrice int, @SellingDescription varchar(500), @PurchasePrice int, @PurchaseDescription varchar(500), @Status varchar(20), @LocationID int, @StoreID int, @RackID int, @BinID int, @Qty decimal(10,2), @Removed varchar(20) AS begin set nocount on declare @ItemID int Insert into tblItem_Master(ItemCode,ItemName,ItemGroup,ItemType,ItemCategory,Unit,MinOrderingLevel,SellorBuy,SellingPrice,SellingDescription,PurchasePrice,PurchaseDescription,Status)values(@ItemCode,@ItemName,@ItemGroup,@ItemType,@ItemCategory,@Unit,@MinOrderingLevel,@SellorBuy,@SellingPrice,@SellingDescription,@PurchasePrice,@PurchaseDescription,@Status) select @ItemID =@@IDENTITY Insert into tblItem_LocationMaster(ItemID,LocationID,StoreID,RackID,BinID,Qty,Removed)values(@ItemID,@LocationID,@StoreID,@RackID,@BinID,@Qty,@Removed) The error i am getting is : Msg 102, Level 15, State 1, Procedure sp_SaveItemDetails, Line 27 Incorrect syntax near ')'

Prabhu Raja · Answer

If both tables having the same table Stucture and columns, then you can do it by using single stored procedure. If not you should use different procedures for both the tables. try this, try { SqlConnection con = new SqlConnection("your connection String"); //Create Sql Connection Object SqlCommand com = new SqlCommand(); // Create Sql Command Object con.open(); //Open your Connection once in a life cycle. com.Connection = con; //Assign connection object to command object to once in a life cycle com.CommandText = "Table1_Insert"; // Stored Procedure Name com.CommandType = CommandType.StoredProcedure; com.Parameters.Add["@ParameterName"].Value = this.TextBox1.Text; //Pass All parameters to Procedure like this. com.ExecuteNonQuery(); //execute your Procedure com.Parameters.Clear(); // Clear your sql Command com.CommandText = "Table2_Insert"; com.CommandType = CommandType.StoredProcedure; com.Parameters.Add["@parameter2"].Value = this.TextBox2.Text; com.ExecuteNonQuery(); con.Close(); //Close you Connection } catch(Exception ex) { // Handle Exceptions Here } ----------------------------------------------------------- If it helps you, then mark as "Correct Answer"

Satyapriya Nayak · Answer

Hi Nethra, Here is your solution Create two stored procedure as below ALTER PROCEDURE insert1 (@custid varchar(50),@custname varchar(50),@custaddress varchar(50),@prodid varchar (50)) AS insert Customer(custid,custname,custaddress,prodid) values (@custid,@custname,@custaddress,@prodid) ALTER PROCEDURE insert2 (@prodid varchar(50),@prodname varchar(50),@price int) AS insert Product(prodid,prodname,price) values (@prodid,@prodname,@price) Untitled Page onclick="btn_insert_Click" /> using System; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page { string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; SqlCommand com; protected void btn_insert_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(strConnString); com = new SqlCommand("insert1", con); com.CommandType = CommandType.StoredProcedure; com.Parameters.Add("@custid", SqlDbType.VarChar).Value = TextBox1.Text; com.Parameters.Add("@custname", SqlDbType.VarChar).Value = TextBox2.Text; com.Parameters.Add("@custaddress", SqlDbType.VarChar).Value = TextBox3.Text; com.Parameters.Add("@prodid", SqlDbType.VarChar).Value = TextBox4.Text; con.Open(); com.ExecuteNonQuery(); con.Close(); com = new SqlCommand("insert2", con); com.CommandType = CommandType.StoredProcedure; com.Parameters.AddWithValue("@prodid", SqlDbType.VarChar).Value = TextBox4.Text; com.Parameters.Add("@prodname", SqlDbType.VarChar).Value = TextBox5.Text; com.Parameters.Add("@price", SqlDbType.Int).Value = TextBox6.Text; con.Open(); com.ExecuteNonQuery(); con.Close(); } } Thanks If this post helps you mark it as answer

Sandeep Kumar · Answer

you can do it from procedure and also from defining different-2 command and you execute both the command.

Krishna Garad · Answer

Best way is use stored procedure like Create Procedure SpAddTwoTableData ( //Parameters ) As Begin Begin INsert Into Tableone Values(...) End begin Insert Into Tabletwo Value(...) End End Write sp in database and call it from you application.

How to store data in 2 different tables at single button click event?

Answers (6)