Help?! with SQL statment -OleDbDataAdapter1

[BlueOysterCult]

I hope I am in the right forum

Hello,

I have a cd collection program that has a datagrid that I have filters on it such as sort by title or artist and I have one that is sort(filter) by Year. I am using an input box to get the users request every filter except the year is working - I can't get it to filter -

can someone help?

Dim strSQL, strYear As String
       strYear = InputBox("Please enter a year to search" & vbNewLine & "Example: 1995")
       strSQL = "select CdArtist,CdTitle, CdYear,CdKey,CdCategory from Cds where CdYear between 1975 and 1995 " 'Here is the problem- what do I put in place of 1975 and 1995?
       OleDbSelectCommand1.CommandText = strSQL

       DbCdCollection1.Clear()
       OleDbDataAdapter1.Fill(DbCdCollection1)
       UpdateCounter()

 

thanks

Rob

Edited November 26, 2005 by PlausiblyDamp

[Mehyar]

What is the problem, the code you put isnot filtering or you want to know what to put in the where clause of the SQL statement ?

[BlueOysterCult]

Hey Mehyar!

What I want is the user to put in a range ie 1980 - 1987 and my datagrid to reflect that request with year descending

R

[Mehyar]

The column year is it an integer or text in your database ?

[BlueOysterCult]

I looked at it in Access and the filed says Number

 

R

[Mehyar]

Your SQL statement is correct what is your problem then ?

[BlueOysterCult]

When I put in the InputBox something like 1989 - 1992 which should only give me those dates.. I get ALL dates no sorting filtering of any kind

here is a filter that works

 

Private Sub mnuByTitle_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuByTitle.Click
       Dim strTitle As String, strSQL As String
       strTitle = InputBox("Please enter a title to search")
       strSQL = "select CdArtist,CdTitle, CdYear,CdKey,CdCategory from Cds where CdTitle like '" & strTitle & "%'"
       OleDbSelectCommand1.CommandText = strSQL
       DbCdCollection1.Clear()
       OleDbDataAdapter1.Fill(DbCdCollection1)
       UpdateCounter()
   End Sub

 

Rob

Edited November 26, 2005 by PlausiblyDamp

[Mehyar]

Ah ok now I get it,

 

So you are inputting for ex 1982 - 1990

 

If this is the case then you need to fix the string strYear before using it in SQL.

 

Try this

Dim strSQL, strYear As String
       strYear = InputBox("Please enter a year to search" & vbNewLine & "Example: 1995")

stryear = stryear.Replace(" ","") 'removes the spaces from the input
Dim ArrYear() as String = stryear.Split("-"c) ' this would divide the string based on the - so you will have two resulting strings (which are the two years inputted by the user)
'Now ArrYear(0) contains the first year
'ArrYear(1) contains the second year
strSQL = "select CdArtist,CdTitle, CdYear,CdKey,CdCategory from Cds where CdYear between " ArrYear(0) & " and " ArrYear(1)        

OleDbSelectCommand1.CommandText = strSQL

       DbCdCollection1.Clear()
       OleDbDataAdapter1.Fill(DbCdCollection1)
       UpdateCounter()

 

Hope this helps,