代码执行时没有错误，但无法使用C#更新Access数据 [英] code executes without error but unable to update Access data using C#

问题描述

我有以下代码可以成功执行，但不会更新数据库中的数据.请考虑mode ="edit"为true的部分，因为INSERT可以正常工作.

I have the following code which executes successfully but does not update data in database. Please consider the part where mode="edit" is true because INSERT works fine.

我有不同的模式来指示是更新还是插入.插入可以在所有数据库字段上正常工作，但是更新不起作用:它不会出现任何错误(执行成功)，但是数据库未更新.

I have different modes to indicate whether to update or insert. Insert works fine with all database fields but update does not work: it does not give any errors (execute is successful) but database is not updated.

double id; // please consider only edit part
if (mode != "edit")
    id = id_increment();
else
    id = Convert.ToInt64(view_gv.CurrentRow.Cells["id"].Value.ToString()); //find currrent row successfully
OleDbCommand com = new OleDbCommand();
con.ConnectionString = con_string;
con.Open();
com.Connection = con;
if (mode != "edit")
{
    com.CommandText = "INSERT INTO [TBLPERSONMST] ([ID],[name],[address],[city],[o_phone],[r_phone],[mo_number],[email],[dob],[ann_date],[bg],[sex],[group]) VALUES (@id,@name,@add,@city,@ophone,@rphone,@mophone,@email,@dob,@ann_date,@bg,@sex,@group)";

}
else
{
    com.CommandText = "UPDATE [TBLPERSONMST] SET [name]=@name,[address]=@add,[city]=@city,[o_phone]=@ophone,[r_phone]=@rphone,[mo_number]=@mophone,[email]=@email,[dob]=@dob,[ann_date]=@ann_date,[bg]=@bg,[sex]=@sex,[group]=@group WHERE [ID]=@id";

}

com.Parameters.Add(new OleDbParameter("@id", id));

com.Parameters.Add(new OleDbParameter("@name", name_txt.Text));
com.Parameters.Add(new OleDbParameter("@add", add_txt.Text));
com.Parameters.Add(new OleDbParameter("@city", city_txt.Text));
if (ophone_txt.Text != "")
    com.Parameters.Add(new OleDbParameter("@ophone", Convert.ToDouble(ophone_txt.Text)));
else
    com.Parameters.Add(new OleDbParameter("@ophone", "0"));
if (rphone_txt.Text != "")
    com.Parameters.Add(new OleDbParameter("@rphone", Convert.ToDouble(rphone_txt.Text)));
else
    com.Parameters.Add(new OleDbParameter("@rphone", "0"));
if (mo_txt.Text != "")
    com.Parameters.Add(new OleDbParameter("@mophone", Convert.ToDouble(mo_txt.Text)));
else
    com.Parameters.Add(new OleDbParameter("@mophone", "0"));
com.Parameters.Add(new OleDbParameter("@email", email_txt.Text));
com.Parameters.Add(new OleDbParameter("@dob", dob_date.Value.ToShortDateString()));
com.Parameters.Add(new OleDbParameter("@ann_date", anni_date.Value.ToShortDateString()));
com.Parameters.Add(new OleDbParameter("@bg", bg_cmbox.SelectedItem));
com.Parameters.Add(new OleDbParameter("@sex", gen_cmbox.SelectedItem));

com.Parameters.Add(new OleDbParameter("@group", grp_cmbox.SelectedItem));

int j=com.ExecuteNonQuery();

推荐答案

您的编辑"问题源于以下事实:ACE/Jet OLEDB中的参数化查询忽略，因此参数必须为按照 exact 的顺序添加到OleDbCommand对象中，以便它们出现在CommandText中.您的INSERT和UPDATE命令以不同的顺序使用参数，因此您必须以不同的顺序处理com.Parameters.Add语句，具体取决于您正在执行INSERT还是UPDATE(编辑").

Your "edit" problem stems from the fact that parameterized queries in ACE/Jet OLEDB ignore parameter names and therefore the parameters must be added to the OleDbCommand object in the exact order that they appear in the CommandText. Your INSERT and UPDATE commands use the parameters in a different order, so you must process the com.Parameters.Add statements in a different order depending on whether you are doing an INSERT or an UPDATE ("edit").

这篇关于代码执行时没有错误，但无法使用C#更新Access数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！