System.InvalidOperationException: ExecuteReader requires an open and available connection. The connection's current state is closed

Question

I am having a problem with a customer service module which is in a different solution. the problem I think is in my method of calling or getting a connection

here is my class called DBConnForAccess

Imports System.Data.OleDb
Imports System.Data.Odbc

Public Class DBConnForAccess

Dim Conn As New OleDbConnection
Dim cmd As New OleDbCommand
Dim Trans As OleDbTransaction

Public Function DBConnect(ByVal filePath As String, pass As String)
    Try
        ' open Database

        'Conn = New SqlConnection("Data Source=" + ServerName + "\" + DBName + ";User ID=" + DBUsername + ";Password=" + DBPassword + ";Initial Catalog= '" + TB + "'")
        Conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Jet OLEDB:Database Password=" + pass + ";")
        '                          "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword;"

        If Conn.State = ConnectionState.Closed Then
            Conn.Open()
        End If

        ' create transaction
        Trans = Conn.BeginTransaction
        Return "Ok"

    Catch ex As Exception
        Return ex.Message
    End Try

End Function

Public Sub ExecuteSQL(ByVal sql As String, ByVal ParamArray Obj() As Object)
    ' command Object
    Dim CMD As New OleDbCommand(sql, Me.Conn, Me.Trans)

    'add the parameters to the sql command
    Dim I As Integer
    For I = 0 To Obj.Length - 1
        CMD.Parameters.AddWithValue("@" & I, Obj(I))
    Next

    'execute the sql
    CMD.ExecuteNonQuery()
End Sub

Public Sub commit()
    Me.Trans.Commit()
    Me.Trans = Me.Conn.BeginTransaction
End Sub

Public Sub rollback()
    Me.Trans.Rollback()
    Me.Trans = Me.Conn.BeginTransaction
End Sub

Public Sub CloseDB()
    Me.Conn.Close()
    Me.Conn.Dispose()
    Me.Trans.Dispose()
End Sub

Public Function ReadData(ByVal sql As String, ByVal ParamArray Obj() As Object)
    ' command Object
    Dim CMD As New OleDbCommand(sql, Me.Conn, Me.Trans)

    'add the parameters to the sql command
    Dim I As Integer
    For I = 0 To Obj.Length - 1
        CMD.Parameters.AddWithValue("@" & I, Obj(I))
    Next

    Dim R = CMD.ExecuteReader()
    'Do While R.Read

    'Loop
    Return R

End Function
End Class

Here is the Sub I use to fetch Data:

