Re: How do I delete all rows in a SQL Server table when my ADO DataSet has more than one table

Thanks, Bill. I read up on it. Best regards, Brad Marshall
"William (Bill) Vaughn" <billvaNoSpam@xxxxxxxxx> wrote in message
news:e$xHcuRbFHA.3400@xxxxxxxxxxxxxxxxxxxxxxx
> If you're working with TSQL you might consider TRUNCATE table instead.
> There are log file issues that might help performance.
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com/blog/billva
> www.betav.com
> www.sqlreportingservices.net
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
>
>
> "Brad Marshall" <bradley.marshall@xxxxxxxxxxx> wrote in message
> news:O4HzepKbFHA.796@xxxxxxxxxxxxxxxxxxxxxxx
>> See, Now, Val - this is just the kind of answer I was hoping for. I know
>> I've learned something valuable when the answer begins with "Why [in the
>> %$@#] are you doing it that way?" Most seriously, Thanks, and I've just
>> learned something new as I embrace ADO. I can knock off for the night and
>> fix that rum and tonic I've been denying myself until I got past this
>> question.
>>
>> Best regards,
>>
>> Brad Marshall
>>
>> "Val Mazur (MVP)" <group51a@xxxxxxxxxxx> wrote in message
>> news:%236$vNUKbFHA.2288@xxxxxxxxxxxxxxxxxxxxxxx
>>> 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")
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: SQL Adapter will NOT update
    ... code that is in the Page_Onload Sub, otherwise, i agree, update should be ... "Val Mazur " wrote: ... > Microsoft MVP ... (this is a work order ...
    (microsoft.public.vb.database.ado)
  • Re: Trapping CommitTrans - returns success even when failed!
    ... >I have an 'On Error Resume Next' statement just before the CommitTrans. ... >> Val Mazur ... >> Microsoft MVP ... >>> handler for ADO CommitTrans. ...
    (microsoft.public.data.ado)
  • Re: OledbDataReader Problem.
    ... Val Mazur ... Microsoft MVP ... > I have 2 computers with the same configuration and my application did'nt ...
    (microsoft.public.data.ado)
  • Re: Syntax error in insert statement?
    ... What is your code that executes this statement? ... >> into square brackets. ... >> Val Mazur ... >> Microsoft MVP ...
    (microsoft.public.data.ado)
  • Re: Timeout Expired (0x80040E31)
    ... This is true for both data files and transaction ... "Val Mazur" wrote in message ... >>> Microsoft MVP ... >>>>> need to set CommandTimeout property of the Command or Connection ...
    (microsoft.public.vb.database.ado)