?
Solved

Need help with C# and ExecuteScalar

Posted on 2007-10-11
2
Medium Priority
?
1,334 Views
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)
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
        }
0
Comment
Question by:PEIAFowler
2 Comments
 
LVL 96

Accepted Solution

by:
Bob Learned earned 2000 total points
ID: 20060977
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
 

Author Comment

by:PEIAFowler
ID: 20061121
It is always the little details that will get you. Good eye.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 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