Oledb.connection.open problem in datalayer

See more:

VB

OleDb

connection

Hi all,

I have a solution called 'AusNet' in VS 2013, written in Visual Basic. I am running Windows 10 64bits and building the solution for any CPU platform.

One of the projects is a datalayer. When I set this as the startup project with the form 'test' as the startup object, it runs like a charm.

This is the code behind my form:

Imports System.Data.OleDb

Public Class test

    Private Sub btnMain_Click(sender As Object, e As EventArgs) Handles btnMain.Click
        Process(Helpers.SetConnection(Helpers.TableType.Main))
    End Sub

    Private Sub btnTabels_Click(sender As Object, e As EventArgs) Handles btnTabels.Click
        Process(Helpers.SetConnection(Helpers.TableType.Tables))
    End Sub

    Private Sub btnCRM_Click(sender As Object, e As EventArgs) Handles btnCRM.Click
        Process(Helpers.SetConnection(Helpers.TableType.Crm))
    End Sub

    Private Sub Process(MainCnn As OleDbConnection)
        ProgressBar1.Value = 0
        lblInfo.Text = ""
        Timer1.Start()
        MainCnn.Open()
        Dim Line As String = "Database " & MainCnn.DataSource & " is " & MainCnn.State.ToString & vbCrLf
        Line &= ", Provider = " & MainCnn.Provider & vbCrLf
        Line &= ", Server = " & MainCnn.ServerVersion & vbCrLf
        Dim Restrictions() As String = New String(3) {}
        Restrictions(3) = "Table"
        Dim UserTables As DataTable = Nothing
        UserTables = MainCnn.GetSchema("Tables", Restrictions)
        Line &= ", " & UserTables.Rows.Count & " DataTabels found: " & vbCrLf
        For i = 0 To UserTables.Rows.Count - 1
            Line &= "   - " & serTables.Rows(i)(2).ToString & vbCrLf
        Next
        MainCnn.Close()
        lblInfo.Text = Line
        Timer1.Stop()
        ProgressBar1.Value = 100
    End Sub

    Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
        If ProgressBar1.Value = 100 Then ProgressBar1.Value = 0
        ProgressBar1.Value += ProgressBar1.Step
    End Sub
End Class

The Helpers class in the datalayer looks like this:

Imports System
Imports System.Data
Imports System.Data.Common
Imports System.Data.OleDb
Imports System.Runtime.InteropServices
Imports System.Xml
Imports System.Environment
Imports Microsoft.VisualBasic
Imports AusNet.BusinessObjects

