用command 可以更新数据吗
我写了一个存储过程,在sql analyzer中可以成功执行,但用vbscritpt的ado.command
总是不能更新、删除数据,请问怎样才能成功!
CREATE PROCEDURE card_add
@number varchar(12),
@password varchar(8),
@userid varchar(50),
@outcheck char(3) output
AS
declare @months tinyint,@deadline datetime,@passwd varchar(8)
set @userid=ltrim(rtrim(@userid))
set @number=ltrim(rtrim(@number))
set @password=ltrim(rtrim(@password))
--insert into card (userid,number,[password],months,[money]) values ('qq','1111','1',1,1)
delete from card where number='123'
--insert into card (userid,number,[password],months,[money]) values (@userid,@number,@password,1,1)
if exists(select * from card where number=@number and userid is null)--存在这卡
begin
select @months=months, @passwd=[password] from card where number=@number
if @password<>@passwd --密码不符
select @outcheck='pwd'
else
begin
select @deadline=deadline from users where userid=@userid
if @deadline<getdate() or @deadline is null
begin
begin transaction
update users
set deadline=dateadd(month,@months,getdate())
where userid=@userid
update card
set userid=@userid,dates=getdate()
where number=@number
commit transaction
end
else
begin
begin transaction
update users set deadline=dateadd(month,@months,deadline) where userid=@userid
update card set userid=@userid,dates=getdate() where number=@number
commit transaction
end
select @outcheck='suc'
end
end
else --不存在这card
select @outcheck='not'
GO
asp中代码:
Set Comm=Server.CreateObject("ADODB.Command")
Comm.ActiveConnection=conn
Comm.CommandText="card_add"
Comm.CommandType=4 'AdCmdStoredProc
'command.CreateParameter (Name, Type, Direction, Size, Value) adsmallint 2 adinteger 3 adcurrency 6 addate 7 adboolean 11 dchar 129 advarchar 200
Set p1=Comm.CreateParameter("number",200,1,12,CStr(number) )
Comm.Parameters.Append p1
Set p1=Comm.CreateParameter("password",200,1,8,CStr(password) )
Comm.Parameters.Append p1
Set p1=Comm.CreateParameter("userid",200,1,50,CStr(userid))
Comm.Parameters.Append p1
Set p1=Comm.CreateParameter("outcheck",129,2,3)
Comm.Parameters.Append p1
Comm.Execute