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
cBuilder("Persist Security Info") = True
cBuilder("Jet OLEDB:Database Password") = My.Settings.DataCode
Select Case data
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="
MainCnn = SetConnection(TableType.Main)
MainCnn.Open()
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)
Using MainCnn
Using mCommand As New OleDbCommand(sql, MainCnn)
mCommand.CommandType = CommandType.Text
MainCnn.open()
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
Return rItem
End Function