Dim connection As String = txt_util.readFromTextFile("ConnStr_2.txt", 0) + "Billing.mdb"
 'connection string is in a text file with text at line 0 as "C:\BILLSERV\"

 Private Sub searchAccnt(ByVal SIN As String)
    'clear other fields
    Clear("Acct")
    Try
        AccntDetail.DBConnect("ConcessionairesAccnt")
        'Dim RS = AccntDetail.ReadData("Select * From AllAccounts Where SIN=@0", txtSIN.Text.Trim)
        Dim RS = AccntDetail.ReadData("Select * From viewCSFAccnt Where SIN=@0", SIN)

        RS.Read()
        If RS.Hasrows = 0 Then
            MsgBox("Accounts not found. Check the SIN you Enter.", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Records not found.")
            'txtAppNo.Focus()
            Exit Sub
        Else
            'MsgBox("Accounts correct.", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Records found.")
            txtZoneNo.Text = RS.Item("Zone").ToString
            txtSeq.Text = RS.Item("SeqNo").ToString
            txtAccntName.Text = RS.Item("AccountName").ToString
            txtAccntAddress.Text = RS.Item("HouseNo").ToString + " " + RS.Item("BLDGName").ToString + " " + RS.Item("Street").ToString + _
                                   " " + RS.Item("BRGY").ToString
            txtMeterNo.Text = RS.Item("MeterNo").ToString
            txtAccntStatus.Text = varA.AccntStatus(RS.Item("Status").ToString)


            'Dim con = AccessAccnt.DBConnect(connection, "")
            'If con <> "Ok" Then
            '    MsgBox("Cannot establish a Connection to the server.", MsgBoxStyle.Critical, "Connection Lost...")
            'End If

            Dim ZoneNo = "Z" + GetChar(txtZoneNo.Text, 1) + GetChar(txtZoneNo.Text, 2) + "B" + GetChar(txtZoneNo.Text, 3) + GetChar(txtZoneNo.Text, 4)
            AccessAccnt.DBConnect(connection, "")
            Dim Acc = AccessAccnt.ReadData("SELECT * FROM " & ZoneNo & " WHERE AcctNo3=@1", txtSeq.Text)
            Acc.Read()
            txtLastReading.Text = Acc.Item("LastReading").ToString
            Acc.close()
            AccessAccnt.CloseDB()

        End If
        RS.Dispose()
        AccntDetail.CloseDB()

        dbCounter.DBConnect("ConcessionairesAccnt")
        Dim result = dbCounter.ReadData("Select Top 1 CSFNo From CSFMaster WHERE (CSFNo LIKE '%" & ctrDate() & "%') order by CSFNo Desc")
        result.read()
        If result.hasrows = 0 Then
            txtTrackingNo.Text = ctrDate() & "-" & "000001"
        Else
            txtTrackingNo.Text = counter.CtrLastItemWithChar("ConcessionairesAccnt", "CSFNo", "CSFMaster", "WHERE (CSFNo LIKE '%" & ctrDate() & "%') ORDER BY CSFNo DESC", 5)
        End If
        dbCounter.CloseDB()

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

End Sub

The Error is thrown here:

Dim Acc = AccessAccnt.ReadData("SELECT * FROM " & ZoneNo & " WHERE AcctNo3=@1", txtSeq.Text)

It Says:

System.InvalidOperationException: ExecuteReader requires an open and available connection. The connection's current state is closed.

The Other Parts of the Sub Works fine, The part where I fetch Data on my MSSQL Server database. The Problem lies in the Access data-Fetching Code.

I tried Using the code on another project (just the code where i fetch access data) I worked on other solutions. But I copy and paste my code on any form in this solution I keeps giving the Error.

I thought Maybe I closed the connection somewhere but this is the only instance I used this code in this entire project. (Just to get the last reading record.)

The Database is in the correct place (C:\BILLSERV)

I've Tried searching it here on SE but all I can see where suggestions about maybe forgetting to open the connection. I used this code before and this code works on my other solutions. I just cant seem to have it work here on this particular project. I wonder Why..

I tried running another project using this code and it works fine.

Is there a bug about Access connection on VB.net 2012, I have been using this code (DBConnForAccess Class) for about a year now and this is the first time I have encountered this error. btw I use Access 2003 because this database used to be for an old system created in VB6.

Lastly could this be because the solution came from another computer using a VB.Net Program same as mine. Because We work as a team here. Hope someone with a better knowledge on these systems could help. Thanks in advance.

EDIT

If a connection has been made, there should be a .ldb file in access which should appear. (I tested this on another project and an ldb file appear once a connection has been made, as we all know ldb file contains the data of the user using the db file.) I tried to re-run the system and while I'm using the system no ldb file was created.

Also,I thought Maybe I closed the connection somewhere but this is the only instance I opened a connection in access and which I used the code in this entire project. (Just to get the last reading record.)

So this is NOT a duplicate of “There is already an open DataReader…” Reuse or Dispose DB Connections? Just for clarifications

Answer 1

After a week of debugging and reading articles in the web. We have found the culprit of the Error.

Seems that the Target CPU Option in Advance compiler Settings was change to AnuCPU.

We noticed this after we checked the other application projects we used.

Changing it Back to x86 solves the connection problem.

I wonder why this affected the connection to the access.

All is working fine now. thank you Plutonix and Steve for all your suggestions we'll be having a change in our codes.