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

Question

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()

You don't need the *. Also use parameters otherwise you're open to SQL injection. — Bugs
– Bugs, Commented Feb 7, 2017 at 10:01
Those DBObject ought not be global objects; they are all meant to be created, used and disposed in the smallest scope possible (with a few exceptions) — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Feb 7, 2017 at 15:13

Dai · Accepted Answer · 2017-02-07 09:59:29Z

1

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.

answered Feb 7, 2017 at 9:59

Dai

157k31 gold badges314 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bugs · Accepted Answer · 2017-02-07 10:34:48Z

0

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()

edited Feb 7, 2017 at 10:34

Bugs

4,4799 gold badges34 silver badges41 bronze badges

answered Feb 7, 2017 at 10:04

Trevor_G

1,3311 gold badge8 silver badges17 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related