Why does insert command not work in a transaction?

  • Thread starter Thread starter Dan V.
  • Start date Start date
D

Dan V.

Why does the insert command not work in a transaction? I am using OleDb
with MS Access and ADO.Net and VS 2003.net

I create the connection and dataadapter initially in a function and return
them so I can reuse them
And in one case I would like a transaction. But when I fill the datarow it
fails for some reason.
I followed: http://support.microsoft.com/kb/310351

Do I need to create another dataadapter for this to work?
Thanks for any help.


The setup is like this:


DataSet dsTrans = dsVerify.GetChanges();

OleDbTransaction trVerify;

trVerify = vConnVerify.BeginTransaction(IsolationLevel.ReadCommitted);

vDA.SelectCommand.Transaction = trVerify;

try

{

FillDataRow( sDB, sOrig, psc, Form_ID, tif, tifNonForm, tct, res, img, "",
"", tblVerify, vDA, dsTrans, tifFound, tifNonFormFound, tctFound, resFound,
imgFound, false, false);

etc.

----------------------------------------------------------------------------
-------


Function that creates connection and dataadpter and update commands:
// Connect to a MS Access Database with standard security

conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +

sDataSource + ";User Id=admin;Password=");

conn.Open();

// Create an updateable DataTable in a DataSet

da = CreateVerifyDataAdapter( sTableName, conn );

ds = new DataSet();

da.Fill(ds, sTableName);

dt = new DataTable();

dt = ds.Tables[sTableName];



-------------------------------------------------------

private OleDbDataAdapter CreateVerifyDataAdapter(string sTableName,
OleDbConnection conn)

{

OleDbDataAdapter da = new OleDbDataAdapter();

OleDbCommand cmd;

// Create the SelectCommand.

cmd = new OleDbCommand("SELECT * FROM " + sTableName, conn);

da.SelectCommand = cmd;

// Create the InsertCommand.

cmd = new OleDbCommand("INSERT INTO " + sTableName + " (fld1, fld2 ) " +

"VALUES (@fld1, @fld2)", conn);

cmd.Parameters.Add("@fld1", OleDbType.VarWChar, 35, "fld1");


cmd.Parameters.Add("@fld2", OleDbType.VarWChar, 35, "fld2");

da.InsertCommand = cmd;

return da;

}







private void FillDataRow(string dn, string tn, Int32 psc, Int32 Form_ID,
string tif, string tifNonForm, string tct, string res, string img, string
extraSus, string extraImg,

DataTable tbl, OleDbDataAdapter bda, DataSet ds, Boolean tifFound, Boolean
tifNonFormFound, Boolean tctFound, Boolean resFound,

Boolean imgFound, Boolean extraSusFound, Boolean extraImgFound)

{

DataRow workrow;

try

{

workrow = tbl.NewRow();

workrow["DatabaseName"] = dn;

workrow["TableName"] = tn;

if ( psc > 0 )

workrow["PageSeq_cod"] = psc;

workrow["Form_ID"] = Form_ID;

if ( tif != "" )

workrow["tif"] = tif;

if ( tifNonForm != "" )

workrow["tifNonForm"] = tifNonForm;

workrow["tct"] = tct;

workrow["res"] = res;

if ( img != "" )

workrow["img"] = img;

workrow["extraSus"] = extraSus;

workrow["extraImg"] = extraImg;

workrow["tifFound"] = tifFound;

workrow["tifNonFormFound"] = tifNonFormFound;

workrow["tctFound"] = tctFound;

workrow["resFound"] = resFound;

workrow["imgFound"] = imgFound;

workrow["extraSusFound"] = extraSusFound;

workrow["extraImgFound"] = extraImgFound;

tbl.Rows.Add(workrow);

bda.Update(ds, tbl.TableName); // run SQL: insert statement

}

catch

{

MessageBox.Show("Post Error using DataRow.\n" );

throw;


}

}
 
It does not update and there is not any error given, strange, can we see
your code part.

Cor

