passing parameters to Mysqldatareader

Question

2.00/5 (1 vote)

See more:

Hi,

SELECT machine_id,operator_id,member_id,card_id,name,paid_amount,due_amount,paid_date,phone_number ,@curRow := @curRow + 1 AS row_number FROM transaction JOIN  (SELECT @curRow := 0) r where card_id=@card order by Row_number desc limit 3 ;

my query is giving correct results when i run in Mysql workbench. But in program it's giving error

Parameter '@curRow' must be defined.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: MySql.Data.MySqlClient.MySqlException: Parameter '@curRow' must be defined.

Source Error:

Line 218: MySqlCommand command3 = new MySqlCommand(query3, con);
Line 219: command3.Parameters.AddWithValue("@card", cardid);
Line 220: using (MySqlDataReader rdr3 = command3.ExecuteReader())
Line 221: {
Line 222: if (rdr3.Read())

Source File: E:\Online Billing System\Online Billing System\DataTransaction.aspx.cs Line: 220

Stack Trace:

[MySqlException (0x80004005): Parameter '@curRow' must be defined.]
MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlPacket packet, String parmName, Int32 parameterIndex) +294
MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlPacket packet) +697
MySql.Data.MySqlClient.Statement.BindParameters() +128
MySql.Data.MySqlClient.Statement.Execute() +31
MySql.Data.MySqlClient.PreparableStatement.Execute() +59
MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +1919

[MySqlException (0x80004005): Fatal error encountered during command execution.]
MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +2531
MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() +32
Online_Billing_System.DataTransaction.Page_Load(Object sender, EventArgs e) in E:\Online Billing System\Online Billing System\DataTransaction.aspx.cs:220
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
System.Web.UI.Control.OnLoad(EventArgs e) +91
System.Web.UI.Control.LoadRecursive() +74
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.225

Posted 2-Feb-14 20:41pm

Member 10263519

Updated 2-Feb-14 21:12pm

JoCodes

v2

Add a Solution

Comments

thatraja 3-Feb-14 3:28am

Error message clearly tells that you have to pass values for @curRow

Member 10263519 3-Feb-14 4:13am

ok, but in query only am passing valus, there is no column in table ,but just for clarity am using this column, i need to get last 3 inserted rows for same id.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ravikhoda · Answer 1 · 2014-02-02T21:45:00

Solution 1

in your code where did you pass the @curRow parameter like @card.
this may solve your issue.

C#

command3.Parameters.AddWithValue("@curRow", curRow);

Posted 2-Feb-14 21:45pm

ravikhoda

Comments

Member 10263519 3-Feb-14 4:11am

if i add like you
am getting erropr:

Server Error in '/' Application.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= 0 + 1 AS row_number FROM transaction JOIN (SELECT 0 := 0) r where card_id='c' at line 1

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= 0 + 1 AS row_number FROM transaction JOIN (SELECT 0 := 0) r where card_id='c' at line 1

Source Error:

Line 220: command3.Parameters.AddWithValue("@card", cardid);
Line 221: command3.Parameters.AddWithValue("@curRow", curRow);
Line 222: using (MySqlDataReader rdr3 = command3.ExecuteReader())
Line 223: {
Line 224: if (rdr3.Read())

Source File: E:\Online Billing System\Online Billing System\DataTransaction.aspx.cs Line: 222

Stack Trace:

[MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= 0 + 1 AS row_number FROM transaction JOIN (SELECT 0 := 0) r where card_id='c' at line 1]
MySql.Data.MySqlClient.MySqlStream.ReadPacket() +376
MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) +116
MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) +49
MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) +135
MySql.Data.MySqlClient.MySqlDataReader.NextResult() +1258
MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +2536
MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() +32
Online_Billing_System.DataTransaction.Page_Load(Object sender, EventArgs e) in E:\Online Billing System\Online Billing System\DataTransaction.aspx.cs:222
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
System.Web.UI.Control.OnLoad(EventArgs e) +91
System.Web.UI.Control.LoadRecursive() +74
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.225

ravikhoda 3-Feb-14 4:16am

try check your query manual for Mysql. i think you have some small issue on this line
@curRow := @curRow + 1 AS row_number. instead of this try
@curRow = @curRow + 1 AS row_number

remove : from the query and check.

Member 10263519 3-Feb-14 5:05am

not that , i solved it by keeping "Allow user Variables=true" in connection String.

But here the problem is its giving only last 2 records not 3, if i keep 4 instead of 3 , it's giving last records not the latest last record.