Click here to Skip to main content
15,041,525 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Parameter object is improperly defined. Inconsistent or incomplete information was provided.(varbinary(max))
.

I need to upload a pdf file to a varbinary(max) SQL .

What I have tried:

I used this code for store procedure. I receive the error at @vbin_Document

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open ConnectionString_ 
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = objConnection 
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "[Documents_Add]"
cmd.parameters.append cmd.createParameter("@str_NameByFacility",    adVarChar, adParamInput, 25, NameByFacility)
cmd.parameters.append cmd.createParameter("@str_ScanCode",          adVarChar, adParamInput, 70, ScanCode)
cmd.parameters.append cmd.createParameter("@vbin_Document",             adVarBinary, adParamInput, -1, Document)
cmd.parameters.append cmd.createParameter("@str_DocumentType",          adVarChar, adParamInput, 100, DocumentType)
cmd.parameters.append cmd.createParameter("@str_NodeName",          adVarChar, adParamInput, 100, NodeName)
cmd.parameters.append cmd.createParameter("@str_ApplicationName",   adVarChar, adParamInput, 100, ApplicationName)
cmd.parameters.append cmd.createParameter("@str_UserName",          adVarChar, adParamInput, 100, UserName)
cmd.parameters.append cmd.createParameter("@bit_isDone",            adInteger, adParamReturnValue)
cmd.parameters.append cmd.createParameter("@int_ErrorCode",         adInteger, adParamReturnValue)
cmd.parameters.append cmd.createParameter("@bint_ErrorID",          adInteger, adParamReturnValue)
cmd.execute 
Posted
Updated 11-Nov-20 23:53pm
Comments
Richard MacCutchan 9-Nov-20 7:51am
   
Try using the actual size in the vbin_Document parameter, rather than -1.

1 solution

As @Richard-MacCutchan has already pointed out, the problem in line
VB
cmd.parameters.append cmd.createParameter("@vbin_Document", adVarBinary, adParamInput, -1, Document)
is that you have given a data size of -1.

If you are not sure what the size should be then you would normally omit it entirely rather than use an impossible value (nothing can be -1 in length), but if you check the documentation CreateParameter Method (ADO) - SQL Server | Microsoft Docs[^] you will note
Quote:
If you specify a variable-length data type in the Type argument, you must either pass a Size argument or set the Size property of the Parameter object before appending it to the Parameters collection; otherwise, an error occurs.
Unfortunately you haven't given us enough code to determine exactly how to find out the size of the variable Document, but you should calculate that first and use that value when creating the parameter.

Alternatively, you could work out the defined size of the field and use that - see DefinedSize Property - SQL Server | Microsoft Docs[^]

At worst, you could just use a large number (not recommended, backstop only)
VB
cmd.parameters.append cmd.createParameter("@vbin_Document", adVarBinary, adParamInput, 8000, Document)
Personally, I never store documents in databases - I store them on a File System (it's what they are designed for after all) and record the location of the file on the database as a string (along with other information for integrity checks such as date last amended, hash value). Having been caught out previously I also only store the path relative to a "base folder" - we had to move repository once and the server name had been embedded in the data - it was not a fun time.

You would still have to provide the length of the data, but it would be trivial to work out, or you could just use the max path length of 260
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900