Dan V. said:
Why does the insert command not work in a transaction? I am using OleDb
with MS Access and ADO.Net and VS 2003.net

I create the connection and dataadapter initially in a function and return
them so I can reuse them
And in one case I would like a transaction. But when I fill the datarow
it
fails for some reason.
I followed: http://support.microsoft.com/kb/310351

Do I need to create another dataadapter for this to work?
Thanks for any help.


The setup is like this:


DataSet dsTrans = dsVerify.GetChanges();

OleDbTransaction trVerify;

trVerify = vConnVerify.BeginTransaction(IsolationLevel.ReadCommitted);

vDA.SelectCommand.Transaction = trVerify;

try

{

FillDataRow( sDB, sOrig, psc, Form_ID, tif, tifNonForm, tct, res, img, "",
"", tblVerify, vDA, dsTrans, tifFound, tifNonFormFound, tctFound,
resFound,
imgFound, false, false);

etc.

----------------------------------------------------------------------------
-------


Function that creates connection and dataadpter and update commands:
// Connect to a MS Access Database with standard security

conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+

sDataSource + ";User Id=admin;Password=");

conn.Open();

// Create an updateable DataTable in a DataSet

da = CreateVerifyDataAdapter( sTableName, conn );

ds = new DataSet();

da.Fill(ds, sTableName);

dt = new DataTable();

dt = ds.Tables[sTableName];



-------------------------------------------------------

private OleDbDataAdapter CreateVerifyDataAdapter(string sTableName,
OleDbConnection conn)

{

OleDbDataAdapter da = new OleDbDataAdapter();

OleDbCommand cmd;

// Create the SelectCommand.

cmd = new OleDbCommand("SELECT * FROM " + sTableName, conn);

da.SelectCommand = cmd;

// Create the InsertCommand.

cmd = new OleDbCommand("INSERT INTO " + sTableName + " (fld1, fld2 ) " +

"VALUES (@fld1, @fld2)", conn);

cmd.Parameters.Add("@fld1", OleDbType.VarWChar, 35, "fld1");


cmd.Parameters.Add("@fld2", OleDbType.VarWChar, 35, "fld2");

da.InsertCommand = cmd;

return da;

}







private void FillDataRow(string dn, string tn, Int32 psc, Int32 Form_ID,
string tif, string tifNonForm, string tct, string res, string img, string
extraSus, string extraImg,

DataTable tbl, OleDbDataAdapter bda, DataSet ds, Boolean tifFound, Boolean
tifNonFormFound, Boolean tctFound, Boolean resFound,

Boolean imgFound, Boolean extraSusFound, Boolean extraImgFound)

{

DataRow workrow;

try

{

workrow = tbl.NewRow();

workrow["DatabaseName"] = dn;

workrow["TableName"] = tn;

if ( psc > 0 )

workrow["PageSeq_cod"] = psc;

workrow["Form_ID"] = Form_ID;

if ( tif != "" )

workrow["tif"] = tif;

if ( tifNonForm != "" )

workrow["tifNonForm"] = tifNonForm;

workrow["tct"] = tct;

workrow["res"] = res;

if ( img != "" )

workrow["img"] = img;

workrow["extraSus"] = extraSus;

workrow["extraImg"] = extraImg;

workrow["tifFound"] = tifFound;

workrow["tifNonFormFound"] = tifNonFormFound;

workrow["tctFound"] = tctFound;

workrow["resFound"] = resFound;

workrow["imgFound"] = imgFound;

workrow["extraSusFound"] = extraSusFound;

workrow["extraImgFound"] = extraImgFound;

tbl.Rows.Add(workrow);

bda.Update(ds, tbl.TableName); // run SQL: insert statement

}

catch

{

MessageBox.Show("Post Error using DataRow.\n" );

throw;


}

}
Click to expand...
 
Gladly.

The exception is 'thrown' when I call: "bda.Update(ds, tbl.TableName);" in
the FillDataRow function.

In the catch section of code after the error was found, I looked at the
local variables and the variable "ds" (DataSet) was undefined. In fact when
I traced it back farther to the calling code line, it is undefined after
assignment for some reason.
DataSet dsTrans = dsVerify.GetChanges();


