• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2128
  • Last Modified:

Passing a BLOB as a longvarbinary parameter to a stored proc ?


I'm having problems passing a byte array to a stored proc as a longvarbinary.
The sample code that follows throws an error:
"Application uses a value of the wrong type for the current operation."
when I attempt to append a chunk.

Any ideas?

Public Function AddLongBin() As Boolean

    Dim cmd As New ADODB.Command
    Dim prmID As ADODB.Parameter
    Dim prmBin As ADODB.Parameter
    Dim aBin() As Byte

On Error GoTo ErrHandler

    cmd.ActiveConnection = cnDatabase
    cmd.CommandText = "AddLongBin"
    cmd.CommandType = adCmdStoredProc
    ReDim aBin(100) As Byte
    Set prmBin = cmd.CreateParameter("Bin", adLongVarBinary, adParamInput, 100)
    cmd.Parameters.Append prmBin
    prmBin.Attributes = adFldLong
    prmBin.AppendChunk aBin

    If Err.Number <> 0 Then
        MsgBox Err.Description
    End If

End Function
  • 2
1 Solution
Alan WarrenCommented:
Heres one I use maybe it will help you.
The thing I see as different is the defined size for you blob parameter.

  with cmd
    ' zips are stored in another table so use different stored query
    If sFileExtension = "zip" Then
        .CommandText = "spJetPackBlob_zip"
      .CommandText = "spJetPackBlob"
    End If      
    .CommandType = adCmdStoredProc        
      .Parameters.Append .CreateParameter("pFileName", adVarChar, adParamInput, 255, sFileName)
      .Parameters.Append .CreateParameter("pFileType", adVarChar, adParamInput, 255, sFileType)
      .Parameters.Append .CreateParameter("pDateCreated", adDBTimeStamp, adParamInput, 8, dDateCreated)
      .Parameters.Append .CreateParameter("pDateLastAccessed", adDBTimeStamp, adParamInput, 8, dDateLastAccessed)
      .Parameters.Append .CreateParameter("pFileSize", adInteger, adParamInput, 4, lFileSize)
      .Parameters.Append .CreateParameter("pFileExtension", adVarChar, adParamInput, 255, sFileExtension)
      .Parameters.Append .CreateParameter("pFileBinary", adLongVarBinary, adParamInput,2147483647, oFileBinary)
    Set .ActiveConnection = cn
  end with

Might find some usefull information and samples relating to using blobs here too:

areganAuthor Commented:
Thanks Alan

Just figured this out last nite. Your right - it was the paremeter length that was throwing it out.

Alan WarrenCommented:
Thanks Alan,

you know you can actually set the param size to the len(oFileBinary)

.Parameters.Append .CreateParameter("pFileBinary", adLongVarBinary, adParamInput,len(oFileBinary), oFileBinary)

Alan :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now