2021腾讯云限时秒杀，爆款1核2G云服务器298元/3年！（领取2860元代金券），
地址：https://cloud.tencent.com/act/cps/redirect?redirect=1062

2021阿里云最低价产品入口+领取代金券(老用户3折起)，
入口地址：https://www.aliyun.com/minisite/goods

推荐：ASP调用SQL Server视图和存储的过程

文章关键字：|ASP|SQL Server|视图|存储|过程|服务器|环境|微软|IIS|HTML|Web|ActiveX|界面|浏览器|模型| 　　一、前言 　　ASP (Active Server Pages) 是服务器

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
  @Pos = nvarchar(10)
  @Tar = nvarchar(50)
  @Rel = int
  @Datum = datetime2(0)
  @Aantal = real
  @Omsch = nvarchar(150)
  @Bedrag = money (will be changed to Decimal(17,2) soon
  @Totaal = money (will be changed to Decimal(17,2) soon)
  @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:

推荐：如何从 ASP 调用 SQL Server 存储过程

来源：http://support.microsoft.com/kb/q164485 有朋问，asp中如何调用存储过程。整理如下 概要 有关本文的 Microsoft Visual Basic .NET 版本，请参阅 306574

  <!-- 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)

sql-server parameters asp-classic
  |
  this question edited May 19 '15 at 11:33 asked May 19 '15 at 9:20 Nicolas 508 5 23 1   Try changing INSERT INTO boekingen ([Order],[Positie],[Tariefnummer],[Relatie],[Datum],[AantalE‌​enheden],[Omschrijvi‌​ng],[Bedrag],[Totaal‌​],[Status]) VALUES (@Order,@Pos,@Tar,@Rel,@Datum,@Aantal,@Omsch,@Bedrag,@Totaal‌​,@Status) to INSERT INTO boekingen ([Order],[Positie],[Tariefnummer],[Relatie],[Datum],[AantalE‌​enheden],[Omschrijvi‌​ng],[Bedrag],[Totaal‌​],[Status]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and set spSQL.CommandType = adCmdText. Example of this - stackoverflow.com/a/22037613/692942 –  Lankymart May 19 '15 at 9: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 (best ADO type mapping resource on the web!) –  Lankymart May 19 '15 at 10:24 2   Are your ADO constants defined adInteger for example is 3. If not consider using this method Using METADATA to Import DLL Constants 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. –  Lankymart May 19 '15 at 11:15  |  show more comments

1 Answers
1

解决方法

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. <!-- METADATA TYPE="typelib" UUID="00000200-0000-0010-8000-00AA006D2EA4" -->

• 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!

  |
  this answer answered May 19 '15 at 12:37 Nicolas 508 5 23

---

---

 | 

推荐：ASP连接sql server实例解析

1、首先确定自己的iis没有问题 2、其次确定自己sqlserver没有问题   然后在iis的目录wwwroot里，建立一个文件 名为testSqlServer.asp，编写代码如下即可 <% 'ole