问大家一个VB6调用MSSQL更新储过程的问题:
错误提示没有提供:@DifferentId参数,怎么回事?存储过程:
CREATE PROCEDURE Update_People
@DifferentId int, ------等于 1 表示Update时使用不同的编号作修改
@Id Varchar(50),
@Name Varchar(50),
@Sex Varchar(50),
@Sy Varchar(50),
@Phone Varchar(50),
@Jw Varchar(50),
@Bm Varchar(50),
@Address Varchar(50),
@Bz Varchar(50),
@Image image
as
if exists(Select * From T_People Where fsId=@Id) and @DifferentId=1
return 1
else
Update T_People set fsId=@Id,
fsName=@Name,
fsSex=@Sex,
fsSy=@sy,
fsPhone=@Phone,
fsJw=@Jw,
fsBm=@Bm,
fsAddress=@Address,
fsBz=@Bz,
fsImage=@Image
where fsId=@Id
return 0GO
------------------------------------------------------------------------
VB部分调用代码:'修改
cmd.CommandText = "Update_People" '返回值
Set param = cmd.CreateParameter("ReturnValue", adInteger, adParamReturnValue, 4)
cmd.Parameters.Append param '@DifferentId,@Id,@Name,@Sex,@Sy,@Phone,@Jw,@Bm,@Address,@Bz,@Image 一共11个参数 '输入参数
If Trim(txtId.Text) <> strBeforeId Then intDifferentId = 1 Else intDifferentId = 0
Set param = cmd.CreateParameter("@DifferentId", adInteger, adParamInput, intDifferentId)
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Id", adVarChar, adParamInput, 50, Trim(txtId.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Name", adVarChar, adParamInput, 50, Trim(txtName.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Sex", adVarChar, adParamInput, 50, Trim(cobSex.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Sy", adVarChar, adParamInput, 50, Trim(dptSr.Value))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Phone", adVarChar, adParamInput, 50, Trim(txtPhone.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Jw", adVarChar, adParamInput, 50, Trim(txtJw.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Bm", adVarChar, adParamInput, 50, Trim(txtBm.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Address", adVarChar, adParamInput, 50, Trim(txtAddress.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Bz", adVarChar, adParamInput, 50, Trim(txtBz.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Image", adBinary, adParamInput, 200, Null)
cmd.Parameters.Append param Rs1.CursorType = adOpenKeyset
Rs1.LockType = adLockOptimistic
Set Rs1 = cmd.Execute() If cmd.Parameters("ReturnValue").Value = 1 Then MsgBox "该编号已登记!", vbOKOnly + vbInformation, "提示": Exit Sub frmPeople.RefreshNew '刷新
Unload Me
错误提示没有提供:@DifferentId参数,怎么回事?存储过程:
CREATE PROCEDURE Update_People
@DifferentId int, ------等于 1 表示Update时使用不同的编号作修改
@Id Varchar(50),
@Name Varchar(50),
@Sex Varchar(50),
@Sy Varchar(50),
@Phone Varchar(50),
@Jw Varchar(50),
@Bm Varchar(50),
@Address Varchar(50),
@Bz Varchar(50),
@Image image
as
if exists(Select * From T_People Where fsId=@Id) and @DifferentId=1
return 1
else
Update T_People set fsId=@Id,
fsName=@Name,
fsSex=@Sex,
fsSy=@sy,
fsPhone=@Phone,
fsJw=@Jw,
fsBm=@Bm,
fsAddress=@Address,
fsBz=@Bz,
fsImage=@Image
where fsId=@Id
return 0GO
------------------------------------------------------------------------
VB部分调用代码:'修改
cmd.CommandText = "Update_People" '返回值
Set param = cmd.CreateParameter("ReturnValue", adInteger, adParamReturnValue, 4)
cmd.Parameters.Append param '@DifferentId,@Id,@Name,@Sex,@Sy,@Phone,@Jw,@Bm,@Address,@Bz,@Image 一共11个参数 '输入参数
If Trim(txtId.Text) <> strBeforeId Then intDifferentId = 1 Else intDifferentId = 0
Set param = cmd.CreateParameter("@DifferentId", adInteger, adParamInput, intDifferentId)
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Id", adVarChar, adParamInput, 50, Trim(txtId.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Name", adVarChar, adParamInput, 50, Trim(txtName.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Sex", adVarChar, adParamInput, 50, Trim(cobSex.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Sy", adVarChar, adParamInput, 50, Trim(dptSr.Value))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Phone", adVarChar, adParamInput, 50, Trim(txtPhone.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Jw", adVarChar, adParamInput, 50, Trim(txtJw.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Bm", adVarChar, adParamInput, 50, Trim(txtBm.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Address", adVarChar, adParamInput, 50, Trim(txtAddress.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Bz", adVarChar, adParamInput, 50, Trim(txtBz.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Image", adBinary, adParamInput, 200, Null)
cmd.Parameters.Append param Rs1.CursorType = adOpenKeyset
Rs1.LockType = adLockOptimistic
Set Rs1 = cmd.Execute() If cmd.Parameters("ReturnValue").Value = 1 Then MsgBox "该编号已登记!", vbOKOnly + vbInformation, "提示": Exit Sub frmPeople.RefreshNew '刷新
Unload Me
我以前真的没有见过,不知道行不行。下面跟楼主分享一下我调用带参数存储过程的方式。举例如下:
create procedure sp_test(@para int, @result int output)
as
begin
set @result = @para * 2
end
这里的参数@result是输出参数。通过ADO的Command对象调用该存储过程:
cmd_Test.CommandType = adCmdStoredProc
cmd_Test.CommandText = "sp_Test"
cmd_Test.Parameters("@para").Value = 5
cmd_Test.Prepared = True
cmd_Test.Execute则现在可以通过如下方法得到返回值:
cmd_Test.Parameters("@result").Value
先搞掂SQL,再搞VB
-----创建测试用例
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_People]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
drop table [dbo].[T_People]
end
go
----------------------------------------------------------------------------------------------------CREATE TABLE [T_People] (
[fsId] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[fsName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[fsSex] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[fsSy] [datetime] NULL ,
[fsPhone] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[fsJw] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[fsBm] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[fsAddress] [varchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
[fsBz] [varchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
[fsImage] [image] NULL ,
[fsAutoId] [decimal](18, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOinsert T_People select '101','102','103','2001-01-04','105','106','107','108','109',Null
select * from T_People ---查看结果1
---------更新存储过程
if exists(select name from sysobjects where name='Update_People' and type='p')
begin
drop Proc Update_People
endgo
CREATE PROCEDURE Update_People
@DifferentId int, ------等于 1 表示Update时使用不同的编号作修改
@Id Varchar(50),
@Name Varchar(50),
@Sex Varchar(50),
@Sy Varchar(50),
@Phone Varchar(50),
@Jw Varchar(50),
@Bm Varchar(50),
@Address Varchar(50),
@Bz Varchar(50),
@Image image
asif exists(Select * From T_People Where fsId=@Id) and @DifferentId=1
return 1 ----记录的编号已存在不允许更新
else
Update T_People set fsId=@Id,
fsName=@Name,
fsSex=@Sex,
fsSy=@sy,
fsPhone=@Phone,
fsJw=@Jw,
fsBm=@Bm,
fsAddress=@Address,
fsBz=@Bz,
fsImage=@Image
where fsId=@Id
return 0 ------记录不存在允许更新GOEXEC Update_People 1,'201','202','203','2002-02-04','205','206','207','208','209',Null select * from T_People ----查看结果2
-------------删除测试用例
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_People]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
drop table [dbo].[T_People]
end
goif exists(select name from sysobjects where name='Update_People' and type='p')
begin
drop Proc Update_People
endgo
好像insert后面少了一个into吧!!
以下存储过程通过:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_People]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
drop table [dbo].[T_People]
end
go
----------------------------------------------------------------------------------------------------CREATE TABLE [T_People] (
[fsId] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[fsName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[fsSex] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[fsSy] [datetime] NULL ,
[fsPhone] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[fsJw] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[fsBm] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[fsAddress] [varchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
[fsBz] [varchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
[fsImage] [image] NULL ,
[fsAutoId] [decimal](18, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOinsert T_People select '101','102','103','2001-01-04','105','106','107','108','109',Null
select * from T_People ---查看结果1
---------更新存储过程
if exists(select name from sysobjects where name='Update_People' and type='p')
begin
drop Proc Update_People
endgo
CREATE PROCEDURE Update_People
@DifferentId int, ------等于 1 表示Update时使用非本记录编号作修改
@WhereId varchar(50),------------当前记录的编号
@Id Varchar(50),-----------新输入的编号
@Name Varchar(50),
@Sex Varchar(50),
@Sy Varchar(50),
@Phone Varchar(50),
@Jw Varchar(50),
@Bm Varchar(50),
@Address Varchar(50),
@Bz Varchar(50),
@Image image
asif @DifferentId=1 ----使用非当前记录编号更新
begin
if exists(Select * From T_People Where fsId=@Id)
begin
return 1 ----记录的编号已存在不允许更新
end
else------------记录不存在允许更新
begin
Update T_People set fsId=@Id,
fsName=@Name,
fsSex=@Sex,
fsSy=@sy,
fsPhone=@Phone,
fsJw=@Jw,
fsBm=@Bm,
fsAddress=@Address,
fsBz=@Bz,
fsImage=@Image
where fsId=@WhereId
return 0
end
endelse ----------使用当前记录编号更新
begin
Update T_People set fsId=@Id,
fsName=@Name,
fsSex=@Sex,
fsSy=@sy,
fsPhone=@Phone,
fsJw=@Jw,
fsBm=@Bm,
fsAddress=@Address,
fsBz=@Bz,
fsImage=@Image
where fsId=@WhereId
return 0
end
GO
EXEC Update_People 0,'101',----(第一个参数1为非当前记录编号,0为当前记录编号,)(第二个参数为当前原始记录编号)
'101','202','203','2002-02-04','205','206','207','208','209',Null
select * from T_People ----查看结果2(编号不改其它改)EXEC Update_People 1,'101',----(第一个参数1为非当前记录编号,0为当前记录编号,)(第二个参数为当前原始记录编号)
'202','202','203','2002-02-04','205','206','207','208','209',Null
select * from T_People ----查看结果3(全部都改)
-------------删除测试用例
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_People]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
drop table [dbo].[T_People]
end
goif exists(select name from sysobjects where name='Update_People' and type='p')
begin
drop Proc Update_People
endgo
大家再帮帮忙,究意VB哪里出了什么样问题VB修改后加一个传入参数的代码:'修改
cmd.CommandText = "Update_People" '返回值
Set param = cmd.CreateParameter("ReturnValue", adInteger, adParamReturnValue, 4)
cmd.Parameters.Append param '@DifferentId,@WhereId,@Id,@Name,@Sex,@Sy,@Phone,@Jw,@Bm,@Address,@Bz,@Image 一共12个参数 '输入参数 '全部改(为1);编号不改而其他改(为0);strBeforeId是当前记录编号,txtId.Text为新输入编号
If Trim(txtId.Text) <> strBeforeId Then intDifferentId = 1 Else intDifferentId = 0
Set param = cmd.CreateParameter("@DifferentId", adInteger, adParamInput, intDifferentId)
cmd.Parameters.Append param
'原始记录编号
Set param = cmd.CreateParameter("@WhereId", adVarChar, adParamInput, 50, strBeforeId)
cmd.Parameters.Append param '以下为新输入的全部内容
Set param = cmd.CreateParameter("@Id", adVarChar, adParamInput, 50, Trim(txtId.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Name", adVarChar, adParamInput, 50, Trim(txtName.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Sex", adVarChar, adParamInput, 50, Trim(cobSex.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Sy", adVarChar, adParamInput, 50, Trim(dptSr.Value))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Phone", adVarChar, adParamInput, 50, Trim(txtPhone.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Jw", adVarChar, adParamInput, 50, Trim(txtJw.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Bm", adVarChar, adParamInput, 50, Trim(txtBm.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Address", adVarChar, adParamInput, 50, Trim(txtAddress.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Bz", adVarChar, adParamInput, 50, Trim(txtBz.Text))
cmd.Parameters.Append param Set param = cmd.CreateParameter("@Image", adBinary, adParamInput, 200, Null)
cmd.Parameters.Append param Rs1.CursorType = adOpenKeyset
Rs1.LockType = adLockOptimistic
Set Rs1 = cmd.Execute() If cmd.Parameters("ReturnValue").Value = 1 Then MsgBox "该编号已登记!", vbOKOnly + vbInformation, "提示": Exit Sub frmPeople.RefreshNew '刷新
Unload Me