Using Fill() with .mdb to pass dataTable to Sub

Question

User793909578 posted

I give up...  Signing up to that truck driving school in about an hour.

I have a function that is a hack of the code found at http://msdn.microsoft.com/en-us/library/ms228044%28v=VS.85%29.aspx.  I am using a .mdb to pass a table to a Sub that fills OnDayRender of the Calendar control but I can't figure out how to pass the dataTable to it.  Here is my code;

    Function GetCurrentMonthData(ByVal firstDate As DateTime, ByVal lastDate As DateTime) As DataSet

        Dim dsMonth As New DataSet
        Dim sConnectionString, sSQL As String
        'Dim strConnection As String

        'SQL Connection String
        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/CalendarDb/app_Data/CalendarDb.mdb") & ";User Id=admin;Password=;"
        Dim objConnection As New OleDbConnection(sConnectionString)
        objConnection.Open()
        Dim conn As New OleDbConnection(sConnectionString)
        sSQL = "select * from tblEvents WHERE eventDate >= firstDate AND eventDate < lastDate"

        'Create the Command object
        Dim objCommand As OleDbCommand
        objCommand = New OleDbCommand(sSQL, objConnection)

        Try
            ' WTF do I do now???
        Catch
        End Try
        Return dsEvents
    End Function

    Protected Sub Calendar1_DayRender(ByVal sender As Object, _
            ByVal e As System.Web.UI.WebControls.DayRenderEventArgs) _
            Handles Calendar1.DayRender
        Dim nextDate As DateTime
        If Not dsEvents Is Nothing Then
            For Each dr As DataRow In dsEvents.Tables(0).Rows
                nextDate = CType(dr("eventDate"), DateTime)
                If nextDate = e.Day.Date Then
                    e.Cell.BackColor = System.Drawing.Color.Pink
                End If
            Next
        End If
    End Sub

What I can't figure out is how to convert the Fill() so that it passes the returned rows to the sub.

Answer 1

User793909578 posted

Found a fix, changing the SQL to handle passed dates was the biggest issue.

   Function GetCurrentMonthData(ByVal firstDate As DateTime, ByVal lastDate As DateTime) As DataSet

        Dim sConnectionString, sSQL As String

        'SQL Connection String
        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/CalendarDb/app_Data/CalendarDb.mdb") & ";User Id=admin;Password=;"
        Dim objConnection As New OleDbConnection(sConnectionString)
        'SQL parameters
        sSQL = "SELECT * FROM tblEvents WHERE eventDate >= #" & firstDate & "# And eventDate < #" & lastDate & "#"
        Dim Table As New DataSet
        Dim Adpt As New OleDbDataAdapter(sSQL, objConnection)
        Table.Clear()

        Try
            Adpt.Fill(Table)
        Catch
        End Try

        Return Table
    End Function

Answer 2

User-1199946673 posted

But you better use parameters:

http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

And regarding date(time)s and Access:

http://www.mikesdotnetting.com/Article/92/MS-Access-Date-and-Time-with-ASP.NET