Sql Query Parameter Name Must Be Defined

Question

Hello I have this SQL query:

SQL = "SELECT SUM( " + "CASE WHEN rn =1" + "THEN v.FirstAmount" 
      + "WHEN rn =2" + "THEN v.SecondAmount " + "ELSE v.ThirdAmount " + "END ) " 
      + "FROM (" + "SELECT cv. * , @rn := IF( @vi = `Violation ID` , @rn +1, 1 ) AS rn,          
       @vi := `Violation ID` " + "FROM class_violation cv" + "CROSS JOIN (" 
      + "SELECT @rn :=0, @vi := ''" + ")CONST" + "ORDER BY `Violation ID`" + ")cv" 
      + "JOIN violation v ON cv.`Violation ID` = v.`Violation ID` " 
      + "JOIN class_record tr ON cv.`Class No.` = tr.`Class No.` " 
      + "WHERE tr.`Class ID` = '" + where + "'";

And I get this error:

MySql.Data.MySqlClient.MySqlException: Fatal Error encountered during
command execution ---> MySql.Data.MySqlClient.MySqlException: Parameter
'@rn' must be defined
 at
MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection
parameters, MySqlPacket packet, String parmName, Int32 parameterIndex)
    at MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql,
    MySqlParameterCollection parameters,MySqlPacket packet)

How would I correct my SQL query and define the parameter?

You might get better answers if you pull your SQL query out of the string concatenation mayhem and paste it as a full SQL query.. — Simon Whitehead
– Simon Whitehead, Commented Feb 15, 2014 at 9:15
Where is your C# code? Are you providing value for all Parameters including @rn? — Iqbal
– Iqbal, Commented Feb 15, 2014 at 9:16
Additionally, you should parameterize the whole query - your "WHERE tr.``Class ID`` = '" + where + "'" is open to SQL injection, for example. — Jon Skeet
– Jon Skeet, Commented Feb 15, 2014 at 9:19
If I paste the full SQL query it would not let me. It says here in that my question is FULL OF CODE ADD SOME DETAILS ON YOUR QUESTION etc etc. — user3233787
– user3233787, Commented Feb 15, 2014 at 9:22
You should still get into the habit of parameterizing all SQL. It makes your queries easier to read, and avoids many conversions issues as well as protecting you from SQL injection attacks. And you never know when someone will take your "exercise" code and just copy it into a real application. Also, if the exercise is to improve your skills, why wouldn't you want to follow the most important best practices? — Jon Skeet
– Jon Skeet, Commented Feb 15, 2014 at 9:30

user3233787 · Accepted Answer · 2014-02-15 09:56:01Z

7

I think I know how to answer my question. In my connection string, I just added Allow User Variables = True and it works now!

answered Feb 15, 2014 at 9:56

user3233787

3772 gold badges10 silver badges38 bronze badges

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

1 Comment

ZoolWay Over a year ago

Okay, interesting question is if you then can still use .NET command parameters or if this disables it. Of if he is able to distinguish between those two...

Collectives™ on Stack Overflow

Sql Query Parameter Name Must Be Defined

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related