Public Class Helpers

    Public Enum TableType
        Main = 0
        Tables = 1
        Crm = 2
        Accounting = 3
    End Enum

    Private Shared cnStr As String = ""
    Private Shared Cnn As OleDbConnection = Nothing

    Friend Shared Function SetConnection(ByVal Data As TableType) As OleDbConnection
        Cnn = New OleDbConnection(GetConnectionString(Data, Nothing))
        Return Cnn
    End Function

    Friend Shared ReadOnly Property Connection(ByVal Data As TableType) As OleDbConnection
        Get
            If Cnn Is Nothing Then
                Cnn = SetConnection(Data)
            End If
            Return Cnn
        End Get
    End Property

    Friend Shared ReadOnly Property DataSource As String
        Get
            Return Cnn.DataSource
        End Get
    End Property

    Friend Shared ReadOnly Property Provider As String
        Get
            Return Cnn.Provider.ToString
        End Get
    End Property

    Friend Shared ReadOnly Property Directory As String
        Get
            Return Cnn.DataSource.Substring(0, Cnn.DataSource.LastIndexOf("\"))
        End Get
    End Property

    Friend Shared ReadOnly Property Password As String
        Get
            Return cnStr.Substring(cnStr.LastIndexOf("Password=") + 9)
        End Get
    End Property

    Private Shared Function GetConnectionString(ByVal data As TableType, company As BusniessObjects.Company) As String
        Dim cBuilder As New OleDbConnectionStringBuilder
        Dim subDir As String = ""
        
        If Not company Is Nothing Then subDir = company.Code & "\"
        cBuilder("Provider") = My.Settings.DataProvider
        'Returns "Microsoft.Jet.OLEDB.4.0"
        cBuilder("Persist Security Info") = True
        cBuilder("Jet OLEDB:Database Password") = My.Settings.DataCode
        'Returns the password
        Select Case data
        'My.Settings.DataDirectory returns "C:\Visual Studio\Projects\AusNet\AusNet.DataLayer\DataFiles\"
        'My.Settings.DataXXXFile returns the name of the database to open, with .mdb suffix
            Case TableType.Main
                cBuilder("Data Source") = Replace(My.Settings.DataDirectory & My.Settings.DataMainFile, "\\", "\")
            Case TableType.Crm
                cBuilder("Data Source") = Replace(My.Settings.DataDirectory & subDir & My.Settings.DataCrmFile, "\\", "\")
            Case TableType.Tables
                cBuilder("Data Source") = Replace(My.Settings.DataDirectory & subDir & My.Settings.DataTabFile, "\\", "\")
            Case TableType.Accounting
                cBuilder("Data Source") = Replace(My.Settings.DataDirectory & subDir & My.Settings.DataAccFile, "\\", "\")
        End Select

        Return cBuilder.ConnectionString
    End Function

End Class

The code in the form (test.vb) opens the connection and shows all the requested information.

WHen I set my UIlayer as startup project, it checks if a user is present. If not it opens the frmLogin form in the Security Project. After filling out the usercode this forms sents a request to the businesslayer witch passes the request to the datalayer. This works all fine. In the datalayer my Userdata, using the same helper as above, stops the application when the MainCnn.Open() is called:

mports System
Imports System.Data
Imports System.Data.OleDb
Imports AusNet.DataLayers.Helpers
Imports AusNet.BusinessObjects

Public Class UserData

...

    Public Function UsersGetByCode(userCode As String) As User
        Dim rItem As New User
        Dim MainCnn As OleDbConnection = New OleDbConnection
        Dim sql As String = "SELECT * FROM Users WHERE Code="'" & userCode & "'"

        MainCnn = SetConnection(TableType.Main)

        'DataTable Connection
        MainCnn.Open()
        ' ===> HERE THE APPLICATION STOPS RUNNING <===

        Using mCommand As New OleDbCommand(sql, MainCnn)
            mCommand.CommandType = CommandType.Text
            Using mReader As OleDbDataReader = mCommand.ExecuteReader
                If mReader.Read Then
                    rItem = FillDataRecord(mReader)
                End If
            End Using
            mCommand.Dispose()
        End Using
        MainCnn.Close()
        Return rItem
    End Function

    Private Function FillDataRecord(ByVal DataRecord As IDataRecord) As User
        Dim returnUser As User = New User
        With returnUser
            .Id = DataRecord.GetInt32(DataRecord.GetOrdinal("ID"))
            .Code = DataRecord.GetString(DataRecord.GetOrdinal("Code"))
            .Name = DataRecord.GetString(DataRecord.GetOrdinal("Naam"))
            .SurName = DataRecord.GetString(DataRecord.GetOrdinal("Voornaam"))
            .Password = DataRecord.GetString(DataRecord.GetOrdinal("pwPaswoord"))
            .Email = DataRecord.GetString(DataRecord.GetOrdinal("Email"))
            .Phone = DataRecord.GetString(DataRecord.GetOrdinal("Telefoon"))
            .Language = DataRecord.GetInt32(DataRecord.GetOrdinal("Taal"))
        End With
        Return returnUser
    End Function

End Class

Any ideas why this happens, however it is the same datalayer project where the test form runs well? Thanks for your help.

What I have tried:

First the UserData class was opening the connection with a using clause. Removing this helped solving the 'connection already opened exclusively by an other user'-error but created the above descibed problem.

Public Function UsersGetByCode(userCode As String) As User
    Dim rItem As New User
    Dim MainCnn As OleDbConnection = New OleDbConnection
    Dim sql As String = "SELECT * FROM Users WHERE Code='" & userCode & "'"

    MainCnn = SetConnection(TableType.Main)

    'DataTable Connection
    Using MainCnn 'Now removed
    Using mCommand As New OleDbCommand(sql, MainCnn)
        mCommand.CommandType = CommandType.Text
        MainCnn.open() 'Placed outside the using mCommand loop
        Using mReader As OleDbDataReader = mCommand.ExecuteReader
            If mReader.Read Then
                rItem = FillDataRecord(mReader)
            End If
        End Using
        mCommand.Dispose()
    End Using
    MainCnn.Close()
    End Using 'Now removed
    Return rItem
End Function