The rollback call ( trVerify.Rollback(); ) executes without error.

The function "SetupVerifyTable" below is the same one that I sent before
that I called:
"Function that creates connection and dataadpter and update commands"

----------------------------------------------------------------------------
-----
....
SetupVerifyTable(txtVerifyDB.Text, "Summary", chkClearSummary.Checked, out
vConnVerify, out vDA, out dsVerify, out tblVerify );

/* variables passed back from SetupVerifyTable() function

DataRow[] drow;

OleDbConnection vConnVerify;

OleDbDataAdapter vDA;

DataSet dsVerify;

DataTable tblVerify;


*/

DataSet dsTrans = dsVerify.GetChanges();

OleDbTransaction trVerify;

trVerify = vConnVerify.BeginTransaction(IsolationLevel.ReadCommitted);

vDA.SelectCommand.Transaction = trVerify;

try

{

// Copy files and get file info

FillDataRow( sDB, sOrig, psc, Form_ID, tif, tifNonForm, tct, res, img, "",
"", tblVerify, vDA, dsTrans, tifFound, tifNonFormFound, tctFound, resFound,
imgFound, false, false);

trVerify.Commit();

} // try

catch

{

try

{

MessageBox.Show("Error encountered. Will rollback last transaction.",
"Organize Teleform Files", MessageBoxButtons.OK,
MessageBoxIcon.Information);

trVerify.Rollback();

return;

} // try

catch (OleDbException ex)

{

if (trVerify.Connection != null)

{

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

" was encountered while attempting to roll back the transaction.", "Organize
Teleform Files", MessageBoxButtons.OK, MessageBoxIcon.Information);

return;

}

} // catch


} // catch



//---------------------------------------------------------------------

private void SetupVerifyTable(string sDataSource, string sTableName, Boolean
isClear, out OleDbConnection conn, out OleDbDataAdapter da, out DataSet ds,
out DataTable dt )

{

// Connect to a MS Access Database with standard security

conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +

sDataSource + ";User Id=admin;Password=");

conn.Open();

// Create an updateable DataTable in a DataSet

da = CreateVerifyDataAdapter( sTableName, conn );

ds = new DataSet();

da.Fill(ds, sTableName);

dt = new DataTable();

dt = ds.Tables[sTableName];

if (isClear)

{

OleDbCommand cmd = new OleDbCommand("Delete From " + sTableName, conn);

cmd.ExecuteNonQuery(); // Empty the DataTable initially

}

}



Cor Ligthert said:
It does not update and there is not any error given, strange, can we see
your code part.

Cor

Dan V. said:
Why does the insert command not work in a transaction? I am using OleDb
with MS Access and ADO.Net and VS 2003.net

I create the connection and dataadapter initially in a function and return
them so I can reuse them
And in one case I would like a transaction. But when I fill the datarow
it
fails for some reason.
I followed: http://support.microsoft.com/kb/310351

Do I need to create another dataadapter for this to work?
Thanks for any help.


The setup is like this:


DataSet dsTrans = dsVerify.GetChanges();

OleDbTransaction trVerify;

trVerify = vConnVerify.BeginTransaction(IsolationLevel.ReadCommitted);

vDA.SelectCommand.Transaction = trVerify;

try

{

FillDataRow( sDB, sOrig, psc, Form_ID, tif, tifNonForm, tct, res, img, "",
"", tblVerify, vDA, dsTrans, tifFound, tifNonFormFound, tctFound,
resFound,
imgFound, false, false);

etc.

--------------------------------------------------------------------------
Click to expand...
--
-------


Function that creates connection and dataadpter and update commands:
// Connect to a MS Access Database with standard security

conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+

sDataSource + ";User Id=admin;Password=");

conn.Open();

// Create an updateable DataTable in a DataSet

da = CreateVerifyDataAdapter( sTableName, conn );

ds = new DataSet();

da.Fill(ds, sTableName);

