为什么C#调用存储过程不成功

[humdark]

存储过程：
ALTER PROCEDURE [dbo].[qt_getsyscounter]
@A VARCHAR(6),
@B VARCHAR(4),
@C DATETIME,
@COUNTNUM INT OUTPUT
AS
DECLARE @NUM INT
SET @NUM = (SELECT CountNum AS xh FROM tb1
WHERE (a = @A) AND (b = @B) AND (c = @C))
IF @NUM IS NULL
BEGIN
UPDATE tb1 SET c = @C, CountNum = 1 WHERE (a = @A) AND (b = @B)
SET @COUNTNUM = 1
return @COUNTNUM
END
ELSE
BEGIN
SET @COUNTNUM = @NUM + 1
UPDATE tb1 SET CountNum = CountNum + 1 WHERE (a = @A) AND (b = @B)
return @COUNTNUM
END

调用程序：
public void AddStoredParm(SqlCommand scAdd, ParameterDirection IO, SqlDbType valType, string paramName, object paramValue)
{
SqlParameter param = new SqlParameter();
param.Direction = IO;
param.SqlDbType = valType;
param.ParameterName = paramName;
param.Value = paramValue;
scAdd.Parameters.Add(param);
}

public string GetSerial(string A, string B)
{
int iSerial = 1;

SqlCommand scSerial = new SqlCommand();

scSerial.Connection = UserConn;
scSerial.CommandType = CommandType.StoredProcedure;
scSerial.CommandText = "qt_getsyscounter";

DataConn.AddStoredParm(scSerial, ParameterDirection.Input, SqlDbType.VarChar, "@A", A);
DataConn.AddStoredParm(scSerial, ParameterDirection.Input, SqlDbType.VarChar, "@B", B);
DataConn.AddStoredParm(scSerial, ParameterDirection.Input, SqlDbType.DateTime, "@C", DateTime.Now.Date);
DataConn.AddStoredParm(scSerial, ParameterDirection.Output, SqlDbType.Int, "@COUNTNUM", @iSerial);

int iRow = scSerial.ExecuteNonQuery();

string strSerial;
strSerial = strSerType + DateTime.Now.ToString("yyMMdd") + iSerial.ToString("#00000");
return strSerial;
}

现在SQL SERVER 中直接运行存储过程能够成功，但用程序调用不成功，iSerial没有发生改变，这是为什么啊

[hahajzh1]

output 参数需要预先定义，然后再add到param里面

[czhenq]

设置输出参数的大小

[mapserver]

DataConn是个什么东西？

用SelectCommand.Parameters.Add()方法。

[mapserver]

给点例子：

public DataSet GetDataSetFromAdapter(
DataSet dataSet, string connectionString, string queryString)
{
using (OleDbConnection connection =
new OleDbConnection(connectionString))
{
OleDbDataAdapter adapter =
new OleDbDataAdapter(queryString, connection);

// Set the parameters.
adapter.SelectCommand.Parameters.Add(
"@CategoryName", OleDbType.VarChar, 80).Value = "toasters";
adapter.SelectCommand.Parameters.Add(
"@SerialNum", OleDbType.Integer).Value = 239;

// Open the connection and fill the DataSet.
try
{
connection.Open();
adapter.Fill(dataSet);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// The connection is automatically closed when the
// code exits the using block.
}
return dataSet;
}

[humdark]

预先定义是什么意思？
int iSerial = 1;

DataConn.AddStoredParm(scSerial, ParameterDirection.Output, SqlDbType.Int, "@COUNTNUM", @iSerial);
这样算不算？

[humdark]

存储过程直接在数据库里用sql语句运行是没问题的

[flyin2006]

up

[Aden]

有几个问题
应该指明输入参数的类型及大小．

DataConn.AddStoredParm(scSerial, ParameterDirection.Output, SqlDbType.Int, "@COUNTNUM", @iSerial);
你这个@iSerial是什么？

[zlkingdom]

应该是可以正常调用的，可能问题出在数据库的存储过程中

[myminimouse]

up

[job_2006]

帮顶

[maojun1980]

C#里 要把传出参数定义上 而且还要定义类型 是个枚举类型
Parameter( "@参数", SqlDbType.数据类型 ).Direction = ParameterDirection.Ouput;
可能你疏忽这个参数了

[伴老思源]

DataConn.AddStoredParm(scSerial, ParameterDirection.Output, SqlDbType.Int, "@COUNTNUM", @iSerial);

?????

-------------------------------------------------------------
SqlConnection _conn = new SqlConnection( "数据库连接字符串" );
_conn.Open();
SqlCommand _comm = new SqlCommand("存储过程名", _conn);
try
{
_comm.CommandType = CommandType.StoredProcedure;
_comm.Parameter( "@参数", SqlDbType.数据类型 ).Value = 传入值；
_comm.Parameter( "@参数", SqlDbType.数据类型 ).Direction = ParameterDirection.Ouput;

_comm.ExecuteNonQuery();
}
catch( Execption Message )
{
//抛出异常
}
_conn.Close();

[伴老思源]

给个例子，供lz一看

[humdark]

DataConn.AddStoredParm(scSerial, ParameterDirection.Output, SqlDbType.Int, "@COUNTNUM", @iSerial);

这个是我自己写的函数，函数代码：
public void AddStoredParm(SqlCommand scAdd, ParameterDirection IO, SqlDbType valType, string paramName, object paramValue)
{
SqlParameter param = new SqlParameter();
param.Direction = IO;
param.SqlDbType = valType;
param.ParameterName = paramName;
param.Value = paramValue;
scAdd.Parameters.Add(param);
}

参数的类型和大小通过这个函数来设置应该可以吧。现在的问题是程序调用存储过程没有成功，其中 int iRow = scSerial.ExecuteNonQuery()返回值是1

[criedshy]

输出参数的问题

[criedshy]

public string GetSerial(string A, string B)
{
int iSerial = 1;

SqlCommand scSerial = new SqlCommand();

scSerial.Connection = UserConn;
scSerial.CommandType = CommandType.StoredProcedure;
scSerial.CommandText = "qt_getsyscounter";

DataConn.AddStoredParm(scSerial, ParameterDirection.Input, SqlDbType.VarChar, "@A", A);
DataConn.AddStoredParm(scSerial, ParameterDirection.Input, SqlDbType.VarChar, "@B", B);
DataConn.AddStoredParm(scSerial, ParameterDirection.Input, SqlDbType.DateTime, "@C", DateTime.Now.Date);
DataConn.AddStoredParm(scSerial, ParameterDirection.Output, SqlDbType.Int, "@COUNTNUM");

int iRow = scSerial.ExecuteNonQuery();
string iSerial=iSerial.Parameters[3].Value;
string strSerial;
strSerial = strSerType + DateTime.Now.ToString("yyMMdd") + iSerial.ToString("#00000");
return strSerial;
}