C# and ms sql存储过程问问题

[mengyao]

//string StfId = Request.QueryString.GetValues("StfId").GetValue(0).ToString();
//string QbNumber = Request.QueryString.GetValues("QbNumber").GetValue(0).ToString();
//string Money = Request.QueryString.GetValues("Money").GetValue(0).ToString();
//以上为取值，现在测试直接用
string StfId = "12";
string QbNumber = "2222222";
string Money = "5";

SqlConnection Conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings.Get("DataConnectionString"));
Conn.Open();


SqlCommand CmdUpdate = new SqlCommand("Pro_QbType", Conn);
CmdUpdate.CommandType = CommandType.StoredProcedure;
CmdUpdate.Parameters.Add("@StfId", SqlDbType.VarChar,4).Value = StfId;
CmdUpdate.Parameters.Add("@QBValues",SqlDbType.VarChar,4).Value = Money;



//建立并添加和“@QBcode OUTPUT”对应的参数
SqlParameter parmReturnValue = new SqlParameter("@QBcode", SqlDbType.VarChar,20);
//parmReturnValue.IsNullable = true;
parmReturnValue.Direction = ParameterDirection.Output;
CmdUpdate.Parameters.Add(parmReturnValue);

CmdUpdate.ExecuteNonQuery();
QBcode = CmdUpdate.Parameters["@QBcode"].Value.ToString();

SqlTransaction Trans = Conn.BeginTransaction();
try
{
CmdUpdate.Transaction = Trans;
CmdUpdate.ExecuteNonQuery();
QBcode =CmdUpdate.Parameters["@QBcode"].Value.ToString();
Trans.Commit();
CmdUpdate.Dispose();
Conn.Close();
}
catch (Exception Err)
{

Response.Write(Err.Message);

}
说明：
在MS SQL2005中执行有返回值，在程序中测试就是为空？

[leohuang]

试试
QBcode =CmdUpdate.Parameters[2].Value.ToString();

[mengyao]

同样的效果，还有什么地方可以注意啊？

[xyq1986]

CmdUpdate.ExecuteNonQuery();怎么执行了两次

[xyq1986]

QBcode =CmdUpdate.Parameters["@QBcode"].Value.ToString();
Trans.Commit();
这两句的顺序换一下，先提交事务，再取数据

[mengyao]

CREATE PROCEDURE [dbo].[Pro_QbType]
@StfId varchar(10),
@QBValues varchar(4),
@QBCode varchar(20) output

AS
IF @QBValues =5
begin
select top 1 @QBCode=QBCode from QB5 where QBFlag like '0'
update QB5 set QBFlag='5',StfId=@StfId,OutTime=getdate() where QBCode=@QBCode
select QBcode from QB5 where QBcode=@QBcode
end
else if @QBValues =10
begin
select top 1 @QBCode=QBCode from QB10 where QBFlag like '0'
update QB10 set QBFlag='5',StfId=@StfId,OutTime=getdate() where QBCode=@QBCode
select QBcode from QB10 where QBcode=@QBcode
end
else if @QBValues =20
begin
select top 1 @QBCode=QBCode from QB20 where QBFlag like '0'
update QB20 set QBFlag='5',StfId=@StfId,OutTime=getdate() where QBCode=@QBCode
select QBcode from QB20 where QBcode=@QBcode
end
else
begin
select top 1 @QBCode=QBCode from QB where QBFlag like '0'
update QB set QBFlag='5',StfId=@StfId,OutTime=getdate() where QBCode=@QBCode
select QBcode from QB where QBcode=@QBcode
end

[mengyao]

谢谢两位，原来是我搞了一个超级错误，数据库链接是服务器上的，那上边确是只有空状态了，而我一直在本地的SQL上执行，郁闷的很！

[mengyao]

楼上，我把多一次的执行也取消了，顺序也换了，还不行
不用事务，也是不行，真得不解！

[whb147]

一定是顺序有问题

[xyq1986]

debug一下，看有没有抛出异常

[mengyao]

string QBcode = "";
//string StfId = Request.QueryString.GetValues("StfId").GetValue(0).ToString();
//string QbNumber = Request.QueryString.GetValues("QbNumber").GetValue(0).ToString();
//string Money = Request.QueryString.GetValues("Money").GetValue(0).ToString();

//测试用
string StfId = "12";
string QbNumber = "24251880";
string Money = "5";

SqlConnection Conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings.Get("DataConnectionString"));
Conn.Open();


SqlCommand CmdUpdate = new SqlCommand("Pro_QbType", Conn);
CmdUpdate.CommandType = CommandType.StoredProcedure;
CmdUpdate.Parameters.Add("@StfId", SqlDbType.VarChar,4).Value = StfId;
CmdUpdate.Parameters.Add("@QBValues",SqlDbType.VarChar,4).Value = Money;



//建立并添加和“@QBcode OUTPUT”对应的参数
SqlParameter parmReturnValue = new SqlParameter("@QBcode", SqlDbType.VarChar,20);
//parmReturnValue.IsNullable = true;
parmReturnValue.Direction = ParameterDirection.Output;
CmdUpdate.Parameters.Add(parmReturnValue);

//A 直接执行

//CmdUpdate.ExecuteNonQuery();
//QBcode = CmdUpdate.Parameters["@QBcode"].Value.ToString();
//Response.Write(QBcode);

//B 事务方式
SqlTransaction Trans = Conn.BeginTransaction();
try
{
CmdUpdate.Transaction = Trans;
CmdUpdate.ExecuteNonQuery();
Trans.Commit();
//QBcode = CmdUpdate.Parameters["@QBcode"].Value.ToString();
QBcode = Convert.ToString(CmdUpdate.Parameters["@QBcode"].Value);
Response.Write(QBcode);
CmdUpdate.Dispose();
Conn.Close();
}
catch (Exception Err)
{
Response.Write(Err.Message);
}
就这样，顺序哪有问题啊？

[xyq1986]

确定没发生异常之类的？
把CmdUpdate.Parameters.Add("@StfId", SqlDbType.VarChar,4).Value = StfId;
中字符串的长度换大点

[mengyao]

换了还是没有用？

[xyq1986]

存储过程帖出来看一下