Question

C#,如何将修改后的数据保存到数据库

数据表Recover有5列，分别为Type,PayType,Pay,Day,Number。程序有2个下拉列表，一个选择Type（comboBox2），一个选择日期Day(comboBox2),一个“查看”的bo、utton1，一个“保存”的button2。根据选择的Day和Type，在下面的3个对应textbox中显示对应的数据，并可以进行修改，修改完毕按“保存”，把数据写入到数据库。程序运行时，能顺利读取数据，但修改按“保存”后，Button2_Click事件的cmd.ExecuteNonQuery()弹出消息框,显示“update语句的语法错误”。请教各位高手，该如何修改呢？
代码如下：
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=kcDB.mdb");
private void button1_Click(object sender, EventArgs e)
{
string sql = "select * from Recover where Day='" + comboBox1.Text + "'and Type='"+comboBox2.Text+"'";
OleDbCommand cmd = new OleDbCommand(sql, conn);
conn.Open();
OleDbDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
textBox1.Text = dr["PayType"].ToString();
textBox2.Text = dr["Pay"].ToString();
textBox3.Text = dr["Number"].ToString();
}
else
{
MessageBox.Show("找不到相关记录！");
}
dr.Close();
conn.Close();
}
private void button2_Click(object sender, EventArgs e)
{
OleDbCommand cmd = new OleDbCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show("修改成功");
}

Answer 1

namespace DategridviewToSQL
{
public partial class Form1 : Form
{
private DataTable DT = new DataTable();
private SqlDataAdapter SDA = new SqlDataAdapter();
private Boolean isUpdate = false;

public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
load();
}

private void load()
{
SqlConnection conn = new SqlConnection(@"server = (local)\SQL2005;Integrated Security = true;" + "DataBase = test1");
SqlCommand SCD = new SqlCommand("select * from aaa ", conn);
SDA.SelectCommand = SCD;
SDA.Fill(DT);
dataGridView1.DataSource = DT;
}

private void button1_Click(object sender, EventArgs e)
{

if (isUpdate)
{
try
{
SqlCommandBuilder SCB = new SqlCommandBuilder(SDA);
SDA.Update(DT);

isUpdate = false;
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
return;
}
MessageBox.Show("更新成功! ");
}
else
{
MessageBox.Show("没有更新内容! ");
}

for (int i = 0; i < DT.Rows.Count; i++)
for (int j = 0; j < DT.Columns.Count; j++ )
{
dataGridView1[j, i].Style.BackColor = Color.White;
}

}

private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
isUpdate = true;

dataGridView1[e.ColumnIndex,e.RowIndex].Style.BackColor = Color.Blue;

}
自己写的一个小实例，连接一个数据库，取了一个表显示，里面的链接语句可以根据你自己的数据库修改。试试看

Answer 2

你的Update语句都没写 。。。。

追问

update语句如下： string sql = "update Recover set Type='" + comboBox2.Text + "',PayType='" + textBox1.Text + "',Pay='" + textBox2.Text + "',Number='" + textBox3 + "',where Day='" + comboBox1.Text + "'";
复制的时候出错了。请问是哪里有问题呢？

追答

If(Page.IsPostBack())
{
string sql = "select * from Recover where Day='" + comboBox1.Text + "'and Type='"+comboBox2.Text+"'";
OleDbCommand cmd = new OleDbCommand(sql, conn);
conn.Open();
OleDbDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
textBox1.Text = dr["PayType"].ToString();
textBox2.Text = dr["Pay"].ToString();
textBox3.Text = dr["Number"].ToString();
}
else
{
MessageBox.Show("找不到相关记录！");
}
dr.Close();
conn.Close();

}