Trying to complete a transaction and it fails

[Blaine Tuisee]

Here's the code i'm running. I know it's not the connection because it connects fine, it just doesn't like the transaction. Thank you to any who help.

OracleTransaction txn = conn.BeginTransaction();
        try
        {
            // Set the connection property of the command object
            OracleCommand cmd = conn.CreateCommand();
            cmd.Connection = conn;
            // Set the text of the command to the insert statement
            OracleParameter[] prm = new OracleParameter[12];
            prm[0] = cmd.Parameters.Add(prognum, OracleDbType.Varchar2, prognum, ParameterDirection.Input);
            prm[1] = cmd.Parameters.Add(firstname, OracleDbType.Varchar2, firstname, ParameterDirection.Input);
            prm[2] = cmd.Parameters.Add(lastname, OracleDbType.Varchar2, lastname, ParameterDirection.Input);
            prm[3] = cmd.Parameters.Add(csname, OracleDbType.Varchar2, csname, ParameterDirection.Input);
            prm[4] = cmd.Parameters.Add(q1,OracleDbType.Varchar2, q1, ParameterDirection.Input);
            prm[5] = cmd.Parameters.Add(q2, OracleDbType.Varchar2, q2, ParameterDirection.Input);
            prm[6] = cmd.Parameters.Add(q3, OracleDbType.Varchar2, q3, ParameterDirection.Input);
            prm[7] = cmd.Parameters.Add(q4, OracleDbType.Varchar2, q4, ParameterDirection.Input);
            prm[8] = cmd.Parameters.Add(q5, OracleDbType.Varchar2, q5, ParameterDirection.Input);
            prm[9] = cmd.Parameters.Add(q6, OracleDbType.Varchar2, q6, ParameterDirection.Input);
            prm[10] = cmd.Parameters.Add(q7, OracleDbType.Varchar2, q7, ParameterDirection.Input);
            prm[11] = cmd.Parameters.Add(q8, OracleDbType.Varchar2, q8, ParameterDirection.Input);
            cmd.CommandText = 
                "insert into customer_survey(program, first_name, last_name, cs_name, question_1, question_2, question_3, question_4, question_5, question_6, question_7, question_8) values (:9,:10,:11,:12,:1,:2,:3,:4,:5,:6,:7,:8)";            
            // Set the transaction property of the Command object
            //Execute the statement with ExecuteNonQuery
            cmd.ExecuteNonQuery();
            txn.Commit();
            // Display any exceptions
        }
        catch (Exception ex)
        {
            // Display any exceptions
            Console.WriteLine(ex.Message);
            MessageBox.Show("Transaction Failed");
            // If anything failed after the connection was opened, roll back the transaction
            if (txn != null)
            {
                txn.Rollback();
            }
        }

[sknake]

What is the error message...?

[kvprajapati]

Assign a reference of Transaction.

cmd.Transaction=txn;
  cmd.ExecuteNonQuery();

[Blaine Tuisee]

Assign a reference of Transaction.

cmd.Transaction=txn;
  cmd.ExecuteNonQuery();

Used that code but received the following error

"Property or indexer 'Oracle.DataAccess.Client.OracleCommand.Transaction' cannot be assigned to -- it is read only

[Blaine Tuisee]

What is the error message...?

No error message other than a pop up that says the transaction failed, this is a pop up message from the catch portion of the code.

[Ramy Mahrous]

ex.InnerException and plus you didn't show the error because you are working on Windows-based application
modify your catch block to

catch (Exception ex)
{
// Display any exceptions
//Console.WriteLine(ex.Message);
MessageBox.Show(ex.Message);
MessageBox.Show(ex.InnerException);
//MessageBox.Show("Transaction Failed");
// If anything failed after the connection was opened, roll back the transaction
if (txn != null)
{
txn.Rollback();
}
}

[Blaine Tuisee]

ex.InnerException and plus you didn't show the error because you are working on Windows-based application
modify your catch block to

catch (Exception ex)
{
// Display any exceptions
//Console.WriteLine(ex.Message);
MessageBox.Show(ex.Message);
MessageBox.Show(ex.InnerException);
//MessageBox.Show("Transaction Failed");
// If anything failed after the connection was opened, roll back the transaction
if (txn != null)
{
txn.Rollback();
}
}

This helped alot, thank you. The issue was i had forgotten a column in my table and that is why the query was not working. Thanks again.