SQL2000存储过程有四个输出参数 正常测试得到四个参数得到正确结果分别是 8 7 6 9 在程序中调之后总是得到 8 7DBNULL DBNULL
存储过程太长 不让往上贴 正常输入测试条件 8 5 2007-5-1 2007-5-8 得到正确结果8 7 6 9
程序中调用 8 5 2007-5-1 2007-5-8 得到正确结果8 7 DBNULL DBNULL
抛出异常 无法将DBNULL转换为其它值类型SqlConnection conn = null;
SqlParameter sp;
SqlParameter sprunum;
SqlParameter sprdnum;
SqlParameter sprlnum;
SqlParameter sprrnum;
ArrayList arr = new ArrayList();
int[] returns = new int[4];
this.objSqlCommand = new SqlCommand();
this.objSqlCommand.CommandText = "proc_select_rollbook";
this.objSqlCommand.CommandType = CommandType.StoredProcedure;
sp = new SqlParameter();
sp.ParameterName = "@DAYS";
sp.SqlDbType = SqlDbType.Int;
sp.Value = 8;
this.objSqlCommand.Parameters.Add(sp); sp = new SqlParameter();
sp.ParameterName = "@LID";
sp.SqlDbType = SqlDbType.Int;
sp.Value = 5;
this.objSqlCommand.Parameters.Add(sp); sp = new SqlParameter();
sp.ParameterName = "@BTIME";
sp.SqlDbType = SqlDbType.VarChar;
sp.Value = "2007-5-1";
this.objSqlCommand.Parameters.Add(sp); sp = new SqlParameter();
sp.ParameterName = "@ETIME";
sp.SqlDbType = SqlDbType.VarChar;
sp.Value = "2007-5-8";
this.objSqlCommand.Parameters.Add(sp); sprunum = new SqlParameter();
sprunum.ParameterName = "@RUNUM";
sprunum.SqlDbType = SqlDbType.Int;
sprunum.Direction = ParameterDirection.Output;
sprunum.Value = 0;
this.objSqlCommand.Parameters.Add(sprunum); sprdnum = new SqlParameter();
sprdnum.ParameterName = "@RDNUM";
sprdnum.SqlDbType = SqlDbType.Int;
sprdnum.Value = 0;
sprdnum.Direction = ParameterDirection.Output;
this.objSqlCommand.Parameters.Add(sprdnum); sprlnum = new SqlParameter();
sprlnum.SqlDbType = SqlDbType.Int;
sprlnum.ParameterName = "@RLNUM";
sprlnum.Direction = ParameterDirection.Output;
this.objSqlCommand.Parameters.Add(sprlnum); sprrnum = new SqlParameter();
sprrnum.SqlDbType = SqlDbType.Int;
sprrnum.ParameterName = "@RRNUM";
sprrnum.Direction = ParameterDirection.Output;
this.objSqlCommand.Parameters.Add(sprrnum); conn = new SqlConnection(this.conString);
this.objSqlCommand.Connection = conn;
try
{
if (conn.State == ConnectionState.Closed)
conn.Open();
this.objSqlCommand.ExecuteNonQuery();
}
catch
{
return null;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
try
{
returns[0] = Convert.ToInt32(sprunum.Value);
returns[1] = Convert.ToInt32(sprdnum.Value);
returns[2] = Convert.ToInt32(sprlnum.Value);
returns[3] = Convert.ToInt32(sprrnum.Value);
arr.Add(returns);
}
catch
{
return null;
}
存储过程太长 不让往上贴 正常输入测试条件 8 5 2007-5-1 2007-5-8 得到正确结果8 7 6 9
程序中调用 8 5 2007-5-1 2007-5-8 得到正确结果8 7 DBNULL DBNULL
抛出异常 无法将DBNULL转换为其它值类型SqlConnection conn = null;
SqlParameter sp;
SqlParameter sprunum;
SqlParameter sprdnum;
SqlParameter sprlnum;
SqlParameter sprrnum;
ArrayList arr = new ArrayList();
int[] returns = new int[4];
this.objSqlCommand = new SqlCommand();
this.objSqlCommand.CommandText = "proc_select_rollbook";
this.objSqlCommand.CommandType = CommandType.StoredProcedure;
sp = new SqlParameter();
sp.ParameterName = "@DAYS";
sp.SqlDbType = SqlDbType.Int;
sp.Value = 8;
this.objSqlCommand.Parameters.Add(sp); sp = new SqlParameter();
sp.ParameterName = "@LID";
sp.SqlDbType = SqlDbType.Int;
sp.Value = 5;
this.objSqlCommand.Parameters.Add(sp); sp = new SqlParameter();
sp.ParameterName = "@BTIME";
sp.SqlDbType = SqlDbType.VarChar;
sp.Value = "2007-5-1";
this.objSqlCommand.Parameters.Add(sp); sp = new SqlParameter();
sp.ParameterName = "@ETIME";
sp.SqlDbType = SqlDbType.VarChar;
sp.Value = "2007-5-8";
this.objSqlCommand.Parameters.Add(sp); sprunum = new SqlParameter();
sprunum.ParameterName = "@RUNUM";
sprunum.SqlDbType = SqlDbType.Int;
sprunum.Direction = ParameterDirection.Output;
sprunum.Value = 0;
this.objSqlCommand.Parameters.Add(sprunum); sprdnum = new SqlParameter();
sprdnum.ParameterName = "@RDNUM";
sprdnum.SqlDbType = SqlDbType.Int;
sprdnum.Value = 0;
sprdnum.Direction = ParameterDirection.Output;
this.objSqlCommand.Parameters.Add(sprdnum); sprlnum = new SqlParameter();
sprlnum.SqlDbType = SqlDbType.Int;
sprlnum.ParameterName = "@RLNUM";
sprlnum.Direction = ParameterDirection.Output;
this.objSqlCommand.Parameters.Add(sprlnum); sprrnum = new SqlParameter();
sprrnum.SqlDbType = SqlDbType.Int;
sprrnum.ParameterName = "@RRNUM";
sprrnum.Direction = ParameterDirection.Output;
this.objSqlCommand.Parameters.Add(sprrnum); conn = new SqlConnection(this.conString);
this.objSqlCommand.Connection = conn;
try
{
if (conn.State == ConnectionState.Closed)
conn.Open();
this.objSqlCommand.ExecuteNonQuery();
}
catch
{
return null;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
try
{
returns[0] = Convert.ToInt32(sprunum.Value);
returns[1] = Convert.ToInt32(sprdnum.Value);
returns[2] = Convert.ToInt32(sprlnum.Value);
returns[3] = Convert.ToInt32(sprrnum.Value);
arr.Add(returns);
}
catch
{
return null;
}
解决方案 »
- 请问C# 类里面的 get 和 set 到底是个什么情况 ???? 字段 类 属性 方法 ????
- treeview 节点 index
- 怎么让字符串显示在网页上。用控件还是不用,用什么好?
- 请问:byte,string,stream三种格式的字符串,哪种在网络的传输速度最快?
- 请教:winform中怎么使用form2中的button给form1中的treeView添加节点?
- c# 数据库操作计算
- 还是关于ads的问题!!
- 一个TextBox控件输入int类型的值 怎么用int 的变量 value 得到 TextBox控件输入的值
- 序设置DataGrid任意行和列颜色问题
- 面向对象编程问题
- 最简单的、最奇怪的问题:TextBox有换行bug吗?
- iframe自适应高度的问题,特殊情况,大家指点一下。
ALTER PROCEDURE PROC_SELECT_ROLLBOOK
@DAYS INT=1,
@LID INT,
@BTIME VARCHAR(50),
@ETIME VARCHAR(50),
@RUNUM INT OUTPUT,
@RDNUM INT OUTPUT,
@RLNUM INT OUTPUT,
@RRNUM INT OUTPUT
AS
DECLARE @DAYNUMS INT
DECLARE @FACTRUNUM INT
DECLARE @FACTRDNUM INT
DECLARE @TEMP INT
DECLARE @TUTIME1 VARCHAR(50)
DECLARE @TUTIME2 VARCHAR(50)
DECLARE @TUTIME3 VARCHAR(50)
DECLARE @TDTIME1 VARCHAR(50)
DECLARE @TDTIME2 VARCHAR(50)
DECLARE @TDTIME3 VARCHAR(50)
DECLARE @DAYNUM INT
SET @FACTRUNUM=0
SET @FACTRDNUM=0
SELECT @TUTIME1=TIME1,@TUTIME2=TIME3,@TUTIME3=TIME5,@TDTIME1=TIME2,@TDTIME2=TIME4,@TDTIME3=TIME6 FROM RBTIME_SET
IF @TUTIME3<>''
BEGIN
SET @DAYNUM=3
END
ELSE IF @TUTIME2<>''
BEGIN
SET @DAYNUM=2
END
ELSE IF @TUTIME1<>''
BEGIN
SET @DAYNUM=1
END
SET @DAYNUMS=@DAYNUM*@DAYS
IF @DAYS<>1
BEGIN
IF @DAYNUM=3
BEGIN
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME AND TIME1<>''
SET @FACTRUNUM=@FACTRUNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME AND TIME3<>''
SET @FACTRUNUM=@FACTRUNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME AND TIME5<>''
SET @FACTRUNUM=@FACTRUNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME AND TIME2<>''
SET @FACTRDNUM=@FACTRDNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME AND TIME4<>''
SET @FACTRDNUM=@FACTRDNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME AND TIME6<>''
SET @FACTRDNUM=@FACTRDNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME1>@TUTIME1 AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME
SET @RLNUM=@RLNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME3>@TUTIME2 AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME
SET @RLNUM=@RLNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME5>@TUTIME3 AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME
SET @RLNUM=@RLNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME2<@TDTIME1 AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME
SET @RRNUM=@RRNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME4<@TDTIME2 AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME
SET @RRNUM=@RRNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME6<@TDTIME3 AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME
SET @RRNUM=@RRNUM+@TEMP
END
ELSE IF @DAYNUM=2
BEGIN
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME AND TIME1<>''
SET @FACTRUNUM=@FACTRUNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME AND TIME3<>''
SET @FACTRUNUM=@FACTRUNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME AND TIME2<>''
SET @FACTRDNUM=@FACTRDNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME AND TIME4<>''
SET @FACTRDNUM=@FACTRDNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME1>@TUTIME1 AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME
SET @RLNUM=@RLNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME3>@TUTIME2 AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME
SET @RLNUM=@RLNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME2<@TDTIME1 AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME
SET @RRNUM=@RRNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME4<@TDTIME2 AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME
SET @RRNUM=@RRNUM+@TEMP
END
ELSE IF @DAYNUM=1
BEGIN
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME AND TIME1<>''
SET @FACTRUNUM=@FACTRUNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME AND TIME2<>''
SET @FACTRDNUM=@FACTRDNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME1>@TUTIME1 AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME
SET @RLNUM=@RLNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME2<@TDTIME1 AND ROLLBOOK_DATE>=@BTIME AND ROLLBOOK_DATE<=@ETIME
SET @RRNUM=@RRNUM+@TEMP
END
SET @RUNUM=@DAYNUMS-@FACTRUNUM
SET @RDNUM=@DAYNUMS-@FACTRDNUM
END
BEGIN
IF @DAYNUM=3
BEGIN
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE=@BTIME AND TIME1<>''
SET @FACTRUNUM=@FACTRUNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE=@BTIME AND TIME3<>''
SET @FACTRUNUM=@FACTRUNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE=@BTIME AND TIME5<>''
SET @FACTRUNUM=@FACTRUNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE=@BTIME AND TIME2<>''
SET @FACTRDNUM=@FACTRDNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE=@BTIME AND TIME4<>''
SET @FACTRDNUM=@FACTRDNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE=@BTIME AND TIME6<>''
SET @FACTRDNUM=@FACTRDNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME1>@TUTIME1 AND ROLLBOOK_DATE=@BTIME
SET @RLNUM=@RLNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME3>@TUTIME2 AND ROLLBOOK_DATE=@BTIME
SET @RLNUM=@RLNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME5>@TUTIME3 AND ROLLBOOK_DATE=@BTIME
SET @RLNUM=@RLNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME2<@TDTIME1 AND ROLLBOOK_DATE=@BTIME
SET @RRNUM=@RRNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME4<@TDTIME2 AND ROLLBOOK_DATE=@BTIME
SET @RRNUM=@RRNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME6<@TDTIME3 AND ROLLBOOK_DATE=@BTIME
SET @RRNUM=@RRNUM+@TEMP
END
ELSE IF @DAYNUM=2
BEGIN
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE=@BTIME AND TIME1<>''
SET @FACTRUNUM=@FACTRUNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE=@BTIME AND TIME3<>''
SET @FACTRUNUM=@FACTRUNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE=@BTIME AND TIME2<>''
SET @FACTRDNUM=@FACTRDNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE=@BTIME AND TIME4<>''
SET @FACTRDNUM=@FACTRDNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME1>@TUTIME1 AND ROLLBOOK_DATE=@BTIME
SET @RLNUM=@RLNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME3>@TUTIME2 AND ROLLBOOK_DATE=@BTIME
SET @RLNUM=@RLNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME2<@TDTIME1 AND ROLLBOOK_DATE=@BTIME
SET @RRNUM=@RRNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME4<@TDTIME2 AND ROLLBOOK_DATE=@BTIME
SET @RRNUM=@RRNUM+@TEMP
END
ELSE IF @DAYNUM=1
BEGIN
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE=@BTIME AND TIME1<>''
SET @FACTRUNUM=@FACTRUNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND ROLLBOOK_DATE=@BTIME AND TIME2<>''
SET @FACTRDNUM=@FACTRDNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME1>@TUTIME1 AND ROLLBOOK_DATE=@BTIME
SET @RLNUM=@RLNUM+@TEMP
SELECT @TEMP=COUNT(*) FROM V_ROLLBOOK WHERE LOGIN_ID=@LID AND TIME2<@TDTIME1 AND ROLLBOOK_DATE=@BTIME
SET @RRNUM=@RRNUM+@TEMP
END
SET @RUNUM=@DAYNUMS-@FACTRUNUM
SET @RDNUM=@DAYNUMS-@FACTRDNUM
END
>>
sprunum.Direction = ParameterDirection.InOutput;我上次碰到过,问题在这.
参数的是 InOutput
//……至于时间的格式,如果是标准的时间 写成dateTime是最合适的
写成varchar也是可以的,但是在不同语言时区下 时间可能会格式不同 2007/05/30 05/30/2007
这样的时间对比 varchar显然是有问题的
当然,具体问题还是要具体分析的