第二次尝试登录时出错 [英] Error on 2nd try of log-in

问题描述

大家好，我的登录代码有问题。当我运行/调试程序时，我可以第一次登录顺利登录，但是当我退出然后尝试再次登录时我收到此错误：



System.Data.dll中发生了'System.Data.SqlClient.SqlException'类型的未处理异常



附加信息：变量名称' @username'已经被声明了。变量名在查询批处理或存储过程中必须是唯一的。





任何人都可以帮我修复此问题。这是我的代码：

Hi guys, there's a problem with my log-in codes. I can log-in smoothly for 1st time log-in when I run/debug the program, but when I log out then try to log-in again I get this error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: The variable name '@username' has already been declared. Variable names must be unique within a query batch or stored procedure.


Can anyone help me fix this. Here's my code:

Public Class frmLogin

    Dim cn As New SqlConnection("Data Source=TANSENGCO\SQLEXPRESS;Initial Catalog=MonitoringSystem;Integrated Security=True")
    Dim cmd As New SqlCommand
    Dim dr As SqlDataReader
    Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click
        frmMainMenu.Height = 720
        frmMainMenu.Width = 950
        Dim numID As Integer = 2
        cmd.Connection = cn
        cn.Open()
        cmd.CommandText = "SELECT ID, username, password FROM tblUsers WHERE username = @username and password = @password"
        cmd.Parameters.Add(New SqlClient.SqlParameter("@username", SqlDbType.VarChar, 20)).Value = txtUsername.Text
        cmd.Parameters.Add(New SqlClient.SqlParameter("@password", SqlDbType.VarChar, 20)).Value = txtPassword.Text
        dr = cmd.ExecuteReader
        If dr.HasRows Then
            dr.Read()
            If dr("ID") = numID Then
                Me.Hide()
                frmMainMenu.lblAccessLevel.Text = "Admin"
                frmMainMenu.Show()
            Else
                Me.Hide()
                frmMainMenu.lblAccessLevel.Text = "User"
                frmMainMenu.TSMOffertory.Enabled = False
                frmMainMenu.TSMOffertory.Visible = False
                frmMainMenu.Show()
            End If
        Else
            MsgBox("Invalid Credentials", MsgBoxStyle.Exclamation, "Invalid LogIn")
        End If
        txtPassword.Clear()
        txtUsername.Clear()
        cn.Close()
    End Sub

我尝试了什么：



我真的不知道发生了什么，所以我不知道该尝试什么。

What I have tried:

I don't really know what's going on so I don't know what to try.

推荐答案

在按钮点击事件中声明此行

declare this line inside the button click event

Dim cmd As New SqlCommand

（或）

add

(or)
add

cmd.Parameters.Clear()

此行之前的

before this line

cmd.Parameters.Add(New SqlClient.SqlParameter("@username", SqlDbType.VarChar, 20)).Value = txtUsername.Text

你的cmd是表单的成员变量，所以在你用它之后保留它的参数第一次。

然后，当你试图再次将@username参数添加到*相同的* cmd对象时，它就会呱呱叫。



始终将sql Command对象包装在一个使用块中，以便正确处理它。

Your cmd is a member variable of the form, so retains it's parameters after you've used it for the first time.
Then, when you try to add the @username parameter to *the same* cmd object again, it croaks.

Always dispose a sql Command object by wrapping it in a using block so that it is disposed correctly.

using (cmd = new SqlCommand(...))
{
   cmd.Open();
   ....
}

是的，不应在类范围内声明SqlConnection，SqlCommand和SqlDataReaders。它们应该在方法范围中声明，并在您的查询不再需要时处理。如果没有在Connection上调用Dispose，你就会泄漏资源，并且比你需要的时间长得多。



尽可能快地连接，尽可能快地查询，并尽快断开连接和处理。

Yeah, the SqlConnection, SqlCommand, and SqlDataReaders should NOT be declared in class scope. They should be declared in the method scope and Disposed of when not needed by your query any more. By failing to call Dispose on the Connection you're leaking resources and holding on to connections much longer than needed.

Connect as late as possible, query as fast as possible, and disconnect and Dispose as soon as possible.

这篇关于第二次尝试登录时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！