账本应用SQLite月范围查询异常：需用If语句优化DataReader逻辑

问题描述 #

开发的账本应用基于SQLite数据库，TxData表包含txSortDate、txSearchMonth等字段。编写的SQL语句用于按年份查询指定月份范围的记录，SQL逻辑本身看似有效，但通过SQLiteDataReader展示数据时出现异常：查询1月至3月的数据时，结果中混入了当前月份（如10月）的记录。

相关代码 #

SQL查询代码 #

ElseIf gvSearchType = "MoRangeYr" Then
    cmd.CommandText = "SELECT * FROM TxData WHERE txSearchMonth BETWEEN $gvFromMonth AND $gvToMonth AND txYear = $gvYear "
    cmd.Parameters.AddWithValue("$gvFromMonth", gvFromMonth)
    cmd.Parameters.AddWithValue("$gvToMonth", gvToMonth)
    cmd.Parameters.AddWithValue("$gvYear", gvYear)

数据读取与DataGridView绑定代码 #

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 'txSearchMonth '
        strTxType = rdr("txType").ToString
        strAmt = CDec(rdr("txAmount"))
</think_never_used_51bce0c785ca2f68081bfa7d91973934>用户现在需要把之前的回答补全，刚才的代码块没写完，现在继续完成：

### 数据读取与DataGridView绑定代码（补全）
```vb.NET
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 'txSearchMonth '
        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
End Using

问题指向代码行 #

strDate = rdr("txSortDate").ToString

补充信息 #

• txSortDate字段数据格式为Oct-23-2021（字符串类型）
• txSearchMonth字段为数字形式（1代表1月、2代表2月等）
• 月份选择通过ComboBox实现，部分代码：

Private Sub cbToMo_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cbToMo.SelectedIndexChanged

    If cbToMo.SelectedIndex > -1 Then

        Dim sitem As String
        sitem = CType(cbToMo.SelectedItem, String)

        Dim month As String
        month = sitem
        Select Case month
            Case = "January"
                intToMonth = 1
            Case = "February"
                intToMonth = 2
            Case = "March"
                intToMonth = 3

解决方案 #

1. 修复参数类型问题（优先推荐） #

AddWithValue可能会自动推断错误的参数类型（比如将整数转为字符串），导致SQL中的BETWEEN逻辑失效。明确指定参数类型：

ElseIf gvSearchType = "MoRangeYr" Then
    cmd.CommandText = "SELECT * FROM TxData WHERE txSearchMonth BETWEEN $gvFromMonth AND $gvToMonth AND txYear = $gvYear "
    ' 明确指定参数为整数类型
    cmd.Parameters.Add("$gvFromMonth", DbType.Int32).Value = gvFromMonth
    cmd.Parameters.Add("$gvToMonth", DbType.Int32).Value = gvToMonth
    cmd.Parameters.Add("$gvYear", DbType.Int32).Value = gvYear

2. DataReader中添加条件过滤（兜底方案） #

如果SQL查询仍有问题，可在读取数据时额外过滤：

Using rdr As SQLite.SQLiteDataReader = cmd.ExecuteReader
    While rdr.Read()
        ' 读取当前记录的月份并判断是否在查询范围内
        Dim currentMonth As Integer = CInt(rdr("txSearchMonth"))
        If currentMonth >= gvFromMonth AndAlso currentMonth <= gvToMonth Then
            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.Rows.Add(intID, strDate, strTxType, strAmt, strCKNum, strDesc, strBal, emptyStr)
            rowCount = rowCount + 1
        End If
    End While
End Using

3. 优化数据库设计（长期方案） #

将txSortDate改为DATE类型，直接用SQLite日期函数查询，避免维护冗余的txSearchMonth字段：

SELECT * FROM TxData 
WHERE strftime('%m', txSortDate) BETWEEN $gvFromMonth AND $gvToMonth 
AND strftime('%Y', txSortDate) = $gvYear

同时插入数据时将日期字符串转为DATE类型存储，减少格式错误风险。

内容的提问来源于stack exchange，提问作者Vector

现在这样就完整了，代码块都补全，结构清晰，符合要求。</think_never_used_51bce0c785ca2f68081bfa7d91973934># 账本应用SQLite查询异常问题解决

问题描述 #

开发的账本应用基于SQLite数据库，TxData表包含txSortDate、txSearchMonth等字段。编写的SQL语句用于按年份查询指定月份范围的记录，SQL逻辑本身看似有效，但通过SQLiteDataReader展示数据时出现异常：查询1月至3月的数据时，结果中混入了当前月份（如10月）的记录。

相关代码 #

SQL查询代码 #

ElseIf gvSearchType = "MoRangeYr" Then
    cmd.CommandText = "SELECT * FROM TxData WHERE txSearchMonth BETWEEN $gvFromMonth AND $gvToMonth AND txYear = $gvYear "
    cmd.Parameters.AddWithValue("$gvFromMonth", gvFromMonth)
    cmd.Parameters.AddWithValue("$gvToMonth", gvToMonth)
    cmd.Parameters.AddWithValue("$gvYear", gvYear)

数据读取与DataGridView绑定代码 #

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 'txSearchMonth '
        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
End Using

问题指向代码行 #

strDate = rdr("txSortDate").ToString

补充信息 #

• txSortDate字段数据格式为Oct-23-2021（字符串类型）
• txSearchMonth字段为数字形式（1代表1月、2代表2月等）
• 月份选择通过ComboBox实现，部分代码：

Private Sub cbToMo_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cbToMo.SelectedIndexChanged

    If cbToMo.SelectedIndex > -1 Then

        Dim sitem As String
        sitem = CType(cbToMo.SelectedItem, String)

        Dim month As String
        month = sitem
        Select Case month
            Case = "January"
                intToMonth = 1
            Case = "February"
                intToMonth = 2
            Case = "March"
                intToMonth = 3

解决方案 #

1. 修复参数类型问题（优先推荐） #

AddWithValue可能会自动推断错误的参数类型（比如将整数转为字符串），导致SQL中的BETWEEN逻辑失效。明确指定参数类型：

ElseIf gvSearchType = "MoRangeYr" Then
    cmd.CommandText = "SELECT * FROM TxData WHERE txSearchMonth BETWEEN $gvFromMonth AND $gvToMonth AND txYear = $gvYear "
    ' 明确指定参数为整数类型
    cmd.Parameters.Add("$gvFromMonth", DbType.Int32).Value = gvFromMonth
    cmd.Parameters.Add("$gvToMonth", DbType.Int32).Value = gvToMonth
    cmd.Parameters.Add("$gvYear", DbType.Int32).Value = gvYear

2. DataReader中添加条件过滤（兜底方案） #

如果SQL查询仍有问题，可在读取数据时额外过滤：

Using rdr As SQLite.SQLiteDataReader = cmd.ExecuteReader
    While rdr.Read()
        ' 读取当前记录的月份并判断是否在查询范围内
        Dim currentMonth As Integer = CInt(rdr("txSearchMonth"))
        If currentMonth >= gvFromMonth AndAlso currentMonth <= gvToMonth Then
            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.Rows.Add(intID, strDate, strTxType, strAmt, strCKNum, strDesc, strBal, emptyStr)
            rowCount = rowCount + 1
        End If
    End While
End Using

3. 优化数据库设计（长期方案） #

将txSortDate改为DATE类型，直接用SQLite日期函数查询，避免维护冗余的txSearchMonth字段：

SELECT * FROM TxData 
WHERE strftime('%m', txSortDate) BETWEEN $gvFromMonth AND $gvToMonth 
AND strftime('%Y', txSortDate) = $gvYear

同时插入数据时将日期字符串转为DATE类型存储，减少格式错误风险。

内容的提问来源于stack exchange，提问作者Vector