此段代码功能是从SQL读出数据写到datatable 中,然后通过oledbdataadpter 批量更新的access中去,但总是出现concurrency violation the updatecommand affected 0 of the expected 1 records错误,网上说是由自填充字段引起,但并没有自填充字段。
protected void SQLToMDB(string Str_MDBsource)
{
try
{ String Str_SQLconnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
String Str_MDBconnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=password;Data source=" + Str_MDBsource + "";
using (System.Data.SqlClient.SqlConnection SQLconn = new System.Data.SqlClient.SqlConnection(Str_SQLconnectionString))
{ SQLconn.Open();
using (System.Data.OleDb.OleDbConnection MDBconn = new System.Data.OleDb.OleDbConnection(Str_MDBconnectionString))
{
MDBconn.Open();
using (System.Data.SqlClient.SqlCommand sqlcmd1 = new System.Data.SqlClient.SqlCommand("select [Index], [Property],[Test],[Min],[Max] from [Tbl_Detail111]", SQLconn))
{
sqlcmd1.CommandType = System.Data.CommandType.Text;
SqlDataAdapter sqladp = new SqlDataAdapter(sqlcmd1);
DataTable SQL_dt = new DataTable();
sqladp.Fill(SQL_dt);
int rowcount = SQL_dt.Rows.Count;
OleDbDataAdapter mdbadp = new OleDbDataAdapter ();
mdbadp.SelectCommand = new OleDbCommand("select [Index],[Property],[Test],[Min], from tbl_detail111 where 1=0", MDBconn);
mdbadp.UpdateCommand = new OleDbCommand("update tbl_mcsdetail set [Index]='?',[Property]='?',[Test]='?',[Min]='?',[Max]='?'”, MDBconn);
// mdbadp.UpdateBatchSize = 0;
DataSet dataset= new DataSet();
mdbadp.Fill (dataset);
for (int i = 0; i < 200; i++)
{
DataRow dtrow = dataset.Tables[0].NewRow();
dtrow["MCSIndex"] = "TEMP";
dataset.Tables[0].Rows.Add(dtrow);
}
dataset.AcceptChanges(); mdbadp.UpdateCommand.Parameters.AddWithValue("@[Index]","Index");
mdbadp.UpdateCommand.Parameters.AddWithValue("@[Property]", "Property");
mdbadp.UpdateCommand.Parameters.AddWithValue("@[Test]", "Test");
mdbadp.UpdateCommand.Parameters.AddWithValue("@[Min]", "Min");
mdbadp.UpdateCommand.Parameters.AddWithValue("@[Max]", "Max");
mdbadp.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
for (int count = 0; count < (rowcount - 1) / 100 + 1; count++)
{
for (int i = count * 100; i < count * 100 + 100; i++)
{
dataset.Tables[0].Rows[i].BeginEdit();
dataset.Tables[0].Rows[i]["Index"] = SQL_dt.Rows[i]["Index"].ToString();
dataset.Tables[0].Rows[i]["Property"]= SQL_dt.Rows[i]["Property"].ToString();
dataset.Tables[0].Rows[i]["Test"]= SQL_dt.Rows[i]["Test"].ToString();
dataset.Tables[0].Rows[i]["Min"] = SQL_dt.Rows[i]["Min"].ToString();
dataset.Tables[0].Rows[i]["Max"]= SQL_dt.Rows[i]["Max"].ToString(); dataset.Tables[0].Rows[i].EndEdit(); }
mdbadp.Update(dataset.Tables[0]); } dataset.Tables[0].Clear();
mdbadp.Dispose(); }
} }
} catch (Exception ex)
{
Response.Write(ex.Message); }
}
protected void SQLToMDB(string Str_MDBsource)
{
try
{ String Str_SQLconnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
String Str_MDBconnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=password;Data source=" + Str_MDBsource + "";
using (System.Data.SqlClient.SqlConnection SQLconn = new System.Data.SqlClient.SqlConnection(Str_SQLconnectionString))
{ SQLconn.Open();
using (System.Data.OleDb.OleDbConnection MDBconn = new System.Data.OleDb.OleDbConnection(Str_MDBconnectionString))
{
MDBconn.Open();
using (System.Data.SqlClient.SqlCommand sqlcmd1 = new System.Data.SqlClient.SqlCommand("select [Index], [Property],[Test],[Min],[Max] from [Tbl_Detail111]", SQLconn))
{
sqlcmd1.CommandType = System.Data.CommandType.Text;
SqlDataAdapter sqladp = new SqlDataAdapter(sqlcmd1);
DataTable SQL_dt = new DataTable();
sqladp.Fill(SQL_dt);
int rowcount = SQL_dt.Rows.Count;
OleDbDataAdapter mdbadp = new OleDbDataAdapter ();
mdbadp.SelectCommand = new OleDbCommand("select [Index],[Property],[Test],[Min], from tbl_detail111 where 1=0", MDBconn);
mdbadp.UpdateCommand = new OleDbCommand("update tbl_mcsdetail set [Index]='?',[Property]='?',[Test]='?',[Min]='?',[Max]='?'”, MDBconn);
// mdbadp.UpdateBatchSize = 0;
DataSet dataset= new DataSet();
mdbadp.Fill (dataset);
for (int i = 0; i < 200; i++)
{
DataRow dtrow = dataset.Tables[0].NewRow();
dtrow["MCSIndex"] = "TEMP";
dataset.Tables[0].Rows.Add(dtrow);
}
dataset.AcceptChanges(); mdbadp.UpdateCommand.Parameters.AddWithValue("@[Index]","Index");
mdbadp.UpdateCommand.Parameters.AddWithValue("@[Property]", "Property");
mdbadp.UpdateCommand.Parameters.AddWithValue("@[Test]", "Test");
mdbadp.UpdateCommand.Parameters.AddWithValue("@[Min]", "Min");
mdbadp.UpdateCommand.Parameters.AddWithValue("@[Max]", "Max");
mdbadp.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
for (int count = 0; count < (rowcount - 1) / 100 + 1; count++)
{
for (int i = count * 100; i < count * 100 + 100; i++)
{
dataset.Tables[0].Rows[i].BeginEdit();
dataset.Tables[0].Rows[i]["Index"] = SQL_dt.Rows[i]["Index"].ToString();
dataset.Tables[0].Rows[i]["Property"]= SQL_dt.Rows[i]["Property"].ToString();
dataset.Tables[0].Rows[i]["Test"]= SQL_dt.Rows[i]["Test"].ToString();
dataset.Tables[0].Rows[i]["Min"] = SQL_dt.Rows[i]["Min"].ToString();
dataset.Tables[0].Rows[i]["Max"]= SQL_dt.Rows[i]["Max"].ToString(); dataset.Tables[0].Rows[i].EndEdit(); }
mdbadp.Update(dataset.Tables[0]); } dataset.Tables[0].Clear();
mdbadp.Dispose(); }
} }
} catch (Exception ex)
{
Response.Write(ex.Message); }
}
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货