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

Lei

You need to give adVarChar a value, it's empty right now and that's invalid. Use docs: w3schools.com/asp/met_comm_createparameter.asp. adVarChar should have a value of 200. I actually have a nice function that does this automatically with VarType, when I get my VM back up and source code down I'll post it for you. – Paeon

Secondly, Parameters do sql injection protection internally, you don't need to manually do it. If you parameter your whole query you can forget about sql injection. – Paeon

Also, if you install visual studio 2015, you can enable ASP server side debugging in IIS at the machine level (root node of IIS) and at the site level. Then install the Visual Studio Remote debugger on the IIS Server. You can then attach visual studio remotely to the w3wp.exe where the site is running. Then you can hit the site and actually step through the code, and it will breakpoint at the error, then you can use the debugger watches to see values, and figure out the problem. – Paeon

And furthermore, if you add <% Option Explicit %> to the first line of the page causing the error, it will turn on strict variable checking and throw errors if variables are not defined, this will tell you for sure if adVarChar has no value anywhere. – Paeon

Possible duplicate of Using SQL Parameters in ASP CLASSIC, object improperly defined error – Steamtight

@Ryios Why do that when you can just declare the Type Library using METADATA tag in the page or global.asa and you have access to all the ADODB DLL constants you could want. Sounds like you have a function that re-invents the wheel. I'd even recommend adovbs.asp over that and that is saying something. – Steamtight

Ah yeah, I didn't think about moving the constants to global asa, will do that. In many cases though I've seen people use adVarChar, or adCmdText, etc and they don't have them defined Anywhere. That's what I was getting at. As to reinventing the wheel, what I'm actually doing is building a POCO generator for classic asp, using Node and T4 templates in visual studio. Like PetaPoco, but for Classic Asp. – Paeon

@Ryios Wow seriously? That has to be the ultimate definition of re-inventing the wheel. How would your average ASP code translate to POCOs? It's an interesting idea I guess, good luck with it. – Steamtight

@Ryios I wasn't suggesting defining them in the global.asa although you could my suggest was to let IIS load them direct from the Type Library for you that way you don't have to define then just add a METADATA directive to the page/global.asa. – Steamtight

Ah, I did not know about the TypeLibrary declarations. I've recently been pushed into classic asp and come from newer pastures. I do have experience with com objects though, I even wrote some we use in classic asp atm. As to the poco thing, you can write classes in VBScript to be the pocos, the T4 template I am working on will just write them for me, so instead of writing 300 lines of asp for each table as a class, I'll just set a table name in the T4 template and it will generate the class automatically. – Paeon

@Ryios fair enough, although surely switching to something like .net would be the smarter thing instead of investing time in a tech that is in all honesty dead and buried? – Steamtight

Would if I could, I'm a .net dev actually, mvc 5/6, nuget, etc etc.. This project I'm on is the companies last Classic project, The decision to make it was well past made before I had a say in anything. And as we have tons of other classic asp things in production the things I make right now come in handy in cleaning up the bad code and making it easier to port to .net or java spring. – Paeon

adVarChar is already defined. As for installing anything on the IIS Server, I wish I could, but we don't have a lot of ASP sites so the only place I have to put them is on a shared hosting server with limited access to things like that. @Ryios <% Option Explicit %> was a great idea. It's telling me that "conn" is not defined. I think my lack of experience with ASP might be an issue here, but I thought "set conn = Server.CreateObject("ADODB.Connection")" was defining it. I'm not great with ASP, but is there any issues with my connection set up? – Lei

PS: These problems started with our hosting company upgrading their servers a few months back, before then it was working flawlessly for years. So I suspect the possibility of this being an antiquated method and/or security setting issue more so than anything explicitly wrong with the code. I just don't know what things to look for. – Lei

Can you tell me anything about the old hosting location vs the new one. E.g. version of windows vs current? Version of IIS vs current? 32 bit vs 64 bit etc? – Paeon

@Ryios I know that the old server was IIS-7 and the new one is IIS-8. I do not know about the version of Windows, but I'd assume they are both 64-bit based on the versions of IIS. Its on a Plesk goDaddy shared hosting windows server so it's been hard researching the problem with limited access to server settings, etc. – Lei

You could just be having a problem because the application pool on the old server was probably set to classic mode and the app pool on the new server is integrated mode maybe. Or maybe the new app pool is running in 64 bit mode and the app relies on a dll that only has a 32 bit version so it needs to run in 32 bit mode to work. It's also possible your typelib metadata tag in global.asa for ADO is referring to an ado DLL that does not exist on the new server which would make more sense in context of your error. – Paeon

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

Steamtight

I already have an included config file with adVarChar defined in it just like this that got missed when considering my code snippet. – Lei

@Lei That's fine but then again this answer isn't just for you. – Steamtight

This was awesome advice, off topic from OP, but the MetaData tag removed a WHOLE lot of nasty declare code from an app I was picking up development on. Also to add, parameterized queries do work in classic asp, but you can't use the name. Classic asp only supports the ? wildcard in queries which is basically going to grab your parameters in the order they were added to the command object. So you can just leave the name (first parameter) of CreateParameter empty. Unless you know of a different wildcard I don't. e.g. "where email = @email" doesn't work, but "where email = ?" does. – Paeon

@Ryios Since ADO 2.6 you can use the ADODB.Command property NamedParameters to allow you to query parameters by name. Not all providers support it though. – Steamtight

Ah, so maybe postgresql doesn't support it and only works with Question Marks. – Paeon

@Ryios I've not worked with PostgreSQL so can't comment but the support is provider specific, only thing I can suggest is give it a try, remember to set NamedParameters = True first though. – Steamtight

Answer 2

In the places where you are defining adVarChar, and other ADO magic numbers, get rid of them completely.

Instead add this to the top of your global.asa (create one if it doesn't) exist.

<!--METADATA TYPE="TypeLib" file="C:\Program Files (x86)\Common Files\System\ado\msado15.dll" -->

This will import the msado15 type library which already has all of those magic numbers defined. It will pull in all the defines for you automatically. Meaning you won't have to define adVarChar etc yourself, the metadata tag will import them automatically for you. Credit to @Lankymart for this as his comment lead me to discovering this and figuring it out, and it works great.

Now, turn create a test asp page that's empty and add some test code to it with option explicit on.

<%
    Option Explicit
    Response.Write "adVarChar: " & adVarChar
%>

If you get an error that adVarChar is not defined, then msado15.dll is not on the server. So you will need to contact godaddy etc to have them tell you the path to whatever version of MSADO is on there and change your metadata tag to reference the right version.

Paeon

Which part fixed it? Adding the msado15.dll metadata tag, or finding out that the server didn't have ado15 on it? If it was the metadata tag, give the answer to LankyMart because I wouldn't even know how to do this if he hadn't mentioned it in his comment. I learned it after the fact :) – Paeon

It was the msado15.dll. And yes, I agree that Lankymart discovered the problem, but I find that this answer is a lot more clear for people who don't already know much about ASP; so, two thumbs up to both of you guys for all of your help. Thanks. – Lei