VB6.0中如何实现sql语句中带参数的查询？

Question

在VB6里的一条数据库更新语句：
update Ass_UserPass set username='" & txtName & "',department='" & cboBm.Text & "',UsFunction='" & cboLb.Text & "',pword='" & strPassStor & "' where user_id='" & lblId & "' 
由于 strPassStor 是经加密运算得出，所以这种写法会有错误，因为产生的密码中会有特殊字符。
于是想用参数化查询的方式：
 Set Cmd = New ADODB.Command
    strSql = "update Ass_UserPass set username='" & txtName & "',department='" & cboBm.Text & "',UsFunction='" & cboLb.Text & "',pword=@Pword where user_id='" & lblId & "' "
    With Cmd
       .Prepared = False
      .CommandText = strSql
      .CommandType = adCmdText
      .NamedParameters = True
      .Parameters.Append .CreateParameter("@Pword", adVarChar, adParamInput, 32, strPassStor)
      Set .ActiveConnection = cnErp
      .Execute
    End With
结果提示变量“@Pword ”未定义，试了好多次都不能解决参数化查询问题，网上也只有调用存储过程的例子。请问如何实现Sql语句中带参数的查询呢？谢谢！

Answer 1

可以考虑下有  Select 的SQL语句

Answer 2

Append 和 CreateParameter 方法范例 (VB)
本范例使用 Append 和 CreateParameter 方法执行具有输入参数的存储过程。'BeginAppendVB
    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string
Public Sub Main()
    On Error GoTo ErrorHandler
    'recordset, command and connection variables
    Dim Cnxn As ADODB.Connection
    Dim cmdByRoyalty As ADODB.Command
    Dim prmByRoyalty As ADODB.Parameter
    Dim rstByRoyalty As ADODB.Recordset
    Dim rstAuthors As ADODB.Recordset
    Dim strCnxn As String
    Dim strSQLAuthors As String
    Dim strSQLByRoyalty As String
     'record variables
    Dim intRoyalty As Integer
    Dim strAuthorID As String
    ' Open connection
    Set Cnxn = New ADODB.Connection
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    Cnxn.Open strCnxn
    ' Open command object with one parameter
    Set cmdByRoyalty = New ADODB.Command
    cmdByRoyalty.CommandText = "byroyalty"
    cmdByRoyalty.CommandType = adCmdStoredProc
    ' Get parameter value and append parameter
    intRoyalty = Trim(InputBox("Enter royalty:"))
    Set prmByRoyalty = cmdByRoyalty.CreateParameter("percentage", adInteger, adParamInput)
    cmdByRoyalty.Parameters.Append prmByRoyalty
    prmByRoyalty.Value = intRoyalty
    ' Create recordset by executing the command
    Set cmdByRoyalty.ActiveConnection = Cnxn
    Set rstByRoyalty = cmdByRoyalty.Execute
    ' Open the Authors Table to get author names for display
    ' and set cursor client-side
    Set rstAuthors = New ADODB.Recordset
    strSQLAuthors = "Authors"
    rstAuthors.Open strSQLAuthors, Cnxn, adUseClient, adLockOptimistic, adCmdTable
    ' Print recordset adding author names from Authors table
    Debug.Print "Authors with " & intRoyalty & " percent royalty"
    Do Until rstByRoyalty.EOF
        strAuthorID = rstByRoyalty!au_id
        Debug.Print "   " & rstByRoyalty!au_id & ", ";
        rstAuthors.Filter = "au_id = '" & strAuthorID & "'"
        Debug.Print rstAuthors!au_fname & " " & rstAuthors!au_lname
        rstByRoyalty.MoveNext
    Loop
    ' clean up
    rstByRoyalty.Close
    rstAuthors.Close
    Cnxn.Close
    Set rstByRoyalty = Nothing
    Set rstAuthors = Nothing
    Set Cnxn = Nothing
    Exit Sub
ErrorHandler:
    ' clean up
    If Not rstByRoyalty Is Nothing Then
        If rstByRoyalty.State = adStateOpen Then rstByRoyalty.Close
    End If
    Set rstByRoyalty = Nothing
    If Not rstAuthors Is Nothing Then
        If rstAuthors.State = adStateOpen Then rstAuthors.Close
    End If
    Set rstAuthors = Nothing
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
'EndAppendVB

Answer 3

以下模块已测试通过，有不懂的可以问我：
窗体：
Private Sub Command3_Click()
Dim db As New AdoByCs
db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb" & ";Persist Security Info=False"
db.OpenDatabaseDim iAffectedRows
Dim iResult
Dim vData(1, 4)
vData(0, 0) = "Cs_ID"
vData(0, 1) = 3
vData(0, 2) = 4
vData(0, 3) = 1iResult = db.PrepareScalar("SELECT COUNT(*) FROM 表1 WHERE ID=@Cs_ID", vData)MsgBox "Rows: " & iResultdb.CloseDatabase
Set db = Nothing
End Sub'-----------------------------
'以下的建一个类,类名：AdoByCs
Option ExplicitPrivate connStr As String
Private conn As ADODB.Connection' 打开数据库连接
Sub OpenDatabase(Optional Mdb As String, Optional Pass As String)
    Set conn = New ADODB.Connection
    If connStr = "" Then
        connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Mdb    '使打开的连接与Command对象关联    End If
    conn.ConnectionString = connStr
    conn.Open
