无法获得如何更新数据库 ms-access 中选定行的算法

debugcn 发表于 Dev

阿迪尔·阿利耶夫

酒店有一个程序。这是“到达”客人的代码：

 try
                {

                    string dtparrival = this.dateTimePicker4.Text.ToString();
                    string statusarrival = "Arrival";

                    string strSql = "SELECT * FROM guestreg WHERE g_status = '" + statusarrival + "' AND g_ad = '" + dtparrival + "'";


                    OleDbCommand cmd = new OleDbCommand(strSql, connection);
                    connection.Open();
                    cmd.CommandType = CommandType.Text;
                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                    DataTable scores = new DataTable();
                    da.Fill(scores);
                    arrivaldgv.DataSource = scores;
                    /**************************************************************************/
                    this.arrivaldgv.Columns[0].Visible = false;
                    this.arrivaldgv.Columns["g_totalrate"].Visible = false;
                    this.arrivaldgv.Columns["u_added"].Visible = false;
                    this.arrivaldgv.Columns["u_timeadded"].Visible = false;
                    /**************************************************************************/
                    this.arrivaldgv.Columns["g_name"].HeaderText = "Name";  //1
                    this.arrivaldgv.Columns["g_surname"].HeaderText = "Surname"; //2
                    this.arrivaldgv.Columns["g_company"].HeaderText = "Company"; //3
                    this.arrivaldgv.Columns["g_ad"].HeaderText = "Arrival Day"; //4
                    this.arrivaldgv.Columns["g_dd"].HeaderText = "Departure Day"; //5
                    this.arrivaldgv.Columns["g_amountofdays"].HeaderText = "Amount of Days"; //6
                    this.arrivaldgv.Columns["g_na"].HeaderText = "Number of Adults"; //7
                    this.arrivaldgv.Columns["g_amountofdays"].HeaderText = "Amount of Days"; //8
                    this.arrivaldgv.Columns["g_room"].HeaderText = "Room Number"; //9
                    this.arrivaldgv.Columns["g_rate"].HeaderText = "Rate"; //10
                    this.arrivaldgv.Columns["g_totalrate"].HeaderText = "Total Rate"; //11
                    this.arrivaldgv.Columns["g_info"].HeaderText = "Information"; //12
                    this.arrivaldgv.Columns["u_added"].HeaderText = "User"; //13
                    this.arrivaldgv.Columns["u_timeadded"].HeaderText = "Time"; //14
                    this.arrivaldgv.Columns["g_status"].HeaderText = "Status"; //15
                    /**************************************************************************/
                    connection.Close();
                }
                catch
                {
                    MessageBox.Show("Please, try again.");
                }

我的应用程序中有 datagrid 视图（这是屏幕截图）Screenshot

有“入住”按钮，我想将数据库中“g_status”的值从“到达”更改为“内部”。问题在于搜索所选行的SQL 命令和算法。这是代码，但它不起作用：（更新）

 private void checkinbtn_Click(object sender, EventArgs e)
    {
        int selectedrowindex = arrivaldgv.SelectedCells[0].RowIndex;
        DataGridViewRow selectedRow = arrivaldgv.Rows[selectedrowindex];
        string value = Convert.ToString(selectedRow.Cells["g_status"].Value);

        OleDbDataAdapter adapter;
        OleDbCommandBuilder cmdBuilder;
        string checkin = "In House";
        DataSet ds = new DataSet();
        string sql = "UPDATE guestreg SET g_status = '" + checkin + "'" + " where ID = '" + value + "';";
        Int32 i;
        try
        {
            connection.Open();
            adapter = new OleDbDataAdapter(sql, connection);
            cmdBuilder = new OleDbCommandBuilder(adapter);
            adapter.Fill(ds);

            for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
            {
                ds.Tables[0].Rows[i].ItemArray[2] = checkin;
            }

            adapter.Update(ds.Tables[0]);
            connection.Close();
            MessageBox.Show("Data updated ! ");
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error:     " + ex);
        }
    }

这是数据库结构：截图

提前致谢。

凯尔帕特尔

是否有任何列包含数据的唯一 ID（数据库中的主键）？

如果是，您可以使用它：

int selectedrowindex = arrivaldgv.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = arrivaldgv.Rows[selectedrowindex];  
string value = Convert.ToString(selectedRow.Cells["corresponding column name"].Value);   //maybe the first invisible column?

string sql = "UPDATE guestreg SET g_status = '"+checkin+"'" + " where *name of primary key* = '" + value + "';";

此外，这是执行 sql 命令的一种更简单的方法：

private void checkinbtn_Click(object sender, EventArgs e)
{
    try
    {
        var cmd = new OleDbCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "UPDATE guestreg SET g_status='In House' where *name of primary key*=@id";
        cmd.Parameters.AddWithValue("@id", value);
        cmd.Connection = connection;
        connection.Open();
        cmd.ExecuteNonQuery();
        {
            MessageBox.Show("Update Success!");
            connection.Close();
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error:     " + ex);
    }
}

让我知道我是否误解了什么，或者语法是否有错误，因为我无法测试它。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。