Help with updating access database using Update(dataTable)

Question

I have looked all over the place but I cant seem to get this to work.

I am trying to update an Access database from a DataGridView. The loading of the database to the grid works fine. I used the instructions described in this site.

However, to update the database based on changes made to the DataGridView, I used the command dataAdapter.Update(datatable);, but depending on the placement (next to this code) the code runs but the database does not update. If I put it in a button it throws an exception "syntax error in insert into statement".

Other question: Should I close the connection variable after loading the DataGridView? If so, should I reopen it to perform the update and then reclose it? How does that work?.

Any help would be greatly appreciated. EDIT: Putted whole class as Tim asked.

 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();
    }

Can you show more of the code? Specifically where the errors are happening. As far as closing the connection, yes, you should close it every time you're done. It's best to wrap the connection in a using statement, which will take care of closing it for you automatically. — Tim
– Tim, Commented Aug 21, 2011 at 23:12
can you elaborate on that using statement?, i'm fairly new to programming — Joaquin
– Joaquin, Commented Aug 21, 2011 at 23:29
I added an answer that covers the using statement and a couple other things. — Tim
– Tim, Commented Aug 22, 2011 at 0:02
With a file-based database engine like Jet/ACE, it's actually more efficient to keep the connection open and re-use it, as there's a lot of overhead involved in opening the connection (mostly concerned with creating the record-locking file). — David-W-Fenton
– David-W-Fenton, Commented Aug 22, 2011 at 1:00
@David-W-Fenton - I wasn't aware of that. Good inofrmation to know. Thanks. — Tim
– Tim, Commented Aug 22, 2011 at 2:42

Tim · Accepted Answer · 2011-08-23 02:51:50Z

1

To elaborate, as per OP's request, on the using statement, let's take the comboBox1_SelectedIndexChanged method:

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.
}

UPDATE

MSDN says "When you create a new instance OleDbCommandBuilder, any existing OleDbCommandBuilder associated with this OleDbDataAdapter is released."

However, if you want to get away from the OleDbCommandBuilder completely, you can do so (I updated my code above to do just that). Since you believe you're having issues with special characters, it's probably worthwhile to do it this way.

Something like this should help - you'll have to modify the update command based on what your table columns are:

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
    }
}

This code is a slightly modified version of what MSDN has:

How to: Bind Data to the Windows Forms DataGridView Control

Note that they use a SqlDbCommandBuilder in the example, but the overall principle remains the same.

Based on David-W-Fenton's comment, you might want to do your connection and dataadapter initializations in the Form_Load event, and then close the connection on Form_Closing event.

edited Aug 23, 2011 at 2:51

answered Aug 22, 2011 at 0:01

Tim

28.6k8 gold badges65 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Joaquin Over a year ago

hmmmm What is that? :S, maybe that's it, i just followed the website's instructions it said that all sql instructions were going to be automatically generated by the Oldbcommand Builder, what should i write in the Updatecommand?, a sql instruction?

Tim Over a year ago

@Joaquin - I think (never done it this way myself, just going off the MSDN documentation) you have to set the Select command on the OleDbAdapter to auto-generate the remaining commands. I've edited my answer to reflect that.

Joaquin Over a year ago

i've located the error, my commandbuilder is not making my insert, delete or update commands!. on my dataadapter they are all null when the exception occurs, so naturally there is an exception when i try to insert or update... The question now is Why are they null? i did what u said tim oledbcommand comando = new oledbcommand (sql, conn); Adaptador.selectcommand = comando; but i get the same result.... It's worth noting that even before doing this my select command was set as the one in the "sql" string, so the select is not the problem.. what can it be?

Joaquin Over a year ago

OK, so i think i know what's going on: Browsing trhough msdn i found an article called "Weaning Developers from the CommandBuilder", and it says "If your SelectCommand SQL columns contain special characters, such spaces, periods, quotes or non-alphanumeric characters, you can't use the CommandBuilder.".... The thing is that access sql commands needs the select in this form: "Select * From [Table]", if i dont put the brakets the instruction does not work!, but if i put the brackets the commandbuilder cannot make the commands.... Any ideas??

Tim Over a year ago

@Joaquin - I'll post some later this evening.

|

Luc Morin · Accepted Answer · 2011-08-21 23:27:22Z

0

First thing to check is the runtime path of the Access file that you're actually updating.

If you're using VS.NET, that the MDB file is part of your project, and that the "Copy Local" option is set to true on the MDB file, then it could just be that every time you run your program you overwrite the updates from the last execution.

I'm just mentioning because I've been bitten by this one ;-)

Cheers

answered Aug 21, 2011 at 23:27

Luc Morin

5,39024 silver badges40 bronze badges

Collectives™ on Stack Overflow

Help with updating access database using Update(dataTable)

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related