mysql - vb 6.0谁能帮我提供我的代码？

我正在使用我的项目清单系统，我想在mysql的listview1中，使用2个DTPicker在my books表中显示过滤后的日期，并为其生成一个报告。我在类模块idk中的查询有一个错误，如果它只是一个查询，我真的很困惑我是vb 6.0中的乞丐…请在需要你的帮助的家伙。
我使用两个表，即书籍和供应商。
“类模块”中的代码：

Sub DisplayList(ListView1 As ListView, DateFrom As Date, DateTo As Date)
Dim lstItem As ListItem, a As Integer
Dim rs As New ADODB.Recordset
Dim sql As String
If rs.State = adStateOpen Then rs.Close

sql = " SELECT supplier.category,books.title,books.dataAcquired,books.amount,books.quantity,books.accesionno,books.conditions" & _
      " From supplier INNER JOIN books" & _
      " ON supplier.code=books.code" & _
      " WHERE (((books.dataAcquired)>=#" & DateFrom & "#) and ((books.dataAcquired) <=#" & DateTo & "#))" & _
      " GROUP BY supplier.category,books.title,books.dataAcquired,books.amount,books.quantity,books.accesionno,books.conditions" & _
      " ORDER BY books.dataAcquired DESC;"
   rs.Open sql, cnn

   ListView1.ListItems.Clear
   Do While Not rs.EOF
    a = a + 1
        Set lstItem = ListView1.ListItems.Add(, , a, 1, 1)
            lstItem.SubItems(1) = rs(0).Value
            lstItem.SubItems(2) = rs(1).Value
            lstItem.SubItems(3) = rs(2).Value
            lstItem.SubItems(4) = rs(3).Value
            lstItem.SubItems(5) = rs(4).Value
            lstItem.SubItems(6) = rs(5).Value
            lstItem.SubItems(7) = rs(6).Value
            rs.MoveNext
            Loop

End Sub

我表单中的代码：

Private Sub Show_Click()
clsData.DisplayList ListView1, DTPicker1.Value, DTPicker2.Value
lblCount.Caption = ListView1.ListItems.Count
End Sub

Private Sub Form_Load()
DTPicker1.Value = Date
DTPicker2.Value = Date
End Sub

Private Sub Form_Activate()
clsData.DisplayList ListView1, DTPicker1.Value, DTPicker2.Value
lblCount.Caption = ListView1.ListItems.Count
End Sub

最佳答案

更改

format date how yyyy-MM-dd or yyyyMMdd

sql = " SELECT supplier.category,books.title,books.dataAcquired,books.amount,books.quantity,books.accesionno,books.conditions" & _
  " From supplier INNER JOIN books" & _
  " ON supplier.code=books.code" & _
  " WHERE (((books.dataAcquired)>='" & format(DateFrom,"yyyy-MM-dd") & "') and ((books.dataAcquired) <='" & format(DateTo,"yyyy-MM-dd") & "'))" & _
  " GROUP BY supplier.category,books.title,books.dataAcquired,books.amount,books.quantity,books.accesionno,books.conditions" & _
  " ORDER BY books.dataAcquired DESC;"

为记录集空添加验证时更改循环

 if RecordsetIsClosed(rs) then exit sub

 While Not RecordSetIsEmpty(rs)
      a = a + 1
      Set lstItem = ListView1.ListItems.Add(, , a, 1, 1)
      lstItem.SubItems(1) = rs(0).Value
      lstItem.SubItems(2) = rs(1).Value
      lstItem.SubItems(3) = rs(2).Value
      lstItem.SubItems(4) = rs(3).Value
      lstItem.SubItems(5) = rs(4).Value
      lstItem.SubItems(6) = rs(5).Value
      lstItem.SubItems(7) = rs(6).Value
      rs.MoveNext
 wend

Public Function RecordSetIsEmpty(ByRef rs As ADODB.Recordset) As Boolean
'   On Local Error GoTo RecordSetIsEmpty_Error
'       RecordSetIsEmpty = True
'       If rs Is Nothing Then
'           RecordSetIsEmpty = True
'           Exit Function
'       End If
'       If RecordsetIsClosed(rs) = True Then
'           RecordSetIsEmpty = True
'           Exit Function
'       End If
    RecordSetIsEmpty = (rs.BOF = True And rs.EOF = True)
'   RecordSetIsEmpty_Done:
'       Exit Function
'   RecordSetIsEmpty_Error:
'       Resume RecordSetIsEmpty_Done
End Function


Public Function RecordsetIsClosed(ByRef rs As ADODB.Recordset) As Boolean
On Local Error GoTo RecordsetIsClosed_Error
    RecordsetIsClosed = True
    If rs Is Nothing Then
        RecordsetIsClosed = True
    End If
    If rs.State <> adStateClosed Then
        RecordsetIsClosed = False
    End If
RecordsetIsClosed_Done:
    Exit Function
RecordsetIsClosed_Error:
    Resume RecordsetIsClosed_Done
End Function

别忘了打开数据库连接
更新感谢Mark Bertenshaw
RecordSetIsEmpty用于在执行movenext时出现问题。。我记得
使用RecordsetIsClosed是因为在某些情况下，数据库管理器返回的不是记录集，或者记录集未正确初始化
例如，在执行movenext或读取值之前，必须使用movefist进行访问

关于mysql - vb 6.0谁能帮我提供我的代码？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7938242/