How to delete rows in an excel spreadsheet using asp.net vb

I understand how to access the data. I do not know how to query and delete depending on a where clause. In other words I may want to find all rows that have order_id 1245 and delete them. Order_id is in colum A.

How are you accessing the data? A DataTable? Other object?

In my program I am building a spreadsheet with 4 to 10 line items from each order that I have in a SQL2005 table. If one line items fails I want to delete the other line items associated with the bad order.

So what I want to do is record the order number if there is a problem and as soon and them wait to the end of the building process and delte all the bad records out of the spread sheet.

Sorry that was some bad writeing.

In my program I am building a spreadsheet with 4 to 10 line items from each order. My data is  coming from  SQL2005 table. If one line items fails I want to delete the other line items associated with the bad order.

So what I want to do is find the bad order numbers and and delete all the line items.

Ok, I think I've got it:

1) You are creating a single spreadsheet
2) You want to populate an excel spreadsheet with the data from one SQL query
3) A second query does validates the rows from the first query, if the rows in the datatable don't meet some condition you want to delete them from the datatable before generating the spreadsheet.

Does that sound right?

Does your DtaTable have a primarykey set? If not, do this:

Dim ColArray As DataColumn() = Nothing
ColArray.SetValue(ds.Tables("Table")(0), 0)
ds.Tables("Table").PrimaryKey = ColArray

Then you can do a simple find against the datatable:

ds.Tables("Table").Rows.Find("123").Delete

Do all this before creating/saving the spreadsheet

No I will have already built the spreadsheet and then i want to delete the data. I will have a list of orders like this (456,458,452) and I want to delete all rows that have colum a in (456,458,452)

I think with all the comments I've provided you have the tools to solve the problem.

If the spreadsheet is already created, look to my first reply for modifying data from an excel spreadsheet. My last post shows how to tell the datatable which column is the primary key, and how to find/delete records from the datatable.

And, you already know how to create a spreadsheet from a datatable... right?

I may be missing what you are saying.

Lets just say the spreadsheet is already created. If sheet1 was called OrderSheet how would I get the row numbers that have colum A = 12547

If I get the row number then I can use ds.Tables("Table").Rows(0).Delete() to delete right?

My spreadsheet may be layed out like this

orderid  linetotal    product
A            B               C
12547   89.42         4
12547   24.52         6
12547   45.32         3
12547          0         2
12548   54.58         4
12548   48.66         6
12548   10.25         3
12548   45.65         2

I have already identified that order numbber 12547 is did not locate a total for one of the products. So i want to be able to delete all line items that have colum a = 12547

If the spreadsheet is already created, does that mean that it's saved to the file system? Or is it in memory? If it's in memory, can you provide the code that has the instance of the "spreadsheet"?

Please, read my comments about taking an excel spreadsheet and converting it to a datatable, then applying a primary key (I even used the first column as my example). After that you can find the rows in the datatable based on your list of IDs to remove and delete using this:

ds.Tables("Table").Rows.Find([INSERT YOUR ID HERE]).Delete

after that, take the datatable, and save it as an excel spreadsheet....

I suspect that you could modify two (or are there more) queries in your database to do this all in 1 query so that you don't have to do all this within the "spreadsheet"