DataAdapter.Update()不更新数据库

c# database dataadapter

38718 观看

9回复

51 作者的声誉

我敢肯定有一个非常简单的原因,这条线无法正常工作,但是在过去的一周里它一直在逃避,所以我希望其他人会注意到我的错。

我已经在这个项目上工作了几个星期到一个月。我一直在使用旧的DataAdapter,CommandBuiler等,以及在多个数据库应用程序窗体上对1个数据库进行linq to sql编码的混合使用。这种特定形式使用DataAdapter,Dataset和Command Builder从数据库中编辑或删除行。在我切换计算机之前,它一直运行良好。现在,数据集正在更新,但数据库未更新。

这是此表单的完整代码:

private void exitToolStripMenuItem_Click(object sender, EventArgs e)
{
    if (MessageBox.Show("Exit Cook Book?", "Exit?", MessageBoxButtons.OKCancel) == DialogResult.OK)
    {
        Application.Exit();
    }
}

private void goBackToolStripMenuItem_Click(object sender, EventArgs e)
{
    AddRecipe goBack = new AddRecipe();

    Close();
    goBack.Show();
}

private void helpToolStripMenuItem_Click(object sender, EventArgs e)
{
    MessageBox.Show("Scan through the Cook Book to find recipes that you wish to edit or delete.", "Help!");
}

SqlConnection con;
SqlDataAdapter dataAdapt;
DataSet dataRecipe;
SqlCommandBuilder cb;

int MaxRows = 0;
int inc = 0;


private void EditRecipe_Load(object sender, EventArgs e)
{
    con = new SqlConnection();
    dataRecipe = new DataSet();

    con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Recipes.mdf;Integrated Security=True;User Instance=True";

        con.Open();

        //MessageBox.Show("Database Open");

        string sql = "SELECT* From CookBookRecipes";
        dataAdapt = new SqlDataAdapter(sql, con);

        dataAdapt.Fill(dataRecipe, "CookBookRecipes");
        NavigateRecords();
        MaxRows = dataRecipe.Tables["CookBookRecipes"].Rows.Count;

        con.Close();
}


private void NavigateRecords()
{
    DataRow dRow = dataRecipe.Tables["CookBookRecipes"].Rows[inc];

    tbRName.Text = dRow.ItemArray.GetValue(0).ToString();
    listBox1.SelectedItem = dRow.ItemArray.GetValue(1).ToString();
    tbRCreate.Text = dRow.ItemArray.GetValue(2).ToString();
    tbRIngredient.Text = dRow.ItemArray.GetValue(3).ToString();
    tbRPrep.Text = dRow.ItemArray.GetValue(4).ToString();
    tbRCook.Text = dRow.ItemArray.GetValue(5).ToString();
    tbRDirections.Text = dRow.ItemArray.GetValue(6).ToString();
    tbRYield.Text = dRow.ItemArray.GetValue(7).ToString();
    textBox1.Text = dRow.ItemArray.GetValue(8).ToString();
}

private void btnNext_Click(object sender, EventArgs e)
{
    if (inc != MaxRows - 1)
    {
        inc++;
        NavigateRecords();
    }
    else
    {
        MessageBox.Show("That's the last recipe of your Cook Book!", "End");
    }
}

private void btnBack_Click(object sender, EventArgs e)
{
    if (inc > 0)
    {
        inc--;
        NavigateRecords();
    }
    else
    {
        MessageBox.Show("This is the first recipe of your Cook Book!", "Start");
    }
}

private void btnSave_Click(object sender, EventArgs e)
{
    cb = new SqlCommandBuilder(dataAdapt);

    DataRow daRow = dataRecipe.Tables["CookBookRecipes"].Rows[inc];

    daRow[0] = tbRName.Text;
    daRow[1] = listBox1.SelectedItem.ToString();
    daRow[2] = tbRCreate.Text;
    daRow[3] = tbRIngredient.Text;
    daRow[4] = tbRPrep.Text;
    daRow[5] = tbRCook.Text;
    daRow[6] = tbRDirections.Text;
    daRow[7] = tbRYield.Text;
    daRow[8] = textBox1.Text;

    if (MessageBox.Show("You wish to save your updates?", "Save Updates?", MessageBoxButtons.OKCancel) == DialogResult.OK)
    {

        dataAdapt.Update(dataRecipe, "CookBookRecipes");

        MessageBox.Show("Recipe Updated", "Update");
    }
}

private void btnDelete_Click(object sender, EventArgs e)
{
    SqlCommandBuilder cb;
    cb = new SqlCommandBuilder(dataAdapt);

    if (MessageBox.Show("You wish to DELETE this recipe?", "Delete?", MessageBoxButtons.OKCancel) == DialogResult.OK)
    {
        dataRecipe.Tables["CookBookRecipes"].Rows[inc].Delete();
        MaxRows--;
        inc = 0;
        NavigateRecords();

        dataAdapt.Update(dataRecipe, "CookBookRecipes");

        MessageBox.Show("Your Recipe has been Deleted", "Delete");
    }
}

这应该更新表:

dataAdapt.Update(dataRecipe, "CookBookRecipes");

我没有收到任何错误,但数据表不会更新。

预先感谢您的帮助,如果您需要更多信息,请告诉我。

作者: Miffed 的来源 发布者: 2011 年 7 月 26 日

