//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中执行有返回值,在程序中测试就是为空?
//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中执行有返回值,在程序中测试就是为空?
QBcode =CmdUpdate.Parameters[2].Value.ToString();
Trans.Commit();
这两句的顺序换一下,先提交事务,再取数据
//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);
}
就这样,顺序哪有问题啊?
把CmdUpdate.Parameters.Add("@StfId", SqlDbType.VarChar,4).Value = StfId;
中字符串的长度换大点
@StfId varchar(10),
@QBValues varchar(4),
@QBCode varchar(20) outputAS
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