I am using Visual Studio .NET Framework and vb.net programming language, I've been struggling to fix my error for about a week now. I used MS Access Database (.accdb) as my Database.
Module1.vb:
Imports System.Data.OleDb
Module Module1
Public cn As New OleDbConnection
Public cm As New OleDbCommand
Public dr As OleDbDataReader
Sub ConnectToDatabase()
Try
Dim startupPath As String = AppDomain.CurrentDomain.BaseDirectory
Dim connectionString As String = $"Provider=Microsoft.JET.OLEDB.4.0;Data Source={startupPath}\OSA_Records.accdb"
Using cn As New OleDbConnection(connectionString)
cn.Open()
End Using
Catch ex As Exception
Console.WriteLine("ConnectToDatabase Error: " & ex.ToString())
MsgBox(ex.Message, vbCritical)
End Try
End Sub
End Module
I also have already called the method in the 'frmMain.vb' Load Event frmMain.vb:
Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
ConnectToDatabase()
LoadRecordsStudent()
LoadRecordsViolation()
LoadRecordsProgram()
LoadRecordsSection()
LoadRecordsAcademicYear()
AutoSuggestStudent()
End Sub
I already tried all of the youtube videos that are possibly related to my issue but its still not working, I hope someone can help me fix my problem.
If I still need to provide any relevant information that will help fix my issue, please do let me know. Thank you very much!
In your module, you have this line:
Public cn As New OleDbConnection
That declares a field, creates a new connection object WITHOUT a connection string and assigns the object to the field. You then have this code:
Using cn As New OleDbConnection(connectionString)
cn.Open()
End Using
That is actually declaring a different local variable, creating a new connection object WITH a connection string, assigning the object to the variable, opening the connection and then closing it again. None of that has any effect whatsoever on your field and the connection object assigned to it.
Firstly, why are you creating a new object in the first snippet at all if you are then just going to create a new object in the second snippet? Don't create new objects that you have no intention of using.
As for the issue, it's the fact that you are introducing a local variable in the method when, presumably, you think you're using the field with the same name. While the architecture you're using is not great, lets stick with it for now. In that case, you should change the first snippet to create the connection object WITH a connection string:
Public cn As New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;Data Source=|DataDirectory|\OSA_Records.accdb")
and then, in your method, use that field and that object:
Try
cn.Open()
cn.Close()
Catch
'...
End Try
Note the use of "|DataDirectory|"
for the folder path in the connection string, that allows you to put a connection string somewhere without the need for code to set the database folder path. It will resolve to the same value as Application.StartupPath
for a WinForms app, which is what you should having been using in code.