Delete record from SQL database in VB.NET

Question

I want to delete a record which is related to the SerialNo in the database.

This is my code:

Using con = New MySqlConnection("server=" & server & ";" & "user id=" & userid & ";" & "password=" & password & ";" & "database=" & database)
    con.Open()
    Dim sqlText = "DELETE * FROM datatable WHERE SerialNo = @ulogin"
    Using cmd = New MySqlCommand(sqlText, con)
        cmd.Parameters.AddWithValue("@ulogin", frmmain.txtinput.Text)
        cmd.ExecuteNonQuery()
    End Using
    con.Close()
End Using

This code doesn't work. When I run the program, the following error appears:

Please be kind enough to suggest a suitable solution.

NOTE: 221 means the entered number.

I tested the code without * also @Jinx88909 But it doesn't working. — Pasindu Jayaneth
– Pasindu Jayaneth, Commented Jan 17, 2017 at 16:45
That would provide a different error. The error in your question is relevant to the * and is not valid syntax. As for the DataType, I would explicitly set it like so cmd.Parameters.Add("@ulogin", SqlDbType.Int).Value = — Bugs
– Bugs, Commented Jan 17, 2017 at 16:48
Can we stop using AddWithValue() already? Note that ExecuteNonQuery is a function telling you how many rows were affected: Dim rows = ExecuteNonQuery() is the place to start. If it is non zero something was deleted — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jan 17, 2017 at 16:53
use cmd.Parameters.AddWithValue("@ulogin", CInt(frmmain.txtinput.Text))... convert that txtinput.text to integer... of course, made some validation if You enter something other then number. — nelek
– nelek, Commented Jan 17, 2017 at 18:04

Joel Coehoorn · Accepted Answer · 2017-01-17 18:10:04Z

4

The * does not belong. You can't delete only specific columns from a record. You either delete the whole record or do nothing, and so there is no column list portion to a DELETE statement.

While I'm here, there's no need to call con.Close() (the Using block takes care of that for you) and it's better to avoid AddWithValue() in favor of an Add() overload that lets you be explicit about your parameter type.

Const sqlText As String = "DELETE FROM datatable WHERE SerialNo = @ulogin"
Using con As New MySqlConnection("server=" & server & ";" & "user id=" & userid & ";" & "password=" & password & ";" & "database=" & database), _
      cmd AS New MySqlCommand(sqlText, con)
    cmd.Parameters.Add("@ulogin", MySqlDbType.Int32).Value = frmmain.txtinput.Text
    con.Open()
    cmd.ExecuteNonQuery()
End Using

edited Jan 17, 2017 at 18:10

answered Jan 17, 2017 at 18:03

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

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

3 Comments

Bugs Over a year ago

+1 I like the use of nested Using statements, makes code much simpler to read. It would be worth doing what Plutonix suggested with Dim rows = cmd.ExecuteNonQuery to be in the know if the record actually was deleted.

Joel Coehoorn Over a year ago

@Jinx88909 I started doing this in C#, but honestly I think this VB equivalent might be a bug. The , separator likely makes a new statement rather than continuing the Using declaration. But I've never had a trouble ticket as a result, and I like the conciseness. It's really the DB connection you need to care about most.

Pasindu Jayaneth Over a year ago

Thank you so much @JoelCoehoorn and those who came to help me in this problem

Collectives™ on Stack Overflow

Delete record from SQL database in VB.NET

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related