Display Record According to Month

Question

I have a database named PriceTesting ( using Microsoft Access 2007 ) that contains a table named tbl_order with columns:

Order_ID, Customer_Name, Dress_Type, Dress_Price, Quantity, Date_Of_Pickup, Payment_Status

I've succeed display the data into datagridview using this code :-

 Private Sub dgvReportShow()

        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\annonymous\Documents\Visual Studio 2012\Projects\TMS Final\TMS Final\db\db_TMS.accdb"

        If Not con.State = ConnectionState.Open Then
            con.Open()
        End If

        Dim ds As New DataSet
        Dim dt As New DataTable
        ds.Tables.Add(dt)
        Dim da As New OleDb.OleDbDataAdapter

        da = New OleDb.OleDbDataAdapter("SELECT Order_ID, Customer_Name, Dress_Type, Dress_Price, Quantity, Date_Of_Pickup, Payment_Status, Dress_Price * Quantity as Total " & _
                                        "FROM tbl_order " & _
                                        "WHERE (Payment_Status = 'paid') ", con)

        da.Fill(dt)

        dgvReport.DataSource = dt.DefaultView

        dgvReport.SelectionMode = DataGridViewSelectionMode.FullRowSelect

End Sub

Date_Of_Pickup is displayed like this 28-Dec-13 in the datagridview (sry... i dont have enough point to post a snapshot )

Now i've added a comboboxMonth that contains month ( January, February, March,... and so on ) so that i can view the record by the selected month in the comboboxMonth

how do i convert the " 28-Dec-13 " to month so i can add

 " WHERE (Payment_Status = 'paid') AND Date_Of_Pickup = comboboxMonth.value "

can anyone help me solved this problem?

Answer 1

If you populate manually your combo with the month names in the ascendig order (January the first, February the second and so on, you could write in your SelectedIndexChanged event something like this

Private Sub cbo_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles comboBoxMonths.SelectedIndexChanged
    Dim cbo = CType(sender, ComboBox)
    Dim monthIndex = cbo.SelectedIndex
    if monthIndex <> -1 then
       Dim cmdText = "SELECT Order_ID, Customer_Name, Dress_Type, Dress_Price, Quantity, " & _
                     "Date_Of_Pickup, Payment_Status, Dress_Price * Quantity as Total " & _
                     "FROM tbl_order WHERE (Payment_Status = 'paid') AND " & _ 
                     "Month(Date_Of_Pickup) = " & (monthIndex + 1).ToString

      .......


    End if
End Sub