C# datasource select command sql variables not parameters

[|-|x]

I have a datasource on my page, the select query of which creates a pivot table. The complexity is that the columns in the pivot are determined by records in another table (the users table). So I have to use a prepared statement to build and execute the pivot query, as beow;

SelectCommand="set @q = CONCAT('SELECT Question,',
(SELECT GROUP_CONCAT(CONCAT('NULLIF(COUNT(NULLIF(t.user=',ID,',0)),0) AS `',Firstname,' ',Lastname,'`')) FROM users),
' FROM archiveresponses LEFT JOIN archivetransactions t ON t.TransactionID=archiveresponses.TransactionID 
GROUP BY Question,Compliance HAVING Compliance=0');
PREPARE smt FROM @q;
EXECUTE smt;"

The actual select query that runs will look something like this (obviously the number of user-named columns is dynamic)

SELECT Question,NULLIF(COUNT(NULLIF(t.user=2,0)),0) AS `John Smith`,NULLIF(COUNT(NULLIF(t.user=3,0)),0) AS `Jane Doe` FROM archiveresponses LEFT JOIN archivetransactions t ON t.TransactionID=archiveresponses.TransactionID GROUP BY Question,Compliance HAVING Compliance=0

This works perfectly when I run it manually against the database. However, in the aspx page it throws an exception stating
MySql.Data.MySqlClient.MySqlException: Parameter '@q' must be defined.

I am positive that this worked a couple days ago, but no longer. Only some minor changes have been made to the query and page layout which should not have affected the workflow of the PREPARE-EXECUTE structure of the query.

Is there any way to tell the datasource that @q is not in fact a parameter, but just part of the query that needs to be executed?

Alternately,

does anyone know how to use the Prepared statement construct without requiring the interum variable?

I know you can use PREPARE stmt FROM 'string constant query' but it doesn't seem to work with the CONCAT function or a SELECT query that returns the query string.

I'm trying to avoid having to manually execute and build the required query in code, due to the additional calls to the database this would require, but at this stage it looks like my only option. Any advice greatly appreciated.

[Kane14]

Hi there, If I am not wrong @Q is the name of the field in the datatable where you are going to update the info... so, what about to use a String.Format to avoid the use of @Q

SelectCommand= string.format("set {0} = CONCAT('SELECT Question,',
(SELECT GROUP_CONCAT(CONCAT('NULLIF(COUNT(NULLIF(t.user=',ID,',0)),0) AS ',Firstname,' ',Lastname,'')) FROM users),
' FROM archiveresponses LEFT JOIN archivetransactions t ON t.TransactionID=archiveresponses.TransactionID
GROUP BY Question,Compliance HAVING Compliance=0');
PREPARE smt FROM @q;
EXECUTE smt;", Q)

So the parameter {0} is going to be replaced with the name of your field and you still have the query in a string.

Hope it helps.

[|-|x]

Kane this is not a C# string as such, that I was tring to maintain. It is an SQL string, and the @q is actualy a local SQL variable. It seems there is just an issue with the SQLDataSource that misinterprets this query representation.

As I mentioned, the query runs perfectly as is when executed directly on the database. Its just that ASPNET can't parse it properly.

I worked around the solution by executing the 'construction' subquery first in code, and assembling the full query string manually, then passing it to the data source and manually databinding. Not as elegant as I like, but it works.

I will leave this question open, in the event that someone stumbles accross it with an answer. I'd still be interested to figure it out for future reference.