Need help with C# and ExecuteScalar

Posted on 2007-10-11
Last Modified: 2013-12-17
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)
    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 {
            temp = (int)getSearchCount.ExecuteScalar();
            if (temp > 0) { } // used as a breakpoint to check the value
Question by:PEIAFowler
    LVL 96

    Accepted Solution

    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.


    Author Comment

    It is always the little details that will get you. Good eye.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Email Body 4 32
    Problem to event 3 27
    VB.NET - Extract Pairs out of a string (JSON) 4 21
    how to do this MS SQL script? 11 26
    Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
    Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now