时间戳

首页 > 解决方案 > 错误类型错误的 ADODB 命令值

sql - 错误类型错误的 ADODB 命令值

问题描述

我一直在尝试解决这个问题一段时间，我想我几乎得到了它。我正在使用以下代码来更新 SQL 表。该子程序在单击按钮时执行。单击时，我得到“应用程序对当前操作使用错误类型的值”错误。

它突出了这Pm.Value = myarray条线。在本地窗口中，我可以看到它myarray正在提取正确的值，这是从用户窗体上的列表框中选择的名称。

Sub testingcommand()
database_connect
Dim AttendRecord As New ADODB.Recordset
Dim Cm As ADODB.Command
Dim myarray As Variant
Dim Pm As ADODB.Parameter
myarray = GetSelectedItems(attendance.Agentname)
If Appconn.State = 0 Then
Call database_connect
End If
Set Cm = New ADODB.Command
With Cm
    .ActiveConnection = Appconn
    .CommandText = "UPDATE [Attendance] SET [Seated] = '1' WHERE [Agentname]=?;"
    .CommandType = adCmdText
    Set Pm = .CreateParameter("AgentName", adVarChar, adParamInput, 200, "myarray")
    Pm.value = myarray
    .Parameters.Append Pm
    Set AttendRecord = .Execute
End With
Set AttendRecord = Nothing
PhoneHiring_Disconnect
End Sub

我已经使用返回数组的函数对其进行了编辑。

 Public Function GetSelectedItems(lBox As MSForms.ListBox) As Variant


 Dim tmpArray() As Variant
 Dim i As Integer
 Dim selCount As Integer

    selCount = -1
    For i = 0 To lBox.ListCount - 1
        If lBox.Selected(i) = True Then
            selCount = selCount + 1
            ReDim Preserve tmpArray(selCount)
            tmpArray(selCount) = lBox.list(i)

        End If
    Next
    If selCount = -1 Then
        GetSelectedItems = Array()
    Else
        GetSelectedItems = tmpArray
    End If
End Function

标签： sqlvbams-word

解决方案

未经测试：

Sub testingcommand()

    Dim Cm As ADODB.Command
    Dim myarray As Variant
    Dim Pm As ADODB.Parameter, v

    myarray = GetSelectedItems(attendance.Agentname)
    If Appconn.State = 0 Then
        Call database_connect
    End If

    Set Cm = New ADODB.Command
    With Cm
        .ActiveConnection = Appconn
        .CommandText = "UPDATE [Attendance] SET [Seated] = '1' WHERE [Agentname]=?"
        .CommandType = adCmdText
        Set Pm = .CreateParameter("AgentName", adVarChar, adParamInput, 200, "myarray")
        .Parameters.Append Pm

        'loop over your array
        for each v in myarray
            Pm.value = v
            .Execute
        Next v

    End With
    Set AttendRecord = Nothing
    PhoneHiring_Disconnect

End Sub

推荐阅读