problem retrieving output value from StoredProc

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
 
P

Paul Clement

¤ 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)
 
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");
¤          }
¤

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."
 
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");
¤          }
¤

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
 
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");
¤          }
¤

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

parameter prefix '@' vs "?' with MySql 3
problem using parameters with mysql and C# 3
Stored procedure parameter 1
Error message accessing stored proc with C# 1
Retrieve varbinary from SqlServer using OUTPUT params in StoredPro 3
OracleClient and pls-00306 error message 4
Oracle provider Output Param bug? 4
Call SP with application block (daab) & multiple out REF CURSOR parameters 2

Top