PRB: Error Message 3421 Passing a BLOB Argument to a Stored Procedure

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 library.
4. In the Load event of the form, paste the following code. Modify the ConnectionString to connect to your SQL Server, and modify IMG_FILE_GIF to point to an image file on your system:
   Note You must change UID=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

      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=<username>;pwd=<strong password>;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
   					

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.