dt = new DataTable();

dt = ds.Tables[sTableName];



-------------------------------------------------------

private OleDbDataAdapter CreateVerifyDataAdapter(string sTableName,
OleDbConnection conn)

{

OleDbDataAdapter da = new OleDbDataAdapter();

OleDbCommand cmd;

// Create the SelectCommand.

cmd = new OleDbCommand("SELECT * FROM " + sTableName, conn);

da.SelectCommand = cmd;

// Create the InsertCommand.

cmd = new OleDbCommand("INSERT INTO " + sTableName + " (fld1, fld2 ) " +

"VALUES (@fld1, @fld2)", conn);

cmd.Parameters.Add("@fld1", OleDbType.VarWChar, 35, "fld1");


cmd.Parameters.Add("@fld2", OleDbType.VarWChar, 35, "fld2");

da.InsertCommand = cmd;

return da;

}







private void FillDataRow(string dn, string tn, Int32 psc, Int32 Form_ID,
string tif, string tifNonForm, string tct, string res, string img, string
extraSus, string extraImg,

DataTable tbl, OleDbDataAdapter bda, DataSet ds, Boolean tifFound, Boolean
tifNonFormFound, Boolean tctFound, Boolean resFound,

Boolean imgFound, Boolean extraSusFound, Boolean extraImgFound)

{

DataRow workrow;

try

{

workrow = tbl.NewRow();

workrow["DatabaseName"] = dn;

workrow["TableName"] = tn;

if ( psc > 0 )

workrow["PageSeq_cod"] = psc;

workrow["Form_ID"] = Form_ID;

if ( tif != "" )

workrow["tif"] = tif;

if ( tifNonForm != "" )

workrow["tifNonForm"] = tifNonForm;

workrow["tct"] = tct;

workrow["res"] = res;

if ( img != "" )

workrow["img"] = img;

workrow["extraSus"] = extraSus;

workrow["extraImg"] = extraImg;

workrow["tifFound"] = tifFound;

workrow["tifNonFormFound"] = tifNonFormFound;

workrow["tctFound"] = tctFound;

workrow["resFound"] = resFound;

workrow["imgFound"] = imgFound;

workrow["extraSusFound"] = extraSusFound;

workrow["extraImgFound"] = extraImgFound;

tbl.Rows.Add(workrow);

bda.Update(ds, tbl.TableName); // run SQL: insert statement

}

catch

{

MessageBox.Show("Post Error using DataRow.\n" );

throw;


}

}
Click to expand...
Click to expand...
 
When I send the DataSet to FillDataRow, that is setup correctly (dsverify )
, I still can't update the data adapter.

I'm not very familiar at all with Visual Studio 2003.net but in the local
variables under DataSet "ds" and workrow there is an error for line: Error|
rowError and Error | Text and RowError that all read the same:

"Execute requires the command to have a transaction object when the
connection assigned to the command is in a pending local transaction. The
Transaction property of the command has not been initialized."

So does this mean "The Transaction property of the command has not been
initialized."
If so, I wonder why?

Cor Ligthert said:
It does not update and there is not any error given, strange, can we see
your code part.

Cor

Dan V. said:
Why does the insert command not work in a transaction? I am using OleDb
with MS Access and ADO.Net and VS 2003.net

I create the connection and dataadapter initially in a function and return
them so I can reuse them
And in one case I would like a transaction. But when I fill the datarow
it
fails for some reason.
I followed: http://support.microsoft.com/kb/310351

Do I need to create another dataadapter for this to work?
Thanks for any help.


The setup is like this:


DataSet dsTrans = dsVerify.GetChanges();

OleDbTransaction trVerify;

trVerify = vConnVerify.BeginTransaction(IsolationLevel.ReadCommitted);

vDA.SelectCommand.Transaction = trVerify;

try