End Sub' 关闭数据库连接
Sub CloseDatabase()
    conn.Close
    Set conn = Nothing
End Sub' 执行参数 INSERT/UPDATE/DELETE 查询
' -------------------------------------------------------------------------------------
' @Parameter vSql           将要执行的 SQL 语句。
' @Parameter iAffectedRows 返回查询所影响的行数。
' @Parameter vData 参数数组(二维)。
' -------------------------------------------------------------------------------------
Public Sub PrepareExecute(ByVal vSql As String, Optional ByRef iAffectedRows As Variant, Optional ByRef vData As Variant)
    Dim oCommand As New ADODB.Command
    Dim oParameter As Parameter
    
    oCommand.ActiveConnection = conn
    oCommand.Prepared = True
    oCommand.CommandType = adCmdText
    oCommand.CommandText = vSql
    
    If Not IsMissing(vData) Then
        Dim i As Integer
        Dim size As Integer
        size = UBound(vData, 1) - 1
        For i = 0 To size Step 1
            Set oParameter = oCommand.CreateParameter("@" & vData(i, 0), CInt(vData(i, 1)), adParamInput, CInt(vData(i, 2)), vData(i, 3))
            oCommand.Parameters.Append oParameter
        Next
    End If
    
    oCommand.Execute iAffectedRows
    
    Set oCommand = Nothing
End Sub' 执行无参数 INSERT/UPDATE/DELETE 查询
' -------------------------------------------------------------------------------------
' @Parameter vSql           将要执行的 SQL 语句。
' @Parameter iAffectedRows 返回查询所影响的行数。
' -------------------------------------------------------------------------------------
Public Sub Exec(ByVal vSql As String, Optional ByRef iAffectedRows As Variant)
    Dim oCommand As New ADODB.Command
    
    oCommand.ActiveConnection = conn
    oCommand.CommandType = adCmdText
    oCommand.CommandText = vSql
    
    oCommand.Execute iAffectedRows
    
    Set oCommand = Nothing
End Sub' 执行统计查询
' -------------------------------------------------------------------------------------
' @Parameter vSql   将要执行的 SQL 语句。
' @Parameter vData 参数数组(二维)。
'
' @Return Integer   返回统计记录数。
' -------------------------------------------------------------------------------------
Public Function PrepareScalar(ByVal vSql As String, Optional ByRef vData As Variant)
    Dim iResult As Integer
    Dim oCommand As New ADODB.Command
    Dim oResult As ADODB.Recordset
    Dim oParameter
    oCommand.ActiveConnection = conn
    oCommand.Prepared = True
    oCommand.CommandType = adCmdText
    oCommand.CommandText = vSql
    
    If Not IsMissing(vData) Then
        Dim i As Integer
        Dim size As Integer
        size = UBound(vData, 1) - 1
        For i = 0 To size Step 1
            Set oParameter = oCommand.CreateParameter("@" & vData(i, 0), CInt(vData(i, 1)), adParamInput, CInt(vData(i, 2)), vData(i, 3))
            oCommand.Parameters.Append oParameter
        Next
    End If
    
    Set oResult = New ADODB.Recordset
    oResult.Open oCommand, , adOpenForwardOnly, adLockReadOnly
    If Not oResult.EOF Then
        iResult = CInt(oResult.Fields(0).Value)
    End If
    
    oResult.Close
    Set oResult = Nothing
    Set oCommand = Nothing
    
    PrepareScalar = iResult
End Function' 设置连接字符串
Public Property Let ConnectionString(ByVal vNewValue As String)
    connStr = vNewValue
End Property

Answer 4

上面的都看了，不过似乎并没有说明我的上述代码有何错误。添加参数的方法也差不多，怎么执行起来就会提示变量@Pword未定义呢？

Answer 5

这个我试过可以用的了Dim strSql As String
Dim CMD As ADODB.Command
Set CMD = New ADODB.Command
  strSql = "update 表1 set 用户=@Pword"
  With CMD
  '.Prepared = False
  '.CommandText = strSql
  '.CommandType = adCmdText
  '.NamedParameters = True
  
      .Prepared = True
    .CommandType = adCmdText
    .CommandText = strSql
    
  .Parameters.Append .CreateParameter("@Pword", adVarChar, adParamInput, 32, Text1)
  Set .ActiveConnection = db.conn
  .Execute
  End With
'---------------------------------
db.CloseDatabase
Set db = Nothing

Answer 6

pword='@Pword'
或
pword=' & @Pword & "..."

Answer 7

