我参照网上的例子写了个调用存储过程的程序 但是出了问题
Dim parm_startime As ADODB.Parameter '参数1
Dim parm_endtime As ADODB.Parameter '参数2
Dim startime As Data
Dim endtime As Data
Set mycommand = New ADODB.Command
Set parm_startime = New ADODB.Parameter
parm_startime.Name = "startime"
parm_startime.Type = adDBDate '参数类型
'parm_startime.Size = 8 '参数长度
parm_startime.Direction = adParamInput '参数方向,输入或输出
parm_startime.Value = #7/1/2002#
mycommand.Parameters.Append parm_startime '加入参数
Set parm_endtime = New ADODB.Parameter
parm_endtime.Name = "endtime"
parm_endtime.Type = adDBDate
'parm_endtime.Size = 3
parm_endtime.Direction = adParamInput
parm_endtime.Value = #8/1/2002#
mycommand.Parameters.Append parm_endtime
mycommand.ActiveConnection = cnn
'指定该command 的当前活动连接
mycommand.CommandText = "pinlei1"
'myprocedure 是你要调用的存储过程名称
mycommand.CommandType = adCmdStoredProc
'表明command 为存储过程
Set biaotemp = New ADODB.Recordset
biaotemp.CursorLocation = 3
biaotemp.CursorLocation = adUseClient
biaotemp.CursorType = adOpenStatic
biaotemp.LockType = adLockReadOnly
Set biaotemp = mycommand.Execute()
但是运行到
Set biaotemp = mycommand.Execute()
系统报错
“[microsoft][ODBC SQL Server Driver]
Optional feature not implememted”
是不是我传递的参数有问题?存储过程代码如下------------------
CREATE PROCEDURE pinlei1 (@startime datetime,@endtime datetime) AS
select a.药品编码,a.数量合计,a.销售额,catalog.购进价,a.数量合计*catalog.购进价 as 成本,catalog.品类 from catalog,
(select 药品编码,sum(数量) as 数量合计,sum(数量*单价*折扣比例/100) as 销售额 from retail where
发生时间>@startime and 发生时间<@endtime
and (类别='销售' or 类别='退货') group by 药品编码) as a
where a.药品编码=catalog.药品编码
GO
请求各位指点了
Dim parm_startime As ADODB.Parameter '参数1
Dim parm_endtime As ADODB.Parameter '参数2
Dim startime As Data
Dim endtime As Data
Set mycommand = New ADODB.Command
Set parm_startime = New ADODB.Parameter
parm_startime.Name = "startime"
parm_startime.Type = adDBDate '参数类型
'parm_startime.Size = 8 '参数长度
parm_startime.Direction = adParamInput '参数方向,输入或输出
parm_startime.Value = #7/1/2002#
mycommand.Parameters.Append parm_startime '加入参数
Set parm_endtime = New ADODB.Parameter
parm_endtime.Name = "endtime"
parm_endtime.Type = adDBDate
'parm_endtime.Size = 3
parm_endtime.Direction = adParamInput
parm_endtime.Value = #8/1/2002#
mycommand.Parameters.Append parm_endtime
mycommand.ActiveConnection = cnn
'指定该command 的当前活动连接
mycommand.CommandText = "pinlei1"
'myprocedure 是你要调用的存储过程名称
mycommand.CommandType = adCmdStoredProc
'表明command 为存储过程
Set biaotemp = New ADODB.Recordset
biaotemp.CursorLocation = 3
biaotemp.CursorLocation = adUseClient
biaotemp.CursorType = adOpenStatic
biaotemp.LockType = adLockReadOnly
Set biaotemp = mycommand.Execute()
但是运行到
Set biaotemp = mycommand.Execute()
系统报错
“[microsoft][ODBC SQL Server Driver]
Optional feature not implememted”
是不是我传递的参数有问题?存储过程代码如下------------------
CREATE PROCEDURE pinlei1 (@startime datetime,@endtime datetime) AS
select a.药品编码,a.数量合计,a.销售额,catalog.购进价,a.数量合计*catalog.购进价 as 成本,catalog.品类 from catalog,
(select 药品编码,sum(数量) as 数量合计,sum(数量*单价*折扣比例/100) as 销售额 from retail where
发生时间>@startime and 发生时间<@endtime
and (类别='销售' or 类别='退货') group by 药品编码) as a
where a.药品编码=catalog.药品编码
GO
请求各位指点了
我给你一个完整的例子,实际运行通过。
首先打开数据库连接
Public Function C_LianJie() As Boolean
On Error GoTo ine
'If conn Is Nothing Then '如果conn没有初始化
Set conn = New ADODB.Connection
With conn
.Provider = "SQLOLEDB" '设置成OLEDB连接
.CommandTimeout = 10
.ConnectionTimeout = 10
.IsolationLevel = adXactReadCommitted
End With
'End If
If conn.State = adStateClosed Then '如果连接关闭。重新建立连接
conn.Open "server=shiqiang;database=搅拌站;", "sa", ""
'MsgBox "连接成功", , "提示信息"
End If
GoTo inerr
ine:
MsgBox Err.Description, , "错误信息"
inerr:
End Function
然后使用存储过程
Dim lcmwuqi As New ADODB.Command
Dim rs As New ADODB.RecordSet
On Error GoTo inerr
'If lcmwuqi Is Nothing Then
'Set lcmwuqi = New ADODB.command
With lcmwuqi
.ActiveConnection = conn
.CommandText = "p_NewStudentTest"
.CommandType = adCmdStoredProc
End With
'End If
lcmwuqi.Parameters.Append lcmwuqi.CreateParameter("ID", adInteger, adParamInput, 4, CInt(stuID))
lcmwuqi.Parameters.Append lcmwuqi.CreateParameter("Name", adVarChar, adParamInput, 30, tbTextName.Text)
lcmwuqi.Parameters.Append lcmwuqi.CreateParameter("成绩", adVarChar, adParamInput, 6, tbText.Text)
set rs = lcmwuqi.Execute
而且如果我把存储过程转化为select命令
写在程序中可以运行通过
但是我想进行复杂查询不可能用select命令
lcmwuqi.Parameters.Append lcmwuqi.CreateParameter("startime", datetime, adParamInput, 8, "你的时间参数")
Exec #7/1/2002#,#8/1/2002#
GO如果出错,运行
Exec '2002-7-1','2002-8-1'
GO如果正确看看你的参数是不是要改一下~~
Exec '2002-7-1','2002-8-1'
通过
但是用vb传递参数运行就死都过不去
只好使用
strsql = "exec pinlei1 '" & startime1 & " ' , '" & endtime & "'"
biaotemp.Open strsql, cnn, 3, 3
运行通过