parameter must be defined error on mysql with c#

Question

User-982924510 posted

the below code gives Parameter '@row' must be defined error on filling data adaptor

what is wrong with my code ?...

best regards

        StringBuilder sql = new StringBuilder();

        sql.Append("SET @row:=0;");
        sql.Append("SELECT * From (");
        sql.Append("Select @row:=@row+1 As Rec_No, _Col1, _Col2");
        sql.Append("From _Tab1) As _T1");
        sql.Append("WHERE Rec_No > 1 And Rec_No <= 30");

        DataSet ds = new DataSet();
        using (MySqlConnection conn = new MySqlConnection(_connectionString))
        {
            conn.Open();
            using (MySqlDataAdapter da = new MySqlDataAdapter(sql, conn))
            {
                da.Fill(ds);
            }
        }

Answer 1

User-693248168 posted

ARe you missing the declare statement

sql.Append("declare @row as integer");

Answer 2

User-982924510 posted

i added

sql.Append("declare @row as integer;");

it is same

Answer 3

User644842238 posted

try this.. 

StringBuilder sql = new StringBuilder();

 sql.Append("declare row int;");       
sql.Append("SET row=0;");
        sql.Append("SELECT * From (");
        sql.Append("Select row=row+1 As Rec_No, _Col1, _Col2");
        sql.Append("From _Tab1) As _T1");
        sql.Append("WHERE Rec_No > 1 And Rec_No <= 30");

        DataSet ds = new DataSet();
        using (MySqlConnection conn = new MySqlConnection(_connectionString))
        {
            conn.Open();
            using (MySqlDataAdapter da = new MySqlDataAdapter(sql, conn))
            {
                da.Fill(ds);
            }
        }

Answer 4

User-982924510 posted

gives syntax error

Answer 5

User-693248168 posted

Are you using MySQL or MS Sql server?

Answer 6

User-982924510 posted

MySql 5.5 with .net connector (6.3.6)

Answer 7

User644842238 posted

gives syntax error

what was the error??

 

Answer 8

User-982924510 posted

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 'declare row as integer;SET row=0;SELECT * From (Select row=row+1 As Sira_No, Fat' at line 1

same with

        sql.Append("declare row as integer;");
        sql.Append("SET row:=0;");
        sql.Append("SELECT * From (");

the first version's error is

{"Fatal error encountered during command execution."}

Parameter '@row' must be defined.

        sql.Append("declare @row as integer;");
        sql.Append("SET @row:=0;");
        sql.Append("Select row:=@row+1 As Sira_No')

Answer 9

User644842238 posted

sql.Append("declare row as integer;");

try this 

 sql.Append("declare row int;");       

in mysql @ and := will give error

Answer 10

User-982924510 posted

it gives error also.

i tried below code and it works on HeidiSql's query window

but gives

{"Fatal error encountered during command execution."}

{"Parameter '@row' must be defined."}

error on my c# application

        StringBuilder sql = new StringBuilder();
        sql.Append("SET @row:=0; SELECT @row:=@row+1 As Sira_No, Sirano from fatura") ;

Answer 11

User-1694870838 posted

Hi,

I have check through your codes, @row must be definded,please try the codes follow:

        StringBuilder sql = new StringBuilder();
        sql.Append("DECLARE @row INT; SET @row:=0; SELECT @row:=@row+1 As Sira_No, Sirano from fatura") ;

Best Regards,

Damon

Answer 12

User-982924510 posted

Same !..

i simplfied the code as below

        string _connectionString = ConfigurationManager.ConnectionStrings["MySqlConnect"].ConnectionString;
        MySqlConnection conn = new MySqlConnection(_connectionString);
        string mycommand = "DECLARE @row INT; SET @row:=0; SELECT @row:=@row+1 As Sira_No, Sirano from fatura";
        MySqlCommand cmdfat = new MySqlCommand(mycommand, conn);
        DataSet ds = new DataSet();
        conn.Open();
        MySqlDataAdapter da = new MySqlDataAdapter(mycommand, conn);
        da.Fill(ds);

at last line it gives

[MySqlException (0x80004005): Parameter '@row' must be defined.]
   MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlPacket packet, String parmName) +172
   MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlPacket packet) +496
   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) +1073

[MySqlException (0x80004005): Fatal error encountered during command execution.]
   MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +1611
   MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +36
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +144
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +319
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +94
   _Default.Page_Load(Object sender, EventArgs e) in d:\WebSamples\FaturaDen\Default.aspx.cs:27
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +37
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +95
   System.Web.UI.Control.OnLoad(EventArgs e) +145
   System.Web.UI.Control.LoadRecursive() +134
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3412

Answer 13

User-1694870838 posted

Hi,

This clause: string mycommand = "DECLARE @row INT; SET @row:=0; SELECT @row:=@row+1 As Sira_No, Sirano from fatura";

I guess you can't use '@row:=@row+1 As Sira_No', you can define a temporary table with  autoincrement column, inser  the query resultinto the temporary table ,

then select the result from tempotary table , the autoincrement column as Sira_No.

Best Reagrds,

Damon

Answer 14

User-1694870838 posted

Hi feridunt,

I will give you a sample code follow:

USE pubs
SELECT IDENTITY(INT, 1, 1) AS Sira_No,Sirano
INTO #tmp
FROM fatura
SELECT * FROM #tmp
DROP TABLE #tmp

Best Regards,

Damon