英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论