Question How do I select range of date from datagrid?

[black zero]

Here's the scenario:

I would like to select transaction id based from its creation date. All transactions are displayed on form load event on a datagrid. The select function should be executed on 2 datetimepicker (the first should be the start date, the last should be the end date).

So there are three controls on a form, 2 datetimepicker and a datagrid. I managed to load all transactions into a datagrid, and managed to select/filter those transaction based on id, but not date.

Oh, the table is called 'transaction_details', it contains 'transaction_id', 'transaction_date', and few other columns. transaction_id is the primary key.

Any help is really appreciated.

 

[jmcilhinney]

If you haven't already, I'd suggest that you bind your data to the grid via a BindingSource, i.e. bind your DataTable to the BindingSource and then bind the BindingSource to the grid.

To filter the data by date you would then do something like this:

myBindingSource.Filter = String.Format("transaction_date >= #{0:M/dd/yyyy}# AND transaction_date <= #{1:M/dd/yyyy}#", Me.startDatePicker.Value, Me.endDatePicker.Value)

 

[black zero]

BindingSource? What's that? I can find the control, but don't know how to use it properly.

You're talking to a vb net newbie. >.< So, please don't use difficult explanation.

I am using SQL Server 2000 btw.

 

[jmcilhinney]

I just told you on another forum that I'd answered you here. Small world.

A BindingSource isn't a control. It has no user interface. It's a component that makes data-binding easier. You should read the MSDN documentation for the BindingSource class and get as much information as you can. If you still need more then you can ask here but there's no point my repeating what you already have access to.

 

[black zero]

Let me recap,

I've just added a BindingSource component. Click here and there till I was able to DISPLAY ALL DATA on my Kelancaran database to datagrid. Now what's next?

And oh, there are an additional weird things called 'KelancaranBindingSource' and KelancaranTableAdapter just below my form designer now... What....?

 

[black zero]

Double Post, sorry.

Hey, I'VE SUCCEDED! Hurrahhhhhhh LONG LIVE! Yeeeeyyyyy!

The code went like this:

KelancaranBindingSource.Filter = String.Format("transaction_date >= #{0:M/dd/yyyy}# AND transaction_date <= #{1:M/dd/yyyy}#", Me.timestart.Value, Me.timeend.Value)

Just AS EXACTLY you told me to! Thanks alot!!!!

NEXT (I don't want to open new thread.)

Could you please explain a little bit the meaning of that syntax I quoted? Although I used that, I still don't understand that code, especially this line 'String.Format("transaction_date >= #{0:M/dd/yyyy}# AND transaction_date <= #{1:M/dd/yyyy}#"'

 

[jmcilhinney]

Exactly how the Filter property of the BindingSource behaves depends on the underlying data source. More often than not the data source will be a DataTable, so the Filter property corresponds to the RowFilter property of the table's DefaultView, which is type DataView. The DataView.RowFilter property requires a string that resembles a SQL WHERE clause. If you wanted to filter your data by date as you retrieved it from the database you might use a SQL query like this:

SELECT *
FROM Kelancaran
WHERE [B][U]transaction_date >= @StartDate AND transaction_date <= @EndDate[/U][/B]

Hopefully you can see the similarity.

That line of code uses the String.Format method to insert values into a format string in specific ways. You should read the documentation for the String.Format method first, then ask questions if you still need more information. You should ALWAYS read the relevant documentation first and then ask questions later. There's no point our repeating what you can already read off your own machine, but we can add more specific information once we know what you still don't undertsand.

 

[black zero]

SQL Syntax is one of my weaknesses. >.< Sorry for that. And noted on that documentation advice.

Overall, you've helped me alot, thanks.