problem retrieving output value from StoredProc

  • Thread starter Thread starter John Coltrane
  • Start date Start date
J

John Coltrane

I am trying to use parameters for a storedproc with C# and MySQL and I
am getting a null return value.

when I try to run the following snippet I find that the output value is
null. If I run the storedproc in the mysql query app I get a valid
return value. Is there something wrong with the code. btw I also have
found that parameters must be prefixed with '?' instead of '@'.

statement = "my_sqrt";
using ( MySqlCommand command = new MySqlCommand(statement, conn) ) {
command.CommandType = CommandType.StoredProcedure;

MySqlParameter param = new MySqlParameter();
param.ParameterName = "?input_number";
param.MySqlDbType = MySqlDbType.Int32;
param.Value = 9;
param.Direction = ParameterDirection.Input;
command.Parameters.Add(param);

param = new MySqlParameter();
param.ParameterName = "@l_sqrt";
param.MySqlDbType = MySqlDbType.Int32;
param.Direction = ParameterDirection.Output;
command.Parameters.Add(param);

command.ExecuteNonQuery();
if ( command.Parameters[ "?l_sqrt" ].Value == null ) {
Console.WriteLine("null");
}

thanks for the help

john
 
¤ I am trying to use parameters for a storedproc with C# and MySQL and I
¤ am getting a null return value.
¤
¤ when I try to run the following snippet I find that the output value is
¤ null. If I run the storedproc in the mysql query app I get a valid
¤ return value. Is there something wrong with the code. btw I also have
¤ found that parameters must be prefixed with '?' instead of '@'.
¤
¤ statement = "my_sqrt";
¤ using ( MySqlCommand command = new MySqlCommand(statement, conn) ) {
¤ command.CommandType = CommandType.StoredProcedure;
¤
¤ MySqlParameter param = new MySqlParameter();
¤ param.ParameterName = "?input_number";
¤ param.MySqlDbType = MySqlDbType.Int32;
¤ param.Value = 9;
¤ param.Direction = ParameterDirection.Input;
¤ command.Parameters.Add(param);
¤
¤ param = new MySqlParameter();
¤ param.ParameterName = "@l_sqrt";
¤ param.MySqlDbType = MySqlDbType.Int32;
¤ param.Direction = ParameterDirection.Output;
¤ command.Parameters.Add(param);
¤
¤ command.ExecuteNonQuery();
¤ if ( command.Parameters[ "?l_sqrt" ].Value == null ) {
¤ Console.WriteLine("null");
¤ }
¤

You may want to try making the output parameter the first parameter in the Parameters collection. I
seem to remember that this is a requirement.

Yes, MySQL uses somewhat different notation for parameter names.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
¤ I am trying to use parameters for a storedproc with C# and MySQL and I
¤ am getting a null return value.
¤
¤ when I try to run the following snippet I find that the output value is
¤ null. If I run the storedproc in the mysql query app I get a valid
¤ return value. Is there something wrong with the code. btw I also have
¤ found that parameters must be prefixed with '?' instead of '@'.
¤
¤ statement = "my_sqrt";
¤ using ( MySqlCommand command = new MySqlCommand(statement, conn) ) {
¤      command.CommandType = CommandType.StoredProcedure;
¤
¤          MySqlParameter param = new MySqlParameter();
¤          param.ParameterName = "?input_number";
¤          param.MySqlDbType = MySqlDbType.Int32;
¤          param.Value = 9;
¤          param.Direction = ParameterDirection.Input;
¤          command.Parameters.Add(param);
¤
¤          param = new MySqlParameter();
¤          param.ParameterName = "@l_sqrt";
¤          param.MySqlDbType = MySqlDbType.Int32;
¤          param.Direction = ParameterDirection.Output;
¤          command.Parameters.Add(param);
¤
¤          command.ExecuteNonQuery();
¤          if ( command.Parameters[ "?l_sqrt" ].Value == null ) {
¤            Console.WriteLine("null");
¤          }
¤

You may want to try making the output parameter the first parameter in the Parameters collection. I
seem to remember that this is a requirement.

Yes, MySQL uses somewhat different notation for parameter names.

Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...

I moved the output parameter to the first parameter and now I get the
following exception:

"A MySqlParameter with ParameterName '?l_sqrt' is not contained by
this MySqlParameterCollection."
 
¤ I am trying to use parameters for a storedproc with C# and MySQL and I
¤ am getting a null return value.
¤
¤ when I try to run the following snippet I find that the output value is
¤ null. If I run the storedproc in the mysql query app I get a valid
¤ return value. Is there something wrong with the code. btw I also have
¤ found that parameters must be prefixed with '?' instead of '@'.
¤
¤ statement = "my_sqrt";
¤ using ( MySqlCommand command = new MySqlCommand(statement, conn) ) {
¤      command.CommandType = CommandType.StoredProcedure;
¤
¤          MySqlParameter param = new MySqlParameter();
¤          param.ParameterName = "?input_number";
¤          param.MySqlDbType = MySqlDbType.Int32;
¤          param.Value = 9;
¤          param.Direction = ParameterDirection.Input;
¤          command.Parameters.Add(param);
¤
¤          param = new MySqlParameter();
¤          param.ParameterName = "@l_sqrt";
¤          param.MySqlDbType = MySqlDbType.Int32;
¤          param.Direction = ParameterDirection.Output;
¤          command.Parameters.Add(param);
¤
¤          command.ExecuteNonQuery();
¤          if ( command.Parameters[ "?l_sqrt" ].Value == null ) {
¤            Console.WriteLine("null");
¤          }
¤

You may want to try making the output parameter the first parameter in the Parameters collection. I
seem to remember that this is a requirement.

Yes, MySQL uses somewhat different notation for parameter names.

Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...

correction, the exception was caused by a typo in my code. So having
the output parameter first still yields a null output value. I posted
this problem in the MySQL group and didn't get a response. grrrr!

thanks
 
¤ I am trying to use parameters for a storedproc with C# and MySQL and I
¤ am getting a null return value.
¤
¤ when I try to run the following snippet I find that the output value is
¤ null. If I run the storedproc in the mysql query app I get a valid
¤ return value. Is there something wrong with the code. btw I also have
¤ found that parameters must be prefixed with '?' instead of '@'.
¤
¤ statement = "my_sqrt";
¤ using ( MySqlCommand command = new MySqlCommand(statement, conn) ) {
¤      command.CommandType = CommandType.StoredProcedure;
¤
¤          MySqlParameter param = new MySqlParameter();
¤          param.ParameterName = "?input_number";
¤          param.MySqlDbType = MySqlDbType.Int32;
¤          param.Value = 9;
¤          param.Direction = ParameterDirection.Input;
¤          command.Parameters.Add(param);
¤
¤          param = new MySqlParameter();
¤          param.ParameterName = "@l_sqrt";
¤          param.MySqlDbType = MySqlDbType.Int32;
¤          param.Direction = ParameterDirection.Output;
¤          command.Parameters.Add(param);
¤
¤          command.ExecuteNonQuery();
¤          if ( command.Parameters[ "?l_sqrt" ].Value == null ) {
¤            Console.WriteLine("null");
¤          }
¤

You may want to try making the output parameter the first parameter in the Parameters collection. I
seem to remember that this is a requirement.

Yes, MySQL uses somewhat different notation for parameter names.

Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...

I just solved my problem. It was the StoredProc code. Of course!
 
Back
Top