Tuesday, July 27, 2004

Problems with updates using OleDbConnection in VS.Net when dates are present.

In my last post I forgot to mention, if you're having problems updating something and you're connecting to Access or another database using OleDb, do yourself a favor and edit the autogenerated code that handles record creation and deletes.

In your oleDbUpdateCommand section, you'll find that date fields are handled with the attribute

System.Data.OleDb.OleDbType.DBDate
 
Instead switch the "DBDate" to "Date" and the update will actually function, like

System.Data.OleDb.OleDbType.Date
 
It is safe to do a global search and replace on this, as the other areas that use DBDate without problems aren't affected by the change.

This reminds me of all the trouble you have to go through with date delimiters when you're working with old ADO in Access and SQL Server, that is, using "#" or " ' " respectively.
Somtimes, they lie to you. Microsoft has plastered all over it's documentation that you can just call "Update" on a dataset, and bingo, all relevant changes will be posted back to the datastore in a bandwidth conserving manner.

An example...

private void btnSave_Click(object sender, System.EventArgs e)
{
     oleDbDataAdapter1.Update(dataSet1);
}


How tidy this would be! To bad it doesn't work if you're doing something of any value with the dataset. For example, you might be using databinding on a form. Not exactly stretching the limits of the IDE here. The thing is, the CurrencyManager, which handles the task of binding datasets to form elements, is a bit sloppy. You get intermittent results if you just bind a form element to a dataset and then call update. The form does not consistently "let go" of the bound value, so when you call update, it may be the case that the dataset does not appear to have been altered, so you're hosed.

Here is an example of a properly built function, replacing the one line piece of crap above...

private void btnSave_Click(object sender, System.EventArgs e)
{
     this.BindingContext[dataSet1, "TableName"].EndCurrentEdit();
     try
     {
        if(dataSet1.GetChanges() != null)
        {
           this.oleDbDataAdapter1.Update(this.dataSet1.GetChanges());
           this.dataSet1.AcceptChanges();
        }
     }
     catch(Exception err)
     {
        MessageBox.Show(err.ToString());
     }
}


Not exactly the same, but it works.