0

I am trying to set up a database with Students, Courses and grades.
This is the main bulk of my code, I am stuck with the error below, could anybody help me?

Imports System.Data.OleDb
Imports ADODB
Public Class Form_Main
    Dim cn As OleDbConnection
    Dim StudentUpdateCommand As New OleDbCommand
    Dim CourseUpdateCommand As New OleDbCommand
    Dim GradeUpdateCommand As New OleDbCommand
    Dim cmd As OleDbCommand
    Dim DA As OleDbDataAdapter
    Dim DS As DataSet
    Dim Tables(2) As String
    Dim StudentTable, CourseTable, GradeTable As DataTable
    Dim StudRow As DataRow
    Dim Index, StartItem, CurrentCust, NoStudent, NoGrade As Integer
    Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\TestDataBase.mdb;"
    Dim strSQL As String
    Dim objDB As New ADOX.Catalog
    Dim objConn As New ADODB.Connection
    Dim objCmd As New ADODB.Command

    Public Sub DataBaseCreator()
        'Subroutine to create a Jet engine V4.0 DB

        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\TestDataBase.mdb;"
        ' ------------------------
        ' Create New ADOX  Database Object
        ' ------------------------
        Try
            objDB.Create(strConn)
        Catch ex As Exception
            'Need to change this
        End Try

        objConn.ConnectionString = strConn
        objConn.Open()

        ' ------------------------
        ' Create Grades Table
        ' ------------------------

         strSQL = "CREATE TABLE tblGrades " & _
            "(GradID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
            "Grade CHAR(3), " & _
            "DateCreated DATE DEFAULT NOW);"
        objCmd.CommandText = strSQL
        objCmd.ActiveConnection = objConn
        objCmd.Execute()
        MessageBox.Show("tblGrades created.")
        ' ------------------------
        ' Create Student Table
        ' ------------------------

          strSQL = "CREATE TABLE tblStudents " & _
          "(StudID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
          "GradID LONG REFERENCES tblGrades (GradID) ON DELETE SET NULL,  " & _
          "FirstName TEXT(60), " & _
          "SurName TEXT(60), " & _
          "DateOfBirth DATE, " & _
          "DateLastChecked DATE, " & _
          "DateLastReviewed DATE, " & _
          "DateCreated DATE DEFAULT NOW);"
        objCmd.CommandText = strSQL
        objCmd.ActiveConnection = objConn
        objCmd.Execute()
        MessageBox.Show("tblStudent created.")

        ' ------------------------
        ' Create Courses Table
        ' ------------------------

        strSQL = "CREATE TABLE tblCourses " & _
            "(StudID LONG REFERENCES tblStudents (StudID) ON DELETE SET NULL,  " & _
            "GradID LONG REFERENCES tblGrades (GradID) ON DELETE SET NULL,  " & _
            "CourseName TEXT(20), " & _
            "DateCreated DATE DEFAULT NOW);"
        objCmd.CommandText = strSQL
        objCmd.ActiveConnection = objConn
        objCmd.Execute()
        MessageBox.Show("tblCourses created.")

        Dim tbl As ADOX.Table       'Each Table in Tables.
        Dim col As ADOX.Column      'Each Column in the Table.

        'Point the catalog to the current project's connection.
        objDB.ActiveConnection = objConn

    End Sub


     Private Sub Button_TestDebug_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button_TestDebug.Click

        'Form connection string and open the database connection
        'cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=TestDataBase.accdb;Persist Security Info=False;")

        Try
            DataBaseCreator()
        Catch ex As Exception
            MessageBox.Show("DB Allrdy there")
        End Try

        MessageBox.Show("DONE")
        'Define student DA and fill the DS with DB info
        DA = New OleDbDataAdapter
        DS = New DataSet
        cmd = New OleDbCommand
        cmd.Connection = cn
        DA.SelectCommand = cmd
        Tables(0) = "Students"
        Tables(1) = "Courses"
        Tables(2) = "Grades"
'Call a generic routine to fill the DS tables
        For i = 0 To Tables.GetUpperBound(0)
            DA.SelectCommand = New OleDbCommand("Select * from " & Tables(i), cn)
            DA.SelectCommand.Connection = cn
            DA.Fill(DS, Tables(i))
        Next
End sub
End Class

This next section of code produces this error:
"Fill: SelectCommand.Connection property has not been initialized."
because of this line

DA.Fill(DS, Tables(i))

In this section

'Call a generic routine to fill the DS tables
        For i = 0 To Tables.GetUpperBound(0)
            DA.SelectCommand = New OleDbCommand("Select * from " & Tables(i), cn)
            DA.SelectCommand.Connection = cn
            DA.Fill(DS, Tables(i))
        Next
2
Contributors
1
Reply
2
Views
5 Years
Discussion Span
Last Post by hericles
0

From what I can see here the error is occurring because the line that actually sets your cn variable to the real instance of the connection object is commented out. Without that line your cn variable is null.

'cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=TestDataBase.accdb;Persist Security Info=False;")
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.