Using OleDbConnection to populate DataGridView

Using OleDbConnection to populate DataGridView

• WoFe

  So, here is my grid init code.  The grid seems to display fine:
              // make the connection to the database
              _db = new OleDbConnection(strBuilder.ConnectionString);
              _db.Open();
  
              // setup the internal data set
              _dataSet = new DataSet();
  
              // populate the users table
              _dataAdapter = new OleDbDataAdapter("SELECT * FROM Users;", _db);
              _dataAdapter.Fill(_dataSet, "Users");
  
              // populate the time trials table
              _dataAdapter = new OleDbDataAdapter("SELECT * FROM TimeTrials;", _db);
              _dataAdapter.Fill(_dataSet, "TimeTrials");
  
              // close the connection
              _db.Close();
  
  And here is a function I use to update the data when the dialog is closing:
              _db.Open();
              _dataAdapter.Update(_dataSet);
              _dataSet.AcceptChanges();
              _db.Close();
  
  There is a problem with the second functoin... I'm getting an exception about an invalid table mapping.
  
  Thanks for all your help so far... I feel so close!
  
  
  

• Junar

  Okay, I think I have it... I'm not sure why it works, but it seems to.
  
  After creating the DataAdapter, I create a OleDbCommandBuilder on that adapter. Then, after calling Fill() on the adapter, I call GetUpdateCommand() on the command builder. During the save part, I just call dataSet.GetChanges, then use the adapter to Update using the changes (with the correct table name). That seems to make thing work.
  
  
  

• anxvariety

  take out the dataSet.AcceptChanges() and see what happens.

  have you set some mapping before hand or in some area of code with the dataAdapter make sure you clear the table mapping anyway (if you don't need them) by:

  _dataAdapter.TableMappings.Clear();

  
  
  

• nrf

  I'm still getting this exception:
  System.InvalidOperationExcception: "Update unable to find TableMapping['Table'] or DataTable 'Table'."
  
  I haven't changed the data set (knowingly) between these two function calls. If it is easier, I could send my full project (it is pretty small).
  
  --jah
  
  
  

• Corgalore

  sure send it (email in profile) but I will also post the findings etc... here for the benefit of yourself and others
  
  

• Moonshadow

  yes for both, its the same adapter you used to "Fill" the dataset
  
  

• steve_thomas

  is the textboxes bound to the grid (in other words, is the textbox control in the grid )

  if its seperate, as I am thinking and reading your reply, the values will not be updated in the dataset, you would have to update this in some way.

  If it was in a grid, it should automatically set the updated values in the dataset, settings its state to something like "to be updated" so when you call the data Adapters update() method, it will look at the rowstates to see which rows needing to be updated to the datasource (SQL) and execute the appropriate command for you.

  
  
  

• MuscleHead

  Sent...
  
  Thanks again...
  
  
  

• Olle SW

  The only problem that I had with this was trying to get to the data in the columns, just not showing them. I ended up with this:
  
  // hide all columns & restore the ones we want to see
  DataGridViewColumnCollection columns = userGrid.Columns;
  foreach (DataGridViewColumn col in columns) {
  col.Visible = false;
  }
  columns["firstNameCol"].Visible = true;
  columns["lastNameCol"].Visible = true;
  
  Now, I'm trying to commit the changes back to the database. I call AcceptChanges() on my data set, but it does not get saved. Any help here Thanks!
  
  
  

• GILLT

  Okay, so that proved to be easy :) I just used the DataSource attribute of the DataGrid and set it to the table I wanted to display.
  
  Now, is it possible to restrict the columns that are displayed on the grid Currently, it is displaying all columns from my table in the grid. I only want to show a couple of them...
  
  
  

• eldiener

  The text boxes are bound to the data set via the DataBindings property on them.
  
  How do I access the adapter for the data grid Is it the same adapter that I used to execute the Fill command
  
  If I understand correctly, I need to update the data adapter, which will cause the data set to receive the updates and then AcceptChanges will update the database, correct
  
  
  

• meiraz

  you should use the Update() method on the Sql/OleDbDataAdapter - this will do it for you.

  Also how do you know if the changes are saved or not you need to refill the dataset to load the data back and re-bind it, it does not "auto update" the datagridview.

  
  
  

• zabrucewayne

  I would suggest you filter your query to show the specific columns you would like to show :-)

   

  or you could remove it from your dataGridView Columns:

   

  this.theDataGridView.Columns.Remove(ColumnName);

  
  
  

• Ro0ke

  Hmmm... This is a good question.
  
  I have a grid and a set of text boxes that are bound to my table. The grid is read only. As I make changes in the text boxes. I'm able to move away and go back to a record, and my changes are there. I assumed it was being updated in the DataSet.
  
  Is that correct
  
  
  
• Using OleDbConnection to populate DataGridView