我找遍了所有的地方,但我似乎不能让这个工作。
我正在尝试从DataGridView更新Access数据库。将数据库加载到网格可以正常工作。我使用了本网站中描述的说明。
然而,要根据对DataGridView所做的更改更新数据库,我使用命令dataAdapter.Update(datatable);
,但根据位置(在此代码旁边),代码运行但数据库不更新。如果我把它放在一个按钮中,它会抛出一个异常" insert into statement语法错误"。
其他问题:我应该关闭连接变量后加载DataGridView?如果是,我应该重新打开它来执行更新,然后再关闭它吗?这是怎么做到的?
任何帮助都将非常感激。编辑:按照蒂姆的要求放全班。
public partial class Pantalla_Proyecto : Form
{
private Inicio Inicio;
private List<string> Logueado;
private OleDbConnection conn;
private OleDbDataAdapter Adaptador;
private DataTable Tabla;
private BindingSource Bsource;
private OleDbCommandBuilder Builder;
public Pantalla_Proyecto(Inicio Inicio, List<string> Logueado)
{
this.Inicio =Inicio;
this.Logueado = Logueado;
InitializeComponent();
}
private void Pantalla_Proyecto_Load(object sender, EventArgs e)
{
}
private void importarCDPToolStripMenuItem_Click(object sender, EventArgs e)
{
Importar_CDP Importar_CDP = new Importar_CDP();
Importar_CDP.Show();
}
private void importarListasToolStripMenuItem_Click(object sender, EventArgs e)
{
WindowsFormsApplication1.Formularios.Lista_Lazos.Importar_Listas1 Importar_Listas = new WindowsFormsApplication1.Formularios.Lista_Lazos.Importar_Listas1();
Importar_Listas.Show();
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
}
private void flowLayoutPanel2_Paint(object sender, PaintEventArgs e)
{
}
private void menuStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
{
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
string sql = "Select * From ["+TABLE (THIS IS A STRING I GET FROM PREVIOUS FORM)+"]";
conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=proyectos" + Location(this is a string i get on the previous form) + ".mdb;User Id=admin;Password=;");
conn.Open();
// Extraemos info de mi database y la meto en un datatable
Adaptador = new OleDbDataAdapter(sql, conn);
Builder = new OleDbCommandBuilder(Adaptador);
Tabla = new DataTable();
Adaptador.Fill(Tabla);
// LLENO EL DATA GRID VIEW
Bsource = new BindingSource();
Bsource.DataSource = Tabla;
dataGridView1.DataSource = Bsource;
dataGridView1.Dock = DockStyle.Fill;
Adaptador.Update(Tabla);//if i put it here nothing happens
conn.Close();
}
private void dataGridView1_Validating(object sender, CancelEventArgs e)
{
}
private void button1_Click_1(object sender, EventArgs e)
{
conn.Open();
Adaptador.Update(Tabla);//IF i put it here i get an exception
conn.Close();
}
按照OP的要求,为了详细说明using语句,让我们使用comboBox1_SelectedIndexChanged方法:
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
string sql = "Select * From ["+TABLE (THIS IS A STRING I GET FROM PREVIOUS FORM)+"]";
using (conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=proyectos" + Location(this is a string i get on the previous form) + ".mdb;User Id=admin;Password=;"))
{
conn.Open();
// Extraemos info de mi database y la meto en un datatable
Adaptador = new OleDbDataAdapter(sql, conn);
// Remove the OleDbCommandBuilder
Tabla = new DataTable();
Adaptador.Fill(Tabla);
// LLENO EL DATA GRID VIEW
Bsource = new BindingSource();
Bsource.DataSource = Tabla;
dataGridView1.DataSource = Bsource;
dataGridView1.Dock = DockStyle.Fill;
// ** NOTE:
// At this point, there's nothing to update - all that
// has happened is that you've bound the DataTable
// to the DataGridView.
Adaptador.Update(Tabla);//if i put it here nothing happens
} // Your connection will be closed at this point when the using
// statement goes out of scope.
}
MSDN说"当你创建一个新的OleDbCommandBuilder实例时,任何与这个OleDbDataAdapter关联的现有OleDbCommandBuilder都会被释放。"
但是,如果您想完全摆脱OleDbCommandBuilder,您可以这样做(我更新了上面的代码来做到这一点)。既然你认为你有特殊字符的问题,那么这样做可能是值得的。
类似这样的东西应该会有所帮助-您必须根据表列的内容修改更新命令:
private void button1_Click_1(object sender, EventArgs e)
{
conn.Open();
Adaptador.UpdateCommand = "<Your SQL here>" // I.e., UPDATE [TABLE] SET....
try
{
Adaptador.Update((DataTable)Bsource.DataSource);
}
catch (Exception ex)
{
// Do something with the exception
}
}
这段代码是MSDN的一个稍微修改的版本:
如何:将数据绑定到Windows窗体DataGridView控件
请注意,它们在示例中使用了SqlDbCommandBuilder,但总体原则保持不变。
根据David-W-Fenton的评论,你可能想在Form_Load事件中初始化连接和数据适配器,然后在Form_Closing事件中关闭连接。
首先要检查的是实际要更新的Access文件的运行时路径。
如果您使用的是VS.NET, MDB文件是您项目的一部分,并且MDB文件上的"Copy Local"选项设置为true,那么可能只是每次运行程序时,您都会覆盖上次执行的更新。
我只是提一下,因为我已经被这个咬了;-)
欢呼