存储过程为
CREATE PROCEDURE sys_eventlogproc
@reader int
AS
select * from ht_eventlog where readerid=@reader and progress=0
GO在VB中的调用为
Dim cmdEvent As New ADODB.Command
Dim rstOutEvent As New ADODB.Recordset
cmdEvent.CommandText = "sys_eventlogproc"
cmdEvent.CommandType = adCmdStoredProc
cmdEvent.ActiveConnection = dbMain 'dbMain为Connection对象
cmdEvent.Parameters("@reader") = OutReaderid
Set rstOutEvent = cmdEvent.Execute如果存储过程有输出参数
Parameters("@输出参数") 返回输出参数的值
CREATE PROCEDURE sys_eventlogproc
@reader int
AS
select * from ht_eventlog where readerid=@reader and progress=0
GO在VB中的调用为
Dim cmdEvent As New ADODB.Command
Dim rstOutEvent As New ADODB.Recordset
cmdEvent.CommandText = "sys_eventlogproc"
cmdEvent.CommandType = adCmdStoredProc
cmdEvent.ActiveConnection = dbMain 'dbMain为Connection对象
cmdEvent.Parameters("@reader") = OutReaderid
Set rstOutEvent = cmdEvent.Execute如果存储过程有输出参数
Parameters("@输出参数") 返回输出参数的值
如果我的存储过程有3个输入参数,@input1, @input2, @input3 有两个输出参数 @output1 output, @output2 output调用时的格式应该怎么写呢?
Create procedure AddOne
( @input1 varchar(10),
@input2 varchar(10),
................... ---输入参数
@output1 numeric output,
............................) ---输出参数
as
begin
select @output1=serial,@output2=..... from serial
end 调用: Dim cmdEvent As New ADODB.Command
Dim rstOutEvent As New ADODB.Recordset
cmdEvent.CommandText = "sys_eventlogproc"
cmdEvent.CommandType = adCmdStoredProc
cmdEvent.ActiveConnection = dbMain 'dbMain为Connection对象
cmdEvent.Parameters("@input1") = a
....................................
Set rstOutEvent = cmdEvent.Executeout1=cmdEvent.Parameters("@output1")
.................
Create procedure test
( @input1 varchar(10),
@input2 varchar(10),
@output1 numeric output,
@output2 numeric output) ---输出参数
as
............................................ 调用: Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim s AS strings = "test @input1,@input2,0,0"
Set rs = cn.Execute(s)
Text1.Text = rs(0)
Text2.Text = rs(1)
本方法适用于“Select”不是很多的存储过程
@job_id smallint,
@job_lvl tinyint
AS
SELECT *
FROM employee
WHERE job_id < @job_id
AND job_lvl > @job_lvl
---- 2. 在VB中生成一个新的工程,工程有一窗体,一个COMMAND(NAME:COMMAND1) 按钮,一个 MSFlexGrid(NAME:MSFlexGrid1)控件。
---- 3. 创建连接ADO connection; ---- 4. 创建命令ADO command; ---- 5. 创建参数并设置各个参数的属性; ---- 6. 执行ADO command; ---- 7. 对数据进行处理;MSFlexGrid显示查询到的数据 ---- 8. 释放连接,退出程序。 ---- 其中代码如下: 在窗体中声明以下变量:
Dim cnn1 As ADODB.Connection ‘连接
Dim mycommand As ADODB.Command ‘命令
Dim parm_jobid As ADODB.Parameter ‘参数1
Dim parm_joblvl As ADODB.Parameter ‘参数2
Dim rstByQuery As ADODB.Recordset ‘结果集
Dim strCnn As String ‘连接字符串在窗体的LOAD事件中加入如下代码:
Set cnn1 = New ADODB.Connection
‘生成一个连接
strCnn = "DSN=MYDSN;uid=sa;pwd="
‘创建的系统数据源MYDSN指向PUBS数据库
cnn1.Open strCnn ‘打开连接
在窗体的UNLOAD中的加入代码如下:
cnn1.Close ‘关闭连接
Set cnn1 = Nothing ‘释放连接在按钮中的代码如下:
Dim i As integer
Dim j as integer
Set parm_jobid = New ADODB.Parameter
Set mycommand = New ADODB.Command
' parm_jobid.Name = "name1"
this line can be ommited
parm_jobid.Type = adInteger ‘