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

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

推荐：classic asp中使用ADODB.Command防止sql injection

原始代码如下 Set Conn = Server.CreateObject("Adodb.Connection")Conn.Open "Provider=Microsoft.Jet.Oledb.4.0;Data Source="&Server.MapPath("*****.mdb")

I've written a stored procedure ("UpdateTable") that takes one parameter and inserts a new row into a table when run.

I've got the following code in an asp page:-

dim conn
dim cmd

set conn = server.createobject("ADODB.Connection")
conn.open "Provider=SQLNCLI11;Data Source=(localdb)\Projects;Persist Security Info=False;Password=password;User ID=username;Initial Catalog=SQLLearning"

set cmd = server.createobject("ADODB.Command")

cmd.ActiveConnection = conn
cmd.CommandText = "UpdateTable"
cmd.CommandType =adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("Text",adVarChar,adParamInput, 100)

cmd("Text") = "Jessica"
cmd.Execute

When I run this I get the following error:-

ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/sp.asp, line 14

推荐：SQL Debug && Stored Procedure Debug ----- SQL Server Management Studio

SQL Server Management Studio SQL 脚步Debug： 存储过程 Debug:

Where line 14 is the cmd.CommandType =adCmdStoredProc line. I've tried numerous things, including declaring a constant called adCmdStoredProc with the value of 4, and this still doesn't work. Can somebody please point me in the right direction?

The 'UpdateTable' code is:-

ALTER PROCEDURE UpdateTable
    @Text varchar(100)
AS
BEGIN

    SET NOCOUNT ON;
    INSERT INTO StoredProcTest (Text)
    OUTPUT inserted.ID, inserted.Text
    VALUES (@Text)
END
GO

sql sql-server stored-procedures asp-classic
  |
  this question edited Jun 12 '14 at 20:55 John Saunders 141k 21 186 332 asked Jun 12 '14 at 20:27 Mat Richardson 2,800 1 15 39      paste the UpdateTable procedure –  T McKeown Jun 12 '14 at 20:30      One more suggestion; in your stored proc this line INSERT INTO StoredProcTest (Text) here Text is a reserve word. Try escaping it saying INSERT INTO StoredProcTest ([Text]) –  Rahul Jun 12 '14 at 20:45      Might be silly, but try having Set cmd.ActiveConnection = conn i.e. add "Set", since it's an object trying to set it without the "Set" might cause weird problems. –  Shadow Wizard Jun 15 '14 at 6:33

---

---

 | 

1 Answers
1

Set cmd = Server.CreateObject("ADODB.Command")
    With cmd
        .ActiveConnection = conn
        .CommandType = adCmdStoredProc
        .CommandText = "UpdateTable"

        .Parameters.Append .CreateParameter("@Text",adVarChar,adParamInput, 100)
        .Parameters("@Text") = "Jessica"
        set rs = .Execute
    End With
set cmd = nothing

You should then be able to get the result doing something similar to;

if not rs.eof then
    response.write rs("ID")
end if

  |
  this answer answered Jun 18 '14 at 8:28 Albofish 86 1 7      What's with the extra indent on the With? Plus why bother with ADODB.Connection when you can pass the connection string directly to ADODDB.Command. ActiveConnection property? That way the connection is instantiated with the Execute() method and disposed when the command object is released from memory. –  Lankymart Jun 19 '14 at 10:23      The extra indent on the With is purely a coding style - personally I find it easier to read. Could you clarify what you mean in the second part of your comment please, as I haven't used ADODB.Connection in my example, unless you mean the conn object? In my scripts this is an object created in a header include and destroyed in a footer include - again purely a personal coding style. –  Albofish Jun 19 '14 at 15:20 1   Actually, @Albofish, using the ADODB.Connection like that isn't ideal, as it defeats the ability of the connection pool to handle things; you're locking up a connection for time when it's not being used. It's best to open it as late as possible and close it as early as possible. As for the indentation on the With, you are exactly right there; there's no functional difference. –  Andrew Barber Jun 19 '14 at 19:33      @AndrewBarber Thanks, I never knew that. –  Albofish Jun 23 '14 at 15:01      Very welcome, @Albofish! :) –  Andrew Barber Jun 23 '14 at 15:40

---

---

 | 

推荐：SQL Server Stored Procedures Optimization Tips

1. Use stored procedures instead of heavy-duty queries. This can reduce network traffic as your client will send to the server only the stored procedu