{

FillDataRow( sDB, sOrig, psc, Form_ID, tif, tifNonForm, tct, res, img, "",
"", tblVerify, vDA, dsTrans, tifFound, tifNonFormFound, tctFound,
resFound,
imgFound, false, false);

etc.

--------------------------------------------------------------------------
Click to expand...
--
-------


Function that creates connection and dataadpter and update commands:
// Connect to a MS Access Database with standard security

conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+

sDataSource + ";User Id=admin;Password=");

conn.Open();

// Create an updateable DataTable in a DataSet

da = CreateVerifyDataAdapter( sTableName, conn );

ds = new DataSet();

da.Fill(ds, sTableName);

dt = new DataTable();

dt = ds.Tables[sTableName];



-------------------------------------------------------

private OleDbDataAdapter CreateVerifyDataAdapter(string sTableName,
OleDbConnection conn)

{

OleDbDataAdapter da = new OleDbDataAdapter();

OleDbCommand cmd;

// Create the SelectCommand.

cmd = new OleDbCommand("SELECT * FROM " + sTableName, conn);

da.SelectCommand = cmd;

// Create the InsertCommand.

cmd = new OleDbCommand("INSERT INTO " + sTableName + " (fld1, fld2 ) " +

"VALUES (@fld1, @fld2)", conn);

cmd.Parameters.Add("@fld1", OleDbType.VarWChar, 35, "fld1");


cmd.Parameters.Add("@fld2", OleDbType.VarWChar, 35, "fld2");

da.InsertCommand = cmd;

return da;

}







private void FillDataRow(string dn, string tn, Int32 psc, Int32 Form_ID,
string tif, string tifNonForm, string tct, string res, string img, string
extraSus, string extraImg,

DataTable tbl, OleDbDataAdapter bda, DataSet ds, Boolean tifFound, Boolean
tifNonFormFound, Boolean tctFound, Boolean resFound,

Boolean imgFound, Boolean extraSusFound, Boolean extraImgFound)

{

DataRow workrow;

try

{

workrow = tbl.NewRow();

workrow["DatabaseName"] = dn;

workrow["TableName"] = tn;

if ( psc > 0 )

workrow["PageSeq_cod"] = psc;

workrow["Form_ID"] = Form_ID;

if ( tif != "" )

workrow["tif"] = tif;

if ( tifNonForm != "" )

workrow["tifNonForm"] = tifNonForm;

workrow["tct"] = tct;

workrow["res"] = res;

if ( img != "" )

workrow["img"] = img;

workrow["extraSus"] = extraSus;

workrow["extraImg"] = extraImg;

workrow["tifFound"] = tifFound;

workrow["tifNonFormFound"] = tifNonFormFound;

workrow["tctFound"] = tctFound;

workrow["resFound"] = resFound;

workrow["imgFound"] = imgFound;

workrow["extraSusFound"] = extraSusFound;

workrow["extraImgFound"] = extraImgFound;

tbl.Rows.Add(workrow);

bda.Update(ds, tbl.TableName); // run SQL: insert statement

}

catch

{

MessageBox.Show("Post Error using DataRow.\n" );

throw;


}

}
Click to expand...
Click to expand...
 
Issue looks resolved. i.e. I can post a row of data now and commit the
changes. I'll test rollback next.

I replace the 3 wrong lines of the calling code:
OleDbTransaction trVerify;
trVerify = vConnVerify.BeginTransaction(IsolationLevel.ReadCommitted);
vDA.SelectCommand.Transaction = trVerify;


with this good code:

myTrans =
vDA.InsertCommand.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
vDA.InsertCommand.Transaction = myTrans;

Answer found at:
http://www.tek-tips.com/viewthread.cfm?qid=1095432&page=7



Dan V. said:
When I send the DataSet to FillDataRow, that is setup correctly (dsverify )
, I still can't update the data adapter.

I'm not very familiar at all with Visual Studio 2003.net but in the local
variables under DataSet "ds" and workrow there is an error for line: Error|
rowError and Error | Text and RowError that all read the same:

"Execute requires the command to have a transaction object when the
connection assigned to the command is in a pending local transaction. The
Transaction property of the command has not been initialized."

So does this mean "The Transaction property of the command has not been
initialized."
If so, I wonder why?

