Advertisement

10.11.2007 at 01:38PM PDT, ID: 22888197
[x]
Attachment Details

Need help with C# and ExecuteScalar

Asked by PEIAFowler in Microsoft Visual C#.Net, C# Programming Language, SQL Query Syntax

Tags: 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
        }Start Free Trial
[+][-]10.11.2007 at 01:46PM PDT, ID: 20060977

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Microsoft Visual C#.Net, C# Programming Language, SQL Query Syntax
Tags: executescalar
Sign Up Now!
Solution Provided By: TheLearnedOne
Participating Experts: 1
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_1_20070628