Need help with C# and ExecuteScalar

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(ConfigurationManager.ConnectionStrings["CS"].ConnectionString);

        SqlCommand getSearchCount = new SqlCommand("Products_SelectSearchCount", conn);
        getSearchCount.CommandType = CommandType.StoredProcedure;
        getSearchCount.Parameters.Add("@searchCriteria", SqlDbType.Char, 255).Value = searchValue;

        try {
            conn.Open();
            temp = (int)getSearchCount.ExecuteScalar();
            if (temp > 0) { } // used as a breakpoint to check the value
        }
PEIAFowlerAsked:
Who is Participating?
 
Bob LearnedConnect With a Mentor Commented:
Everything looks right, but there is a small difference:  the parameter in the stored procedure is nvarchar, and the type in the parameter declaration is char.

Bob
0
 
PEIAFowlerAuthor Commented:
It is always the little details that will get you. Good eye.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.