Using variables in Classic ASP parameterized SQL

Question

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.

I'm using Classic ASP, coded in jScript.

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.

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.

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

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?

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.

Answer 1

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

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

Answer 2

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:

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

Answer 3

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.

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.

See this MSDN article for more info.