Using SQL Parameters in ASP CLASSIC, object improperly defined error

Question

I am trying to protect my INSERT statement from SQL injection using Parameters, but for some reason I am getting the error: Parameter object is improperly defined. Inconsistent or incomplete information was provided...

Though, I have no clue what is causing it.

My SQL statement is as follows:

Set spSQL = Server.CreateObject("ADODB.Command")
Set spSQL.ActiveConnection = con_vhs
vrdSQL = "INSERT INTO boekingen ([Order],[Positie],[Tariefnummer],[Relatie],[Datum],[AantalEenheden],[Omschrijving],[Bedrag],[Totaal],[Status]) VALUES (@Order,@Pos,@Tar,@Rel,@Datum,@Aantal,@Omsch,@Bedrag,@Totaal,@Status)"
spSQL.commandtext= vrdSQL
spSQL.Parameters.Append(spSQL.CreateParameter("@Order", adInteger,,,1506))
spSQL.Parameters.Append(spSQL.CreateParameter("@Pos", adVarWChar,,10,"0"))
spSQL.Parameters.Append(spSQL.CreateParameter("@Tar", adVarWChar,,50,"VRD"))
spSQL.Parameters.Append(spSQL.CreateParameter("@Rel", adInteger,,,4020))
spSQL.Parameters.Append(spSQL.CreateParameter("@Datum", adDate,,,iDatumTotaal))
spSQL.Parameters.Append(spSQL.CreateParameter("@Aantal", adSingle,,,"5,25"))
spSQL.Parameters.Append(spSQL.CreateParameter("@Omsch", adVarWChar,,150,OmschrijvingGoed))
spSQL.Parameters.Append(spSQL.CreateParameter("@Bedrag", adDecimal,,,sBedrag))
spSQL.Parameters.Append(spSQL.CreateParameter("@Totaal", adDecimal,,,sTotaal))
spSQL.Parameters.Append(spSQL.CreateParameter("@Status", adInteger,,,StatusVRD))

Dim oPrm
For Each oPrm In spSQL.Parameters
    If oPrm.Type = adDecimal Then
        oPrm.NumericScale = 2
        oPrm.Precision = 17
    End If
Next
set rst= spSQL.execute(vrdSQL)

Some parameters values are set hard-coded (just for test purposes) and some are set using variables. I am getting the error however already on the first append parameter line.. What am I doing wrong?

Some additional information:

I am inserting the data into a SQL 2012 Server.
The types in the SQL server are as follows:

