CODEWITHSUNDEEP
sqldatabasevb.netms-access
rwan
rwan

Reputation: 1

VB.NET / SQL for deleting database rows in a MS Access database

I want to delete all the contents of a MS Access table however I am not sure how to set up my SQL to do so.

So far I have found this online:

Dim SqlQuery As String = "DELETE * FROM QuestionResults WHERE Quizname = " & txtQuizName.Text & ";"

I have the connection to the database linked up due to my previous code, I am just unsure how to edit this code so that it deletes the contents of the table (i want it to delete every record, not the table itself)

Public provider As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
Public dataFile As String = "U:\My Documents\Visual Studio 2013\Projects\COMP4 Project\COMP4 Project\bin\Debug\QuizDatabase.accdb"

Public connString As String
Public myConnection As OleDbConnection = New OleDbConnection
Public dr As OleDbDataReader

connString = provider & dataFile
myConnection.ConnectionString = connString
myConnection.Open()

Upvotes: 0

Views: 129

Answers (2)

Trevor_G
Trevor_G

Reputation: 1331

What Dai said. Also you do not need an OleDbDataReaderuse an OleDbCommand:

Represents an SQL statement or stored procedure to execute against a data source.

Example use:

Dim command As New OleDbCommand(queryString, connection)
command.ExecuteNonQuery()

Upvotes: 0

Dai
Dai

Reputation: 155708

Simply DELETE FROM QuestionResults without a WHERE clause will delete every row.

The alternative command TRUNCATE TABLE QuestionResults will be faster, however I don't know if Access/JET supports the TRUNCATE statement or not.

Note that you must not generate SQL using string concatenation. Your program will break if someone puts "Baba O'Reilly" into your txtQuizName textbox. Instead use parameters.

Upvotes: 1

Related Questions