VB6连接MySQL按日期范围查询报错：SQL语法错误排查求助

错误信息 #

[MYSQL][ODBC 5.1 Driver] [mysql-5.5.5-10.4.28-MariaDB] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB Server version for the right syntax to use near "" at line 1

问题根源 #

原代码兼容Access但不兼容MySQL，核心问题包括：

• 日期分隔符：Access用#包裹日期，MySQL要求用单引号'
• 日期格式：MySQL仅识别YYYY-MM-DD等标准日期格式，直接传递VB6 Date变量会导致格式不匹配
• 语法疏漏：日期赋值语句有语法错误；Do Events应为DoEvents；Select*缺少空格；未声明rs对象

修复后的完整代码 #

Dim list As ListItem
Dim rs As ADODB.Recordset ' 显式声明Recordset对象
ListView1.ListItems.Clear

' 将日期转换为MySQL兼容的字符串格式
Dim date1 As String
Dim date2 As String
date1 = Format(Me.DTPicker1.Value, "YYYY-MM-DD")
date2 = Format(Me.DTPicker2.Value, "YYYY-MM-DD")

' 关闭已有连接（如果存在）
If mysqlcn.State = adStateOpen Then mysqlcn.Close
DoEvents ' 修复空格问题

' 初始化数据库连接
Set mysqlcn = New ADODB.Connection
With mysqlcn
    .ConnectionString = "Driver={MySQL ODBC 5.1 Driver};" _
    & "Server=localhost;" _
    & "Database=db_test;" _
    & "User=root;" _
    & "Password=;" _
    & "Option=3;"
    .CursorLocation = adUseClient
    .Open
End With

' 执行SQL查询（替换日期分隔符为单引号，修复SELECT语法）
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM register WHERE Date0 BETWEEN '" & date1 & "' AND '" & date2 & "'", mysqlcn, adOpenDynamic, adLockPessimistic

' 将数据加载到ListView
Do Until rs.EOF
    Set list = ListView1.ListItems.Add(, , rs!ID)
    list.SubItems(1) = rs!Date0
    list.SubItems(2) = rs!CashStatus
    list.SubItems(3) = rs!TrxId
    list.SubItems(4) = rs!To
    list.SubItems(5) = rs!AccountNo
    list.SubItems(6) = rs!Amount
    list.SubItems(7) = rs!AccountTitle
    list.SubItems(8) = rs!From
    list.SubItems(9) = rs!PrintedBy
    list.SubItems(10) = rs!Comission
    rs.MoveNext
Loop

' 清理资源
rs.Close
Set rs = Nothing
Set mysqlcn = Nothing

更安全的参数化查询方案 #

直接拼接SQL存在注入风险，推荐使用参数化查询：

' 替代字符串拼接的参数化查询
Set rs = New ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = mysqlcn
cmd.CommandText = "SELECT * FROM register WHERE Date0 BETWEEN ? AND ?"
' 添加参数，直接传递Date类型无需格式化
cmd.Parameters.Append cmd.CreateParameter("startDate", adDBDate, adParamInput, , Me.DTPicker1.Value)
cmd.Parameters.Append cmd.CreateParameter("endDate", adDBDate, adParamInput, , Me.DTPicker2.Value)
Set rs = cmd.Execute()

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