Thursday, 31 May 2012

Delete a Record / Row from Excel with ADO.NET

 

In a WPF project I’m working on, I’m using an Excel document as a data source, and utilizing ADO.NET to access and manage my data.

 

The benefits of such is I can create DataSet’s that represent my data as well as outline key fields (that really don’t exist in Excel) and relationships (that technically don’t exist in excel… there are calculated columns – but I would advice against using them especially if you want to use ADO.NET) and generate calculated columns during runtime and create custom adapters to maintain my SQL outside of my code.

 

But one super negative is:

YOU CANNOT DELETE A ROW / RECORD IN EXCEL WITH ADO

Microsoft explains that excel has a different meaning of a row and a record, and has some sort of complicated excuse of why they give you this error when you try:

 

Deleting data in a linked table is not supported by this ISAM. 

 

Additionally, and perhaps unrelated. I found even the DataRow.Delete() method acts up with null reference errors when performing such activity during run time, even though I physically didn’t attempt to remove the data. This I can only assume is related as well – however I cannot confirm this.

The Blanking Key Field Workaround

Without the ability to delete a row, a usable workaround would be to use an UPDATE statement to blank out the Key Field and ensuring that the DataAdapter filters out blank key fields when SELECTing the data.

 

Here’s a Walk Through:

 

  1. Right Click on the Table Adapter in the Dataset Designer, and select it’s properties. There Change the “CommandText” of the “SelectCommand”

    DeleteRow1
  2. At the end of the SQL Statement of the SelectCommand add a WHERE clause:

    WHERE (NOT (Key_Field = ''))

    The key field being the field you will use to identify Rows during Updates and Inserts. Be sure to ensure your “UpdateCommand” and “InsertCommand” reflect identification or records via a manually determined Key.

    DeleteRow2
  3. When clicking OK a Dialog window will appear asking if you want to regenerate your Update commands. Say “No” as you should manually rewrite Update commands if needed.

    DeleteRow3
  4. As well, Visual Studio will ask you if want to update alternative Queries you may have entered in the Data Adapter as well. Hit “No” as well, as the filter itself is simply a work around. However, do manually review your other SQL statements for accuracy.

    DeleteRow4
  5. Finally, change or add a “DeleteCommand” removing the SQL from a typical DELETE statement entirely, and changing the Command type to Update

    DeleteRow5
  6. Change “DeleteCommand” to reflect something similar to: 

    UPDATE TABLENAME
    SET    Key_Field = ''
    WHERE (Key_Field = ?)


    DeleteRow6

Now, when you want to delete a field in your Excel through ADO, when you tell the Adapter to delete a record, the result will be a blank cell under the key column you specified, and your ADO layer will never show such record due to your filtering it out upon selection. Thus, it’s an imperfect workaround, but it’s one that works.

 

Resources:

  1. http://support.microsoft.com/kb/257819
  2. http://www.xtremevbtalk.com/showthread.php?p=969632

Please feel free to comment or leave your questions, Sincerely,

Twitter

Page Hits