回应 (9)


2

8819 作者的声誉

什么是SqlCommand用于更新什么样子的?我看到了该命令,但没有看到任何SqlText,这就是您所缺少的。

您需要.Update通过.UpdateCommandSqlDataAdapter

该链接提供了很好的细分方法:http : //msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqldataadapter.updatecommand.aspx

作者: Tom Studee 发布者: 26.07.2011 04:33

12

994 作者的声誉

为了更新数据库上的数据,您的SqlDataAdapter需要设置其InsertCommand,UpdateCommand和DeleteCommand属性。您创建的SqlCommandBuilder实例具有以下命令,但是您需要将其设置为SqlDataAdapter。

在其他世界中:

 SqlCommandBuilder cb;
 cb = new SqlCommandBuilder(dataAdapt);

 dataAdapt.Update(dataRecipe, "CookBookRecipes");

你需要

dataAdapt.DeleteCommand = cb.GetDeleteCommand(true);
dataAdapt.UpdateCommand = cb.GetUpdateCommand(true);
dataAdapt.InsertCommand = cb.GetInsertCommand(true);
作者: Daniel Zazula 发布者: 16.10.2012 12:56

0

1 作者的声誉

您可能需要

DataAdapeter.AcceptChanges()
作者: user2418654 发布者: 24.05.2013 06:40

0

559 作者的声誉

我遇到了同样的问题:用一些新行填充了新的数据集,但是更新没有任何反应。我使用了类似的MySqlDataAdapter。

原来,当您需要MySqlCommandBuilder中的InsertCommand时,必须指定添加的行状态。另请参阅:MSDN

作者: Cerveser 发布者: 21.06.2013 07:07

0

1 作者的声誉

//change this line

DataRow daRow = dataRecipe.Tables["CookBookRecipes"].NewRow();

daRow[0] = tbRName.Text;
daRow[1] = listBox1.SelectedItem.ToString();
daRow[2] = tbRCreate.Text;
daRow[3] = tbRIngredient.Text;
daRow[4] = tbRPrep.Text;
daRow[5] = tbRCook.Text;
daRow[6] = tbRDirections.Text;
daRow[7] = tbRYield.Text;
daRow[8] = textBox1.Text;

if (MessageBox.Show("You wish to save your updates?", "Save Updates?", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
//add & change this too
dataRecipe.Tables["CookBookRecipes"].Rows.Add(daRow);
    dataAdapt.Update(dataRecipe, "CookBookRecipes");

    MessageBox.Show("Recipe Updated", "Update");
}

}

作者: Muniba 发布者: 18.02.2015 01:44

0

190 作者的声誉

请尝试以下来源。

private void btnSave_Click(object sender, EventArgs e)
{
    cb = new SqlCommandBuilder(dataAdapt);

    //Old source: DataRow daRow = dataRecipe.Tables["CookBookRecipes"].Rows[inc];

    //Added source code
    DataRow daRow = dataRecipe.Tables["CookBookRecipes"].NewRow();

    //Added source code
    dataRecipe.Tables["CookBookRecipes"].AddRow(daRow);

    daRow.BeginEdit();
    daRow[0] = tbRName.Text;
    daRow[1] = listBox1.SelectedItem.ToString();
    daRow[2] = tbRCreate.Text;
    daRow[3] = tbRIngredient.Text;
    daRow[4] = tbRPrep.Text;
    daRow[5] = tbRCook.Text;
    daRow[6] = tbRDirections.Text;
    daRow[7] = tbRYield.Text;
    daRow[8] = textBox1.Text;
    daRow.EndEdit();

    //Reset state of rows to unchanged
    dataRecipe.Tables["CookBookRecipes"].AcceptChanges();
    //Set modified. The dataAdapt will call update stored procedured 
    //for the row that has Modifed row state. 
    //You can also try SetAdded() method for new row you want to insert
    daRow.SetModified();

    if (MessageBox.Show("You wish to save your updates?", "Save Updates?", MessageBoxButtons.OKCancel) == DialogResult.OK)
    {

        dataAdapt.Update(dataRecipe, "CookBookRecipes");

        MessageBox.Show("Recipe Updated", "Update");
    }
}
作者: culithay 发布者: 01.09.2016 06:46

1

21 作者的声誉

在Update对我有用之前添加AcceptChangesDuringUpdate,例如:

foreach (string tableName in tableNames)
        {             
            da = new SqlDataAdapter("SELECT * FROM " + tableName, cn);
            cb = new SqlCommandBuilder(da); //initialise the update, insert and delete commands of da
            da.AcceptChangesDuringUpdate = true;
            da.Update(myDataSet, tableName);               
        }
作者: dams 发布者: 02.02.2017 11:35

0

31 作者的声誉

我遇到了同样的问题。我的dataadapter.fill有效,但dataadapter.update不起作用。我意识到问题是我的数据库表不包含主键。在修改表以包括具有主键的列之后,dataadapter.fill起作用。希望这对某人有帮助。

作者: Jess 发布者: 15.12.2017 08:39

0

170 作者的声誉

如果使用的是Local-DB,请检查数据库的属性。

在“复制到输出目录”属性中,设置值“如果更新则复制”,您可以使用。

作者: Muhammad Haroon 发布者: 09.09.2019 06:06
32x32