在经典的ASP参数化SQL中使用变量。

I'm transitioning from dynamically generated (albeit heavily sanitized) SQL queries, to parameterized SQL, and I'm running into some trouble with the variable names.

我正在从动态生成的(尽管经过了大量的清理)SQL查询过渡到参数化的SQL，并且在变量名上遇到了一些麻烦。

I'm using Classic ASP, coded in jScript.

我使用的是用jScript编码的经典ASP。

The code below takes a rating value (1-5) and puts it in the database. First it deletes all of the user's prior ratings for that object, and then writes the new rating into the database. The function has already received and I've parsed the Rating variable (a TinyInt). The UserID and PgID values, both integer, have also been sent.

下面的代码接受一个评级值(1-5)并将其放入数据库。首先，它删除用户对该对象的所有先前评级，然后将新的评级写入数据库。函数已经收到，我已经解析了评级变量(一个TinyInt)。UserID和PgID值都是整数，也已发送。

I've already gotten this working by replacing @UserID, @PgID and @Rating with question marks, removing the DECLAREs, and placing the Append/CreateParemeter lines in the proper order (one for each ?). It does involve calling the Append/CreateParameter line multiple times however (once for each instance of UserID), which is just sloppy.

我已经用问号替换了@UserID、@PgID和@Rating，删除声明，并按正确的顺序放置Append/CreateParemeter行(每个行一个?)但是，它确实涉及多次调用Append/CreateParameter行(对每个UserID实例调用一次)，这非常草率。

This chunk of code doesn't throw any errors, but it isn't writing anything to the database. Anyway, I don't know why it would work with the question marks in place (and duplicate parameters), but not work with the declared vars.

这段代码不会抛出任何错误，但它不会向数据库写入任何内容。无论如何，我不知道为什么它可以使用问题标记(以及重复的参数)，但是不能使用声明的vars。

How can I use named variables when using parameterized SQL in Classic ASP jScript?

如何在经典的ASP jScript中使用参数化SQL时使用命名变量?

If there's no way to do it, is there a way to avoid having to repeat the same Append/CreateParamenter line every single time I need, for example, the UserID?

如果没有办法，有没有办法避免每次需要时重复相同的Append/CreateParamenter线，例如UserID?

var sqlReview = "DECLARE @UserID AS Int, @PgID AS Int, @Rating AS TinyInt;"
sqlReview += "DELETE FROM PGrating WHERE (UserID = @UserID) AND (PgID = @PgID);"
sqlReview += "INSERT INTO PGrating (InsertDate, PgID, UserID, Rating) VALUES (GETDATE(), @PgID, @UserID, @Rating);"

var thisConnection = Server.CreateObject("ADODB.Connection");
thisConnection.connectionString = connectString;
thisConnection.Open();

var thisCommand = Server.CreateObject("ADODB.Command");
thisCommand.ActiveConnection = thisConnection;
thisCommand.CommandText = sqlReview;
thisCommand.CommandType = adCmdText;
thisCommand.Parameters.Append(thisCommand.CreateParameter("@UserID", adSmallInt, adParamInput, 2, UserID));
thisCommand.Parameters.Append(thisCommand.CreateParameter("@PgID", adInteger, adParamInput, 4, PgID));
thisCommand.Parameters.Append(thisCommand.CreateParameter("@Rating", adTinyInt, adParamInput, 1, Rating));
var rs = thisCommand.Execute();
thisCommand = null;
thisConnection = null;

I know there might be simpler ways of putting ratings into a database, but this example was created primarily because it was simple and I needed something simple while I learned how to use parameterized SQL. It was also simplified further (and tested again) before I put it up here. I can build the more complex queries once I get this one working. And yes, I'll write stored procedures, but that comes later, after everything is working.

我知道可能有更简单的方法将评级放入数据库，但创建这个示例主要是因为它很简单，我需要一些简单的东西，同时我还学习了如何使用参数化SQL。在我把它放在这里之前，它也被进一步简化(并再次测试)。我可以构建更复杂的查询。是的，我将编写存储过程，但这将在一切正常之后进行。

3 个解决方案

#1

If you want to avoid repetition, you can continue to DECLARE your variables and set their value once:

如果你想避免重复，你可以继续声明你的变量并设置它们的值一次:

var sqlReview = "DECLARE @UserID AS Int = ?, @PgID AS Int = ?, @Rating AS TinyInt = ?;"
sqlReview += "DELETE FROM PGrating WHERE (UserID = @UserID) AND (PgID = @PgID);"
sqlReview += "INSERT INTO PGrating (InsertDate, PgID, UserID, Rating) VALUES (GETDATE(), @PgID, @UserID, @Rating);"

The above is assuming SQL Server 2008 or higher. On lower versions, you'd need a separate line for assignment:

以上假设SQL Server 2008或更高版本。在较低的版本中，你需要一个单独的行来分配任务:

var sqlReview = "DECLARE @UserID AS Int, @PgID AS Int, @Rating AS TinyInt;"
sqlReview += "SELECT @UserID = ?, @PgID = ?, @Rating = ?;"
sqlReview += "DELETE FROM PGrating WHERE (UserID = @UserID) AND (PgID = @PgID);"
sqlReview += "INSERT INTO PGrating (InsertDate, PgID, UserID, Rating) VALUES (GETDATE(), @PgID, @UserID, @Rating);"

#2

When using adCmdText, you have to declare your parameters using ? placeholders. When adding the parameters, ADO determines the parameter sequence based on the order you add them.

在使用adCmdText时，必须使用?占位符。在添加参数时，ADO根据添加参数的顺序确定参数序列。

However, once you convert this to a stored procedure, you can use named parameters as you are trying to do, and sequence will not matter. But you will have to move your query to a stored proc to get the results you want.

但是，一旦您将其转换为存储过程，您就可以使用命名参数，而序列将无关紧要。但是您将不得不将查询移动到存储的proc中，以得到您想要的结果。

See this MSDN article for more info.

更多信息请参阅MSDN的文章。

#3

You are using an ADO provider, not a SQL Server provider.

您使用的是ADO提供程序，而不是SQL服务器提供程序。

ADO parameterized queries syntax is ? for the parameters, not names.

ADO参数化查询语法是?对于参数，而不是名称。

#1