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

ID: Q190450


The information in this article applies to:
  • ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP2


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:


  2. 
       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 
  3. Open Visual Basic and create a new Standard.exe project.


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


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


  6. 
       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 
  7. 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.


  8. Open ISQL_W and run the following statement:


  9. 
          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
MSDN Visual Studio 6.0; search on: "Using Visual InterDev 1.0 to Pass Parameters to and from Microsoft SQL Server Stored Procedures"

Additional query words: kbado200 kbado210sp2 kbsqlserver kbstoredproc kbADO210bug kbADO kbADO150bug kbADO200bug kbDatabase

Keywords : kbGrpVBDB kbGrpMDAC kbDSupport
Version : WINDOWS:1.5,2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbprb


Last Reviewed: November 9, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.