@Order = int <br/>
@Pos = nvarchar(10) <br/>
@Tar = nvarchar(50) <br/>
@Rel = int <br/>
@Datum = datetime2(0) <br/>
@Aantal = real <br/>
@Omsch = nvarchar(150) <br/>
@Bedrag = money (will be changed to Decimal(17,2) soon <br/>
@Totaal = money (will be changed to Decimal(17,2) soon) <br/>
@Status = int

UPDATE 2

Set spSQL = Server.CreateObject("ADODB.Command")
Set spSQL.ActiveConnection=con_vhs
spSQLCommandType = adCmdText
vrdSQL="INSERT INTO boekingen ([Order],[Positie],[Tariefnummer],[Relatie],[Datum],[AantalEenheden],[Omschrijving],[Bedrag],[Totaal],[Status]) VALUES (?,?,?,?,?,?,?,?,?,?)"
spSQL.commandtext= vrdSQL
spSQL.Parameters.Append spSQL.CreateParameter("@Order", adInteger,adParamInput,4)
spSQL.Parameters.Append spSQL.CreateParameter("@Positie", adVarWChar,adParamInput,10)
spSQL.Parameters.Append spSQL.CreateParameter("@Tariefnummer", adVarWChar,adParamInput,50)
spSQL.Parameters.Append spSQL.CreateParameter("@Relatie", adInteger,adParamInput,4)
spSQL.Parameters.Append spSQL.CreateParameter("@Datum", adDate,adParamInput,0)
spSQL.Parameters.Append spSQL.CreateParameter("@AantalEenheden", adSingle,adParamInput,4)
spSQL.Parameters.Append spSQL.CreateParameter("@Omschrijving", adVarWChar,adParamInput,150)
spSQL.Parameters.Append spSQL.CreateParameter("@Bedrag", adDecimal,adParamInput,0)
spSQL.Parameters.Append spSQL.CreateParameter("@Totaal", adDecimal,adParamInput,0)
spSQL.Parameters.Append spSQL.CreateParameter("@Status", adInteger,adParamInput,4)
spSQL.Parameters("@Order").Value = 1506
spSQL.Parameters("@Positie").Value = "0"
spSQL.Parameters("@Tariefnummer").Value = "VRD"
spSQL.Parameters("@Relatie").Value = 4020
spSQL.Parameters("@Datum").Value = iDatumTotaal
spSQL.Parameters("@AantalEenheden").Value = TestAantal
spSQL.Parameters("@Omschrijving").Value = OmschrijvingGoed
spSQL.Parameters("@Bedrag").Value = sBedrag
spSQL.Parameters("@Totaal").Value = sTotaal
spSQL.Parameters("@Status").Value = StatusVRD

Dim oPrm
For Each oPrm In spSQL.Parameters
    If oPrm.Type = adDecimal Then
        oPrm.NumericScale = 2
        oPrm.Precision = 17
    End If
Next
set rst= spSQL.execute(vrdSQL)

Update 2, Removed the parenthesis around the .append and added the right size values in the parameters. Still getting the error:

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.. . ON spSQL.Parameters.Append spSQL.CreateParameter("@Order", adInteger,adParamInput,4)

UPDATE 3

This update was after inserting the meta tag in my global.asa file.

I updated my global.asa file with the following:

<!-- METADATA TYPE="typelib" UUID="00000200-0000-0010-8000-00AA006D2EA4" -->

The global.asa file now looks as follows:

<script language="VBScript" runat="Server">
    Sub Session_OnStart()
        <!-- METADATA TYPE="typelib" UUID="00000200-0000-0010-8000-00AA006D2EA4" -->

        Session.Timeout = 480
    End Sub
</script>

The code of the parameters remained the same. Now I am getting (happily) a different error on the execute:

[Microsoft][ODBC SQL Server Driver]Optional feature not implemented. . ON set rst= spSQL.execute(vrdSQL)

Try changing `INSERT INTO boekingen ([Order],[Positie],[Tariefnummer],[Relatie],[Datum],[AantalEenheden],[Omschrijving],[Bedrag],[Totaal],[Status]) VALUES (@Order,@Pos,@Tar,@Rel,@Datum,@Aantal,@Omsch,@Bedrag,@Totaal,@Status)` to `INSERT INTO boekingen ([Order],[Positie],[Tariefnummer],[Relatie],[Datum],[AantalEenheden],[Omschrijving],[Bedrag],[Totaal],[Status]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)` and set `spSQL.CommandType = adCmdText`. Example of this - http://stackoverflow.com/a/22037613/692942 — user692942, May 19 '15 at 09:48
Thank you for the link @Lankymart. Didn't help though. I changed the values to question marks as suggested and added the `CommandType`. I also added parameters values and left them out in the initial Create parameters. See my original post for the updated code. I am now getting the error: `Expected identifier. .` on `spSQL.Parameters.Append (spSQL.CreateParameter("@Order", adInteger,adParamInput,0))` — Nicolas, May 19 '15 at 10:13
@Lankymart Just saw I had a ' somewhere in my code. Removed it and got the real error message now:) (Same as before) :: `Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.. .` ON `spSQL.Parameters.Append (spSQL.CreateParameter("@Order", adInteger,adParamInput,0))` — Nicolas, May 19 '15 at 10:17
Why you passing `Size` as `0`? `adInteger` should be `4`. See [Data Type Mapping](http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx) *(best ADO type mapping resource on the web!)* — user692942, May 19 '15 at 10:24
First time I am using this way of adding parameters, saw it somewhere and read somewhere that it doesn't matter for INT (not sure where anymore). Though, just thought the same thing just a few min a go and already try'd to change the size of the first line (the int) to 4. Didn't had any effect though, same error.. — Nicolas, May 19 '15 at 10:27
You also don't need `()` around `.Append`. Btw just because something is suggested but doesn't fix your problem straight away doesn't mean it is wrong just means there are more fundamental errors in the code that need addressing first. — user692942, May 19 '15 at 10:29
Very true, I added all the (right sizes) using the information on your link. And also removed the parenthesis on the append. — Nicolas, May 19 '15 at 10:36
Can you update the code in the question please. Are you still getting errors? — user692942, May 19 '15 at 10:57
Just updated the original post. Still getting the same error message on my first append line. — Nicolas, May 19 '15 at 11:10
Are your ADO constants defined `adInteger` for example is 3. If not consider using this method [Using METADATA to Import DLL Constants](http://www.4guysfromrolla.com/webtech/110199-1.shtml) that way you place it once in `global.asa` and you can use the constants in any page in your web application. As a test try changing `adInteger` to 3 and see if the error moves to the next line in your code. — user692942, May 19 '15 at 11:15
UPDATE: Just saw that my `spSQL.CommandType` was missing its dot. Updated it and now I get the error message: `Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.. .` ON the `spSQL.CommandType = adCmdText` — Nicolas, May 19 '15 at 11:17
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/78180/discussion-between-nicolas-and-lankymart). — Nicolas, May 19 '15 at 11:34

score 1 · Accepted Answer · edited May 23 '17 at 11:51

Alright, after much discussion with Lankymart, which continued in the chat, I finally got it fixed.

Because the error was not fixed with just one adjustment, ill post all the adjustments made.

First of all I removed the first (unnecessary) parenthesis of spSQL.Parameters.Append(spSQL.CreateParameter("@Order", adInteger,,,1506))
Secondly, I replaced the @vars in my SQL string with question marks.
Then I separately added the Parameters values and also added the spSQLCommandType = adCmdText (pointed out in this link: stackoverflow.com/a/22037613/692942)
I also changed the SIZES of all the parameter data types to the right size (using this link: Data type mapping) instead of default nothing or 0.
The biggest problem however was caused by not including the right DDL file for handling my ADO parameters. This was added in the global.asa file. 
A few smaller problems remained with one of them being a error on the execute which was changed to: Call spSQL.execute(adExecuteNoRecords)
The last problem was caused because adDate wasn't recognized or viable for my SQL server 2012. I changed the ADO type adDate to adDBTimeStamp which solved the problem.

The entire 'fixed' code is as follow:

Set spSQL = Server.CreateObject("ADODB.Command")
                Set spSQL.ActiveConnection=con_vhs

                spSQL.CommandType = adCmdText

                vrdSQL="INSERT INTO boekingen ([Order],[Positie],[Tariefnummer],[Relatie],[Datum],[AantalEenheden],[Omschrijving],[Bedrag],[Totaal],[Status]) VALUES (?,?,?,?,?,?,?,?,?,?)"
                spSQL.commandtext= vrdSQL

                spSQL.Parameters.Append spSQL.CreateParameter("@Order",adInteger,adParamInput,4)
                spSQL.Parameters.Append spSQL.CreateParameter("@Positie", adVarWChar,adParamInput,10)
                spSQL.Parameters.Append spSQL.CreateParameter("@Tariefnummer", adVarWChar,adParamInput,50)
                spSQL.Parameters.Append spSQL.CreateParameter("@Relatie", adInteger,adParamInput,4)
                spSQL.Parameters.Append spSQL.CreateParameter("@Datum", adDBTimeStamp,adParamInput,0)
                spSQL.Parameters.Append spSQL.CreateParameter("@AantalEenheden", adSingle,adParamInput,4)
                spSQL.Parameters.Append spSQL.CreateParameter("@Omschrijving", adVarWChar,adParamInput,150)
                spSQL.Parameters.Append spSQL.CreateParameter("@Bedrag", adDecimal,adParamInput,0)
                spSQL.Parameters.Append spSQL.CreateParameter("@Totaal", adDecimal,adParamInput,0)
                spSQL.Parameters.Append spSQL.CreateParameter("@Status", adInteger,adParamInput,4)

                spSQL.Parameters("@Order").Value = 1506
                spSQL.Parameters("@Positie").Value = "0"
                spSQL.Parameters("@Tariefnummer").Value = "VRD"
                spSQL.Parameters("@Relatie").Value = 4020
                spSQL.Parameters("@Datum").Value = iDatumTotaal
                spSQL.Parameters("@AantalEenheden").Value = TestAantal
                spSQL.Parameters("@Omschrijving").Value = OmschrijvingGoed
                spSQL.Parameters("@Bedrag").Value = sBedrag
                spSQL.Parameters("@Totaal").Value = sTotaal
                spSQL.Parameters("@Status").Value = StatusVRD

                Dim oPrm

                For Each oPrm In spSQL.Parameters
                    If oPrm.Type = adDecimal Then
                        oPrm.NumericScale = 2
                        oPrm.Precision = 17
                    End If
                Next


                Call spSQL.execute(adExecuteNoRecords)

Thanks to Lankymart for the awesome help fixing this problem!

Using SQL Parameters in ASP CLASSIC, object improperly defined error

1 Answers1

Linked