无法将“System.DBNull"类型的对象转换为“System.String"类型 [英] Unable to cast object of type 'System.DBNull' to type 'System.String'

问题描述

我正在使用 MVC3 ASP，并且已将我的 web.config 文件配置为以 root 身份登录到 MYSQL DB.我创建了许多可以很好连接的存储过程.我现在想将此登录用户更改为公共用户，称为 tempuser 而不是 root 了.

但是，当我将登录用户从root"更改为tempuser"时，出现错误:无法将System.DBNull"类型的对象转换为System.String"类型

我在执行 ExecuteNonQuery() 时遇到上述错误.

我已通过以下方式授予对这些功能的访问权限:GRANT EXECUTE ON FUNCTION check_user_exists to tempuser@'%';

我还在这个表上授予了选择"和更新"权限，我正在访问该函数使用的内容.我可以以 tempuser 身份登录到 mysql 命令行并手动调用该函数，没有任何问题.但是当我运行 ExecuteNonQuery() 时，我收到了上述错误.我目前正在使用 Visual Web Developer 2010、Razor 引擎、MVC3.

任何帮助，请.

我已经尝试了数周但没有成功.

这是正在执行的代码.ExecuteScalar() 函数是错误所在.错误是这个问题的主题:但是，如果我以root"用户身份登录，则不会出现错误.

 [HttpPost]公共 ActionResult 寄存器(RegisterModel 模型){如果(模型状态.IsValid){//尝试注册用户DBController dbcontroller = new DBController();如果 (dbcontroller.DBConnection()){MySqlCommand 命令 = new MySqlCommand("check_user_exists_signup", dbcontroller.conn);command.CommandType = System.Data.CommandType.StoredProcedure;//为 check_user_exists_signup STORED FUNCTION 添加参数command.Parameters.Add(new MySqlParameter("@userName", model.UserName));command.Parameters["@userName"].Direction = System.Data.ParameterDirection.Input;//insert_users STORED FUNCTION 的 RETURN 参数MySqlParameter cnt_user = command.Parameters.Add("@cnt_user", MySqlDbType.Int32);command.Parameters["@cnt_user"].Direction = System.Data.ParameterDirection.ReturnValue;尝试{command.ExecuteScalar();object ret = command.Parameters["@cnt_user"].Value;dbcontroller.conn.Close();

存储过程是:

CREATE DEFINER=`root`@`localhost` FUNCTION `check_user_exists_signup`(用户名 varchar(20)) 返回 int(11)确定性开始声明 cnt_user int;选择 count(*) 到 cnt_user来自用户其中 user_name = 用户名；返回 cnt_user;结尾

解决方案

无法将System.DBNull"类型的对象转换为System.String"类型

好吧，你不能那样做.你不得不说

string s = null;对象值 = reader["columnName"];如果(值！= System.DBNull){s =(字符串)值；}

或者类似的东西.关键是，您不能将 System.DbNull 转换为字符串.因此，如果您当前正在处理的行中列 columnName 的值为 null，那么您必须检测它.否则，继续进行转换是安全的(假设基础数据类型是 string).

<块引用>

我已经尝试了几个星期，但没有成功.

最重要的是，您不应该花费数周来解决这样的问题.我将消息无法将类型为‘System.DBNull’的对象转换为‘System.String’"并弹出 这个答案 基本上是你的问题，和我给你的解决方案相同，只是编码略有不同.>

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

解决方案

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.

这篇关于无法将“System.DBNull"类型的对象转换为“System.String"类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！