'ADO Parameterized Queries with Subqueries Error

I have a legacy classic ASP application running with SQL Server 2012 (also tested with 2016) that I am trying to switch over to using parameterized queries. All the site's queries run through a function which expects a sql statement as a string with parameters represented by question marks as well as an array of those parameters. The function currently filters the parameters to make them sql safe and puts them into the sql string before executing the statement.

Given this, I thought it would be pretty straightforward to switch this to parameterized queries. Initial testing looked good, and everything appeared to be working properly until I hit a sql statement with parameters in subqueries.

Here's a test sample of what works:

Const connectionString = "Provider=SQLNCLI11; DataTypeCompatibility=80; Server=********; Database=********; UID=*******; PWD=*******"

Dim sql, productId, parameters
sql = "SELECT SKU FROM Products WHERE ProductId = ?"
productId = 3
parameters = Array(productId)

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connectionString

Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = sql
cmd.Parameters.Refresh

Dim rs
Set rs = cmd.Execute(, parameters)

Response.Write("SKU: " & rs("SKU"))

No problem, this returns the SKU as expected. However, if I use a subquery:

Const connectionString = "Provider=SQLNCLI11; DataTypeCompatibility=80; Server=********; Database=********; UID=*******; PWD=*******"

Dim sql, productId, parameters
sql = "SELECT SKU FROM ( SELECT SKU FROM Products WHERE ProductId = ? ) AS P"
productId = 3
parameters = Array(productId)

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connectionString

Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = sql
cmd.Parameters.Refresh

Dim rs
Set rs = cmd.Execute(, parameters)

Response.Write("SKU: " & rs("SKU"))

It throws an error on the cmd.Parameters.Refresh line:

Microsoft VBScript runtime error '0x80004005' Microsoft SQL Server Native Client 11.0 Syntax error, permission violation, or other nonspecific error

If I check cmd.Parameters.Count in the first sample, I correctly get 1. In the bad sample it throws the same error.

Is there any explanation as to why putting the parameter into a subquery causes problems with the parameter collection? I did try manually adding the parameter to the Parameters collection, and that works fine, but it means modifying hundreds of existing sql calls, so for the moment the cmd.Parameters.Refresh round-trip was worth the expense.

sql-serverasp-classicprepared-statementado


Solution 1:[1]

For anyone who might stumble across this, I finally figured out the issue thanks to a co-worker. It turns out there is nothing wrong with the code, but rather with the connection string. I somehow left it out of the sample code, but my connection strings included "DataTypeCompatability=80". If that is present, the code throws the error. However, if I remove it, the error no longer occurs and I get the results back as suspected.

My understanding from this KB article on using ADO with the native client is that DataTypeCompatability should be included to ensure newer data types work properly, but so far I have not found any issues with removing it.

Solution 2:[2]

You can give cmd.execute what you want, but I haven't used it in a long time.

cmd.execute("SELECT SKU FROM ( SELECT SKU FROM Products WHERE ProductId = ? ) AS P", Array(productId))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Dave
Solution 2 omerix

Related Questions