SQL Server:删除用户

Question

我正试图编写一个查询来从SQL Server数据库中删除用户注册，但当我试图删除用户时，我会收到以下错误：

System.InvalidOperationException："ExecuteReader:Connection属性尚未初始化。">

我的代码：

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 1

您需要先打开连接，然后才能创建命令。即

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

但是，您当前的代码包含SQL注入。您不应该连接字符串来获取SQL。您应该使用参数。有关应用程序的更好解释，请参阅此答案。

此外，以纯文本形式存储密码从来都不是一个好的做法。曾经您应该只存储密码的散列，并比较散列而不是纯文本。阅读此答案以供参考。以及更多关于为什么你应该散列的背景信息