Re: How do I delete all rows in a SQL Server table when my ADO DataSet has more than one table
- From: "Val Mazur \(MVP\)" <group51a@xxxxxxxxxxx>
- Date: Wed, 8 Jun 2005 23:40:59 -0400
Brad,
Why are you trying to delete the row through the DataSet? You do not need to
load all the data to the client side to delete all the rows. Just use
ExecuteNoQuery method of the SqlCommand to execute DELETE SQL statements and
it will work much faster
--
Val Mazur
Microsoft MVP
http://xport.mvps.org
"Brad Marshall" <bradley.marshall@xxxxxxxxxxx> wrote in message
news:uo$mtHJbFHA.2900@xxxxxxxxxxxxxxxxxxxxxxx
>I want to delete all rows in a SQL Server table at the start of a
>procedure. I have a SqlDataAdapter with a DataSet that has two tables. I
>have the following code to delete all records in both tables. These tables
>are populated from the last execution of the procedure. I fail with a
>concurrency violation on the first delete, but the records are deleted from
>the SQL table! If I execute again after the exception terminated the
>procedure, I succeed on the first Update but fail again on the second
>update. Because I am updating more than one table in the dataset I can't
>use the command builder; rather, I must set a DeleteCommand for the
>adapter. What am I doing wrong?
>
> Dim myConnectionString As String = "Initial Catalog=myDataBase;Data
> Source=localhost;Integrated Security=SSPI;Server=myServer"
> Dim myConnection As New SqlConnection(myConnectionString)
> Dim OmsAdapter As New SqlDataAdapter
> Dim dsMyProject As DataSet
> Dim LogDeleteCommand As New SqlCommand("DELETE TransLog", myConnection)
> Dim ResultDeleteCommand As New SqlCommand("DELETE Result", myConnection)
> Dim ResultRow As DataRow
> Dim LogRow As DataRow
> Dim myQuery As String
> Dim myCommand As New SqlCommand()
> dsMyProject = New DataSet("myProject")
>
> ' Clear out the Result table
>
> myQuery = "SELECT * from Result"
> myCommand.CommandText = myQuery
> OmsAdapter.SelectCommand = myCommand
> OmsAdapter.Fill(dsMyProject, "Result")
>
> For Each ResultRow In dsMyProject.Tables("Result").Rows
> ResultRow.Delete()
> Next
>
> OmsAdapter.DeleteCommand = ResultDeleteCommand
> OmsAdapter.Update(dsMyProject, "Result")
>
> ' Clear out the OMS Results table
>
> myQuery = "SELECT * from TransLog"
> myCommand.CommandText = myQuery
> OmsAdapter.SelectCommand = myCommand
> OmsAdapter.Fill(dsMyProject, "TransLog")
>
> For Each LogRow In dsMyProject.Tables("TransLog").Rows
> LogRow.Delete()
> Next
>
> OmsAdapter.DeleteCommand = LogDeleteCommand
> OmsAdapter.Update(dsMyProject, "TransLog")
>
.
- Follow-Ups:
- References:
- Prev by Date: How do I delete all rows in a SQL Server table when my ADO DataSet has more than one table
- Next by Date: Re: Multiple-step OLE DB operation generated errors.
- Previous by thread: How do I delete all rows in a SQL Server table when my ADO DataSet has more than one table
- Next by thread: Re: How do I delete all rows in a SQL Server table when my ADO DataSet has more than one table
- Index(es):
Relevant Pages
|
