使用存储过程一次存入数据库中.主要出现的是时间问题,请大家来看看

[bolome]

这是我的前台:
ID AA BB TIME_A TIME_B
02 a1 b2 2008-9-22 2008-9-30
2008-10-2 2008-10-25
2008-10-30 2008-11-26

我想使用存储过程一次存入数据库中.主要出现的是时间问题,这是我的语句:

create pro add
@ID nchar(3)
@AA nchar(3)
@BB nchar(3)
@time_a1 datetime
@time_b1 datetime
@time_a2 datetime
@time_b2 datetime
@time_a3 datetime
@time_b3 datetime
as
BEGIN TRAN
 if(@time_a3 is not null and @time_b3 is not null)
  insert into Time (ID,TIME_A,TIME_B) values(@ID,@time_a3,@time_b3)
    else if(@time_a2 is not null and @time_b2 is not null)
    insert into Time (ID,TIME_A,TIME_B) values(@ID,@time_a2,@time_b2)
       else if(@time_a1 is not null and @time_b1 is not null)
        insert into Time (ID,TIME_A,TIME_B) values(@ID,@time_a1,@time_b1)
 insert into product(ID,AA,BB) values(@ID,@AA,@BB)

运行时出现以下提示：Procedure or Function 'product' expects parameter '@arrive_a3', which was not supplied.

[JYR520]

呵呵，学习中，支持

[bolome]

去掉else后解决了一部分问题,我再试试.

[bolome]

现在出现的问题是三个时间对都要赋值才正常运行，只赋一两个是不行的．我想如果不赋值的话，给赋个空值，请问怎么赋？？
string time_a3=string.Empty ?
string time_a3=null ?

[bolome]

试了上面两种都不行。

[Herb2]

那你就赋空串
改成下面就行了。
if(isnull(@time_a3,'')+isnull(@time_b3,'')='')

[Herb2]

--if(isnull(@time_a3,'')+isnull(@time_b3,'')='') 
if(isnull(@time_a3,'')<> '' and isnull(@time_b3,'')<> '') 

[bolome]

试了一下，在存储过程中赋值无效。发现要在前台赋空值才行。

[bolome]

还没找到办法......
这种输入格式很普通，应该有人做过吧？

[bolome]

这样赋空值

string time_a3=string.Empty  ;
                param = cmd.Parameters.AddWithValue("@time_a3 ", time_a3);
                param.Direction = ParameterDirection.Input;
                param.DbType = DbType.String;

把DbType.DateTime 　　改成DbType.String

[yrwx001]

create pro add
@ID nchar(3),
@AA nchar(3),
@BB nchar(3),
@time_a1 datetime,
@time_b1 datetime,
@time_a2 datetime,
@time_b2 datetime,
@time_a3 datetime,
@time_b3 datetime,
as
BEGIN TRAN M1
 if(@time_a3 is not null and @time_b3 is not null)
  insert into Time (ID,TIME_A,TIME_B) values(@ID,@time_a3,@time_b3)
    else if(@time_a2 is not null and @time_b2 is not null)
    insert into Time (ID,TIME_A,TIME_B) values(@ID,@time_a2,@time_b2)
       else if(@time_a1 is not null and @time_b1 is not null)
        insert into Time (ID,AA,BB,TIME_A,TIME_B) values(@ID,@AA,@BB,@time_a1,@time_b1)
COMMIT TRAN M1

[bolome]

楼上的是说这个事务问题吗?

BEGIN TRAN M1
COMMIT TRAN M1

在实际程序中是有的.

[tianhuo_soft]

create pro [add]--应该换个名字
@ID nchar(3),
@AA nchar(3),
@BB nchar(3),
@time_a1 datetime,
@time_b1 datetime,
@time_a2 datetime,
@time_b2 datetime,
@time_a3 datetime,
@time_b3 datetime,
as
BEGIN TRAN M1
 if(@time_a3 is not null and @time_b3 is not null)
  insert into Time (ID,TIME_A,TIME_B) values(@ID,@time_a3,@time_b3)
    else if(@time_a2 is not null and @time_b2 is not null)
    insert into Time (ID,TIME_A,TIME_B) values(@ID,@time_a2,@time_b2)
       else if(@time_a1 is not null and @time_b1 is not null)
        insert into Time (ID,AA,BB,TIME_A,TIME_B) values(@ID,@AA,@BB,@time_a1,@time_b1)
COMMIT TRAN M1

[tianhuo_soft]

create pro [add]--应该换个名字
@ID nchar(3),
@AA nchar(3),
@BB nchar(3),
@time_a1 datetime,
@time_b1 datetime,
@time_a2 datetime,
@time_b2 datetime,
@time_a3 datetime,
@time_b3 datetime, --这里多了一个 逗号
as
BEGIN TRAN M1
 if(@time_a3 is not null and @time_b3 is not null)
  insert into Time (ID,TIME_A,TIME_B) values(@ID,@time_a3,@time_b3)
    else if(@time_a2 is not null and @time_b2 is not null)
    insert into Time (ID,TIME_A,TIME_B) values(@ID,@time_a2,@time_b2)
       else if(@time_a1 is not null and @time_b1 is not null)
        insert into Time (ID,AA,BB,TIME_A,TIME_B) values(@ID,@AA,@BB,@time_a1,@time_b1)
