Add filter for h_name also in yor sp like this....
ALTER PROCEDURE [dbo].[GetProducts]
(
@Region tinyint = 0
,@H_name nvarchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT House_Info.BehCode,
House_Info.Region,House_Info.City,House_p.Name,House_p.Description
,House_p.image
FROM House_Info LEFT OUTER JOIN House_p ON House_Info.Behcode = House_p.Behcode
WHERE ( (LOWER(House_Info.Region) = LOWER(@Region)) OR @Region = 0)
AND LOWER( H_name) = LOWER(@H_name
)
end
Code Behind Change
add checking for region when you add parameter.
Replace this line in your code
_cmd.Parameters.AddWithValue(
"@Region"
, DDLzone1.SelectedItem.Value);
by
if(DDLzone1.SelectedItem.Value != ""
)
{
_cmd.Parameters.AddWithValue(
"@Region"
, DDLzone1.SelectedItem.Value);
}
--Add parameter only when that is valid region