filtering date ranges

[jberger]

I'm having a problem filtering date ranges. Here's what I have:

Dim dv As New DataView(ds.Tables("NameOfTable"))
            Dim x As Integer = dv.Count

            dv.Sort = "Date"
            dv.RowFilter = "Date >= #" & DatePicker1.Value & "#"
            dv.RowFilter = "Date <= #" & DatePicker2.Value & "#"
            NameOfDataGridOnForm.DataSource = dv

When I choose a date range(ex. 03/01/2006 - today), it just gives me all records, even the ones before the beginning date.

 

[cjard]

use short date format MM/dd/yyyy

dtp.Value.ToShortDateString 'or soemthing like that

 

[jberger]

Thanks for the help. I found what worked for me:

dv.RowFilter = "Date >= '#" & DatePicker1.Value & "#' and " & "Date <= '#" & DatePicker2.Value & "#'"

 

[girl]

hihi

this method above is for datepicker? how about month calendar?? i hope to do the following


sqladapter.SelectCommand = New OdbcCommand("SELECT * from " & table & " where date between '05/01/2007' and '05/02/2007'", conn)

i hope to get data from the range of dates from the database.

---------------------------------------------------------------------

i had use datepart to extract the dates from the monthcalendar. these are the codes

Dim date2 As String = "datepart(MM,DATE) = " & Label3.Text & " AND datepart(d,DATE) = " & Label2.Text & " AND datepart(yyyy,date)=" & Label8.Text & ""
Dim date1 As String = "datepart(MM,DATE) = " & Label6.Text & " AND datepart(d,DATE) = " & Label5.Text & " AND datepart(yyyy,date)=" & Label7.Text & ""

where label.text are the extracted dates from the month calendar.

the problem is i am able to retrieve the data if i use the following codes

1. sqladapter.SelectCommand = New OdbcCommand("SELECT * from " & table & " where date between '05/01/2007' and '" & date2 & "'", conn)

2. sqladapter.SelectCommand = New OdbcCommand("SELECT * from " & table & " where date between '"& date1 &"' and '05/01/2007', conn)


BUT when i combine the date1 and date 2 it didnt retrieve any results


1. sqladapter.SelectCommand = New OdbcCommand("SELECT * from " & table & " where date between '"& date1 &"' and '"& date2 &"' , conn)

what should i do??

thnx for helping!!

 

[cjard]

Hio

I'm afraid that using the .Filter or .RowFilter properties has nothing to do with using a select statement to download data from a database. Your query formation isnt correct, and we really, really dont write SQLs like that any more.. Better ways have been available for at least 10 years, maybe 15.

Please take a read of the PQ link in my signature, then also read the DW2 link, section on "creating a form to search data"


The other thing you need to appreciate, is that '10/05/2007' is a STRING, not a DATE. It can be parsed into a date, but is it the 5th of Oct, or the 10th of May? Who knows.. THis is another reason why writing queries like this is somewhere between BAD and WRONG..

You should never again consider writing an SQL how you currently do.. :/

The referenced links will teach you how to write good, quick, secure SQLs. Enjoy your read


Lastly, do you really actually use .NET 3.5?

 

[girl]

hi

thnx for reply. oops that was an error actually when registering. m using vb studio 2005.

i read thru the datawalkthru and understand some concept on data table adaptor and its usage. did try out in vb.net and was useful!! thnx for that.

but i had a slight confusion as i am intending to use treeview to list some table names. when i click on the table name, i want to extract data from the database to display.

if i use dataset, what i read from the walkthrough, a drag and drop to the form will do . but how do i select the table name from the treeview? i have 71 tables.

this is the drag and drop code
Me.abcTableAdapter.Fill(Me.StatisticsDataSet.abc) where abc is my tablename that was dragged and dropped into the form.

can i edit from here? or there are more to the problem.


please correct me if i had misintepret anything wrongly or miss out anithing.

n lastly thnx you!!

 

[cjard]

if i use dataset, what i read from the walkthrough, a drag and drop to the form will do . but how do i select the table name from the treeview? i have 71 tables.

You dont. Treeview is not Data Binding enabled. You must pull the data into a datatable and then put it manually into the treeview.

this is the drag and drop code
Me.abcTableAdapter.Fill(Me.StatisticsDataSet.abc) where abc is my tablename that was dragged and dropped into the form.

can i edit from here?

Edit it to your heart's content

The main thing is, you download info from the database and hold it locally.. You can code your treeview so it mimicks a bound control:

The dra/drop the table and the following appear:
DataGridView
BindingSource
TableAdapter
DataSet

COde is added to fil lthe set.

Delete the grid only
Add a treeview
Add code to popualte it with nodes. In the .Tag of each node, store the .Index of the Bindingsource.List(...) item (use a for loop, for i as integer = 0 to BindSource.List.Count - 1... add the node, index is i..)
When the user clicks a node, retrieve the tag, convert it tint, and say BindSource.Position = theRetrievedInt

All other databound fields attached to that BindSource will change to show the row contents