SQLtransaction on multiple insert query

Question

i get an error of

"This SqlTransaction has completed; it is no longer"

while multiple insert in a single table

mclDb.trans = mclDb.mCon.BeginTransaction();
try
                        {
                            foreach (DataGridViewRow dgvRow in dgvDetail.Rows) // get rows in details
                            {
                                ht.Clear();
                                string dtlPoCode = dgvRow.Cells[2].Value.ToString();
                                if (poCode == dtlPoCode) // check if po code matches
                                {
                                    ht.Add("dtlPoCode", dtlPoCode);
                                    ht.Add("dtlPoLine", dgvRow.Cells[3].Value.ToString());
                                    ht.Add("dtlwhouse", dgvRow.Cells[12].Value.ToString());
                                    ht.Add("dtlSkuCode", dgvRow.Cells[4].Value.ToString());
                                    ht.Add("dtlUom", dgvRow.Cells[6].Value.ToString());
                                    ht.Add("dtlmrQty", dgvRow.Cells[7].Value.ToString());
                                    ht.Add("dtlBCode", dgvRow.Cells[10].Value.ToString());
                                    ht.Add("dtlBExp", dgvRow.Cells[11].Value.ToString());
                                    ht.Add("dtlBin", dgvRow.Cells[9].Value.ToString());
                                    ht.Add("custPoCode", custPoCode);
                                    ht.Add("grnCode", grnCode);
                                    ht.Add("refCode", refCode);

                                    mclDb.IUD(ht);
                                }                                   
                            }
                            MessageBox.Show("Transaction Complete");
                            mclDb.trans.Commit();
                        }
                        catch (Exception error)
                        {
                            MessageBox.Show(error.Message, "Error Encountered", MessageBoxButtons.OK, MessageBoxIcon.Error);  mclDb.trans.Rollback();
                        }

here is my IUD method

public void IUD(Hashtable ht)
    {
        try
        {
            Connect();
            string cmd = "";
            cmd = mStatements.getQuery(ht);
            mAdptr = new SqlDataAdapter(cmd,mCon);
            ds = new DataSet();
            mAdptr.Fill(ds);
        }
        catch (Exception Ex)
        {
            throw new Exception(Ex.Message);
        }
    }

and my getquery method

public string getQuery(Hashtable ht) 
    {
        StringBuilder qry = new StringBuilder();
        qry.Append(" Insert into mTable values ");
        qry.Append(" ('" + ht["custPoCode"] + "','" + ht["dtlPoCode"] + "', ");
        qry.Append(" " + ht["dtlPoLine"] + ",'" + ht["dtlwhouse"] + "', ");
        qry.Append(" '" + ht["dtlSkuCode"] + "','" + ht["dtlUom"] + "', ");
        qry.Append(" " + ht["dtlmrQty"] + ",'" + ht["grnCode"] + "', ");
        qry.Append(" '" + ht["refCode"] + "','" + ht["dtlBCode"] + "', ");
        qry.Append(" '" + ht["dtlBExp"] + "','" + ht["dtlBin"] + "', ");
        qry.Append(" '" + ht["dtlmrQty"] + "','' ) ");
        return qry.ToString();
    }

the number of rows to be inserted in the table will depend on how many rows are selected in dgvDetail (which is a datagridview)

any suggestions in how to use sqltransaction on my codes ? tia

Inside `UID` method, what is `Connect` method doing? Also why data adapter, you want to execute insert sql query, right? — Ivan Stoev, Oct 28 '15 at 07:42
@IvanStoev Connect is actually my method of for database connection and adapter so i can also use select query for retrieving data to datagrids — Isaiah, Oct 28 '15 at 07:50
But it shoud already be opened, otherwise the line `mclDb.trans = mclDb.mCon.BeginTransaction();` will fail — Ivan Stoev, Oct 28 '15 at 07:52
`private void Connect() { if (mCon.State == ConnectionState.Closed || mCon.State == ConnectionState.Broken) mCon.ConnectionString = Stringconn; if (mCon.State == ConnectionState.Open) mCon.Close(); mCon.Open(); } ` — Isaiah, Oct 28 '15 at 07:54
My point is the connection should have been opened before starting a transaction and should stay this way until you commit or rollback the transaction. You cannot reopen it inside, that would make transaction invalid, hence the exception you are getting. Also data adapter is not for execution commands like yours. — Ivan Stoev, Oct 28 '15 at 07:56

SQLtransaction on multiple insert query

0 Answers0