---
title: "Update an entire row in Excel using OleDB command"  
description: "Update an entire row in Excel using OleDB command"  
author: "Anonymous User"  
published: 2013-12-09  
updated: 2013-12-10  
canonical: https://www.mindstick.com/forum/1754/update-an-entire-row-in-excel-using-oledb-command  
category: "c#"  
tags: ["c#"]  
reading_time: 1 minute  

---

# Update an entire row in Excel using OleDB command

I [am trying](https://answers.mindstick.com/qa/36834/which-two-programming-languages-should-i-master-in-if-i-am-trying-to-get-into-google-or-facebook) to blankout/clear an entire [excel](https://www.mindstick.com/articles/12937/how-to-import-excel-data-in-sql-server-2014) tab. But nothing seems to work

I tried the following [approach](https://www.mindstick.com/interview/985/what-are-the-approaches-that-you-will-follow-for-making-a-program-very-efficient):

OleDbConnection [connection](https://www.mindstick.com/articles/13012/what-makes-ethernet-connection-better-than-wifi) = new OleDbConnection(connectionString);

OleDbCommand [command](https://www.mindstick.com/blog/178/synchronous-and-asynchronous-command-execution-in-c-sharp-dot-net) = new OleDbCommand("[Select](https://www.mindstick.com/forum/160534/orderby-then-select-vs-select-then-orderby-performance) * FROM [Sheet1$]", connection);

OleDbCommand count = new OleDbCommand("Select count(*) FROM [Sheet1$]", connection);

[DataSet](https://www.mindstick.com/articles/19/dataset) dataset = new DataSet();

OleDbDataAdapter adapter = new OleDbDataAdapter();

adapter.SelectCommand = new OleDbCommand("Select * from [Sheet1$]", connection);

adapter.Fill(dataset);

for (int i = 0; i < dataset.Tables[0].Rows.Count; i++)

{

DataRow dtRow = dataset.Tables[0].Rows[i];

[foreach](https://www.mindstick.com/forum/33870/how-parallel-foreach-works-internally) (DataColumn col in dataset.Tables[0].Columns)

{

if(col.DataType == typeof([string](https://www.mindstick.com/articles/1527/string-split-in-c-sharp)))

dataset.Tables[0].Rows[i][col] = "";

}

}

dataset.Tables[0].AcceptChanges();

adapter.Update(dataset.Tables[0]);

## Replies

### Reply by Anonymous User

Hi Goti,\

If your Excel file has primary key,you can use OleDbCommandBuilder,if not, OleDbDataAdapter or OleDbCommand will be a better way.adapter.you can't directly use Update(dataset.Tables[0]),here is the code:

```
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT
* FROM [Sheet1$]", myConnection);            DataTable dt = new DataTable();           
adapter.Fill(dt);            string updateSQL = string.Format(@"UPDATE [Sheet1$] SET Status =? WHERE ID IN (
SELECT TOP 5 ID FROM [Sheet1$] WHERE Status <>? OR Status IS NULL
)");           
adapter.UpdateCommand = new OleDbCommand(updateSQL, myConnection);           
adapter.UpdateCommand.Parameters.Add("@Status",
OleDbType.Char, 255).SourceColumn = "Status";           
adapter.UpdateCommand.Parameters.Add("@OldStatus", OleDbType.Char,
255, "Status").SourceVersion = DataRowVersion.Original;           
dt.AsEnumerable().Take(5).ToList().ForEach(o =>
o.SetField("Status", @"Imported"));           
dt.AcceptChanges();           
adapter.Update(dt);
```


---

Original Source: https://www.mindstick.com/forum/1754/update-an-entire-row-in-excel-using-oledb-command

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.