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

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_开发者_如何学Goname 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!

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.

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.

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.