Microsoft KB Archive/190450

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

Article ID: 190450

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q190450



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( \imageFile): Set ADOprm = ADOCmd.CreateParameter (, adLongVarBinary, adParamInput, (ImgLen + 1))



Steps to Reproduce Behavior
  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                    Open Visual Basic and create a new Standard.exe project. From the Project menu, choose References. In the References dialog box, select the Microsoft ActiveX Data Objects library.  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= and PWD= 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= ;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  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.  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. </li></ol>

<div class="references_section">