在传统的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 codeD。

下code需要等级值（1-5），并将其在数据库中。首先它会删除所有的用户事先评级为对象，然后写入新的等级到数据库中。该功能已经收到，我已经解析了评级变量（一个TINYINT）。用户标识和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和问号@评估，消除了声明，并放置在正确的顺序追加/ CreateParemeter线得到了这个工作（每个？）。它涉及调用追加/ CreateParameter行多次但（一次用户ID的每个实例），这仅仅是草率的。

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.

code的这个块不引发任何错误，但它没有任何内容写入数据库。
反正我不知道为什么它会到位的问号工作（和重复参数），但不能与申报工作瓦尔

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参数时使用命名变量？

如果没有办法做到这一点，是有办法避免重复同样的追加/ CreateParamenter线每一次我需要的，例如，用户名？

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.

推荐答案

如果你想避免重复，可以继续定义你的变量，并设置其值为一次：

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屋！