Solution of Report Daywise Weekwise & Itemwise

Imports System.Data.OleDb

Imports System.Data

Public Class Form1

    Dim cn As OleDbConnection

    Dim da As OleDbDataAdapter

    Dim ds As DataSet

   

'Daywise Report

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Report1.mdb;Persist Security Info=True")

        da = New OleDbDataAdapter("SELECT BillDate AS [BillDate By Day], Sum(Rest.TotalBill) AS [Sum Of TotalBill] FROM Rest GROUP BY BillDate", cn)

        ds = New DataSet

        da.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)

    End Sub

'Weekwise Report

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

'To Find Week      

        Dim sdate As Date = New Date(Today.Year, Today.Month, 1)

        Dim edate As Date = sdate.AddDays(6)

        While (edate.Month = Today.Month)

            cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Report1.mdb;Persist Security Info=True")

            da = New OleDbDataAdapter("SELECT Sum(TotalBill) FROM Rest Group by BillDate Having BillDate >= #" & sdate & "# and BillDate <= #" & edate & "#", cn)

            ds = New DataSet

            da.Fill(ds)

            DataGridView1.Columns.Add("Week", "Week")

            DataGridView1.Columns.Add("Total", "Total Collection")

            If ds.Tables(0).Rows.Count > 0 Then

'Dynamically add rows to gridview               

   DataGridView1.Rows.Add()

   DataGridView1.Rows(i).Cells(0).Value = sdate.ToString & "-" &  edate.ToString

   DataGridView1.Rows(i).Cells(1).Value = ds.Tables(0).Rows   (i).Item(1).ToString

End If

'Find Next Week           

            sdate = sdate.AddDays(7)

            edate = sdate.AddDays(6)

        End While

       

    End Sub

'Itemwise Report

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Report1.mdb;Persist Security Info=True")

        da = New OleDbDataAdapter("SELECT Itemname , Sum(Rest.TotalBill) AS [Sum Of TotalBill] FROM Rest GROUP BY Itemname", cn)

        ds = New DataSet

        da.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)

    End Sub

End Class