mysqlexception 未处理 - 已经有一个与此连接关联的打开的 DataReader 必须先关闭 [英] mysqlexception was unhandled - There is already an open DataReader associated with this Connection which must be closed first

问题描述

所以我正在尝试使用该表单来更新我在 xampp 上的数据库.但是当我尝试更新时，我在这部分的标题中得到了错误:reader = objcommand.ExecuteReader() 非常感谢所有帮助.

So I am trying to use the form to update my database on xampp. But when I try update I get the error in the title at this part: reader = objcommand.ExecuteReader() All help is much appreciated.

代码如下:

    Imports MySql.Data
Imports MySql.Data.MySqlClient
Imports System.Drawing.Printing
Imports System
Imports System.Windows.Forms

Public Class frmClientDetails
    Dim form_type As Form
    Dim user_table As String
    Dim objconnection As New MySqlConnection("Server=localhost;database=ba-solutions;user id=root;password=")
    Dim sqlstring As String

    Private Sub frmClientDetails_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DGVClient.Columns.Clear()
        Dim dt As New DataTable
        objdataadapter.SelectCommand = New MySqlCommand()
        objdataadapter.SelectCommand.Connection = objconnection
        objdataadapter.SelectCommand.CommandType = CommandType.Text
        objdataadapter.SelectCommand.CommandText = "SELECT * FROM Client_Details"
        objdataadapter.Fill(dt)


        rowposition = 0

        DGVClient.DataSource = dt

    End Sub
    Private Sub btnBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBack.Click
        frmMainMenu.Show()
        Me.Hide()
    End Sub

    Private Sub btnClearAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClearAll.Click
        txtCompanyName.Clear()
        cbxCompanyType.Items.Clear()
        txtVAT.Clear()
        txtPAYE.Clear()
        txtAddressLine.Clear()
        txtCity.Clear()
        txtPostcode.Clear()
        txtEmail.Clear()
        txtPhoneNumber.Clear()
    End Sub


    Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click


        If Len(txtCompanyName.Text) < 1 Then
            MsgBox("Enter a Company Name")
            Return
        End If

        If Len(cbxCompanyType.Text) < 1 Then
            MsgBox("Enter a Company Type")
            Return
        End If

        If Len(txtVAT.Text) <> 9 Then
            MsgBox("The VAT Registration Number must be 9 numbers")
            Return
        End If

        If Len(txtPAYE.Text) <> 8 Then
            MsgBox("The PAYE and Tax Reference must be 8 characters")
            Return
        End If

        If Len(txtAddressLine.Text) < 1 Then
            MsgBox("Enter a First Line of Address")
            Return
        End If

        If Len(txtCity.Text) < 1 Then
            MsgBox("Enter a City Name")
            Return
        End If

        If Len(txtPostcode.Text) < 1 Then
            MsgBox("Enter a Postcode")
            Return
        End If

        If Len(txtEmail.Text) < 1 Then
            MsgBox("Enter an Email Address")
            Return
        End If

        If Len(txtPhoneNumber.Text) <> 11 Then
            MsgBox("The Phone Number must be 11 numbers ")
            Return
        End If

        Try
            objconnection.Open()
        Catch ex As Exception
            MsgBox("Error connecting to database", MsgBoxStyle.Information, "Connection Failed")
        End Try

        sqlstring = "Select * FROM client_details"
        Dim currentrecord As Integer = DGVClient.CurrentCellAddress.Y


        objconnection.Close()
        objconnection.Open()

        sqlstring = "Insert into `Client_Details` (`CompanyName` , `CompanyType` , `VATRegistrationNumber , `PAYEandTaxReference` , `AddressLine1` , `City` , `Postcode` , `Email` , `PhoneNumber') Values ('" &
 txtCompanyName.Text & "','" & cbxCompanyType.Text & "' , '" & txtVAT.Text & "','" & txtPAYE.Text & "' , '" & txtAddressLine.Text & "' , '" & txtCity.Text & "' , '" & txtPostcode.Text & "' , '" &
 txtEmail.Text & "' , '" & txtPhoneNumber.Text & "')"
        MsgBox("updated")
        objcommand.CommandText = sqlstring

        reader = objcommand.ExecuteReader()
        MsgBox("update")

    End Sub

    Public Sub count_records()
        Dim reccount As Integer
        reccount = DGVClient.Rows.Count = 1

    End Sub

End Class

所以基本上我是填写表单上的文本框，一旦我点击提交，它应该用新记录更新数据库.

So basically I am filling in the text boxes on the form, and once I hit submit it should update the database with the new record.

推荐答案

改用 参数化查询.那会帮助你.参数的数量将按顺序与表中的列数匹配.根据需要创建 ADO 对象并在 Sub 例程结束时关闭它们.

Switch to using Parameterized Queries. That will help you out. The number of parameters will match the number of columns in your table in order. Create the ADO objects as you need them and close them at the end of the Sub routines.

Insert Into [table name] Values(n1, n2, etc...)

SQL 教程

这篇关于mysqlexception 未处理 - 已经有一个与此连接关联的打开的 DataReader 必须先关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！