PRB: Error Passing a Blob Argument to a Stored Procedure

• ActiveX Data Objects (ADO), versions 1.5, 2.0

SYMPTOMS

When you use the CreateParameter method to pass a Binary Large Object (BLOB) data to a stored procedure you need to specify the Maximum size of the data to be passed. However, when specifying the exact size of blob data the following error is generated when the parameter is actually passed:

Run-time error '3421':

The application is using a value of the wrong type for the current operation.

RESOLUTION

Add one to the value of Parameter.Size property when passing a BLOB.

In the following example, ImgLen is LOF(<path>\imageFile):

   Set ADOprm = ADOCmd.CreateParameter
   (, adLongVarBinary, adParamInput, (ImgLen + 1)) 

STATUS

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start ISQL_W, paste and then execute the following statement, which is used to prepare the table and stored procedure for the example:




   CREATE TABLE BLOB_Table
   (
    col1 char(1),
    BLOB image
   )
   GO

   if exists (SELECT * FROM sysobjects WHERE id =
      object_id('dbo.uspInsertBLOB') AND sysstat & 0xf = 4)
      DROP PROCEDURE dbo.uspInsertBLOB
   GO

   CREATE PROCEDURE uspInsertBLOB
   (
    @col1      char (1),
    @BLOB      image
   )
   AS
      INSERT BLOB_Table
      VALUES (@col1, @BLOB)
   GO
 

2. Open Visual Basic and create a new Standard.exe project.



3. From the Project menu, choose References. In the References dialog box, select the Microsoft ActiveX Data Objects 2.0 library.



4. In the Load event of the form, paste the following code:




   Dim ADOCmd As New ADODB.Command
   Dim ADOprm As New ADODB.Parameter
   Dim ADOcon As ADODB.Connection
   Dim intFile As Integer
   Dim ImgBuff() As Byte
   Dim ImgLen As Long

   Set ADOcon = New ADODB.Connection
   With ADOcon
       .Provider = "MSDASQL"
       .CursorLocation = adUseClient
       .ConnectionString = "driver=
           {SQL Server};server=(local);uid=sa;pwd=;database=pubs"
       .Open
   End With

   'Change this to the path of a GIF file you want to use for testing.
   IMG_FILE_GIF = "E:\Graphics\GIF\Image.gif"

   'Read/Store GIF file in ByteArray
    intFile = FreeFile
    Open IMG_FILE_GIF For Binary As #intFile
    ImgLen = LOF(intFile)
    ReDim ImgBuff(ImgLen) As Byte
    Get #intFile, , ImgBuff()
    Close #intFile

    Set ADOCmd.ActiveConnection = ADOcon
    ADOCmd.CommandType = adCmdStoredProc
    ADOCmd.CommandText = "uspInsertBLOB"

    Set ADOprm = ADOCmd.CreateParameter(, adChar, adParamInput, 1, "1")
    ADOCmd.Parameters.Append ADOprm

   'The datatype must be specified as adLongVarBinary

   'For the code to function correctly comment this line.

    Set ADOprm = ADOCmd.CreateParameter(, adLongVarBinary, _
          adParamInput, ImgLen)

    'Uncomment this line.
    'Set ADOprm = ADOCmd.CreateParameter(, adLongVarBinary, _
          adParamInput, (ImgLen + 1))
    ADOCmd.Parameters.Append ADOprm

   'Set the Value of the parameter with the AppendChunk method.

    ADOprm.AppendChunk ImgBuff()

   'The preceding example assumes you are using a small image file.
   'See the article reference in the REFERENCES section for handling a
   'large image file.

    ADOCmd.Execute

    Set ADOCmd = Nothing
    Set ADOprm = Nothing

    Exit Function 

5. Run the preceding code and you will get the error. To correct the preceding code, comment the Createparameter line containing only ImgLen, and uncomment the line that contains ImgLen+1. The code runs without error.



6. Open ISQL_W and run the following statement:




      SELECT * FROM BLOB_Table
 

The result window should show that a row was added to the table and that the BLOB column contains image/BLOB data.

REFERENCES

For additional information, please see the following article in the Microsoft Knowledge Base:

Q180368 HOWTO: Retrieve and Update a SQL Server Text Field Using ADO

Additional query words:

Keywords          : kbADO kbADO150bug kbADO200bug kbDatabase 
Version           : WINDOWS:1.5,2.0
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: July 13, 1999