asked on

ADo - Append chunk - Passing a blob greater than 64K as a stored procedure parameter

Hi,

I'm having problems passing a Blob greater than 64k. If I add a single chunk less than 64k there is no problem .. if I add multiple chunks I get an error on the cmd.Execute line. The error is:
"Application uses a value of the wrong type for the current operation." I've seen this error before when you pass a chunk greater than that specified. If I try pass a chunk greater than 64k I get an errrr due to data truncation.

I'm currently breaking my blob (byte array) into chunks of 60k.

Here's a code extract:

MaxChunkSize = 60000
Set prmData = cmd.CreateParameter("ReportFileData", adLongVarBinary, adParamInput, MaxChunkSize + 1)

prmData.Attributes = adFldLong
cmd.Parameters.Append prmData

For each Chunk in colChunks
prmData.AppendChunk Chunk
Next
cmd.Execute

Any ideas how to append this large blob ?
Alan.

leonstryker

You should be using ADO Stream instead:

http://www.w3schools.com/ado/ado_ref_stream.asp
http://www.betav.com/Files/Content/Whitepapers/Using%20the%20ADO%20Stream%20Object%20to%20Manage%20BLOBs.htm

Leon

aregan

ASKER

Thanks Leon...

I've just tried implenting ADO Stream but not all going to plan.
When I try to set my parameter value to the stream.read I'm getting an error saying it cannt be used in this context ....

- here's an extract

Set prmBin = cmd.CreateParameter("ReportFileData", adLongVarBinary, adParamInput, CHUNKSIZE + 1)
prmBin.Attributes = adFldLong
cmd.Parameters.Append prmBin

Set ADOStream = New ADODB.Stream
With ADOStream
.Open
.LoadFromFile MyBigFileName
.Position = 0
prmBin.Value = .Read
End With

cmd.Execute

leonstryker

What are you loading? look at the example on the bottom of the page in the second link.

Leon

aregan

ASKER

I was trying to base it on that example but the example updates a recordset blob...
I'm trying to pass a blob parameter to a stored procedure.

A.

ASKER CERTIFIED SOLUTION

leonstryker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial