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

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

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 ')'

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"

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)





<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>





   


   

   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

            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

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.