VB.NET SQL Server: Must declare scalar variable

Question

I am trying to make a query that deletes the user from my database. But when i confirm to delete the user it gives me an error:

System.Data.SqlClient.SqlException (0x80131904): Must declare the scalar variable "@Username".

Imports System.Data.SqlClient
Public Class DeleteForm
Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
    Dim RetVal As Integer
    Dim conn = New SqlConnection("Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=dbProject;Integrated Security=True")
    Using cmd = New SqlCommand("select count(*) from tblLogin where username = @Username and password = @Password", conn)
        cmd.Parameters.Add("@Username", SqlDbType.VarChar).Value = txtUsername.Text
        cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = txtPassword.Text

        conn.Open()
        If conn.State = ConnectionState.Open Then
            RetVal = CInt(cmd.ExecuteScalar)
            If RetVal = 1 Then
                If txtPassword.Text And txtCheckPassword.Text <> "" Then
                    If txtCheckPassword.Text = txtPassword.Text Then
                        Dim cancConf As Integer = MessageBox.Show("This cant be undone!" & vbCrLf & "Are you sure?", "Warning!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning)
                        If cancConf = DialogResult.Yes Then
                            Try
                                Dim queryDelete As String = "DELETE FROM tblLogin WHERE username = @Username"
                                Dim cmdDelete As New SqlClient.SqlCommand(queryCancellazione, conn)

                                cmdCancellazione.ExecuteNonQuery()
                                MsgBox("Account deleted succesfully!")
                                cmdCancellazione.Dispose()
                                conn.Close()

                                LoginForm.Show()
                                Me.Close()
                            Catch ex As Exception
                                MsgBox(ex.ToString())
                            End Try
                        ElseIf cancConf = DialogResult.No Then

                        End If
                    Else
                        MsgBox("The passwords arent matching!", MsgBoxStyle.Exclamation)
                    End If
                ElseIf txtPUtenteCANC.Text <> "" And txtPUtenteCONF.Text = "" Then
                    MsgBox("Please, confirm the password")
                End If
            Else
                MsgBox("User not found!", MsgBoxStyle.Exclamation)
                txtNUtenteCANC.Clear()
                txtPUtenteCANC.Clear()
                txtPUtenteCONF.Clear()
                txtNUtenteCANC.Select()
            End If
        Else
            MessageBox.Show("The connection is not open!" & vbCrLf & "The program will close", "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End
        End If
    End Using
End Sub
End Class

On a side note 1) Good job for using parameters for your values. 2) Do not store passwords in clear text, only store a hash of the password using a secure hashing algorithm like pbkdf2, scrypt, or bcrypt. To validate if a password input is the same as the stored hash the code should has the input and compare the 2 hashes to determine equality. — Igor
– Igor, Commented Dec 13, 2018 at 11:17
I know i should hash and salt the passwords but im learning sql database in vb.net alone, so i will do it later, thanks anyway for the suggestion! :) — Nome Cognome
– Nome Cognome, Commented Dec 13, 2018 at 11:22

Tim Schmelter · Accepted Answer · 2018-12-13 11:14:46Z

3

You have added that parameter to the SELECT COUNT command but not to the DELETE command.

Dim queryCancellazione As String = "DELETE FROM tblLogin WHERE username = @Username"
Dim cmdCancellazione As New SqlClient.SqlCommand(queryCancellazione, conn)
cmdCancellazione.Parameters.Add("@Username", SqlDbType.VarChar).Value = txtUsername.Text

answered Dec 13, 2018 at 11:14

Tim Schmelter

462k79 gold badges721 silver badges981 bronze badges

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

Collectives™ on Stack Overflow

VB.NET SQL Server: Must declare scalar variable

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