searching data between multiple combobox

[Yeonng]

Hi, I'm a beginner in Vb.net. I have a problem like this.
My table name in dtabase is tblsales

and i have 4 combobox in month and year

i need to find data between this 4 combobox. And this is the code i've tried before.

 For a As Integer = 0 To 11
            Dim b As DateTime
            ComboBox1.Items.Add(Format(b.AddMonths(a), "MMMM"))
        Next
        ComboBox1.SelectedIndex = 0

that one to fill the combobox with months and this code below to search the data

DA = New SqlDataAdapter("select * from tblpenjualan where month >='" & ComboBox2.Text & "' And month <='" & ComboBox4.Text & "' And year >='" & ComboBox3.Text & "' And year <='" & ComboBox5.Text & "'",CONN)
        DS = New DataSet
        DA.Fill(DS)
        DGV.DataSource = DS.Tables(0)
        DGV.ReadOnly = True

and it doesn't work. when i select between february 2006 and march 2006, it will also show data in january.
and when i select data between January 2006 and March 2006, it will not show data in february. I think it search data based on the first letter like 'J'anuary and 'M'arch..

please help with this...

[tinstaafl]

It looks to me if the dates are stored as strings you'll have to convert them to a date type to be able to compare the months like that.

[wrathness]

Why not get rid of the year and month table entries and make a new one called date and its data type will be Date/Time. Then when you could use a query like this :

Select * from table
where date between 'YYYY-MM-DD' and 'YYYY-MM-DD'

Hope this helps

[Yeonng]

do you mean the one in the dbase?? I've already delete all the record and change my month and year coloumn into datetype and try to input the data again using my inputform but it not works anymore like before. it says 'Conversion failed when converting date and/or time from character string.'

[Yeonng]

hi wrathness,
i need to search all the data in that month not only a specific date. because i don't need the date, just the month and year.

[tinstaafl]

In your routine to add the data, parse the date string to be a date type, then you parse the comboboxes text to a date type and your comparison will be meaningful. The date type has properties for month and year, you can easily compare those properties and ignore the day property. The date types automatically store the month as an integer, but you can easily display them as names with the ToString method, if necessary.

[Yeonng]

Thank you so much, but can you please show me the code? bcause, i'm only a beginner.. Thanks...

[tinstaafl]

When you're adding the data to the database use the Date.Parse method. Simply build a string using a common date format for instance:

Dim tempdate As Date = Date.Parse(Month & " " & Day & ", " & Year)'"January 1, 2012"

Now tempdate can be added to your database. You may have to convert it to a string in a specific format first, possibly tempdate.ToString("YYYY/MM/DD"), but that will depend on how your database is set up.

[Yeonng]

Hi, I'm really sorry, but i don't even know where i have to put it in and how. Can you show me a little detail, please?? Thank you very much. And this is how my database setup..

[tinstaafl]

instead of 2 fields(month, year) have one Datetime field

[Yeonng]

Already done..
so where do i have to put this

    Dim tempdate As Date = Date.Parse(Month & " " & Day & ", " & Year)'"January 1, 2012"

and how about this one,
tempdate.ToString("YYYY/MM/DD")

[tinstaafl]

First you have to tell the database that that is what kind data to expect, then in any records you add to the database instead of month and year you add a date string.

[G_Waddell]

hi,

Just to explain why you had results that appeared odd. Your month and Year columns were both varchar type when you sort these types, it will by default, sort the columns alphabetically... i.e April, August, December etc.
not January, February, March, April, May, June, July etc.

So if you put in a query that where month >= April and Month <=December I would only get records for April, August and December not April, May, June, July, August, September, October, November and December as it varchar will sort alphabetically.

As the others have said, you are better to use a date field this way you can sort by date

[Yeonng]

hi everyone, thank you so much. My friend help me with this problem. Just like tinstaafl said, i have to use date type, and i'm using the datetimepicker. And my friend make the code like this..

    `month1 = DateTimePicker1.Value.Month
    year1 = DateTimePicker1.Value.Year
    month2 = DateTimePicker2.Value.Month
    year2 = DateTimePicker2.Value.Year`

And thanks for all your help...