Well i'm trying the following thing. I want to update 2 tables. See below for the code.
at this line: adapterProducts.InsertCommand.Transaction = adapterProducts.InsertCommand.Connection.BeginTransaction();
i get a error {"OleDbConnection does not support parallel transactions." }
can somebody help me out??
string ConnectionString = @"Provider=Microsoft Jet 4.0 OLE DB Provider;Data Source=C:\test\test_Techno1.mdb;";
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(ConnectionString);
connection.Open();
OleDbTransaction transaction = null;
string strSelectProducts = "SELECT PRODUCT_ID,NAME,PRICE,PRODUCT_CODE,BRAND FROM PRODUCTS";
string strInsertProducts = "INSERT INTO PRODUCTS (NAME,PRICE,PRODUCT_CODE,BRAND) VALUES (?, ?, ?, ?)";
System.Data.Common.DataColumnMapping[] dataColumnMappingProducts = (System.Data.Common.DataColumnMapping[]) Array.CreateInstance( typeof(System.Data.Common.DataColumnMapping), 5 );
dataColumnMappingProducts[0] = new System.Data.Common.DataColumnMapping("PRODUCT_ID","PRODUCT_ID");
dataColumnMappingProducts[1] = new System.Data.Common.DataColumnMapping("NAME,","NAME,");
dataColumnMappingProducts[2] = new System.Data.Common.DataColumnMapping("PRICE","PRICE");
dataColumnMappingProducts[3] = new System.Data.Common.DataColumnMapping("PRODUCT_CODE","PRODUCT_CODE");
dataColumnMappingProducts[4] = new System.Data.Common.DataColumnMapping("BRAND","BRAND");
OleDbDataAdapter adapterProducts = new OleDbDataAdapter();
adapterProducts.SelectCommand = new OleDbCommand(strSelectProducts, connection, transaction);
adapterProducts.InsertCommand = new System.Data.OleDb.OleDbCommand(strInsertProducts, connection, transaction);
adapterProducts.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {new System.Data.Common.DataTableMapping("products", "products", dataColumnMappingProducts)});
OleDbCommandBuilder cbProducts = new OleDbCommandBuilder(adapterProducts);
DataSet dS = new DataSet();
adapterProducts.Fill(dS, "products");
adapterProducts.FillSchema(dS, System.Data.SchemaType.Mapped, "products");
adapterProducts.InsertCommand.Parameters.Add(new OleDbParameter("name", System.Data.OleDb.OleDbType.VarChar , 50 , "name"));
adapterProducts.InsertCommand.Parameters.Add(new OleDbParameter("price", System.Data.OleDb.OleDbType.Currency , 20 , "PRICE"));
adapterProducts.InsertCommand.Parameters.Add(new OleDbParameter("product_code", System.Data.OleDb.OleDbType.VarChar , 50 , "product_code"));
adapterProducts.InsertCommand.Parameters.Add(new OleDbParameter("brand", System.Data.OleDb.OleDbType.VarChar , 20 , "brand"));
string strSelectBrands = "SELECT BRAND_ID, NAME FROM BRANDS";
string strInsertBrands = "INSERT INTO BRANDS (NAME) VALUES (?)";
OleDbDataAdapter adapterBrands = new OleDbDataAdapter();
System.Data.Common.DataColumnMapping[] dataColumnMappingBrands = (System.Data.Common.DataColumnMapping[]) Array.CreateInstance( typeof(System.Data.Common.DataColumnMapping), 1);
dataColumnMappingBrands[0] = new System.Data.Common.DataColumnMapping("NAME","NAME");
adapterBrands.SelectCommand = new OleDbCommand(strSelectBrands, connection, transaction);
adapterBrands.InsertCommand = new System.Data.OleDb.OleDbCommand(strInsertBrands, connection, transaction);
adapterBrands.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {new System.Data.Common.DataTableMapping("brands", "brands", dataColumnMappingBrands)});
adapterBrands.InsertCommand.Parameters.Add(new OleDbParameter("name", System.Data.OleDb.OleDbType.VarChar , 50 , "name"));
OleDbCommandBuilder cbBrands = new OleDbCommandBuilder(adapterBrands);
adapterBrands.Fill(dS, "brands");
adapterBrands.FillSchema(dS, System.Data.SchemaType.Mapped, "brands");
dS.Relations.Add(dS.Tables["brands"].Columns["brand_id"], dS.Tables["products"].Columns["brand"]);
try
{
DataRow drBrand = dS.Tables["brands"].NewRow();
drBrand["name"] = "nieuwe naam";
dS.Tables["brands"].Rows.Add(drBrand);
adapterBrands.InsertCommand.Transaction = adapterBrands.InsertCommand.Connection.BeginTransaction();
adapterBrands.Update(dS.GetChanges(), "brands");
DataRow drProduct = dS.Tables["products"].NewRow();
drProduct["name"] = "nieuw product";
drProduct["product_code"] = "product_code";
drProduct["price"] = "7,5";
drProduct["brand"] = 1;
dS.Tables["products"].Rows.Add(drProduct);
adapterProducts.InsertCommand.Transaction = adapterProducts.InsertCommand.Connection.BeginTransaction();
adapterProducts.Update(dS.GetChanges(), "products");
adapterBrands.InsertCommand.Transaction.Commit();
adapterProducts.InsertCommand.Transaction.Commit();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}