Unable to cast object of type 'System.DBNull' to type 'System.String'

Question

I am using MVC3 ASP, and have configured my web.config file to login into the MYSQL DB as root. I have created many stored procedures which i can connect fine with. i now want to change this login user to a public user, called tempuser and NOT root anymore.

However, when i change the login user from "root" to "tempuser", i get the error: Unable to cast object of type 'System.DBNull' to type 'System.String'

i get the above error at the execution of ExecuteNonQuery().

I have granted access to the functions by: GRANT EXECUTE ON FUNCTION check_user_exists to tempuser@'%';

I have also granted 'select' and 'update' on this table that im accessing which the function uses. I can login to mysql command line as tempuser and call the function manually with no problems. But when i run ExecuteNonQuery() i get the above error. I am currently using Visual Web Developer 2010, Razor Engine, MVC3.

ANy help, please.

I have been trying for weeks now with no luck.

Here is the code that is being executed. The ExecuteScalar() function is where the error is. The error is the subject of this question: However, if i login as "root" user, i dont get the error.

  [HttpPost]
    public ActionResult Register(RegisterModel model)
    {
        if (ModelState.IsValid)
        {
            // Attempt to register the user

            DBController dbcontroller = new DBController();

            if (dbcontroller.DBConnection())
            {
                MySqlCommand command = new MySqlCommand("check_user_exists_signup", dbcontroller.conn);
                command.CommandType = System.Data.CommandType.StoredProcedure;

                // Add parameters for the check_user_exists_signup STORED FUNCTION
                command.Parameters.Add(new MySqlParameter("@userName", model.UserName));
                command.Parameters["@userName"].Direction = System.Data.ParameterDirection.Input;

                // RETURN parameter for the insert_users STORED FUNCTION
                MySqlParameter cnt_user = command.Parameters.Add("@cnt_user", MySqlDbType.Int32);
                command.Parameters["@cnt_user"].Direction = System.Data.ParameterDirection.ReturnValue;

                try
                {
                    command.ExecuteScalar();
                    object ret = command.Parameters["@cnt_user"].Value;             
                    dbcontroller.conn.Close();

The stored proc is :

CREATE DEFINER=`root`@`localhost` FUNCTION `check_user_exists_signup`(
       userName varchar(20)) RETURNS int(11)
    DETERMINISTIC
BEGIN
DECLARE cnt_user int;

  select count(*) into cnt_user
    from users 
   where user_name = userName;

RETURN cnt_user;

END

Just to note, you are not providing the code while your problem is specifically about type conversions. You ought to make some effort, before posting, i.e. trace down the actual line that causes the error using debugger. — Oybek, Apr 13 '12 at 12:50

score 3 · Answer 1 · edited May 23 '17 at 10:34

Unable to cast object of type 'System.DBNull' to type 'System.String'

Well, you can't do that. You have to say

string s = null;
object value = reader["columnName"];
if(value != System.DBNull) {
    s = (string)value;
}

Or something equivalent. The point is, you can't cast System.DbNull to string. So, if the value for the column columnName in the row that you're currently process is null, then you have to detect it. Otherwise, it's safe to proceed with the cast (assuming that the underlying data type is string).

I have been trying for weeks now with no luck.

More than anything, you shouldn't spend weeks solving an issue like this. I put the message "Unable to cast object of type 'System.DBNull' to type 'System.String'" into Google and up popped this answer which is basically your problem, and the same solution that I gave you, just coded a little differently.

The failure occurs at the point of ExecuteScalar. So as soon as the stored proc is executed, it fails and doesnt go pass this point, as per above code. So, at which point do i add your code ? because prior to calling executeScalar, i setup the parameters. — Naren Gokal, Apr 13 '12 at 13:43

score 0 · Answer 2 · answered Apr 13 '12 at 12:52

0

In .Net if the data is NULL it then represent as System.DBNull.Value, it's a specific type to represent NULL in DB and you can't implicit cast it to anything, so let's say if you have an object contains value from DB named as theValue, but the data is NULL then theValue actually is a type of System.DBNull, then the following expression will throw the exception as you got:

if(theValue == "blabla")
{
     ....
}

To prevent is very easy, either check the type, or do a ToString on it(as long as you are sure it's not .net null) and you will get an empty string

answered Apr 13 '12 at 12:52

Simon Wang

2,843
1
16
32

Your second suggestion is inconsistent. It will end up with an empty string. `Console.WriteLine("" == System.DBNull.Value.ToString());` And empty string is not the same as `Null` – Oybek Apr 13 '12 at 12:57
I'm just suggesting on how to avoid that exception, yes it's not the same but in my code experience it's enough for me – Simon Wang Apr 14 '12 at 03:46

Unable to cast object of type 'System.DBNull' to type 'System.String'

2 Answers2

Linked