MySQL System.FormatException: Input string was not in a correct format

Question

Currently, I am creating an application using ASP.NET MVC3 and MySQL and when I try to retrieve a user's first name from the databse I receive a System.FormatException: Input string was not in a correct format.

This is my code:

public string GetUserFirstName(UInt64 id)
{
   DBConnections databaseCnnString = new DBConnections();
   string connectionString = "server=123.123.com;user=me;database=db1;port=3306;password=abcdef";
   MySqlConnection cnn = new MySqlConnection(connectionString);

   try
   {
      cnn.Open();
      string sp_GetFName = "SP_GET_FNAME";
      MySqlCommand cmd = new MySqlCommand(sp_GetFName, cnn);
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.AddWithValue("id", id);
      cmd.Parameters["id"].Direction = ParameterDirection.Input;
      cmd.Parameters.AddWithValue("first_name", MySqlDbType.VarChar);
      cmd.Parameters["first_name"].Direction = ParameterDirection.Output;

      object result = cmd.ExecuteScalar();
      if (result != null)
      {
         string fname = Convert.ToString(result);
         return fname; 
      }
      else
      {
         string fname = "friend";
         return fname; 
      }

   }
   catch (Exception ex)
   {
      throw (ex);
   }
   finally
   {
      cnn.Close();
      cnn.Dispose();
   }
}

This is MySQL Stored Procedure:

CREATE DEFINER=`0001`@`%` PROCEDURE `SP_GET_FNAME`(IN id BIGINT(20), OUT first_name VARCHAR(60))
BEGIN
    DECLARE user_id BIGINT(20) DEFAULT id;
    DECLARE output VARCHAR(60);
    SELECT `FName` FROM `users` WHERE USERID=user_id INTO output;
    SET first_name = output;
END

The problem seems to be when executing cmd.ExecuteScalar().

What is my problem here?

Thank you in advance!

Juan Rodriguez · Accepted Answer · 2011-05-06 05:21:20Z

Copy and paste error on my part. The correct code that works as expected is:

public string GetUserFirstName(UInt64 id)
{
    DBConnections databaseCnnString = new DBConnections();
    string connectionString = "server=123.123.com;user=me;database=db1;port=3306;password=abcdef";
    MySqlConnection cnn = new MySqlConnection(connectionString);

    try
    {
        cnn.Open();
        string sp_GetFName = "SP_GET_FNAME";
        MySqlCommand cmd = new MySqlCommand(sp_GetFName, cnn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("id", id);
        cmd.Parameters["id"].Direction = ParameterDirection.Input;
        cmd.Parameters.Add(new MySqlParameter("first_name", MySqlDbType.VarChar));
        cmd.Parameters["first_name"].Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        string fname = Convert.ToString((cmd.Parameters["first_name"].Value));

        return fname;

     }
     catch (Exception ex)
     {
         throw (ex);
     }
     finally
     {
        cnn.Close();
        cnn.Dispose();
     }
 }

CORRECTION: cmd.Parameters.Add(new MySqlParameter("first_name", MySqlDbType.VarChar)); NOT: cmd.Parameters.AddWithValue("first_name", MySqlDbType.VarChar);

In my case, the FNAME field in the user table cannot be NULL; therefore, checking for NULL values returned in the code is not necessary.

Alain Collins · Accepted Answer · 2011-05-04 17:24:58Z

0

I would guess that the user is not found or the fname is null.

And I really hope you're not querying the database for each user column.

Good luck.

answered May 4, 2011 at 17:24

Alain Collins

16.4k2 gold badges34 silver badges56 bronze badges

1 Comment

Juan Rodriguez Over a year ago

Haha... no... the user is there and I am not querying the database for each user column. If I execute the query directly using Workbench it returns a single value, which is what I am looking for.

John M · Accepted Answer · 2012-01-31 18:43:41Z

0

An additional comment. When trying to return a string value the AddWithValue appears to be trying to convert the output from a string into a number. This results in the string format exception.

answered Jan 31, 2012 at 18:43

John M

14.7k29 gold badges94 silver badges143 bronze badges

Collectives™ on Stack Overflow

MySQL System.FormatException: Input string was not in a correct format

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related