Multiple Table Data insert problem Some time and rollback

krishan kumar singh

I have facing some problem to insert multiple table some time that insert only single table not insert into another table and rollback is not working what happen i can't understand please help me

I use this code(Database is online )

using (SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["Connection"].ToString()))

{

connection.Open();

SqlCommand command = connection.CreateCommand();

SqlTransaction transaction;

transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);

command.Connection = connection;

command.Transaction = transaction;

try

{

// First Store Procedure

command.CommandText = "PrintInvoiceNew";

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@BillNo", SqlDbType.VarChar, 50).Value = addstr;

command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerID;

command.ExecuteNonQuery();

command.Parameters.Clear();

//Second Store Procedure

command.CommandText = "PrintInvoiceDetailsInsert";

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@BillNo", SqlDbType.VarChar, 50).Value = addstr;

command.Parameters.Add("@ItemVariantID", SqlDbType.Int).Value = dgvInvoice[0, i].Value;

command.Parameters.Add("@ItemCode", SqlDbType.NVarChar, 50).Value = dgvInvoice[1, i].Value;

command.Parameters.Add("@ItemName", SqlDbType.NVarChar, 250).Value = dgvInvoice[2, i].Value;

command.ExecuteNonQuery();

command.Parameters.Clear();

// Third store procedure

command.CommandText = "PrintStockUpdate";

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@Stock", SqlDbType.Int).Value = dgvInvoice[3, i].Value;

command.Parameters.Add("@LocationID", SqlDbType.Int).Value = Convert.ToInt32(Variables.LocationID);

command.Parameters.Add("@ItemVariantID", SqlDbType.Int).Value = dgvInvoice[0, i].Value;

command.ExecuteNonQuery();

command.Parameters.Clear();

...........

transaction.Commit();

}

catch (Exception exc)

{

try

{

transaction.Rollback();

}

catch (SqlException ex)

{

if (transaction.Connection != null)

{

MessageBox.Show("An exception of type " + ex.GetType() +

" was encountered while attempting to roll back the transaction.");

}

MessageBox.Show("An exception of type " + exc.GetType() +

" was encountered while inserting the data. Message :" + exc.Message);

MessageBox.Show("Neither record was written to database.");

}

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
then you can do by using only one sp, in that sp just put condition for table1 weather InvoiceId=0 or not, If it zero then only Insert the record, else dont do any thing. For the first record, when you are inserting the Invoice details return the newly inserted InvoiceId (or InvoiceNo - PK) , so that for any further item of the same invoice can be added by passing the InvoiceId

Regards,
Iftikar
0
krishan kumar singh13y ago
yes, i store invoice item one by one and also manage stock , please suggest what is the problem, network or other issue
0
Iftikar Hussain13y ago
are you trying to store Invoice item one by one or all together?

Regards,
Iftikar
0
krishan kumar singh13y ago
Thankyou to reply I think it is not possible to send all parameters values in one procedure, it is trasaction table of product they have multiple value, please suggest
0
13y ago
Try to avoid using transaction in ADO.net and use transaction in sql server side.
Please follow Iftikar Hussain comment.
0
Iftikar Hussain13y ago
Hi,
It is good have one SP instead of two SP, since both are related each other. Create one SP and pass all field value. In that SP use TRANSACTION with TRY-CATCH like this

BEGIN TRY

BEGIN TRAN
//insert data for table1
//insert data for table2
COMMIT TRAN
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);
END CATCH;

Regards,
Iftikar
0