Executescalar从storedprocedure接收错误的值 [英] Executescalar receives wrong value from storedprocedure
本文介绍了Executescalar从storedprocedure接收错误的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我尝试使用executioncalar方法将单个值返回给c#。当在sql server中执行下面的存储过程时,if..blocks工作正常但在c#中执行标量总是返回0.
请参考下面的代码
I try to return single value to c# using executescalar method. When execute below stored procedure in sql server, if..blocks are working fine but executescalar in c# always returns 0.
please refer below code
USE [xx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[prcAddress]
@ID int,
@Name varchar(50),
@Designation varchar(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @count as Integer -- To count records
Declare @Result int -- To return result
SELECT @Result=0
SELECT @count = (SELECT count(*) FROM dbo.Address)
IF @ID >0
BEGIN
--Update the current entry
SELECT @Result=1
END
ELSE IF @ID =0 AND @count=0
BEGIN
-----do something
SELECT @Result=2
END
ELSE IF @ID=0 AND @count>0
BEGIN
----do something
SELECT @Result=3
END
ELSE
BEGIN
SELECT @Result=4
END
SELECT @Result As Result
END
GO
SqlConnection sqlCon = new SqlConnection(ConnectionString);
SqlCommand sqlCom = new SqlCommand();
try
{
sqlCom = new SqlCommand("prcAddress", sqlCon);
sqlCom.CommandType = CommandType.StoredProcedure;
sqlCom.CommandTimeout = 15;
sqlCom.Connection = sqlCon;
foreach (KeyValuePair<Object, Object> parmater in parameters)
{
if (parmater.GetType() == typeof(DateTime))
{
sqlCom.Parameters.Add("@" + parmater.Key, SqlDbType.DateTime).Value = parmater.Value;
}
else
{
sqlCom.Parameters.AddWithValue("@" + parmater.Key, parmater.Value);
}
}
if (sqlCon.State != ConnectionState.Closed)
{
sqlCon.Close();
}
sqlCon.Open();
if (sqlCom.ExecuteScalar() != null)
{
result = sqlCom.ExecuteScalar().ToString();
}
else
{
result = "";
}
}
catch (SqlException sqlEx)
{
System.Web.HttpContext.Current.Response.Redirect("~/Error.aspx", false);
}
finally
{
sqlCon.Close();
sqlCom = null;
}
推荐答案
只需在结尾处返回值。替换
Just RETURN the value at the end. Replace
SELECT @Result As Result
使用
With
RETURN @Result
这篇关于Executescalar从storedprocedure接收错误的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文