sql中存储的Proc不返回值

发布于 2024-09-18 14:00:01 字数 1040 浏览 3 评论 0原文

我的函数没有返回任何内容 - strReturn 为空：

        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;
        }
    }

当我使用“exec GetMerchantLocationZip (3333, 373773)”执行此存储过程时，我在 SQL 中获得了正确的邮政编码。为什么我在 Visual Studio 中找不到它？

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

AS 

Begin

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

End

我正在学习，如果这是一个明显的错误，请道歉。谢谢大家！

原文

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

魔 2024-09-25 14:00:02

未设置 strReturn，您需要调用 ExecuteScalar 而不是 ExecuteNonQuery。

回复收藏 0 原文

∞觅青森が 2024-09-25 14:00:02

检查拼写!! 您可能在 SQL 语句中抛出异常，因为您在某处（或许多地方）拼错了“Merchant”或“Location”。

而且，正如其他人指出的那样，您可能想要这样：

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

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);

回复收藏 0 原文

感情旳空白 2024-09-25 14:00:01

您没有得到结果，因为您没有将代码作为查询执行。

您正在调用 SqlHelper.ExecuteNonQuery() ，它不会返回任何结果。

看起来您正在使用 SqlHelper 应用程序块，所以我认为您想要的代码是（如果您在查询中返回多行）：

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

ds 将包含查询的结果。

如果您尝试从数据库检索单个值而不是一组行，那么您的代码将是：

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

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);

回复收藏 0 原文

美煞众生 2024-09-25 14:00:01

您似乎没有在任何地方向 strReturn 分配任何内容。您还需要使用 ExecuteScalar 来从单行、单列结果集中检索值。

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

或者使用 ExecuteNonQuery 的 OUTPUT 参数。

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.

回复收藏 0 原文

旧时浪漫 2024-09-25 14:00:01

您正在调用 ExecuteNonQuery，它仅返回受影响的行数。试试这个：

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

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);

回复收藏 0 原文

眉目亦如画i 2024-09-25 14:00:01

您正在使用 ExecuteNonQuery ，它不返回结果，它只会返回更新的行数。你想处决一个读者。

回复收藏 0 原文

黎夕旧梦 2024-09-25 14:00:01

您可能想要调用 ExecuteScalar 而非 ExecuteNonQuery，因为预计不会有返回。

也许下面的代码

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

 return (string)zipObject;

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;

回复收藏 0 原文

~没有更多了~