COMMIT TRAN M1

[bolome]

名字改了,逗号去掉还是不对.

create pro product_add
@ID nchar(3),
@AA nchar(3),
@BB nchar(3),
@time_a1 datetime,
@time_b1 datetime,
@time_a2 datetime,
@time_b2 datetime,
@time_a3 datetime,
@time_b3 datetime
as
BEGIN TRAN
 if(@time_a3 is not null and @time_b3 is not null)
  insert into Time (ID,TIME_A,TIME_B) values(@ID,@time_a3,@time_b3)
    else if(@time_a2 is not null and @time_b2 is not null)
    insert into Time (ID,TIME_A,TIME_B) values(@ID,@time_a2,@time_b2)
       else if(@time_a1 is not null and @time_b1 is not null)
        insert into Time (ID,TIME_A,TIME_B) values(@ID,@time_a1,@time_b1)
 insert into product(ID,AA,BB) values(@ID,@AA,@BB)
COMMIT TRAN 

[水族杰纶]

[Quote=引用 5 楼 bolome 的回复:]
名字改了,逗号去掉还是不对.
SQL code
create pro product_add
@ID nchar(3),
@AA nchar(3),
@BB nchar(3),
@time_a1 datetime,
@time_b1 datetime,
@time_a2 datetime,
@time_b2 datetime,
@time_a3 datetime,
@time_b3 datetime
as
BEGIN TRAN
if(@time_a3 is not null and @time_b3 is not null)
insert into Time (ID,TIME_A,TIME_B) values(@ID,@time_a3,@time_b3)
else if(@time_a2 is not null and …
[/Quote]

[bolome]

create pro product_add
@ID nchar(3),
@AA nchar(3),
@BB nchar(3),
@time_a1 datetime,
@time_b1 datetime
as
BEGIN TRAN
       if(@time_a1 is not null and @time_b1 is not null)
       insert into Time (ID,TIME_A,TIME_B) values(@ID,@time_a1,@time_b1)
 insert into product(ID,AA,BB) values(@ID,@AA,@BB)
COMMIT TRAN 

改成现在的样子就可以了。但不是我要的功能。感觉多个IF有问题

[bolome]

create pro product_add
@ID nchar(3),
@AA nchar(3),
@BB nchar(3),
@time_a1 datetime,
@time_b1 datetime
as
BEGIN TRAN
       if(@time_a1 is not null and @time_b1 is not null)
       insert into Time (ID,TIME_A,TIME_B) values(@ID,@time_a1,@time_b1)
 insert into product(ID,AA,BB) values(@ID,@AA,@BB)
COMMIT TRAN 

改成现在的样子，一次只输入一个时间对就可以了。但不是我要的功能。因为我的有几个时间对。感觉多个IF有问题

[bolome]

但是单个IF又没有问题。
运行时出现以下提示：Procedure or Function 'product_add' expects parameter '@arrive_a3', which was not supplied.

[lgxyz]

@arrive_a3
这又是哪来的参数?
上面没看到有这东西啊

product?是表?

[bolome]

[Quote=引用 10 楼 lgxyz 的回复:]
@arrive_a3
这又是哪来的参数?
上面没看到有这东西啊

product?是表?
[/Quote]product是表。觉得是参数问题．这是我的前台赋值语句觉得不点不对

if (tb_time_d1.Text != string.Empty && tb_time_d2.Text != string.Empty)
            {
                string time_a3=tb_time_d1.Text.Trim();
                param = cmd.Parameters.AddWithValue("@time_a3 ", time_a3);
                param.Direction = ParameterDirection.Input;
                param.DbType = DbType.DateTime;

                string time_b3=tb_time_d2.Text.Trim();
                param = cmd.Parameters.AddWithValue("@time_b3 ", time_b3);
                param.Direction = ParameterDirection.Input;
                param.DbType = DbType.DateTime;
            }
            else if (tb_time_c1.Text != string.Empty && tb_time_c2.Text != string.Empty)
            {
                string arrive_a2= tb_time_c1.Text.Trim();
                param = cmd.Parameters.AddWithValue("@time_a2 ", arrive_a2);
                param.Direction = ParameterDirection.Input;
                param.DbType = DbType.DateTime;

                string arrive_b2= tb_time_c2.Text.Trim();
                param = cmd.Parameters.AddWithValue("@time_b2", arrive_b2);
                param.Direction = ParameterDirection.Input;
                param.DbType = DbType.DateTime;
            }
            else if (tb_time_b1.Text != string.Empty && tb_time_b2.Text != string.Empty)
            {
                string arrive_a1= tb_time_b1.Text.Trim();
                param = cmd.Parameters.AddWithValue("@time_a1", arrive_a1);
                param.Direction = ParameterDirection.Input;
                param.DbType = DbType.DateTime;

                string arrive_b1= tb_time_b2.Text.Trim();
                param = cmd.Parameters.AddWithValue("@time_b1", arrive_b1);
                param.Direction = ParameterDirection.Input;
                param.DbType = DbType.DateTime;
            }
                        }