Generate simple html tables using Controller class design + Access db

See more:

VB

MS-Access

Hey guys,

I need to generate (reasonably) simple html reports, taking the information from an Access database.

At the moment, I have a controller called 'ClinicController' and a controller called 'OrderController' - I need to generate a report from this that includes the ClinicID from the Clinics + Orders tables and the Date Ordered + Total Price from the Orders table.


Now, I have created a 'ClinicOrderController' where the report generation code will sit. However, I am unsure how to instantiate the ClinicController + Orders Controller in such a way that I can then do a SELECT join between them, add the data the hashtable and display in an html report.

Any ideas?

Below is findById function in my 'ClinicController' to give you an idea of how i'm writing the methods. The method would be modified depending on the report generated.

'Find by ClinicID button
Public Function findById(cId As String) As List(Of Hashtable)

    'Instantiates a connection object
    Dim oConnection As OleDbConnection = New OleDbConnection(CONNECTION_STRING)
    'Instantiates a list of hashtables
    Dim lsData As New List(Of Hashtable)

    Try
        Debug.Print("Connection string: " & oConnection.ConnectionString)

        oConnection.Open()
        Dim oCommand As OleDbCommand = New OleDbCommand
        oCommand.Connection = oConnection

        'Stored in the CommandText property of the command object
        oCommand.CommandText = "SELECT * FROM clinics WHERE clinic_id = ?;"
        oCommand.Parameters.Add("ClinicID", OleDbType.Integer, 8)
        oCommand.Parameters("ClinicID").Value = CInt(cId)
        'Compiles the prepared statement
        oCommand.Prepare()
        'Executes the SQL statement and stores the results in data reader object
        Dim oDataReader = oCommand.ExecuteReader()

        'Process data set in Hashtable
        Dim htTempData As Hashtable
        Do While oDataReader.Read() = True
            htTempData = New Hashtable
            htTempData("ClinicID") = CStr(oDataReader("clinic_id"))
            htTempData("ClinicName") = CStr(oDataReader("clinic_name"))
            htTempData("ClinicAddress") = CStr(oDataReader("address"))
            htTempData("ClinicFirstName") = CStr(oDataReader("contact_firstname"))
            htTempData("ClinicLastName") = CStr(oDataReader("contact_lastname"))
            htTempData("ClinicEmail") = CStr(oDataReader("email"))
            htTempData("ClinicPhone") = CStr(oDataReader("phone"))
            lsData.Add(htTempData)
        Loop

        Debug.Print("The record was found.")

    Catch ex As Exception
        Debug.Print("ERROR:" & ex.Message)
        MsgBox("An error occured!")
    Finally
        oConnection.Close()
    End Try

    'Return list of hashtables to the calling function
    Return lsData

End Function