Error when passing null value into SQL parameter in C# and how to solve it

Null in C# is different from NULL in SQL. NULL in SQL is expressed in C# as DBNull.Value.

Note: SQL parameters cannot accept the null value of C#. If null is passed in, an error will be reported.

Let’s look at an example below:

1 SqlCommand cmd=new  SqlCommand("Insert into Student values(@StuName,@StuAge)" ,conn);2 cmd.parameters.add("@StuName" ,stuName);3 cmd.parameters.add("@StuAge" ,stuAge);4 cmd.ExecuteNonQuery();

The above code seems to have no problem. In fact, when the value of stuName or stuAge is null, an exception will be thrown. So how to solve it?
Solution: When the value of stuName or stuAge is null, pass in DBNull.Value. Next, write a static general method in the public class to judge the passed parameter value. If it is null, return DBNull.Value, otherwise return the original value.

 1 public static object SqlNull(object obj) 2 { 3   if(obj == null) 4   { 5     return DBNull.Value; 6   } 7   else 8   { 9     return obj;10   }    
11 }

The code after calling the above method is as follows:

1 SqlCommand cmd=new  SqlCommand("Insert into Student values(@StuName,@StuAge)" ,conn);2 cmd.parameters.add("@StuName" ,SqlNull(stuName));3 cmd.parameters.add("@StuAge" ,SqlNull(stuAge));4 cmd.ExecuteNonQuery();

In addition, if the parameter value comes from the value of the control (such as a text box), Then the parameter value passed in will not be null (because the value of the control will not be null, even if there is no value, it will still be ""). If you want to realize that when the value of the control is "" (such as no characters entered in the text box), the data The table field value is NULL, and you only need to slightly modify the SqlNull method:

 1 public static object SqlNull(object obj) 2 { 3    if(obj == null || obj.ToString() == "") 4    { 5      return DBNull.Value; 6    } 7    else 8    { 9      return obj;10    }    
11  }

Extension:

You can also pass parameter groups when passing SQL parameters, as follows:

 1 SqlParameter[] parm = new SqlParameter[] 2 { 3   new SqlParameter("@StuName", SqlNull(stuName)), 4   new SqlParameter("@StuAge", SqlNull(stuAge))  
 5 } 6 if(parm != null) 7 { 8   cmd.Parameters.AddRange(parm);   
 9 }10 cmd.ExecuteNonQuery();

Note: The parameter value in new SqlParameter (parameter name, parameter value) also does not accept null values, and the parm parameter group does not accept null, if(parm != null) cannot be judged without .

The above is the detailed content of Error when passing null value into SQL parameter in C# and how to solve it. For more information, please follow other related articles on the PHP Chinese website!