Cor Ligthert said:
It does not update and there is not any error given, strange, can we see
your code part.

Cor

Dan V. said:
Why does the insert command not work in a transaction? I am using OleDb
with MS Access and ADO.Net and VS 2003.net

I create the connection and dataadapter initially in a function and return
them so I can reuse them
And in one case I would like a transaction. But when I fill the datarow
it
fails for some reason.
I followed: http://support.microsoft.com/kb/310351

Do I need to create another dataadapter for this to work?
Thanks for any help.


The setup is like this:


DataSet dsTrans = dsVerify.GetChanges();

OleDbTransaction trVerify;

trVerify = vConnVerify.BeginTransaction(IsolationLevel.ReadCommitted);

vDA.SelectCommand.Transaction = trVerify;

try

{

FillDataRow( sDB, sOrig, psc, Form_ID, tif, tifNonForm, tct, res, img, "",
"", tblVerify, vDA, dsTrans, tifFound, tifNonFormFound, tctFound,
resFound,
imgFound, false, false);

etc.
Click to expand...

--------------------------------------------------------------------------
--
-------


Function that creates connection and dataadpter and update commands:
// Connect to a MS Access Database with standard security

conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+

sDataSource + ";User Id=admin;Password=");

conn.Open();

// Create an updateable DataTable in a DataSet

da = CreateVerifyDataAdapter( sTableName, conn );

ds = new DataSet();

da.Fill(ds, sTableName);

dt = new DataTable();

dt = ds.Tables[sTableName];



-------------------------------------------------------

private OleDbDataAdapter CreateVerifyDataAdapter(string sTableName,
OleDbConnection conn)

{

OleDbDataAdapter da = new OleDbDataAdapter();

OleDbCommand cmd;

// Create the SelectCommand.

cmd = new OleDbCommand("SELECT * FROM " + sTableName, conn);

da.SelectCommand = cmd;

// Create the InsertCommand.

cmd = new OleDbCommand("INSERT INTO " + sTableName + " (fld1, fld2 ) " +

"VALUES (@fld1, @fld2)", conn);

cmd.Parameters.Add("@fld1", OleDbType.VarWChar, 35, "fld1");


cmd.Parameters.Add("@fld2", OleDbType.VarWChar, 35, "fld2");

da.InsertCommand = cmd;

return da;

}







private void FillDataRow(string dn, string tn, Int32 psc, Int32 Form_ID,
string tif, string tifNonForm, string tct, string res, string img, string
extraSus, string extraImg,

DataTable tbl, OleDbDataAdapter bda, DataSet ds, Boolean tifFound, Boolean
tifNonFormFound, Boolean tctFound, Boolean resFound,

Boolean imgFound, Boolean extraSusFound, Boolean extraImgFound)

{

DataRow workrow;

try

{

workrow = tbl.NewRow();

workrow["DatabaseName"] = dn;

workrow["TableName"] = tn;

if ( psc > 0 )

workrow["PageSeq_cod"] = psc;

workrow["Form_ID"] = Form_ID;

if ( tif != "" )

workrow["tif"] = tif;

if ( tifNonForm != "" )

workrow["tifNonForm"] = tifNonForm;

workrow["tct"] = tct;

workrow["res"] = res;

if ( img != "" )

workrow["img"] = img;

workrow["extraSus"] = extraSus;

workrow["extraImg"] = extraImg;

workrow["tifFound"] = tifFound;

workrow["tifNonFormFound"] = tifNonFormFound;

workrow["tctFound"] = tctFound;

workrow["resFound"] = resFound;

workrow["imgFound"] = imgFound;

workrow["extraSusFound"] = extraSusFound;

workrow["extraImgFound"] = extraImgFound;

tbl.Rows.Add(workrow);

bda.Update(ds, tbl.TableName); // run SQL: insert statement

}

catch

{

MessageBox.Show("Post Error using DataRow.\n" );

throw;


}

}
Click to expand...
Click to expand...
Click to expand...
 
Back
Top