Geek asks and answers

Using Variables in Classic ASP Parameterized SQL

I am moving from dynamically generated (albeit heavily disinfected) SQL queries to parameterized SQL, and I am having problems with variable names.

I am using classic ASP encoded in jScript.

The code below has a rating value (1-5) and places it in the database. First, it deletes all the preliminary user ratings for this object, and then writes the new rating to the database. The function has already been received, and I analyzed the variable Rating (TinyInt). UserID and PgID were also sent as an integer.

I already got this working by replacing @UserID, @PgID and @Rating with question marks, deleting DECLARE and placing the Append / CreateParemeter lines in the correct order (one for each?). This is due to the fact that it called the Append / CreateParameter line several times (once for each UserID instance), which is simply messy.

This piece of code does not cause any errors, but it does not write anything to the database. Anyway, I don’t know why it will work with question marks in place (and duplicate parameters), but it doesn’t work with declared vars.

How to use named variables when using parameterized SQL in classic ASP jScript?

If there is no way to do this, is there a way to avoid repeating the same Append / CreateParamenter line every time I need, for example, 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. , ( ), . , . , , , , .

+3
3

, DECLARE :

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 . :

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

adCmdText ? . ADO .

, , , , . , .

. MSDN.

+2

ADO, SQL Server.

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

0
source

More articles:


All Articles