Imports System.Data.OleDb
Imports System.IO
Module modCourses
Public Function CreateCourse(ByVal DBDirectory As String) As Boolean
Dim DB As New ADOX.Catalog()
Try
With frmNewEditCourse
DB.Create(Connection.ConnectionString)
SQL = "CREATE TABLE [Information] ([CIndex] AutoIncrement, [CourseCode] TEXT(50), [RoomNumber] TEXT(50), [SchoolYear] TEXT, [Teacher] Text)"
Command = New OleDbCommand(SQL, Connection)
Command.Connection.Open()
Command.ExecuteNonQuery()
Command.Connection.Close()
SQL = "INSERT INTO [Information] (CourseCode, RoomNumber, SchoolYear, Teacher) VALUES (?, ?, ?, ?); "
Command = New OleDbCommand(SQL, Connection)
Command.Connection.Open()
Command.Parameters.AddWithValue("CourseCode", .txtCourseCode.Text)
Command.Parameters.AddWithValue("RoomNumber", .txtRoomNumber.Text)
Command.Parameters.AddWithValue("SchoolYear", .txtSchoolYear.Text)
Command.Parameters.AddWithValue("Teacher", .txtTeacher.Text)
Command.ExecuteNonQuery()
Connection.Close()
frmCourses.lstCourses.Items.Add(.txtCourseCode.Text)
End With
Catch Excep As System.Runtime.InteropServices.COMException
Finally
DB = Nothing
Connection.Close()
End Try
End Function
Public Sub NewCourse()
With frmNewEditCourse
DatabaseName = Application.StartupPath & "\Data\Courses\" & .txtCourseCode.Text & ".mdb"
Connection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DatabaseName)
If File.Exists(DatabaseName) = True Then
frmCourses.lblStatus.Text = "Error: Course " & .txtCourseCode.Text & " Already Exists."
Else
CreateCourse(DatabaseName)
frmCourses.lblStatus.Text = "New Course Sucessfully Created."
End If
Connection.Close()
End With
End Sub
Public Sub EditCourse()
End Sub
Public Sub DeleteCourse()
With frmCourses
Dim CourseName As String = .lstCourses.SelectedItem
If .lstCourses.SelectedIndex >= 0 Then
If MessageBox.Show("Are you sure you want to delete " & CourseName & "?", "Delete Course: " & CourseName & "?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
File.Delete(Application.StartupPath & "\Data\Courses\" & CourseName & ".mdb")
.lstCourses.Items.RemoveAt(.lstCourses.SelectedIndex)
End If
Else
MessageBox.Show("Please select a course to delete", "Select Course to Delete.")
End If
End With
End Sub
Public Sub LoadToListCourse()
With frmCourses
Dim FileNames() As String = Directory.GetFiles(Application.StartupPath & "\Data\Courses\")
Dim FileName As String
For i As Integer = 0 To FileNames.Length - 1
FileName = Path.GetFileName(FileNames(i))
If Path.GetExtension(FileName) = ".mdb" Then
Connection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileNames(i))
SQL = "SELECT CourseCode FROM Information"
Command = New OleDbCommand(SQL, Connection)
Command.Connection.Open()
Reader = Command.ExecuteReader()
While Reader.Read = True
.lstCourses.Items.Add(Reader("CourseCode").ToString())
End While
Command.Connection.Close()
End If
Next
End With
End Sub
Public Sub LoadToEditCourse()
With frmNewEditCourse
Dim FileName As String = Application.StartupPath & "\Data\Courses\" & frmCourses.lstCourses.SelectedItem & ".mdb"
Connection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileName)
SQL = "SELECT * FROM Information"
Adapter = New OleDbDataAdapter(SQL, Connection)
DS = New DataSet()
Adapter.Fill(DS)
Dim DRow As DataRow = DS.Tables(0).Rows(0)
If DS.Tables(0).Rows.Count > 0 Then
.txtCourseCode.Text = DRow.Item("CourseCode").ToString()
.txtRoomNumber.Text = DRow.Item("RoomNumber").ToString()
.txtSchoolYear.Text = DRow.Item("SchoolYear").ToString()
.txtTeacher.Text = DRow.Item("Teacher").ToString()
End If
Connection.Close()
End With
End Sub
End Module
