Creating SP in MySQL DB through C# .Net gives error on parameter varia

Posted 17 August 2010 - 02:49 PM

Hi,

I have written C# Console Application which fires a SQL DDL statement to create SP in MySQL DB.

I am storing the SQL DDL Statement to create SP in MySQL DB into String variable.

I then assign this string to CommandText property of MySQLCommand variable and then call ExecuteNonQuery method on MySQLCommand variable.

I have set the MySQLConnection object well and opened the connection also well.

Upon execution of above statement, .NET runtime throws MySQLException {"Fatal error encountered during command execution."} with inner exception {"Parameter '@v_limit' must be defined."}

Kindly see the code below.

namespace DBScriptCreator
{
    class Program
    {
        static void Main(string[] args)
        {
            #region Variable Declaration

            String connectionString = string.Empty;
            MySqlConnection connection = null;
            int result = 0;
            String queryString = String.Empty;

            #endregion

            #region Open Connection and Create the Command

            connectionString = ConfigurationManager.ConnectionStrings["InsuranceDevelopment"].ConnectionString.ToString();
            connection = new MySqlConnection(connectionString);
            connection.Open();
            Console.WriteLine("Connection to MySQL : " + connection.State.ToString());

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = connection;
            cmd.CommandType = CommandType.Text;

            #endregion

            #region Execute the Query
            queryString = @"CREATE DEFINER = 'root'@'%'
PROCEDURE GetByInspaction(IN pLmt INT, IN pOff INT, OUT pTotalRow INT)
BEGIN
  SET pTotalRow = (SELECT
    COUNT(*)
  FROM
    Inspaction);
  PREPARE ps FROM '
                SELECT
                    InspactionId ,
                    ClaimRegNo,
                    Date,
                    Subject,
                    DateOfLoss,
                    InspectionBodyText,
                    CattleTaggingId,
                    Age,
                    Mark,
                    Disease,
                    Conclusion,
                    Genuine
                    FROM Inspaction
                    Order by Date
                    LIMIT ? OFFSET ?';

  SET @v_limit = pLmt;
  SET @v_offset = pOff;
  EXECUTE ps USING[b] @v_limit, @v_offset;[/b]
  DROP PREPARE ps;
END
$$";
                    cmd.CommandText = queryString ;
                    result = cmd.ExecuteNonQuery();

            #endregion
                

            cmd = null;
            connection.Close();
            connection = null;
            Console.ReadKey();
        }
    }
}

Kindly look into this and help me get rid of this problem.

Thanks in Advance.

Regards,
Vimal Thakkar

This post has been edited by JackOfAllTrades: 17 August 2010 - 03:08 PM
Reason for edit:: Added code tags.