CODEWITHSUNDEEP
c#asp.netsqlsql-serverado.net
challengeAccepted
challengeAccepted

Reputation: 7610

Stored Proc in sql that does not return the value

My function isn't returning anything - strReturn is empty:

        try
        {
            SqlParameter[] parameter = new SqlParameter[]
            {
                new SqlParameter("@MerchantID", MercahntID),
                new SqlParameter("@LoactionID", LoactionID)
            };

            SqlHelper.ExecuteNonQuery(DbConnString, System.Data.CommandType.StoredProcedure, "GetMerchantLocationZip", parameter);

            return strReturn;
        }
        catch (Exception ex)
        {
            LogError("Error Occurred When Retrieving Mercahnt Location Zip: MercahntID:" + MercahntID.ToString(), ex);
            return strReturn;
        }
    }

When I execute this stored proc using 'exec GetMerchantLocationZip (3333, 373773)' I get the correct zipcode in SQL. Why don't I get it in Visual Studio?

Create PROCEDURE [dbo].[GetMerchantLocationZip](
@MerchantID bigint,
@LoactionID bigint)

AS 

Begin

Select Zip FROM Merchant_Location 
where MerchantID=@MerchantID AND LocationID =@LoactionID

End

I am learning, so apologies if it's a obvious error. Thanks all!

Upvotes: 2

Views: 3368

Answers (7)

egrunin
egrunin

Reputation: 25073

Check your spelling!! You're probably throwing an exception in the SQL statement because you've misspelled "Merchant" or "Location" somewhere (or many places).

And, as others have pointed out, you probably want this:

return SqlHelper.ExecuteScalar(DbConnString, 
    System.Data.CommandType.StoredProcedure, 
    "GetMerchantLocationZip", parameter);

Upvotes: 0

gkrogers
gkrogers

Reputation: 8356

strReturn isn't being set, and you need to call ExecuteScalar instead of ExecuteNonQuery.

Upvotes: 0

Justin Niessner
Justin Niessner

Reputation: 245449

You're not getting results because you're not executing the code as a Query.

You're calling SqlHelper.ExecuteNonQuery() which doesn't return any results.

It looks like you're using the SqlHelper application block, so I think the code you want would be (if you're returning multiple rows in the query):

DataSet ds = SqlHelper.ExecuteDataSet(DbConnString,
                                      CommandType.StoredProcedure,
                                      "GetMerchantLocationZip",
                                      parameter);

ds will then contain the results of the query.

If you're trying to retrieve a single value from the database rather than a set of rows, then your code would be:

object zip = SqlHelper.ExecuteScalar(DbConnString,
                                     CommandType.StoredProcedure,
                                     "GetMerchantLocationZip",
                                     parameter);

Upvotes: 6

Jason Jong
Jason Jong

Reputation: 4330

You probably want to call the ExecuteScalar instead of ExecuteNonQuery as that is expected to have no return.

Perhaps the following code

 var zipObject = SqlHelper.ExecuteScalar(DbConnString, System.Data.CommandType.StoredProcedure, "GetMerchantLocationZip", parameter);

 return (string)zipObject;

Upvotes: 0

Mark
Mark

Reputation: 983

You are using ExecuteNonQuery which doesn't return results, it's just going to return the number of rows updated. You want to execute a reader.

Upvotes: 0

Martin
Martin

Reputation: 11041

You are calling ExecuteNonQuery which only returns the number of rows affected. Try this:

var zipCode = SqlHelper.ExecuteScalar(DbConnString, System.Data.CommandType.StoredProcedure, "GetMerchantLocationZip", parameter);

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453608

You don't appear to be assigning anything to strReturn anywhere. You would also need to use ExecuteScalar to retrieve the value from a single row, single column result set.

strReturn = SqlHelper.ExecuteScalar(...) as string;

Or an OUTPUT parameter with ExecuteNonQuery.

Upvotes: 4

Related Questions