"not allowed to change the 'connectionstring' property. the connection's current state is open"

Question

so im working on a PDS system for my school act and i came in to this error that i didn't know that much i dont even know what kind of error is it this is the main error of the code : "

 myConnection.ConnectionString = connString
 myConnection.Open()

"

and this is the code:

Imports System.Data.SqlClient
Imports System.Data.OleDb

Public Class personalinfo

    Dim provider As String
    Dim dataFile As String
    Dim connString As String
    Dim myConnection As OleDbConnection = New OleDbConnection
    Private Sub Personal_infoBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles Personal_infoBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.Personal_infoBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.PDS_GabuteroKenuardPercivalDataSet)

    End Sub

    Private Sub personalinfo_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'PDS_GabuteroKenuardPercivalDataSet.personal_info' table. You can move, or remove it, as needed.
        Me.Personal_infoTableAdapter.Fill(Me.PDS_GabuteroKenuardPercivalDataSet.personal_info)




    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Personal_infoBindingSource.AddNew()





    End Sub

    Private Sub Personal_infoBindingSource_CurrentChanged(sender As Object, e As EventArgs) Handles Personal_infoBindingSource.CurrentChanged

    End Sub

    Private Sub CS_IDTextBox_TextChanged(sender As Object, e As EventArgs)

    End Sub

    Private Sub TIN_noTextBox_TextChanged(sender As Object, e As EventArgs) Handles TIN_noTextBox.TextChanged

    End Sub

    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs)

    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        provider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
        dataFile = "F:\PDS_GabuteroKenuardPercival.mdb"
        connString = provider & dataFile

        myConnection.ConnectionString = connString
        myConnection.Open()

        Dim str As String
        str = "Insert in to personal_info([CS_ID]),[Emp_Ln],[Emp_Fn],[Emp_Mi],[Date_Birth],[Place_Birth],[Gender],[Civilstatus],[Height],[Weight],
                [Blood_typ],[Gsis_ID],[Pagibig_ID],[Phil_health_no],[SSS_no],[TIN_no],[Agency_emp_no],[Citizenship],[Indicate_country],[Residential_add],
                [ZIP_code],[Permanent_add],[ZIP_code_perma_add],[Tell_no],[Mobile_no],[Email_add])"
        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
        cmd.Parameters.Add(New OleDbParameter("CS_ID", CType(CS_IDTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Emp_Ln", CType(Emp_LnTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Emp_Fn", CType(Emp_FnTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Email_add", CType(Emp_MiTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Emp_Mi", CType(Date_BirthDateTimePicker.Value, String)))
        cmd.Parameters.Add(New OleDbParameter("Date_Birth", CType(Place_BirthTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Place_Birth", CType(GenderTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Gender", CType(CivilstatusTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Civilstatus", CType(Tell_noTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Height", CType(HeightTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Weight", CType(WeightTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Blood_typ", CType(Blood_typTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Gsis_ID", CType(Gsis_IDTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Pagibig_ID", CType(Pagibig_IDTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Phil_health_no", CType(Phil_health_noTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("SSS_no", CType(SSS_noTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("TIN_no", CType(TIN_noTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Agency_emp_no", CType(Mobile_noTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Citizenship", CType(Agency_emp_noTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Indicate_country", CType(CitizenshipTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Residential_add", CType(Indicate_countryTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("ZIP_code", CType(Residential_addTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Permanent_add", CType(ZIP_codeTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("ZIP_code_perma_add", CType(Permanent_addTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Tell_no", CType(ZIP_code_perma_addTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Mobile_no", CType(Email_addTextBox.Text, String)))


        Try


            cmd.ExecuteNonQuery()
            cmd.Dispose()
            myConnection.Close()

            CS_IDTextBox.Clear()
            Emp_LnTextBox.Clear()
            Emp_FnTextBox.Clear()
            Emp_MiTextBox.Clear()
            Place_BirthTextBox.Clear()
            GenderTextBox.Clear()
            CivilstatusTextBox.Clear()
            Tell_noTextBox.Clear()
            HeightTextBox.Clear()
            WeightTextBox.Clear()
            Blood_typTextBox.Clear()
            Gsis_IDTextBox.Clear()
            Pagibig_IDTextBox.Clear()
            Phil_health_noTextBox.Clear()
            SSS_noTextBox.Clear()
            TIN_noTextBox.Clear()
            Mobile_noTextBox.Clear()
            Agency_emp_noTextBox.Clear()
            CitizenshipTextBox.Clear()
            Indicate_countryTextBox.Clear()
            Residential_addTextBox.Clear()
            ZIP_codeTextBox.Clear()
            Permanent_addTextBox.Clear()
            ZIP_code_perma_addTextBox.Clear()
            Email_addTextBox.Clear()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

i already try to change and fix the close connection and fixing the "provider" and "datafile" still i don't know what's wrong with it

The following may be helpful: https://stackoverflow.com/a/69638011/10024425 - pay particular attention to the comments in the code regarding parameters. Also notice where the `OleDbConnection` statements are. — Tu deschizi eu inchid, Nov 09 '22 at 19:03
Well, the error really does tell you the problem. You're simply trying to modify the connection string on an open connection. Should read up on the _Using_ clause and how it applies to IDisposable interfaces (such as OleDbConnection). That will ensure the connection is closed and disposed correctly — Hursey, Nov 09 '22 at 19:06
@Hursey I don't see anything which obviously modifies the connection after it's been opened (it gets closed in the course of execution of the button event), but maybe I'm missing something. — Craig, Nov 09 '22 at 19:10
@Craig I agree, but the assumption is that the execution is actually hitting that close(). Notice that there are also a number of "questionable" looking TableAdapter method calls so those could be doing something wacky with connections — Hursey, Nov 09 '22 at 19:22
Why are you creating `OleDb` objects at all? You have a typed `DataSet`. Use it. You can populate the appropriate `DataTable` and use the appropriate table adapter to save multiple new records, or you can configure your table adapters to support DB Direct methods and call the `Insert` method to insert a single record. There is rarely any good reason to use straight ADO.NET when you have a typed `DataSet`. Use one or the other, not both. — jmcilhinney, Nov 10 '22 at 01:23

score 0 · Answer 1 · answered Nov 10 '22 at 06:53

Example:

Private connectionString As String = ConfigurationManager.ConnectionStrings("AccessConnectionString").ConnectionString

Private using(Optional ByVal cnn As New OleDbConnection(connectionString))
    cnn.Open()
    Dim sql As String = "select * from Billing where FormNo=" & txtformno.Text & ""

    using(OleDbCommand cmd = New OleDbCommand(sql, cnn))
        If True Then
        using(OleDbDataReader dr = cmd.ExecuteReader())
            If True Then
            '....
            End If
        End If
End Sub

Hope this link can be useful to you. https://www.codeproject.com/Questions/1088503/Not-allowed-to-change-the-connectionstring-propert

Please use parameterized queries. – Tu deschizi eu inchid Nov 10 '22 at 13:38 — Tu deschizi eu inchid, Nov 10 '22 at 13:38

"not allowed to change the 'connectionstring' property. the connection's current state is open"

1 Answers1