The SqlParameterCollection only accepts non-null SqlParameter type objects. Parameter name: value

Question

4.11/5 (2 votes)

See more:

VS2012

C#

//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;
        }
    }
}

Posted 14-Jan-15 18:05pm

Member 3857034

Updated 14-Jan-15 18:13pm

DamithSL

v2

Add a Solution

2 solutions

Solution 2

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

C#

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);

        }
    }
}

//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);
}
}

Posted 23-Aug-15 9:02am

Member 11804811

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DamithSL · Accepted Answer · 2015-01-14T18:21:00

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

C#

SqlParameter[] Cattypeparameters = new SqlParameter[1];

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

C#

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

in your ExecuteDTByProcedure method you can add condition like below

C#

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

3.
in your AddParamater method, change

C#

param.Value = value.ToString();

to

C#

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