I am trying to get a count of the number of items that match the given SearchCriteria (a user input string) from a SQL2005 DB.
If I run the SP from MS SQL SMS, with a SearchCriteria of 'baby', I get the following results:
a single row single column table with the value of 437 and a 'Return Value' of 0.
I get the same results running the query in VS2005.
The problem is after ExecuteScalar is called temp is always 0.
I have included the code involved. Any assistance would be greatly appericiated.
Stored Procedure that has one input parameter.
@SearchCriteria nvarchar(255)
SELECT COUNT(ItemId)
FROM Item
INNER JOIN Item_Inventory ON Item.Sku = Item_Inventory.Sku
WHERE (((Description LIKE '%' + @SearchCriteria + '%') AND (StatusCode < 1000))
OR ((Description LIKE '%' + @SearchCriteria + '%')
AND (StatusCode = 9000) AND (Item_Inventory.InStock > 0)))
C# code that calls the stored procedure.
int temp;
SqlConnection conn = new SqlConnection(Configuratio
nManager.C
onnectionS
trings["CS
"].Connect
ionString)
;
SqlCommand getSearchCount = new SqlCommand("Products_Selec
tSearchCou
nt", conn);
getSearchCount.CommandType
= CommandType.StoredProcedur
e;
getSearchCount.Parameters.
Add("@sear
chCriteria
", SqlDbType.Char, 255).Value = searchValue;
try {
conn.Open();
temp = (int)getSearchCount.Execut
eScalar();
if (temp > 0) { } // used as a breakpoint to check the value
}
Start Free Trial