Answered by:
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);
}
}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 #tmpBest 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 #tmpBest Regards,
Damon
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, June 16, 2011 4:36 AM