deleting all rows in a database table 4

[steve1rm]

Hello,

[VS 2005]

Using typed dataset

I have a table adapter that is filled. I want to delete all the previous data that is in the database before filling this table.

Is there a single command that can be used to delete every single row in the database table, then issue the fill command.

With the delete command you have to enter the fields. However, I don't have the name of the fields values until after the table is filled.

Just want to fill the table with fresh data.

Many thanks,

Steve

 

[chrissie1]

depends on the server, for sqlserver look into truncate.

Christiaan Baes
Belgium

"My old site" - Me

 

[steve1rm]

Thanks

 

[Bruce1965]

You could try this snippet of code .... it will delete records from your database (assuming SQL) where each record meets a particular criteria ...

Dim sqlcnn As New SqlConnection
sqlcnn.ConnectionString = _
        "data source = SQLSERVER;" & _
        "integrated security = true;" & _
        "initial catalog = DATABASE"
        Try
            'now DELETE original record from database file
            Dim sqlcmndDelete As New SqlCommand
            sqlcmndDelete.Connection = sqlcnn
            sqlcmndDelete.CommandText = "delete from TABLE where FIELED = '" & VARIABLE & "'"
            sqlcnn.Open()
            sqlcmndDelete.ExecuteNonQuery()
            sqlcnn.Close()
        Catch ex As Exception
                       sqlcnn.Close()
        End Try

PS the words in CAPITALS in the code above will need to be replaced with your variables/names.

Hope this helps

 

[mansii]

Since you mention dataset and (data)adapter, you can do it this way:

For Each lRow As DataRow In yourtable.Rows
   lRow.Delete
Next
Dim builder As New OdbcCommandBuilder(yourDataAdapter)
yourDataAdapter.DeleteCommand = builder.GetDeleteCommand
yourDataAdapter.Update(yourtable)

 

[onpnt]

I wanted to add a point of view from a DBA stand point. Using truncate can be extremely dangerous. If you use truncate there is no recovery point as this transaction will not be logged in the transaction logs on sql server.

In short if you truncate a table it is gone forever and you will not be able to get it back. Always use delete from your programs. If things are setup correctly on the backend (DB server) you'll be able to recover then in case your program does something undesirable

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[chrissie1]

yep, but truncate is alot faster then delete.

Christiaan Baes
Belgium

"My old site" - Me

 

[onpnt]

Yeah due to the transaction log being taken out of the picture.

Scary stuff though ;-)

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[steve1rm]

Thanks for your help guys.

I have given all a star.

Steve

 

[BKQc]

Am I wrong in saying that truncate also override triggers (at least on Oracle)?