在经典 ASP 参数化 SQL 中使用变量 [英] Using variables in Classic ASP parameterized SQL

问题描述

我正在从动态生成的(尽管经过大量清理)SQL 查询过渡到参数化 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.

我使用的是经典 ASP，用 jScript 编码.

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

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.

我已经通过用问号替换@UserID、@PgID 和 @Rating，删除 DECLARE，并按正确的顺序放置 Append/CreateParemeter 行(每个行一个?)来实现这一目标.然而，它确实涉及多次调用 Append/CreateParameter 行(每个 UserID 实例一次)，这只是草率.

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.

这段代码不会抛出任何错误，但不会向数据库写入任何内容.无论如何，我不知道为什么它可以使用问号(和重复参数)，但不适用于声明的变量.

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.

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

如果没有办法，有没有办法避免每次需要时都重复相同的 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;

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

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.

推荐答案

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

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);"

以上假设是 SQL Server 2008 或更高版本.在较低版本上，您需要单独的一行进行赋值:

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

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);"

这篇关于在经典 ASP 参数化 SQL 中使用变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！