如何解决:”已经存在与此命令相关联的打开的 DataReader,必须首先关闭。”

huangapple go评论63阅读模式
英文:

How to resolve : 'There is already an open DataReader associated with this Command which must be closed first.'

问题

I've been trying to insert data into my SQL database but this problem always shows up.

I've tried redoing it again, and the same problem occurs, and I'm really stumped right now.

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim conn As SqlConnection = New SqlConnection("Data Source=DESKTOP-OBQR58O\SQLEXPRESS;Initial Catalog=Accounts;Integrated Security=True")
    Dim comm As SqlCommand = New SqlCommand("insert into User(username, password) values('" + TextBox1.Text + "', '" + TextBox3.Text + "')", conn)
    Dim data As SqlDataAdapter = New SqlDataAdapter(comm)

    Dim user = TextBox1.Text
    Dim pass = TextBox2.Text
    Dim cpass = TextBox3.Text

    Dim reader As SqlDataReader
    conn.Open()
    Dim cmd As SqlCommand = New SqlCommand("select Username from [User] where Username ='" + TextBox1.Text + "'", conn)
    conn.Close()
    conn.Open()
    reader = cmd.ExecuteReader

    If user.Trim() = "" Or pass.Trim() = "" Or cpass.Trim() = "" Then
        MessageBox.Show("Empty Fields", "Blank Spaces")
    ElseIf Not String.Equals(pass, cpass) Then
        MessageBox.Show("Passwords do not match", "ERROR")
        conn.Close()
    ElseIf reader.HasRows Then
        MessageBox.Show("Username already exists!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        TextBox1.Clear()
        conn.Close()
        reader.Close()
    Else
        MessageBox.Show("Account created successfully!", "Success")
        Dim table As DataTable = New DataTable()
        data.Fill(table) ' this is where the problem occurs.
        TextBox1.Clear()
        TextBox2.Clear()
        TextBox3.Clear()
        Dim log As New Login
        Me.Close()
        log.Show()
        conn.Close()
    End If
    conn.Close()
End Sub

I honestly don't know what to do.

英文:

I've been trying to insert data into my sql database but this problem always show up
i've tried redoing it again and the same problem occurs and i'm really stumped right now

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        Dim conn As SqlConnection = New SqlConnection("Data Source=DESKTOP-OBQR58O\SQLEXPRESS;Initial Catalog=Accounts;Integrated Security=True")
        Dim comm As SqlCommand = New SqlCommand("insert into User(username, password)values('" + TextBox1.Text + "', '" + TextBox3.Text + "')", conn)
        Dim data As SqlDataAdapter = New SqlDataAdapter(comm)

        Dim user = TextBox1.Text
        Dim pass = TextBox2.Text
        Dim cpass = TextBox3.Text

        Dim reader As SqlDataReader
        conn.Open()
        Dim cmd As SqlCommand = New SqlCommand("select Username from [User] where Username ='" + TextBox1.Text + "'", conn)
        conn.Close()
        conn.Open()
        reader = cmd.ExecuteReader

        If user.Trim() = "" Or pass.Trim() = "" Or cpass.Trim() = "" Then
            MessageBox.Show("Empty Fields", "Blank Spaces")

        ElseIf Not String.Equals(pass, cpass) Then
            MessageBox.Show("Passwords do not match", "ERROR")
            conn.Close()

        ElseIf reader.HasRows Then
            MessageBox.Show("Username already exists!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            TextBox1.Clear()
            conn.Close()
            reader.Close()

        Else
            MessageBox.Show("Account created succesfully!", "Success")
            Dim table As DataTable = New DataTable()
            data.Fill(table) ' this is where the problem occurs.
            TextBox1.Clear()
            TextBox2.Clear()
            TextBox3.Clear()
            Dim log As New Login
            Me.Close()
            log.Show()
            conn.Close()
        End If
        conn.Close()
    End Sub

I honestly don't know what to do

答案1

得分: 1

你在代码顶部打开了阅读器:

reader = cmd.ExecuteReader

所以,它已经打开了。然后,当你运行Fill命令时,它与已经打开的阅读器冲突了!

最简单的修复方法 - 尽管个人建议稍微重构一下代码,将OpenReader放到它被使用的地方附近 - 是在Fill之前在阅读器上添加一个Close

         Else
            reader.Close()  ' 这是你需要添加的部分
            MessageBox.Show("账户创建成功!", "成功")
            Dim table As DataTable = New DataTable()
            data.Fill(table) ' 这是问题发生的地方。

非常重要:如果你不熟悉"SQL注入攻击"的概念,请立刻了解它们。绝对不应该执行使用未经用户数据验证构建的字符串构建的SQL语句。你应该使用参数传递。

毕竟,如果我输入了用户名"Irrelevant';DROP TABLE Users;--",你将得到一个包含"SELECT Username from [Users] WHERE [Username] = 'Irrelevant'; DROP TABLE Users; --'"的SQL语句。

当然,你还应该对输入进行验证,以防止嵌入的HTML和脚本等问题!但这比使用SQL参数更复杂。

英文:

You open your reader up at the top:

reader = cmd.ExecuteReader

So, it's open. And then, when you run the Fill command, it conflicts with the open reader!

The simplest fix - although, personally, I would restructure the code a bit, to bring the OpenReader nearer to where it is used - would be to add a Close to your reader right before the Fill.

         Else
            reader.Close()  ' what you would add
            MessageBox.Show("Account created succesfully!", "Success")
            Dim table As DataTable = New DataTable()
            data.Fill(table) ' this is where the problem occurs.

VERY IMPORTANT: If you're not familiar with the concept of "SQL Injection Attacks", read up on them, right away. You should NEVER execute SQL that's been built by constructing a string with unvalidated data from the user. You should pass parameters instead.

After all, what if I typed in the user name of "Irrelevant';DROP TABLE Users;--"? You'd wind up with a SQL Statement that contained "SELECT Username from [Users] WHERE [Username] = 'Irrelevant'; DROP TABLE Users; --'"

And, of course, you should validate the input as well, for things like embedded HTML and script! But that's more complicated than just using SQL Parameters.

huangapple
  • 本文由 发表于 2023年2月9日 01:59:12
  • 转载请务必保留本文链接:https://go.coder-hub.com/75389914.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定