Deleting all records within an Access Table

[SonicBoomAu]

Hi All,

 

I am trying to delete all the records contained in a table.

 

Private Sub DeletePrintedAsset()

 

Dim blnDeleteAsset As Boolean = False

Dim cnADONetConnection As New OleDb.OleDbConnection

Dim daDataAdapter As New OleDb.OleDbDataAdapter

Dim cbCommandBuilder As New OleDb.OleDbCommandBuilder

Dim dtAsset As New DataTable

Dim rowPosition As Integer = 0

Dim strCurrentDBAsset As String

 

'Try

' Open the database connection

 

cnADONetConnection.ConnectionString = _

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & _

strDatabaseName

cnADONetConnection.Open()

 

' Get all the Assets

 

daDataAdapter = _

New OleDb.OleDbDataAdapter("Select * from PrintSignOut", _

cnADONetConnection)

daDataAdapter.Fill(dtAsset)

 

' Set up the commandbuilder so we can delete the record.

 

cbCommandBuilder = New OleDb.OleDbCommandBuilder(daDataAdapter)

 

Do Until rowPosition = -1

 

' Delete the Issue Record

 

dtAsset.Rows(rowPosition).Delete()

 

' Commit the changes to the actual data souce.

 

daDataAdapter.Update(dtAsset)

Loop

 

' Close the connection to the database

 

cnADONetConnection.Close()

 

'Catch ex As Exception

'MessageBox.Show("An error has occurred! Error: " & ex.Message, _

' "DIntTC Asset Management", MessageBoxButtons.OK, MessageBoxIcon.Error)

'End Try

End Sub

 

When I run this piece of code it comes up with the following error at the "daDataAdapter.Update(dtAsset):

 

"An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

 

Additional information: Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information."

 

Want am I doing wrong?????

Please Help.

Edited October 18, 2004 by SonicBoomAu

[kejpa]

Hi there,

open your connection just as you showed then have a CommandObject take care of the delete....

Dim cmdDelete As OleDbCommand = New OleDbCommand("Delete from PrintSignOut", cnADONetConnection)
cmdDelete.ExecuteNonQuery()

 

Simplicity is beautiful, isn't it....

 

HTH

/Kejpa

[SonicBoomAu]

Thank You.

 

I have been pulling my hair out over this for the last week.