ADODB.Parameters error '800a0e7c' Parameter object is improperly defined. Inconsistent or incomplete information was provided

Question

I'm primarily an PHP developer, but I have some old ASP one of our previous developers made that broke and I can't figure out how to fix it. We have a program that sends some variables to a listener page that compares that data to registration codes an msSQL database and then lets the program know if the registration code is valid.

I'm getting the following error where

.Parameters.Append .CreateParameter("@code", adVarChar, 1, 50, x)

is line 134:

ADODB.Parameters error '800a0e7c'

Parameter object is improperly defined. Inconsistent or incomplete information was provided.

/checkregistrationpro.asp, line 134

I've already specified any named constants in an include file that I have not included in the code, so it isn't to do with that.

My Connection String (I've already verified that these settings are right):

set conn = Server.CreateObject("ADODB.Connection")
set cmd = Server.CreateObject("ADODB.Command")
sConnString = "Provider=sqloledb; Data Source=MYDATASOURCE; Initial Catalog=MYCATALOG; User ID=MYUSERID; Password='MYPASSWORD';"
conn.Open sConnString

My Code:

...

Function BlockInjectCode(StrVal)
    BlockInjectCode = Replace(StrVal,"--","")
    BlockInjectCode = Replace(BlockInjectCode,"'","")
    BlockInjectCode = Replace(BlockInjectCode,"""","")
    if instr(lcase(BlockInjectCode),"<") > 0 then
        BlockInjectCode = ""
    end if
End Function

    x = BlockInjectCode(Request.QueryString("rid"))
    uid = BlockInjectCode(Request.QueryString("uid"))
    chkcode = BlockInjectCode(Request.QueryString("Code"))
    CheckPro = BlockInjectCode(Request.QueryString("pro"))
    CheckProProd = BlockInjectCode(Request.QueryString("prod"))
    CheckProMac = BlockInjectCode(Request.QueryString("mac"))
    MacAdd = CheckProMac

    CodeValid = False

    if x <> "" and uid <> "" then

        '-- Get information about this registration code.   
        sqlStr = "select * from MYTABLE where Code = ? and IsValid = 1"

        set cmdCodes = Server.CreateObject("ADODB.Command")
        Set cmdCodes.ActiveConnection = Conn
        cmdCodes.CommandText = sqlStr
        with cmdCodes
            .Parameters.Append .CreateParameter("@code", adVarChar, 1, 50, x)
        end With    
        Set rsCodes = cmdCodes.execute      
...

Answer 1

Common occurrence is likely you do not have adVarChar defined, so the CreateParameter() method is "improperly defined".

This is just an example of one of the many named constants found in the ADODB Library. A messy approach to dealing with this is to just define the value yourself something like;

Const adVarChar = 200

The problem with this approach is you then have to define all the named constants which can be a headache. Another approach is to just skip the whole named constants thing and just use the integer values, so the call would be;

.Parameters.Append .CreateParameter("@code", 200, 1, 50, x)

However this isn't easy to read although it looks as though you are doing this already with the ParameterDirectionEnum value of 1 which is the named constant adParamInput in the ADODB Library. Regardless I wouldn't recommend this approach.

A slightly better approach is to use an #include directive so that it includes in the calling page all the named constant definitions you could want and this is how most do it. Microsoft provide a pre defined file for this very purpose with IIS (or possibly the MDAC library installation I'm not sure off the top of my head) known as adovbs.inc or adovbs.asp, by including this file your page would have access to all the named constant definitions within.

The reason for all this is VBScript doesn't support type libraries, so in a Client scenario defining them yourself or copying and pasting from the adovbs.inc file is your only option. However in a Server scenario we still have the power of IIS which lets us do some funky things.

Wouldn't it be nice if the Type Library could just be added once and you don't have to worry about it?, no annoying constants to have to define? Let's face it they already exist in the Type Library so why can't we get them from there? Well turns out we can thanks to the METADATA directive.

Here is an example;

<!--
   METADATA    
   TYPE="TypeLib"    
   NAME="Microsoft ActiveX Data Objects 2.5 Library"    
   UUID="{00000205-0000-0010-8000-00AA006D2EA4}"    
   VERSION="2.5"
-->

The beauty of this approach is you can use for any Type Library (ideally exposed to COM) and you can define in one page, or add it into the global.asa to have defined across the entire Web Application.

With this approach you are then safe to use code like;

.Parameters.Append .CreateParameter("@code", adVarChar, adParamInput, 50, x)

---

Useful Links

• VBScript ADO Programming
• Answer to ASP 3.0 Declare ADO Constants w/out Including ADOVBS.inc
• Answer to Passing Parameters to a Stored Procedure using ASP
• Using METADATA to Import DLL Constants