Delete SQL statement in VB.NET

Question

I am new to VB.NET and as well as MySqL. I want to delete records in my database. Using,

    Dim SQLStatement As String = "DELETE name,date FROM people"
    RetrieveInfos(SQLStatement)

Where,

Public Sub RetrieveInfos(ByRef SQLStatement As String)
    Dim cmd As MySqlCommand = New MySqlCommand

    With cmd
        .CommandText = SQLStatement
        .CommandType = CommandType.Text
        .Connection = SQLConnection
        .ExecuteNonQuery()

    End With

    SQLConnection.Close()
    SQLConnection.Dispose()
End Sub

But there is an error of InvalidOperationException, I am confused. I want to delete records in my database without referencing the " WHERE number = VALUE " in order to delete the previous recorded value. Is that possible?

Any help would be appreciated. Thanks!

It seems that you need an update statement instead of delete statement. — Thinhbk
– Thinhbk, Commented Jul 27, 2012 at 2:20

competent_tech · Accepted Answer · 2012-07-27 02:24:46Z

1

You've got a few problems:

You do not specify column names in the delete statement since you are deleting records.
Your command, should it be successfully executed, will delete every record in the people table.
You are closing and disposing the SQLConnection at the end of the RetrieveInfos method. If this connection is open elsewhere in your code, only the first execution of the code will work.
Your method should not be called Retrieve if it is deleting.

The command should be wrapped in a using statement

Using cmd As MySqlCommand = New MySqlCommand
    With cmd
        .CommandText = SQLStatement
        .CommandType = CommandType.Text
        .Connection = SQLConnection
        .ExecuteNonQuery()
    End With
End Using

answered Jul 27, 2012 at 2:24

competent_tech

45.1k12 gold badges96 silver badges114 bronze badges

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

1 Comment

dykeag Over a year ago

In addition: you are trying to drop the columns off of the table, are you not? This is not the right syntax to change the database schema. If you are trying to remove the actual values in the columns, this is also wrong. You need to UPDATE command for that.

Collectives™ on Stack Overflow

Delete SQL statement in VB.NET

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related