How to delete a row in sql using vb.net

Question

Sorry if its easy or basic i am brand new to sql and tables so im just trying to figure it out Basically im trying to delete a row that the user selects By having them click on a row then clicking a delete button but i don't know how that should look ive tried a few different ways but none of them seem to work. No error comes up but it just dosen't delete the row

 Private Sub DeleteSelectedUsers()
    Dim connection As SqlConnection = New SqlConnection()
    connection.ConnectionString = "Data Source=GERARD-PC\SQLEXPRESS; Initial Catalog=TestDB;User ID=AccountsUser;Password=password123"

    connection.Open()

    Dim adp As SqlDataAdapter = New SqlDataAdapter _
    ("Delete * from dgrdUsers.SelectedRows(0).Cells", connection)




End Sub
Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
    Try

        Dim username As String = dgrdUsers.SelectedRows(0).Cells.Item("Username").Value.ToString()

        If (MessageBox.Show(Me, "Do you want to delete user " & username & "?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes) Then
            DeleteSelectedUsers()
        End If
    Catch ex As Exception

    End Try
End Sub

Any ideas?

First of all, you need to follow some basic SQL Tutorial. After a DELETE statement there is no * (except ms-access) then after the FROM you need the table name and a WHERE statement (unless you want to delete the WHOLE table). Finally, you can't expect the Sql parser to kwnow anything about what is a datagridview and interpret your string as a reference to the selected rows of your datagrid. — Steve
– Steve, Commented Feb 8, 2023 at 10:22
@Steve ok first off thank you and ive added the table name now in the from but i don't know what i would put in it ive tried putting (username = @username) in it but that dosent work even if i use (byVal username as string) as well it says (arguement not specified for parameter) I know you might not want to spoonfeed it to me but this is my first time trying to delete something in sql — Dylaka83
– Dylaka83, Commented Feb 8, 2023 at 10:34

Steve · Accepted Answer · 2023-02-08 11:56:04Z

The syntax required to delete a row in the database table requires at least three parts.

The DELETE command
The FROM statement to identify the table where the delete action should occur
The WHERE condition to identify the record (or records) to delete

So it is a string like this: DELETE FROM tablename WHERE condition
and applied to your code you get this.

Private Sub DeleteSelectedUsers(userName as String)
    Using connection As SqlConnection = New SqlConnection()
       connection.ConnectionString = "...."
       connection.Open()
       Dim cmd As SqlCommand = New SqlCommand _
          ("DELETE FROM Table WHERE userName = @user", connection)
       cmd.Parameters.Add("@user", SqlDbType.NVarChar).Value = userName
       cmd.ExecuteNonQuery()
   End Using
End Sub

(Table and UserName are fantasy names because we don't know what is the actual schema of your database)

Other things I have changed:

The connection is created inside a using block to be sure a proper closure and dispose happens even in case of errors
The name of the user to remove should be passed as parameter to the function
No need to use an adapter when you just need to execute a single SqlCommand
The query doesn't concatenate strings to build the command but use a parameter added to the command itself and specifying a type for the data matching the type on the database table

Collectives™ on Stack Overflow

How to delete a row in sql using vb.net

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related