Parameter object is improperly defined. Inconsistent or incomplete information was provided.(varbinary(max))

Question

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 9-Nov-20 0:50am

Member 14832230

Updated 11-Nov-20 23:53pm

Add a Solution

Comments

Richard MacCutchan 9-Nov-20 7:51am

Try using the actual size in the vbin_Document parameter, rather than -1.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2020-11-11T23:53:00

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