[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

Date Query in a Database

I am using Access and Vb.net to develop a application. What is the best way to query for a date and see if it falls within the last 10 days.I need to send that information in an email.

Thanks,
0
karthikram
Asked:
karthikram
  • 2
1 Solution
 
davidrichardsonCommented:
This will Query between the dates and export to excell


    Private Sub ExtractData()
        'Initializes Excel and creates a new workbook/worksheet
        Dim excelApp As New Excel.Application
        Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
        Dim excelWorksheet As Excel.Worksheet = _
            CType(excelBook.Worksheets(1), Excel.Worksheet)
        'Makes Excel invisible to the user until spreadsheet is populated
        excelApp.Visible = False
        With excelWorksheet
            'Creates connections to pull data to DataSet
            Dim ds As New DataSet("DataSetName")
            Dim dr As DataRow
            Dim eDate As Date
            eDate = Now()
            Dim dDate As Date
            dDate = DateAdd("d", -10, (Now))

            Dim myConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Production DBF Files\Data.mdb;Persist Security Info=False")
            Dim myAdapter As New OleDb.OleDbDataAdapter
            Dim myCommand As New OleDb.OleDbCommand("SELECT  [Date], SalesOrder  FROM   TblOutstandingOrders  where Date BETWEEN # " & dDate & " # AND # " & eDate & " #  ")
            Dim i As Integer = 2
            'Bind myAdapter to myCommand
            Try
                myCommand.CommandType = CommandType.Text
                myAdapter.SelectCommand = myCommand
                myAdapter.SelectCommand.Connection = myConnection
                myAdapter.Fill(ds)
            Catch ex As Exception

            End Try
            'Format cell headings
            .Range("A1").Value = " Date "
            .Range("A1").Font.Bold = True
            .Range("A1").ColumnWidth = 10
            .Range("B1").Value = " Order "
            .Range("B1").Font.Bold = True
            .Range("B1").ColumnWidth = 35.71


            'Populate Excel spreadsheet
            Try

                For Each dr In ds.Tables(0).Rows
                    .Range("A" & i.ToString).Value = dr("date")
                    .Range("B" & i.ToString).Value = dr("salesorder")


                    i += 1
                Next
            Catch ex As Exception

            End Try
            'Make Excel visible
            excelApp.Visible = True

        End With

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        ExtractData()
    End Sub
0
 
karthikramAuthor Commented:
IS there a way to save the excell spreadsheet without opening ? I want to sent it a a attachment.
thanks,
0
 
davidrichardsonCommented:
Replace

'Make Excel visible
excelApp.Visible = True

With

excelWorksheet.SaveAs("C:\Test")
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now