以下为存储过程,有点长,不好意思CREATE PROCEDURE ch_CarBorneStat
(@vFormCode varchar(50),
@iAlready int,
@iTotal int,
@iStat int,
@iUserID int,
@iNumber int)
ASSelect @iAlready=Count(FormCode)+1
From Cheeda_CarBorne
Where FormCode=@vFormCodeSelect @iTotal=[Number]
From Cheeda_CarName
Where RegMark=(Select distinct RegMark From Cheeda_CarControl Where FormCode=@vFormCode)Select @iNumber=Count(UserID)
From Cheeda_CarBorne
Where UserID=@iUserIDif(@iStat>0 and @iNumber=0)
begin
select isok=0
end
else
begin
select isok=1
end
GO以下为cs代码 protected bool IsHitching(string FormCode)
{
return true;
using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["SqlString"]))
{
using (SqlCommand cmd = new SqlCommand("ch_CarBorneStat", conn))
{
try
{
cmd.Connection.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@vFormCode", FormCode);
cmd.Parameters.AddWithValue("@iUserID", Session["uid"].ToString());
SqlDataReader dr = cmd.ExecuteReader();
if (dr["isok"] == "1")
{
return false;
}
}
catch
{
Response.Write("<script>alert('系统错误,请与管理员联系!');window.close();</script>");
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
conn.Dispose();
}
}
}
}
为什么我无法获得isok的值呢?
(@vFormCode varchar(50),
@iAlready int,
@iTotal int,
@iStat int,
@iUserID int,
@iNumber int)
ASSelect @iAlready=Count(FormCode)+1
From Cheeda_CarBorne
Where FormCode=@vFormCodeSelect @iTotal=[Number]
From Cheeda_CarName
Where RegMark=(Select distinct RegMark From Cheeda_CarControl Where FormCode=@vFormCode)Select @iNumber=Count(UserID)
From Cheeda_CarBorne
Where UserID=@iUserIDif(@iStat>0 and @iNumber=0)
begin
select isok=0
end
else
begin
select isok=1
end
GO以下为cs代码 protected bool IsHitching(string FormCode)
{
return true;
using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["SqlString"]))
{
using (SqlCommand cmd = new SqlCommand("ch_CarBorneStat", conn))
{
try
{
cmd.Connection.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@vFormCode", FormCode);
cmd.Parameters.AddWithValue("@iUserID", Session["uid"].ToString());
SqlDataReader dr = cmd.ExecuteReader();
if (dr["isok"] == "1")
{
return false;
}
}
catch
{
Response.Write("<script>alert('系统错误,请与管理员联系!');window.close();</script>");
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
conn.Dispose();
}
}
}
}
为什么我无法获得isok的值呢?
comm.Parameters["参数"].Direction = System.Data.ParameterDirection.Output
之后你要在你传入参数的地方设置comm.Parameters["@mail "].Direction = System.Data.ParameterDirection.Output
当然ParameterDirection有很多个选项,你自己看一下,有只能输入的,有只能输出的,有即输入又输出的。
之后你就正常设置参数值就可以了。
存储过程:
CREATE PROCEDURE ch_CarBorneStat
(
@FormCode varchar(50),--派车单号
@UserID int,--用户ID
@IsAllowRide int output--是否允许该员工剩坐该车次(0不允许,1允许)
)
AS
Declare @Ride int,--已乘坐人数
@Seat int,--座位数量
@IsDown int--该员工是否已经搭乘该车次Select @Ride=Count(FormCode)
From Cheeda_CarBorne
Where FormCode=@FormCodeSelect @Seat=Number
From Cheeda_CarName
Where RegMark=(Select distinct RegMark From Cheeda_CarControl Where FormCode=@FormCode)Select @IsDown=Count(UserID)
From Cheeda_CarBorne
Where UserID=@UserIDif(@Seat>@Ride and @IsDown<1)
select @IsAllowRide = 1
else
select @IsAllowRide = 0
GO
CS文件
protected bool IsHitching(string FormCode)
{
return true;
using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["SqlString"]))
{
using (SqlCommand cmd = new SqlCommand("ch_CarBorneStat", conn))
{
try
{
cmd.Connection.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FormCode", FormCode).Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("@iUserID", Session["uid"].ToString()).Direction = ParameterDirection.Input;
cmd.Parameters["@IsAllowRide"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int err = (int)cmd.Parameters["@IsAllowRide"].Value; if (err == 0)
{
return false;
}
}
catch
{
Response.Write("<script>alert('系统错误,请与管理员联系!');window.close();</script>");
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
conn.Dispose();
}
}
}
}
dt.rows[0][0].tostring()
CS文件我改了一下,如下:
protected string strIsHitching(string FormCode)
{
string err = "";
using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["SqlString"]))
{
SqlDataAdapter adpt = new SqlDataAdapter("ch_CarBorneStat", conn);
adpt.SelectCommand.CommandType = CommandType.StoredProcedure;
adpt.SelectCommand.Parameters.AddWithValue("@FormCode", FormCode).Direction = ParameterDirection.Input;
adpt.SelectCommand.Parameters.AddWithValue("@iUserID", Session["uid"].ToString()).Direction=ParameterDirection.Input;
adpt.SelectCommand.Parameters["@IsAllowRide"].Direction = ParameterDirection.Output;
DataSet ds = new DataSet();
adpt.Fill(ds); err = ds.Tables.Count.ToString();
}
return err;
}
报错:
此 SqlParameterCollection 中未包含带有 ParameterName“@IsAllowRide”的 SqlParameter。
就是这此行:adpt.SelectCommand.Parameters["@IsAllowRide"].Direction = ParameterDirection.Output;
CS文件
/// <summary>
/// 检查该次车辆是否允许乘坐,0为不允许,1为允许
/// </summary>
/// <param name="FormCode"></param>
/// <returns></returns>
protected bool IsHitching(string FormCode)
{
string err = "1";
using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["SqlString"]))
{
using (SqlCommand cmd = new SqlCommand("ch_CarBorneStat", conn))
{
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FormCode", FormCode);
cmd.Parameters.AddWithValue("@UserID", Session["uid"].ToString()); SqlParameter pRet = new SqlParameter("@ret", SqlDbType.Int, 4); //定义返回值参数
pRet.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(pRet);
cmd.Connection.Open();// conn.Open();
cmd.ExecuteNonQuery();
//取得存储过程返回的值
err = pRet.Value.ToString();
conn.Close();
}
catch
{
Response.Write("<script>alert('系统错误,请与管理员联系!');window.close();</script>");
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
conn.Dispose();
}
}
}
if (err == "1")
return true;
else
return false;
}存储过程
CREATE PROCEDURE ch_CarBorneStat
(
@FormCode varchar(50),--派车单号
@UserID int --用户ID
)
AS
Declare @Ride int,--已乘坐人数
@Seat int,--座位数量
@IsDown int--该员工是否已经搭乘该车次Select @Ride=Count(FormCode)
From Cheeda_CarBorne
Where FormCode=@FormCodeSelect @Seat=Number
From Cheeda_CarName
Where RegMark=(Select distinct RegMark From Cheeda_CarControl Where FormCode=@FormCode)Select @IsDown=Count(UserID)
From Cheeda_CarBorne
Where UserID=@UserID
And FormCode=@FormCodeif(@Seat>@Ride and @IsDown<1)
begin
return 1--是否允许该员工剩坐该车次(0不允许,1允许)
end
else
begin
return 0
end
GO