Procedure or function 'Getproducts9' expects parameter '@Bra

Question

here's my sql stored procedure...

CREATE PROCEDURE Getproducts9

@PageIndex INT

,@PageSize INT = 12

,@CategoryId int

,@Brands nvarchar(50)

,@Price int

,@PageCount INT OUTPUT

AS

BEGIN

DECLARE @RecordCount INT

DECLARE @pricestart INT

DECLARE @priceend INT

if(@Price = 1)

begin

set @pricestart = 1

set @priceend = 3000

end

if(@Price = 2)

begin

set @pricestart = 3001

set @priceend = 7000

end

if(@Price = 3)

begin

set @pricestart = 7001

set @priceend = 12000

end

if(@Price = 4)

begin

set @pricestart = 12001

set @priceend = 18000

end

if(@Price = 5)

begin

set @pricestart = 18001

set @priceend = 30000

end

/*On page Load*/

IF(@CategoryId = 0 and @Brands is null)

if(@Price = 0)

begin

SELECT * FROM [Products]

end

else

begin

SELECT * FROM [Products] where Price between @pricestart and @priceend

end

/*if category id is selected*/

ELSE IF(@CategoryId != 0 and @Brands is null)

if(@Price = 0)

begin

SELECT * FROM [Products] where Categotyid = @CategoryId

end

else if(@Price != 0)

begin

SELECT * FROM [Products] where Categotyid = @CategoryId and Price between @pricestart and @priceend

end

/*Brands is selected*/

IF(@CategoryId != 0 and @Brands is not null)

if(@Price = 0)

begin

SELECT * FROM [Products] where CategotyId = @CategoryId and Name LIKE @Brands + '%'

end

else

begin

SELECT * FROM [Products] where Categotyid = @CategoryId and Name LIKE @Brands + '%' and Price between @pricestart and @priceend

end

END

 

 

and this my code to retrieve data from database...

 

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

//dlCustomers.DataSource = GetCustomersData(pageindex,categoryid,brands,price);

dlCustomers.DataSource = GetCustomersData(1, 0, null, 0);

dlCustomers.DataBind();

}

}

public static DataSet GetCustomersData(int pageindex, int customerId, string brands, int price)

{

string query = "Getproducts9";

//string query = "[GetCustomersPageWise]";

SqlCommand cmd = new SqlCommand(query);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@PageIndex", pageindex);//1

cmd.Parameters.AddWithValue("@PageSize", 12);//2

cmd.Parameters.AddWithValue("@CategoryId", customerId);//3

cmd.Parameters.AddWithValue("@Brands", brands);//4

cmd.Parameters.AddWithValue("@Price", price);//5

cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;

return GetData(cmd);

}

private static DataSet GetData(SqlCommand cmd)

{

string strConnString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

using (SqlConnection con = new SqlConnection(strConnString))

{

using (SqlDataAdapter sda = new SqlDataAdapter())

{

cmd.Connection = con;

sda.SelectCommand = cmd;

using (DataSet ds = new DataSet())

{

sda.Fill(ds, "Customers");

DataTable dt = new DataTable("PageCount");

dt.Columns.Add("PageCount");

dt.Rows.Add();

dt.Rows[0][0] = cmd.Parameters["@PageCount"].Value;

ds.Tables.Add(dt);

return ds;

}

}

}

}