...
cmd.parameters.add(cmd.parameters.createparameter("@id",adint)
cmd.commandtext=='select * from tblTemp where id=@id"
...

Answer 8

我这里运行还是不对，老是提示变量@Pword未定义。我看了参数里确实有了@Pword，但执行Sql语句时就是提示参数未定义。难道到这种方法不行？在.net里用参数很好用，这VB6里的怎么就不行呢？我也照5楼的说得把prepared改为true,把.NamedParameters = True去掉了。仍然如此。究竟还有什么不对的？

Answer 9

如果你是ms的access或者sql数据库，那么sql语句的参数化查询是非常简单的，用cmd.Parameters.Append...方式是没错的，但却是低效的...其实至少对于上述二个数据库，当执行参数化查询时，Parameters这个集合已经由cmd对象自动序列化了...，看如下示例：
 
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim i As Long
    Dim arr As Variant
    
    Set cn = New ADODB.Connection
    cn.Open "....."
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandText = "select * from tb where ID=? and Name=? "
        arr = Array(12, "张三")  '这种方法可以简单传递多个参数
        For i = 0 To .Parameters.Count - 1
            .Parameters(i).Value = arr(i)
        Next
        .Execute
    End With
    Set cmd = Nothing
    Set cn = Nothing
如此参数化查询就这样简单，不但省去你程序中拼接sql的麻烦，而且高效安全...

Answer 10

正要上来说问题已经解决了，恰好又见到了10楼的帖子。其实说的就是这里，原来ado的sql语句里的参数要用“？”的，用@是不行的。10楼的例句也是用的“?”，看来不能使用参数名的方法，只能用"?"代替，所以说10楼说的简单传递参数使用起来正合适。那么command对象的参数添加方法看来只适用于存储过程了？

Answer 11

不过10楼说的是“当执行参数化查询时，Parameters这个集合已经由cmd对象自动序列化了...，”
是不是说update中的参数是不可以这样做的？因为我试过直接用
.Parameters(0).Value = "XXXXX"
这样是不行的，因为Parameters里面还没有一个元素呢，提示找不到项目

Answer 12

无论是insert还是update都是可以的：
update tb set name=? where id=?
insert into tb(id,name) values(?,?)
要注意的是参数赋值必须和sql语句中的参数次序一致....

Answer 13

也不只适用于存储过程，access可以用关键字PARAMETERS指定参数化查询中的参数名称和类型...

Answer 14

不知道你什么数据库，不是所有数据引擎都支持Parameters的序列化...
你可以参考：http://blog.csdn.net/vbman2003/archive/2010/12/06/6057503.aspx

Answer 15

    Set cmd = New ADODB.Command
    strSQL = "update Ass_UserPass set username='" & txtName & "',department='" & cboBm.Text & "',UsFunction='" & cboLb.Text & "',pword= '" & cmd.Parameters("@t").Value & "' where user_id='" & lblId & "' "
    With cmd
        .Prepared = False
        .CommandType = adCmdText
        .NamedParameters = True
        .Parameters.Append .CreateParameter("@Pword", adVarChar, adParamInput, 32, strPassStor)
        Set .ActiveConnection = cnErp
        
        .CommandText = strSQL
        .Execute
    End With

Answer 16

我用的是sql server,不过刚刚注意到你的例子中给参数赋值用的是两个元素的值。
arr = Array(12, "张三") 
....
.Parameters(i).Value = arr(i)
 
而我是将一个实际的值给了参数
.Parameters(i).Value ="张三"
题诗大概就是说参数里元素个数为零，也就是Parameters.Count =0
你的这种用法对吗？

Answer 17

当然回头我再试试这Variant类型的方式

Answer 18

17F的写法跟LZ并不相同,LZ估计没注意吧

Answer 19

你仔细看我示例，包括cmd参数的设置...

Answer 20

这种方式，不管是Variant类型还是String类型都可以。SQL语句中有无参数都是可以，参数和非参数方式混用也可以。关键是注意cmd对象的参数设置，以及按参数顺序对应赋值，你不要在现在代码中测试，许多参数要是默认的才行，自己可以新建工程测试....
唉，不想多说了，只能告诉你这个方法我现在正用着....回个贴觉得累，看来真老了...

Answer 21

补充一句：用的数据引擎是ado 2.8

Answer 22

谢谢你一直以来的答复，是我太罗嗦了。
我是用的ado2.8啊。不过确实在现在的工程里测试的直接赋值，而你是赋id和name两个值，并把它先放到array里。但在我这边确是不好用的，你说新建工程测，但那又会有何不同呢？Parameters没有Add，count不是0吗?

Answer 23

不好意思，前些天倒是看到了，只是看到你后面用的cmd.Parameters,我倒真用过这种方式，只是这样就会把它实际的值填进去，那我为了解决特殊字符的问题而使用参数的目的就达不到了。
只不过当时我用的是cmd.Parameters(0),而你使用的是.Parameters("@t"),我倒真没这样用过。不过呢，不管能不能行，这种用法似乎不是正规的参数用法。

Answer 24

我一直这么用的，返回值adParamOutput 用过没哦

Answer 25

哦，要设置连接对象的CursorLocation = adUseClient
adParamOutput 不要传参数....