SQL Server : delete user

I’m trying to write a query to delete a user registration from my SQL Server database, but when I try to delete a user, I get this error:

System.InvalidOperationException: ‘ExecuteReader: Connection property has not been initialized.’

My code:

Public Class DeleteForm
Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
    Dim conn = New SqlConnection("Data Source=(localdb)MSSQLLocalDB;Initial Catalog=dbProject;Integrated Security=True")

    Using cmd = New SqlCommand("SELECT * FROM tblLogin WHERE username = " & txtUsername.Text, conn)
        conn.Open()
        Dim reader As SqlClient.SqlDataReader = cmd.ExecuteReader

        If reader.Read = True Then
            If txtUserPass.Text = txtCheckPass.Text Then
                Dim deleteOk As Integer = MessageBox.Show("This cant be undone!" & vbCrLf & "Are you sure?", "Warning!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning)
                If deleteOk = DialogResult.Yes Then
                    Dim queryDelete As String = "DELETE FROM tblLogin WHERE username = " & txtUsername.Text & " and password = " & txtPassword.Text
                    Dim cmdDelete As New SqlClient.SqlCommand(queryDelete, conn)

                    If conn.State = ConnectionState.Closed Then conn.Open()
                    reader.Close()
                    cmdDelete.ExecuteNonQuery()
                    MsgBox("Cancellazione eseguita correttamente!")
                    cmdDelete.Dispose()
                    conn.Close()
                ElseIf deleteOk = DialogResult.No Then

                End If
            Else
                MsgBox("The passwords arent matching!")
            End If
        Else
            MsgBox("User not found")
            conn.Close()
            txtUsername.Clear()
            txtUsername.Focus()
            txtUserPass.Clear()
            txtCheckPass.Clear()
        End If
    End Using
  End Sub
End Class

Answer

~~You need to open connection before you can create a command. i.e.~~

Dim conn = New SqlConnection("Data Source=(localdb)MSSQLLocalDB;Initial Catalog=dbProject;Integrated Security=True")
conn.Open()
Using cmd = New SqlCommand(....

However your current code contains SQL Injection. You should not concatenate strings go get your SQL. You should use parameters. See this answer for better explanation about the application.

Also it is never a good practice to store passwords in plain text. Ever. You should store hash of password only and compare the hashes rather than plain-text. Read this answer for reference. And more background info on why you should hash

Advertisement

Answer