c# code for inserting values in two tables in windows forms

Posted by Iliyas under C# on 4/16/2014 | Points: 10 | Views : 4724 | Status : [Member] | Replies : 1
I am using text boxes and data grid view in my winform application. Where text boxes values and data grid view values entered by user should be saved in two different SQL tables,means all textbox entries save in table1 and all gridview entries in table2.

How do I do that?
Here is my stored procedure and tables Purchase and Purchasedetail respectively.

CREATE PROC sp_insert_pdtl (
@purchase_id varchar(15),
@purchase_date date,
@ref_no int,
@product_id int,
@product_name nvarchar(50),
@qty int,
@price float,
@tax int,
@discount int,
@total int
) AS

begin transaction

INSERT INTO Purchase(purchase_id, purchase_date, ref_no)
VALUES(@purchase_id, @purchase_date, @ref_no)
INSERT INTO Purchasedetail(product_id, product_name, qty, price, tax, discount, total)
VALUES (@product_id, @product_name, @qty, @price, @tax, @discount, @total)


commit`enter code here`

private void SAVE(object sender, EventArgs e)
{
try
{
con.Open();
using (SqlTransaction transaction = con.BeginTransaction())
{
auto();
cmd = new SqlCommand("sp_insert_pdtl", con,transaction);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@purchase_id", textid.Text);
cmd.Parameters.AddWithValue("@purchase_date", dateTimePicker1.Value);
cmd.Parameters.AddWithValue("@ref_no", textrno.Text);
for (int i = 0; i < datagrid.Rows.Count; i++)
{
cmd.Parameters.AddWithValue("@product_id", datagrid.Rows[i].Cells["product_id"].FormattedValue);
cmd.Parameters.AddWithValue("@product_name", datagrid.Rows[i].Cells["product_name"].FormattedValue);
cmd.Parameters.AddWithValue("@qty", datagrid.Rows[i].Cells["qty"].FormattedValue);
cmd.Parameters.AddWithValue("@price", datagrid.Rows[i].Cells["price"].FormattedValue);
cmd.Parameters.AddWithValue("@tax", datagrid.Rows[i].Cells["tax"].FormattedValue);
cmd.Parameters.AddWithValue("@discount", datagrid.Rows[i].Cells["discount"].FormattedValue);
cmd.Parameters.AddWithValue("@total", datagrid.Rows[i].Cells["total"].FormattedValue);
}
cmd.ExecuteNonQuery();
transaction.Commit();
}
MessageBox.Show("Added Sucessfully", "OUTPUT", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}

lazaz12


Responses

Posted by: Vinothkumar on: 4/16/2014 [Member] Starter | Points: 25

Up
0
Down
Try two seperate stored procedure.
why r u using it as a single procedure...

Regards,
Vinoth kumar

Iliyas, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response