VB.Net SQLite Search Between two Integers

Choroid

I have a SQLite DB for a Check Book app that has a DB with the field txSearchMonth which contains a Integer corresponding to the Month of the Year. I have written a search that lets the user select a month from two combo boxes and enter the year in a text box. The issue I am experiencing if I select Jan and Feb and enter the year my results includes the current month NOT just January and February I have tried various search statements to no avail. I have used a DataModule to define these variables.

Public gvYear As String
Public gvFromMonth As Integer
Public gvToMonth As Integer

Data is displayed in a DataGridView that is NOT bound to the database. I will post the complete code but the routine that is failing is gvSearchType = "MoRangeYr" Then

Private Sub ViewSearches()

    Dim intID As Integer
    Dim strDate As String
    Dim strTxType As String
    Dim strAmt As Decimal
    Dim strCKNum As String
    Dim strDesc As String
    Dim strBal As Decimal
    Dim rowCount As Integer
    Dim maxRowCount As Integer
    Dim emptyStr As String = "  "


    Using conn As New SQLiteConnection($"Data Source = '{gv\_dbName}';Version=3;")
        conn.Open()

        Using cmd As New SQLiteCommand("", conn)
            ''Using cmd As SQLiteCommand = New SQLiteCommand($"SELECT \* FROM TxData", conn)
            If gvSearchType = "All" Then
                cmd.CommandText = "SELECT \* FROM TxData"
            ElseIf gvSearchType = "MoYr" Then
                cmd.CommandText = "SELECT \* FROM TxData WHERE txSearchMonth = $gvFromMonth AND txYear = $gvYear "
                cmd.Parameters.AddWithValue("$gvFromMonth", gvFromMonth)
                cmd.Parameters.AddWithValue("$gvYear", gvYear)
            ElseIf gvSearchType = "TxMoYr" Then
                cmd.CommandText = "SELECT \* FROM TxData WHERE txType = $gvTxType AND txSearchMonth = $gvFromMonth AND txYear = $gvYear "
                cmd.Parameters.AddWithValue("$gvTxType", gvTxType)
                cmd.Parameters.AddWithValue("$gvFromMonth", gvFromMonth)
                cmd.Parameters.AddWithValue("$gvYear", gvYear)
            ElseIf gvSearchType = "MoRangeYr" Then
                cmd.CommandText = "SELECT \* FROM TxData WHERE txSearchMonth >= $gvFromMonth AND txSearchMonth <= $gvToMonth AND txYear = $gvYear "
                cmd.Parameters.AddWith

Lost User

You show some code and we're to assume the search arguments and data are valid; of which there is no evidence. You used an int for month, and a string for year. Which raises "questions". Not much for others to go on.

"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I

Choroid

I agree on the use of Integer and Strings in a search not the best design. Other searches where I use just one month and the year work fine. FWIW if I search for Jan to Feb by not using Feb and use Mar instead I get the proper results ie Jan & Feb data only. It is as if the rdr does not know when to stop reading ? ? ? When the app loads this test runs.It is to find the 4 Tue of the month. Could it be interfering ?

Function FourthTueOfNextMonth(dt As Date) As Date
    ' Start with the First Day of the Month, from the date passed in.
    ' Add one Month to that to get the first Day of the NEXT month.
    Dim currDate As Date = (New Date(dt.Year, dt.Month, 1)).AddMonths(1)
    ' Find the First Tuesday of the Month
    While currDate.DayOfWeek <> DayOfWeek.Tuesday
        currDate = currDate.AddDays(1)
    End While
    ' Add three more Weeks to jump to Fourth Tuesday
    Return currDate.AddDays(21)
End Function

I have looked at the DB with DB Browser all data looks fine
When I create the table I used this syntax
txSearchMonth TEXT)" same format for Year
Not sure changing to Numeric would solve the issue.
Thanks for the reply

Choroid

After refreshing my brain with a long search through my SQL book I found this ANSWER I have not used the SQL Syntax "IN" before but the key word and the addition of Parenthesis I have a solution. Life is good This is the faulty line of code

cmd.CommandText = "SELECT * FROM TxData WHERE txSearchMonth >= $gvFromMonth AND txSearchMonth <= $gvToMonth AND txYear = $gvYear "

This is the correct syntax below

cmd.CommandText = "SELECT * FROM TxData WHERE txSearchMonth IN ($gvFromMonth, $gvToMonth) AND txYear = $gvYear "

Choroid

I posted an Answer to my question. Tested on a few cases looks like it is a good solution. Per your comment I will look at keeping all the variables as integers and not mixing in strings.

Richard Deeming

That changes the meaning of your query from "records between these two months (inclusive)" to "records in these two specific months". But that doesn't address your question:

Quote:

if I select Jan and Feb and enter the year my results includes the current month NOT just January and February

There are no months between January and February, so the two queries are identical. Unless there's something else odd about your data that you haven't told us?

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Choroid

BACK to the drawing board DAM Just did a search Jan to Mar and NO Feb data You mean I need to know how to test ha ha Might try adding my original >= IN <= parameters If that fails Google search time Thanks for the heads up advice

Choroid

your observation made me wonder why I used string for year?
As I was writing a small test app it happened with strict ON I had a issue converting a INTEGER to a String
So I gave up and just made gvYear a string So you know I learned from my mistake

gvYear = Convert.ToInt32(tbYear.Text)

If you like you can look at my final solution here Link to CP Question[^] Thanks for the comment