Click here to Skip to main content
15,877,280 members
Home / Discussions / Visual Basic
   

Visual Basic

 
GeneralRe: Change Field Data Type in SQLite DB and Use with changes Pin
Choroid30-Oct-23 5:30
Choroid30-Oct-23 5:30 
GeneralRe: Change Field Data Type in SQLite DB and Use with changes Pin
Richard MacCutchan30-Oct-23 6:24
mveRichard MacCutchan30-Oct-23 6:24 
GeneralRe: Change Field Data Type in SQLite DB and Use with changes Pin
jschell31-Oct-23 5:58
jschell31-Oct-23 5:58 
GeneralRe: Change Field Data Type in SQLite DB and Use with changes Pin
Dave Kreskowiak31-Oct-23 6:42
mveDave Kreskowiak31-Oct-23 6:42 
AnswerRe: Change Field Data Type in SQLite DB and Use with changes Pin
Gerry Schmitz29-Oct-23 6:26
mveGerry Schmitz29-Oct-23 6:26 
GeneralRe: Change Field Data Type in SQLite DB and Use with changes Pin
k505429-Oct-23 7:45
mvek505429-Oct-23 7:45 
GeneralRe: Change Field Data Type in SQLite DB and Use with changes Pin
Choroid29-Oct-23 18:33
Choroid29-Oct-23 18:33 
QuestionVB.Net SQLite Search Between two Integers Pin
Choroid24-Oct-23 16:03
Choroid24-Oct-23 16:03 
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.

VB
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
VB
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.AddWithValue("$gvFromMonth", gvFromMonth)
                cmd.Parameters.AddWithValue("$gvToMonth", gvToMonth)
                cmd.Parameters.AddWithValue("$gvYear", gvYear)
            ElseIf gvSearchType = "Year" Then
                cmd.CommandText = "SELECT * FROM TxData WHERE txYear = $gvYear"
                cmd.Parameters.AddWithValue("$gvYear", gvYear)
            End If


            Using rdr As SQLite.SQLiteDataReader = cmd.ExecuteReader
                'dgvTX.DataSource = rdr
                'Statement Above is when DB is bound to dgvTX
                '============================================

                While rdr.Read()
                    intID = CInt((rdr("TID")))
                    strDate = rdr("txSortDate").ToString
                    strTxType = rdr("txType").ToString
                    strAmt = CDec(rdr("txAmount"))
                    strCKNum = rdr("txCKNum").ToString
                    strDesc = rdr("txDesc").ToString
                    strBal = CDec(rdr("txBalance"))
                    dgvTX.Columns(3).DefaultCellStyle.Format = "N"
                    dgvTX.Columns(6).DefaultCellStyle.Format = "N"
                    'dgvTX.Columns(6).DefaultCellStyle.Format = "C"'Adds the $ sign and commas
                    dgvTX.Rows.Add(intID, strDate, strTxType, strAmt, strCKNum, strDesc, strBal, emptyStr)

                    rowCount = rowCount + 1

                End While

                dgvTX.Columns(3).DefaultCellStyle.Format = "N"
                dgvTX.Columns(6).DefaultCellStyle.Format = "N"
                dgvTX.Sort(dgvTX.Columns(0), ListSortDirection.Descending)
            End Using


I decided to use just the corresponding integer for the month to simplify the search.
Perhaps too simplified ! Suggestions appreciated Code Welcomed I have tried various search statements.
AnswerRe: VB.Net SQLite Search Between two Integers Pin
Gerry Schmitz24-Oct-23 19:56
mveGerry Schmitz24-Oct-23 19:56 
GeneralRe: VB.Net SQLite Search Between two Integers Pin
Choroid25-Oct-23 6:26
Choroid25-Oct-23 6:26 
GeneralRe: VB.Net SQLite Search Between two Integers Pin
Choroid25-Oct-23 13:08
Choroid25-Oct-23 13:08 
GeneralRe: VB.Net SQLite Search Between two Integers Pin
Choroid28-Oct-23 21:13
Choroid28-Oct-23 21:13 
AnswerRe: VB.Net SQLite Search Between two Integers Pin
Choroid25-Oct-23 13:04
Choroid25-Oct-23 13:04 
GeneralRe: VB.Net SQLite Search Between two Integers Pin
Richard Deeming25-Oct-23 22:36
mveRichard Deeming25-Oct-23 22:36 
GeneralRe: VB.Net SQLite Search Between two Integers Pin
Choroid26-Oct-23 6:02
Choroid26-Oct-23 6:02 
QuestionIssue with "Windows Form App" Disappearing in VB.NET 2022 Pin
ionline4u26-Aug-23 22:53
ionline4u26-Aug-23 22:53 
AnswerRe: Issue with "Windows Form App" Disappearing in VB.NET 2022 Pin
Victor Nijegorodov27-Aug-23 0:05
Victor Nijegorodov27-Aug-23 0:05 
GeneralRe: Issue with "Windows Form App" Disappearing in VB.NET 2022 Pin
ionline4u27-Aug-23 0:35
ionline4u27-Aug-23 0:35 
GeneralRe: Issue with "Windows Form App" Disappearing in VB.NET 2022 Pin
Dave Kreskowiak27-Aug-23 4:56
mveDave Kreskowiak27-Aug-23 4:56 
GeneralRe: Issue with "Windows Form App" Disappearing in VB.NET 2022 Pin
Richard MacCutchan27-Aug-23 6:28
mveRichard MacCutchan27-Aug-23 6:28 
AnswerRe: Issue with "Windows Form App" Disappearing in VB.NET 2022 Pin
Richard MacCutchan27-Aug-23 1:29
mveRichard MacCutchan27-Aug-23 1:29 
QuestionPrinterSettings.CanDuplex appears to not be working Pin
lewist5721-Aug-23 5:27
lewist5721-Aug-23 5:27 
AnswerRe: PrinterSettings.CanDuplex appears to not be working Pin
Gerry Schmitz22-Aug-23 7:48
mveGerry Schmitz22-Aug-23 7:48 
QuestionConvert/Read SQL Image field into pdf Pin
Benniit19-Jul-23 9:07
Benniit19-Jul-23 9:07 
AnswerRe: Convert/Read SQL Image field into pdf Pin
Richard MacCutchan19-Jul-23 22:18
mveRichard MacCutchan19-Jul-23 22:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.