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