SqlParameterCollection只接受非null的SqlParameter类型对象。参数名称：value [英] The SqlParameterCollection only accepts non-null SqlParameter type objects. Parameter name: value

问题描述

//The following code is in Shopping cart.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace lncshoppingheart.BusinessLayer
{
    public class ShoppingCart
    {
        public string CategoryName;
        public int CategoryID;

        public string SubCategoryName;
        public int SubCatID;
        
        public string CatTypeName;
        public int CatTypeID;

        public string ProductName;
        public string ProductDescription;
        public string ProductPrice;
        public string ProductImage;

        public void AddNewCategory()
        {
            SqlParameter[] categoryparameters = new SqlParameter[1];
            categoryparameters[0] = DataLayer.DataAccess.AddParamater("@CategoryName", CategoryName, System.Data.SqlDbType.NVarChar, 200);
            DataTable ds = DataLayer.DataAccess.ExecuteDTByProcedure("SP_AddNewCategory", categoryparameters);
        }
        public void AddNewSubCategory()
        {
            SqlParameter[] subcategoryparameters = new SqlParameter[1];
            subcategoryparameters[0] = DataLayer.DataAccess.AddParamater("@SubCategoryName", SubCategoryName, System.Data.SqlDbType.NVarChar, 200);
            subcategoryparameters[1] = DataLayer.DataAccess.AddParamater("@CategoryID", CategoryID, System.Data.SqlDbType.Int, 100);

            DataTable ds = DataLayer.DataAccess.ExecuteDTByProcedure("SP_AddNewSubCategory", subcategoryparameters);
        }
        public void AddNewCategoryType()
        {
            SqlParameter[] Cattypeparameters = new SqlParameter[1];
            Cattypeparameters[0] = DataLayer.DataAccess.AddParamater("@CatTypeName", CatTypeName, System.Data.SqlDbType.NVarChar, 200);
            Cattypeparameters[1] = DataLayer.DataAccess.AddParamater("@CategoryID", CategoryID, System.Data.SqlDbType.Int, 100);
            Cattypeparameters[2] = DataLayer.DataAccess.AddParamater("@SubCatID", SubCatID, System.Data.SqlDbType.Int, 100);

            DataTable ds = DataLayer.DataAccess.ExecuteDTByProcedure("SP_AddNewCatType", Cattypeparameters);
        }
        public void AddNewProduct()
        {
            SqlParameter[] productparameters = new SqlParameter[1];
            productparameters[0] = DataLayer.DataAccess.AddParamater("@ProductName", ProductName, System.Data.SqlDbType.NVarChar, 200);
            productparameters[1] = DataLayer.DataAccess.AddParamater("@ProductDescription", ProductDescription, System.Data.SqlDbType.NVarChar, 2000);
            productparameters[2] = DataLayer.DataAccess.AddParamater("@ProductPrice", ProductPrice, System.Data.SqlDbType.NVarChar, 200);
            productparameters[3] = DataLayer.DataAccess.AddParamater("@ProductImage", ProductImage, System.Data.SqlDbType.NVarChar, 200);
            productparameters[4] = DataLayer.DataAccess.AddParamater("@CategoryID", CategoryID, System.Data.SqlDbType.Int, 100);
            productparameters[5] = DataLayer.DataAccess.AddParamater("@SubCatID", SubCatID, System.Data.SqlDbType.Int, 100);
            productparameters[6] = DataLayer.DataAccess.AddParamater("@CatTypeID", CatTypeID, System.Data.SqlDbType.Int, 100);

            DataTable ds = DataLayer.DataAccess.ExecuteDTByProcedure("SP_AddNewProduct", productparameters);
        }
        public DataTable GetCategories()
        {
            SqlParameter[] catparameters = new SqlParameter[1];
            DataTable dt = DataLayer.DataAccess.ExecuteDTByProcedure("SP_GetAllCategories", catparameters);
            return dt;
        }
        public DataTable GetSubCategories()
        {
            SqlParameter[] scatparameters = new SqlParameter[1];
            DataTable dt = DataLayer.DataAccess.ExecuteDTByProcedure("SP_GetAllSubCategories", scatparameters);
            return dt;
        }
        public DataTable GetCategoriesTypes()
        {
            SqlParameter[] ctparameters = new SqlParameter[1];
            DataTable dt = DataLayer.DataAccess.ExecuteDTByProcedure("SP_GetAllCategoriesTypes", ctparameters);
            return dt;
        }
        public DataTable GetProducts()
        {
            SqlParameter[] prodparameters = new SqlParameter[1];
            DataTable dt = DataLayer.DataAccess.ExecuteDTByProcedure("SP_GetAllProducts", prodparameters);
            return dt;
        }
    }
}

