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.

How can I use named variables when creating a parameterized SQL call in Classic ASP jScript? (now added to the question above) ...also if there's no way, is there a way to avoid having to Append/CreateParamenter every single time I need, for example, the UserID? — Michael Wendell
– Michael Wendell, Commented Jun 16, 2011 at 20:06

Cheran Shunmugavel · Accepted Answer · 2011-06-17 02:49:26Z

4

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

answered Jun 17, 2011 at 2:49

Cheran Shunmugavel

8,4691 gold badge35 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Wendell Over a year ago

I don't have enough "Reputation" to vote this up, but would if I could. This is perfect. Exactly the type of thing I was looking for, thank you!

Matthew Dean Over a year ago

This seems like a good start but is incomplete. Since this example differs from the one in the question, how do these variable references then get bound to the SQL statement? It would be nice if this answer contained a complete example.

Jerad Rose · Accepted Answer · 2011-06-17 01:33:45Z

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.

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.

answered Jun 17, 2011 at 1:33

Jerad Rose

15.5k18 gold badges88 silver badges157 bronze badges

Comments

Oded · Accepted Answer · 2011-06-16 19:20:26Z

0

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

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

answered Jun 16, 2011 at 19:20

Oded

501k102 gold badges900 silver badges1k bronze badges

1 Comment

Michael Wendell Over a year ago

I'm not sure what you're saying. Is there a different type of connection object that would allow me to use named variables?

Collectives™ on Stack Overflow

Using variables in Classic ASP parameterized SQL

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest