Thanks for taking the time Bonnie. I must say I was surprised when I saw you had replied.
Now that you mention it I recall reading about Fill preserving the state of the connection. Thanks for the code and thanks for the hint about wrapping it in a transaction. I have not used ADO.NET transactions yet, I have been using transactions within a SP, but since I can't use a SP this will be perfect for testing that out.

Again thanks for a great answer, now go have a great time in France!

Einar

>Einar,
>
>OK, I'm supposed to be on vacation, but I haven't quite left the States yet (still have more flights to go), so I'll give you a quick answer. Yes, you can do it. The .Fill() command leaves the connection in the state it found it, so if you open it first, it will remain open after the .Fill().
>
>Try something like this (totally untested and off the top of my head code):
>

>SqlCommand sc = new SqlCommand("("SELECT * FROM Commands", conn);
>conn.Open();
>SqlDataAdapter da = new SqlDataAdapter(sc);
>da.Fill(this.commandsTable);
>.
>.
>.
>sc.CommandText = "DELETE FROM Commands";
>sc.ExecuteNonQuery();
>conn.Close();
>

>
>You also might want to wrap that up in a Transaction ... I'll let you figure that one out. (hint: ... a transaction is a property of the SqlCommand).
>
>No more now! I'm on vacation! <g>
>
>~~Bonnie
>
>
>
>>I am pretty sure what I want to do can not be done, but I have been wrong before <bg>
>>
>>Here is what I want to do:
>>1. Select all records from a table and put them into a DataTable.
>>2. Delete all the records that I selected a moment ago in the table.
>>
>>Note: No I can't use a SP. The table does not have a PK (No I did not design the table). It is not SQL server it is a very old version of Advantage Database Server.
>>
>>Is there any way to leave the connection open after doing an Adapter.Fill to reduce the chance of any records being inserted into the table between the time I select the records and when I delete the records? Or do I have to use a DataReader and fill the DataTable by hand, and then issue a delete before closing the connection?
>>
>>What is a message without some code:
>>

>>OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Commands", conn);
>>da.Fill(this.commandsTable);
>>// somehow issue a delete command on the same connection
>>

>>
>>Any thoughts on the issue are welcome.
>>
>>Thanks,
>>Einar