//The following code is in dataAccess.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace lncshoppingheart.DataLayer
{
    public class DataAccess
    {
        public static string ConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString.ToString();
            }
        }
        public static SqlParameter AddParamater(string parameterName, object value, SqlDbType DbType, int size)
        {
            SqlParameter param = new SqlParameter();
            param.ParameterName = parameterName;
            param.Value = value.ToString();
            param.SqlDbType = DbType;
            param.Size = size;
            param.Direction = ParameterDirection.Input;
            return param;
        }
        public static DataTable ExecuteDTByProcedure(string ProcedureName, SqlParameter[] Params)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = ProcedureName;
            cmd.Parameters.AddRange(Params);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adopter = new SqlDataAdapter(cmd);
            DataTable dTable = new DataTable();

            try
            {
                adopter.Fill(dTable);
            }
            catch(Exception ex)
            {

            }
            finally
            {
                adopter.Dispose();
                cmd.Parameters.Clear();
                cmd.Dispose();
                conn.Dispose();
            }
            return dTable;
        }
    }
}

推荐答案

修复以下问题，

1.增加参数数组大小和参数数量

示例：AddNewSubCategory方法您已经使用一个项目定义了参数数组

fix below issues,
1. increase parameter array size and number of parameters
example : AddNewSubCategory method you have defined parameter array with one item

SqlParameter[] Cattypeparameters = new SqlParameter[1];

但设置2个参数，你不能这样做。在上面的语句中将数组的大小更改为2并相应地对所有其他方法执行相同的操作



2.如果没有，请不要发送空参数数组参数，您可以发送如下所示的空值

but setting 2 parameters, you can't do that. change the size of array to 2 in above statement and do the same for all other methods accordingly

2. don't send empty parameter array when you don't have parameters, you can send null value like below

public DataTable GetCategories()
{
    DataTable dt = DataLayer.DataAccess.ExecuteDTByProcedure("SP_GetAllCategories", null);
    return dt;
}

你的ExecuteDTByProcedure方法中的
你可以添加如下条件

in your ExecuteDTByProcedure method you can add condition like below

if(Params!= null)
  cmd.Parameters.AddRange(Params);

3.
你的AddParamater方法中的
，更改

3.
in your AddParamater method, change

param.Value = value.ToString();

to

to

param.Value = value?? (object) DBNull.Value;

我得到了同样的错误..请帮帮我排序......

SqlParameterCollection只接受非空的SqlParameter类型对象。

参数名称：value





//业务层

I got the same error..Please help me to sort out...
The SqlParameterCollection only accepts non-null SqlParameter type objects.
Parameter name: value


//Business Layer

namespace ShoppingCart.BussinessLayer
{
    public class BussinessClass
    {
        public string CategoryName;//For Category Page
        public int CategoryId;

        public void AddNewCategory()
        {
            SqlParameter[] parameter = new SqlParameter[1];
           parameter[0] = DataLayer.DataClass.AddParameter("@CategoryName", CategoryName, System.Data.SqlDbType.VarChar, 200);
            DataTable dt = DataLayer.DataClass.ExcecuteDTByProcedure("spAddNewCategory", parameter);

        }
    }
}

//数据层

命名空间ShoppingCart.DataLayer

{

公共类DataClass

{

public DataClass（）

{

}

public static string ConnectionString

{

get

{

返回ConfigurationManager.ConnectionStrings [ShoppingkartConnectionString]。ConnectionString.ToString（）;

}



}

public static SqlParameter AddParameter（string parametername，Object value，SqlDbType Type，int size）

{

SqlParameter param = new SqlParameter（）;

param.ParameterName = parametername;

param.Value = value.ToString（）;

param.SqlDbType = Type;

para m.Size = size;

param.Direction = ParameterDirection.Input;

返回参数;





}

public static DataTable ExcecuteDTByProcedure（string ProcedureName，SqlParameter []参数）

{

using（SqlConnection conn） = new SqlConnection（ConnectionString））

{

SqlCommand cmd = new SqlCommand（）;

cmd.Connection = conn;

cmd.CommandText = ProcedureName;

if（参数！= null）



cmd.Parameters.AddRange（参数）;



cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter adp = new SqlDataAdapter（cmd）;

DataTable dt = new DataTable（）;

试试

{

adp.Fill（dt）;



}

catch（Exception ex）

{





}

终于

{

adp.Dispose（）;

cmd.Parameters.Clear（）;

cmd.Dispose（） ;

conn.Dispose（）;

}

return（dt）;

}

}

//Data layer
namespace ShoppingCart.DataLayer
{
public class DataClass
{
public DataClass()
{
}
public static string ConnectionString
{
get
{
return ConfigurationManager.ConnectionStrings["ShoppingkartConnectionString"].ConnectionString.ToString();
}

}
public static SqlParameter AddParameter(string parametername, Object value, SqlDbType Type, int size)
{
SqlParameter param=new SqlParameter();
param.ParameterName = parametername;
param.Value = value.ToString();
param.SqlDbType = Type;
param.Size = size;
param.Direction = ParameterDirection.Input;
return param;


}
public static DataTable ExcecuteDTByProcedure(string ProcedureName, SqlParameter[] parameter)
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = ProcedureName;
if (parameter != null)

cmd.Parameters.AddRange(parameter);

cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
try
{
adp.Fill(dt);

}
catch (Exception ex)
{


}
finally
{
adp.Dispose();
cmd.Parameters.Clear();
cmd.Dispose();
conn.Dispose();
}
return (dt);
}
}

这篇关于SqlParameterCollection只接受非null的SqlParameter类型对象。参数名称：value的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！