使用c#未知错误更新ms-access数据库中的记录

Question

我想使用c#从表单中更新ms-access数据库中的记录，但是当我单击update按钮时不会发生任何事情。没有错误也没有例外，只是..。它什么也做不了。我迷惑不解，几乎要发疯了，想弄清楚到底出了什么问题。请帮帮我，我是C#的新手。

我的表单有四个文本框和按钮(更新、添加、删除和清除)和一个列表视图。到目前为止，我得到了“添加”和“清除”好，“删除”还没有工作，我目前在“更新”方面有问题。

listview充当一个表，在我的数据库中显示员工的名字和姓氏时，它不明显地保存员工的ID (宽度为0)。

每次从列表视图中选择记录时，计算机将获取所选行的不可见ID号，并将与该ID相关联的相应数据显示到文本框(名、名、姓、地址、位置)。

namespace WindowsFormsApp1
{


public partial class Form1 : Form
{

    string employeeID; //global variable 
    public void LvRefresh()
    {

        //this method is used to load records from the database
        //to the listview, it is also used to REFRESH the records of the listview.

        listView1.Items.Clear();
        listView1.View = View.Details;
        OleDbConnection con = new OleDbConnection();
        con.ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\\Users\\genesis\\Documents\\Database1.accdb";
        con.Open();

        OleDbCommand cmdEmp = new OleDbCommand("Select ID,FN,LN from Employees", con);
        OleDbDataReader rdrEmp = cmdEmp.ExecuteReader();

        if (rdrEmp.HasRows)
        {

            while (rdrEmp.Read())
            {

                ListViewItem listitem = new ListViewItem(rdrEmp["ID"].ToString());
                listitem.SubItems.Add(rdrEmp["FN"].ToString());
                listitem.SubItems.Add(rdrEmp["LN"].ToString());
                listView1.Items.Add(listitem);

            }

        }
        con.Close();
        rdrEmp.Close();
        cmdEmp.Dispose();
    }



    public Form1()
    {

        InitializeComponent();
        LvRefresh(); //load the ID, FN, MN from the database to the listview
    }

    private void button2_Click(object sender, EventArgs e)
    {

        //clear the textboxes
        textADRS.Clear();
        textFN.Clear();
        textMN.Clear();
        textLN.Clear();
        textPOS.Clear();
    }

    private void buttonSUB_Click(object sender, EventArgs e)
    {

        //add records

        string Adrs = textADRS.Text;
        string Fname = textFN.Text;
        string Mname = textMN.Text;
        string Lname = textLN.Text;
        string Pos = textPOS.Text;

        try
        {

            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\\Users\\genesis\\Documents\\Database1.accdb";
            OleDbCommand cmd = new OleDbCommand("Insert into Employees (FN,MN,LN,[Address],[Position]) Values (@FirstName,@MidName,@LastName,@Address,@Position)", con);
            cmd.Parameters.Add(new OleDbParameter("@FirstName", Fname));
            cmd.Parameters.Add(new OleDbParameter("@MidName", Mname));
            cmd.Parameters.Add(new OleDbParameter("@LastName", Lname));
            cmd.Parameters.Add(new OleDbParameter("@Address", Adrs));
            cmd.Parameters.Add(new OleDbParameter("@Position", Pos));
            con.Open();
            cmd.ExecuteNonQuery();
            MessageBox.Show("Record Submitted", "Nice!");
            con.Close();
            cmd.Dispose();
            LvRefresh(); //refresh listview

        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

    private void listView1_SelectedIndexChanged(object sender, EventArgs e)
    {
        //codes that are responsible for the reflecting of records to the textboxes

        try
        {

            ListViewItem item = listView1.SelectedItems[0];
            employeeID = item.Text; //update the value of global variable employeeID
            textBox1.Text = employeeID;

            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\\Users\\genesis\\Documents\\Database1.accdb";
            con.Open();

            OleDbCommand cmdEmp = new OleDbCommand("Select * from Employees where ID = @empID", con);
            cmdEmp.Parameters.Add("@empID", employeeID);
            OleDbDataReader rdrEmp = cmdEmp.ExecuteReader();

            if (rdrEmp.HasRows)
            {

                while (rdrEmp.Read()) 
                {

                    textFN.Text = rdrEmp["FN"].ToString();
                    textMN.Text = rdrEmp["MN"].ToString();
                    textLN.Text = rdrEmp["LN"].ToString();
                    textADRS.Text = rdrEmp["Address"].ToString();
                    textPOS.Text = rdrEmp["Position"].ToString();

                }

            }
            con.Close();
            rdrEmp.Close();
            cmdEmp.Dispose();
        }

        catch (Exception)
        {

        }

    }

问题

    private void buttonUpdate_Click(object sender, EventArgs e)
    {
        string Adrs = textADRS.Text;
        string Fname = textFN.Text;
        string Mname = textMN.Text;
        string Lname = textLN.Text;
        string Pos = textPOS.Text;

        try
        {

            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\\Users\\genesis\\Documents\\Database1.accdb";
            con.Open();

            OleDbCommand cmdEmp = new OleDbCommand("Update Employees set FN = @FirstName, MN = @MidName, LN = @LastName, [Address] = @Address, [Position] = @Pos where ID = @empID", con);
            cmdEmp.Parameters.Add(new OleDbParameter("@empID", employeeID));
            cmdEmp.Parameters.Add(new OleDbParameter("@FirstName", Fname));
            cmdEmp.Parameters.Add(new OleDbParameter("@MidName", Mname));
            cmdEmp.Parameters.Add(new OleDbParameter("@LastName", Lname));
            cmdEmp.Parameters.Add(new OleDbParameter("@Address", Adrs));
            cmdEmp.Parameters.Add(new OleDbParameter("@Position", Pos));
            cmdEmp.ExecuteNonQuery();

            MessageBox.Show("Record Updated!", "Nice!");
            con.Close();
            cmdEmp.Dispose();
            LvRefresh(); //refresh listview
        }

        catch (Exception error)
        {
            MessageBox.Show(error.Message);
        }

    }
}
}

注意:在更新功能中显示“已更新的记录”MESSAGEBOX，尽管实际上没有更新任何内容。

Answer 1

按照参数在sql语句中的出现顺序添加它们。Access需要按照它们出现的顺序创建它们。

 OleDbCommand cmdEmp = new OleDbCommand("Update Employees set FN = @FirstName, MN = @MidName, LN = @LastName, [Address] = @Address, [Position] = @Pos where ID = @empID", con);
        cmdEmp.Parameters.Add(new OleDbParameter("@FirstName", Fname));
        cmdEmp.Parameters.Add(new OleDbParameter("@MidName", Mname));
        cmdEmp.Parameters.Add(new OleDbParameter("@LastName", Lname));
        cmdEmp.Parameters.Add(new OleDbParameter("@Address", Adrs));
        cmdEmp.Parameters.Add(new OleDbParameter("@Position", Pos));
        cmdEmp.Parameters.Add(new OleDbParameter("@empID", employeeID));
       cmdEmp.ExecuteNonQuery();