locked
parameter must be defined error on mysql with c# RRS feed

  • 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);
                }
            }

    Monday, June 13, 2011 3:05 AM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 16, 2011 4:36 AM

All replies

  • User-693248168 posted

    ARe you missing the declare statement

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

    Monday, June 13, 2011 3:08 AM
  • User-982924510 posted

    i added

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

    it is same

    Monday, June 13, 2011 3:34 AM
  • 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);
                }
            }

    Monday, June 13, 2011 3:43 AM
  • User-982924510 posted

    gives syntax error

    Monday, June 13, 2011 4:00 AM
  • User-693248168 posted

    Are you using MySQL or MS Sql server?

    Monday, June 13, 2011 4:07 AM
  • User-982924510 posted

    MySql 5.5 with .net connector (6.3.6)

    Monday, June 13, 2011 4:10 AM
  • User644842238 posted

    gives syntax error

    what was the error??

     

    Monday, June 13, 2011 4:29 AM
  • 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')

    Monday, June 13, 2011 4:38 AM
  • User644842238 posted

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

    try this 

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

    in mysql @ and := will give error

    Monday, June 13, 2011 5:27 AM
  • 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") ;


    Monday, June 13, 2011 6:55 AM
  • 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

    Wednesday, June 15, 2011 10:08 PM
  • 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
    Thursday, June 16, 2011 2:35 AM
  • 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

    Thursday, June 16, 2011 4:17 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 16, 2011 4:36 AM