I have 5 Select Statements that search the SQLite Database the search variables are built on
a Form called frmBuildSearches and passed to a form with a DataGridView
The issue I am trying to overcome is I need to have 5 Sub Routines for each Search Type that is built
I would like to put the 5 Search Statements in ONE Sub Routine and when that Sub Routine is called make a decision
which Search Statement will be used. This is my first time working with the "Using" statement
This is were the challenge starts and after a lot of trial and error has stopped
The code below is one Sub Routine with 4 of the Search Statements commented out
Question How to embed the Search Statements in one Sub Routine with the "Using" statement ?

    Private Sub MoRangeYr()
    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()
        '========================
        'Designed Searches Below
        'All
        'Using cmd As SQLiteCommand = New SQLiteCommand($"SELECT * FROM TxData", conn)
        'MoYr
        'Using cmd As SQLiteCommand = New SQLiteCommand($"SELECT * FROM TxData WHERE txSearchMonth = '{gvFromMonth}' AND txYear = '{gvYear}' ", conn)
        'TxMoYr
        'Using cmd As SQLiteCommand = New SQLiteCommand($"SELECT * FROM TxData WHERE txType = '{gvTxType}' AND txSearchMonth = '{gvFromMonth}'AND txYear = '{gvYear}' ", conn)
        'Year
        'Using cmd As SQLiteCommand = New SQLiteCommand($"SELECT * FROM TxData WHERE txYear = '{gvYear}' ", conn)
        '=========================
        'MoRangeYr
        Using cmd As SQLiteCommand = New SQLiteCommand($"SELECT * FROM TxData WHERE  txSearchMonth = '{gvFromMonth}' AND txSearchMonth = '{gvToMonth}' AND txYear = '{gvYear}' ", conn)

            Using rdr As SQLite.SQLiteDataReader = cmd.ExecuteReader

                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)
                    Dim dateToday = Date.Today


                    Dim lblDate As String = dateToday.ToString("MMM" & " " & "d" & " " & "yyyy")
                    lblAmt.Text = "Current Balance  " & lblDate
                    tbBal.Text = "$ " & String.Format("{0:n}", strBal)
                    rowCount = rowCount   1
                End While

                dgvTX.Sort(dgvTX.Columns(0), ListSortDirection.Descending)
                If rowCount <= 25 Then
                    maxRowCount = 25 - rowCount
                    For iA = 1 To maxRowCount
                        dgvTX.Rows.Add(" ")
                    Next
                End If
                rdr.Close()
            End Using
        End Using
        conn.Close()
    End Using
    tbBal.Focus()

End Sub

This code decides which Sub Routing is used

    Private Sub frmViewTX_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    StyleDGV()

    If gvTEST Is "All" Then
        PopulateDGV()
    ElseIf gvTEST Is "MoYr" Then
        JustOne()
    ElseIf gvTEST Is "Year" Then
        YearOnly()
    ElseIf gvTEST Is "TxMoYr" Then
        TxMoYr()
    ElseIf gvTEST Is "MoRangeYr" Then
        MoRangeYr()
    End If
    'MoRangeYr
End Sub

CodePudding user response：

As I've said already, make sure to use parameterized queries! That out of the way, the basic answer to the question is you can change the CommandText property between when you create the SQLiteCommand object and when you actually run the command:

Private Sub MoRangeYr()
 
    Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;")
    Using cmd As New SQLiteCommand("",conn)
        
        If gvTEST = "ALL" Then
            cmd.CommandText = "SELECT * FROM TxData"
        Else If gvTEST = "MoYr" Then
            cmd.CommandText = "SELECT * FROM TxData WHERE txSearchMonth = $gvFromMonth' AND txYear = $gvYear " 
            cmd.Parameters.AddWithValue("$gvFromMonth", gvFromMonth)
            cmd.Parameters.AddWithValue("$gvYear", gvYear)
        Else If gvTEST = "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)
        Else If gvTEST = "MoRangeYr" Then
            cmd.CommandText = "SELECT * FROM TxData WHERE txYear = $gvYear "
            cmd.Parameters.AddWithValue("$gvYear", gvYear)
        End If   

        conn.Open()
        Using rdr As SQLite.SQLiteDataReader = cmd.ExecuteReader
            dgvTX.DataSource = rdr

             dgvTX.Columns(3).DefaultCellStyle.Format = "N"
             dgvTX.Columns(6).DefaultCellStyle.Format = "N"
        End Using

        lblAmt.Text = $"Current Balance  {DateTime.Today:MMMM d yyyy}"
        ' tbBal.Text = "${strBal:n}" ' This was wrong in the original code

        dgvTX.Sort(dgvTX.Columns(0), ListSortDirection.Descending)

    End Using
    End Using
End Sub

You may also want to look into DataBinding this result. You're writing a lot of extra code to do work the GridView knows how to do for you.