Select row from DataView querying with 'Date'

[nsabarisri6]

Hi
I have a table which contains a date column called 'PurchaseDate'
I have a list box which displays the months. When I click a month , I need to query the dataSource and collect the rows which have the purchase date in the 'SelectedMonth'.
dv2 = New DataView(ds.Tables(0), "PurchaseDate LIKE '" & SelectedMonth & "/%'", "BillNo", DataViewRowState.CurrentRows)

This code is not working . Because.. here 'PurchaseDate' is in Date format like 'MM/DD/YYYY'.I think I need to convert the date into string before using 'LIKE' operator. I also tried using as below..even then it dint go fine..
dv1 = New DataView(ds.Tables(0), "convert(varchar2(20),PurchaseDate,103) LIKE '" & SelectedMonth & "/%'", "BillNo", DataViewRowState.CurrentRows)
Here 'SelectedMonth' will be a string like '01', '10'..
Kindly help me in this regard..

Thanks,
Sabarisri.N

 

[IanRyder]

Hi,

Here is a way that you can set the RowFiler of the DataView using a ComboBox. In this case I have used the a ComboBox to select all records in the current year with the selected month. I have also included the FilterClause in a MsgBox for the moment so you can see what has been constructed.

  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    'Dim myDateFormat As String = "MM/dd/yyyy hh:mm:ss"
    Dim myDateFormat As String = "dd/MM/yyyy hh:mm:ss"
 
    Dim sqlConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
    Dim myDA As New SqlDataAdapter("Select * from Orders", sqlConn)
    Dim myDS As New DataSet
    Dim myDV As DataView
 
    myDA.Fill(myDS, "myDataView")
    Dim FilterClause As String = String.Format("ShippedDate >= '{0}' and ShippedDate <= '{1}'",
                       New DateTime(Now.Year, CInt(ComboBox1.Text), 1, 0, 0, 0).ToString(myDateFormat),
                       New DateTime(Now.Year, CInt(ComboBox1.Text), 1, 23, 59, 59).AddMonths(1).AddDays(-1).ToString(myDateFormat))
    MsgBox(FilterClause)
    myDV = New DataView(myDS.Tables(0), FilterClause, "ShippedDate", DataViewRowState.CurrentRows)
    DataGridView1.DataSource = myDV
  End Sub

You may need to change the date format in the first few lines of the code to match your current local.

Hope that helps.

Cheers,

Ian

 

[nsabarisri6]

Thank you Ian!!