asp.net中使用sql server存储过程获得output参数问题(解决问题再加分)

[comy]

按照msdn的例子获得sql server存储过程的output参数为什么总是为null，而在osql中按照同样的条件执行该存储过程获得正确的结果，有返回值，即存储过程本身没有错误。
附msdn例子
SqlClient[C#]
SqlCommand sampleCMD = new SqlCommand("SampleProc", nwindConn);
sampleCMD.CommandType = CommandType.StoredProcedure;

SqlParameter sampParm = sampleCMD.Parameters.Add("RETURN_VALUE", SqlDbType.Int);
sampParm.Direction = ParameterDirection.ReturnValue;

sampParm = sampleCMD.Parameters.Add("@InputParm", SqlDbType.NVarChar, 12);
sampParm.Value = "Sample Value";

sampParm = sampleCMD.Parameters.Add("@OutputParm", SqlDbType.NVarChar, 28);
sampParm.Direction = ParameterDirection.Output;

nwindConn.Open();

SqlDataReader sampReader = sampleCMD.ExecuteReader();

Console.WriteLine("{0}, {1}", sampReader.GetName(0), sampReader.GetName(1));

while (sampReader.Read())
{
Console.WriteLine("{0}, {1}", sampReader.GetInt32(0), sampReader.GetString(1));
}

sampReader.Close();
nwindConn.Close();

Console.WriteLine(" @OutputParm: {0}", sampleCMD.Parameters["@OutputParm"].Value);
Console.WriteLine("RETURN_VALUE: {0}", sampleCMD.Parameters["RETURN_VALUE"].Value);

[cl_03]

ExecuteReader返回的是一个数据填充对象，而在你的存储过程中并没有返回一个结果集。

[lizhenlz]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[up_Import_Convert_Dbf_Error_Temp_Table_Emms]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[up_Import_Convert_Dbf_Error_Temp_Table_Emms]
go
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS on
go
----系统部分
CREATE PROCEDURE [dbo].[up_Import_Convert_Dbf_Error_Temp_Table_Emms]
@strTemp_Error_Table nvarchar(50) output, --'生成错误数据临时表的名称
@nTableID int output --'对应表tImport_TempTableName中nTableID(错误数据)
AS
set ANSI_Warnings on

--'变量定义
declare @nCount int --'判断表tImport_TempTableName的记录是否为空

begin
select @nCount=count(*) from tImport_TempTableName
if @nCount>0 --'如果有记录
begin
select @nTableID=max(nTableID+1),@strTemp_Error_Table='_Temp_Error_'+ltrim(rtrim(cast(max(nTableID+1) as nvarchar(20))))
from tImport_TempTableName holdlock
insert into tImport_TempTableName(nTableID,strTableName) values(@nTableID,@strTemp_Error_Table)
end
else --'如果没有记录,则取1
begin
set @nTableID=1
set @strTemp_Error_Table='_Temp_Error_1'
insert into tImport_TempTableName(nTableID,strTableName) values(@nTableID,@strTemp_Error_Table)
end
end

[lizhenlz]

我给个例子给你，我这个是成功的.

Dim sqlcmt As New SqlCommand()
Dim sqlconn As New SqlConnection(str_conn)
sqlcmt.CommandType = CommandType.StoredProcedure
sqlcmt.CommandText = "[dbo].[up_Import_Convert_Dbf_Error_Temp_Table_Emms]"
sqlcmt.CommandTimeout = nTimeOut
sqlcmt.Connection = sqlconn
If sqlconn.State = ConnectionState.Closed Then
sqlconn.Open()
End If
Dim oTempTableName As New SqlParameter()
Dim oTempTableID As New SqlParameter()
oTempTableName.ParameterName = "@strTemp_Error_Table"
oTempTableName.SqlDbType = SqlDbType.NVarChar
oTempTableName.Size = 50
oTempTableName.Direction = ParameterDirection.InputOutput
oTempTableName.Value = ""
sqlcmt.Parameters.Add(oTempTableName)
oTempTableID.ParameterName = "@nTableID"
oTempTableID.SqlDbType = SqlDbType.Int
oTempTableID.Direction = ParameterDirection.InputOutput
oTempTableID.Value = 0
sqlcmt.Parameters.Add(oTempTableID)
sqlcmt.ExecuteReader(CommandBehavior.CloseConnection)
Dim Arr(1) As String
Arr(0) = System.Convert.ToString(oTempTableID.Value)
Arr(1) = System.Convert.ToString(oTempTableName.Value)
Return Arr

[lizhenlz]

sampParm.Direction = ParameterDirection.InputOutput

[CMIC]

up

[cl_03]

//连接数据库
System.Data.SqlClient.SqlConnection conn = FileClass.Database.Conn;
//conn.ConnectionString = "server=server;uid=sa;Password=;database=ytdb";
conn.Open();

SqlCommand com = new SqlCommand();
com.Connection = conn;

com.CommandText = "proc_bal_htno";
com.CommandType = CommandType.StoredProcedure;
SqlParameter[] p_htno = new SqlParameter[5];
p_htno[0] = new SqlParameter();
p_htno[1] = new SqlParameter();
p_htno[2] = new SqlParameter();
p_htno[3] = new SqlParameter();
p_htno[4] = new SqlParameter();

p_htno[0].ParameterName = "@year";
p_htno[0].DbType = System.Data.DbType.String;
p_htno[0].Value = this.drop_year.Items[this.drop_year.SelectedIndex].Value;

p_htno[1].ParameterName = "@htxz";
p_htno[1].DbType = System.Data.DbType.String;
p_htno[1].Value = this.drop_character.Items[this.drop_character.SelectedIndex].Value;

p_htno[2].ParameterName = "@ylh";
p_htno[2].DbType = System.Data.DbType.String;
p_htno[2].Value = this.drop_material.Items[this.drop_material.SelectedIndex].Value;

p_htno[3].ParameterName = "@hth";
p_htno[3].DbType = System.Data.DbType.String;
p_htno[3].Direction = ParameterDirection.Output;
p_htno[3].Size = 12;

p_htno[4].ParameterName = "@ylc_code";
p_htno[4].DbType = System.Data.DbType.String;
p_htno[4].Value = this.drop_smelt.Items[this.drop_smelt.SelectedIndex].Value;

for ( int i =0 ;i< p_htno.Length;i++)
{
com.Parameters.Add(p_htno[i]);
}
com.ExecuteScalar();
this.text_hth.Text = com.Parameters["@hth"].Value.ToString();

[comy]

to lizhenlz(小李)
可否看一下你的存储过程脚本
按你说的做，就没有报错了，但是还是得不到值

[New_bug]

Dim oConn As New SqlConnection()
Dim oCommand As New SqlCommand("", oConn)
Dim oStatus As SqlClient.SqlParameter

oStatus = oCommand.Parameters.Add(New SqlParameter("@O_STATUS", SqlDbType.Char, 1))
oStatus.Direction = ParameterDirection.Output

取值：oStatus.Value

[comy]

to cl_03(燕七)
用ExecuteScalar();可以，但是为什么用.ExecuteReader就不对呢？

[comy]

非常感谢lizhenlz(小李)、cl_03(燕七)
请至下面连接领分
http://expert.csdn.net/Expert/topic/1868/1868573.xml?temp=.6063654