excel - ADO 流音乐会转二进制

Question

这是我使用 VBA 将文件上传到数据库的代码，但这个循环只能运行一次。在第二个循环中它会给出错误，错误是

在这种情况下不允许操作

有人可以建议我该怎么做吗？错误在行adoStream.Type = adTypeBinary

Dim stCon As String 'SQL Connection string
Dim stProcName As String 'Stored Procedure name

Dim strCmd As String

Dim adoStream               As Object
Dim adocmd                  As Object
Dim strFilePath             As String
Dim adoCon                  As Object

Set adoCon = CreateObject("ADODB.Connection")
Set adoStream = CreateObject("ADODB.Stream")
Set adocmd = CreateObject("ADODB.Command")

adoCon.CursorLocation = adUseClient
adoCon.Open "Provider=SQLOLEDB; " & _
            "Data Source=#########; " & _
            "Initial Catalog=#######;" & _
            "User ID=#######; Password=########;"

For Each fl In fld.Files    
    If fl.name Like Mask Then
        strInput = fl.name
        strFilePath = fld.path & "\" & fl.name

        MsgBox (strFilePath)

        adoStream.Type = adTypeBinary
        adoStream.Open
        adoStream.LoadFromFile strFilePath 'It fails if file is open

        With adocmd 
            .CommandText = "INSERT INTO dbo.coc_upload(Data) " & _
                           "VALUES (?)"
            .CommandType = adCmdText
            '---adding parameters
            .Parameters.Append .CreateParameter("@Data", adVarBinary, adParamInput, adoStream.Size, adoStream.Read)
            '---
        End With

        adocmd.ActiveConnection = adoCon
        adocmd.Execute
        adoCon.Close

        MsgBox ("Done")    
    End If      
Next    

标签： excelvba