如何通话程序？ [英] How call procedure?
问题描述
如何使用visual
基本调用来自Access的SQL Server上的程序？
谢谢，
mw
Hi,
How to call procedure, which is on SQL Server from Access using visual
basic?
Thanks,
mw
推荐答案
" Marcin Wasilewski" <毫安******************** @ gmail.com>在消息中写道
news：11 ********************** @ g49g2000cwa.googlegr oups.com ...
"Marcin Wasilewski" <ma********************@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
如何使用visual
基本调用Access上的SQL Server上的程序？
谢谢，
mw
Hi,
How to call procedure, which is on SQL Server from Access using visual
basic?
Thanks,
mw
我们看不到存储过程，所以我们不知道你想要的是什么。存储过程可用于执行许多操作 - 有时它们返回
一个记录集，有时是多个记录集，有时甚至没有。有时他们
有参数（输入和输出），有时没有。所以你应该发布你的存储过程并说出你需要做什么。
但你可以尝试在你问之前进行搜索 - 必须有足够的
例子。
We can''t see the stored procedure, so we don''t know what you are trying to
do. Stored procedures can be used to do many things - sometimes they return
a recordset, sometimes multiple recordsets, sometimes none. Sometimes they
have parameters (input and output) and sometimes none. So really you should
post your stored procedure and say what you need to do with it.
But you could try doing a search before you ask - there must be plenty of
examples.
当然你是对的。
下面是需要的程序代码致电：
创建程序dbo。[年销售额]
@BeginningDate DateTime，@ EndingDate DateTime
AS
如果@BeginningDate为空或@EndingDate为空
BEGIN
RAISERROR（'''NULL值不允许''，14,1,1）
返回
结束
SELECT O.ShippedDate，
O.OrderID，
OS.Subtotal，
DATENAME（yy，ShippedDate）AS年
来自ORDERS O INNER JOIN [订单小计] OS
ON O. OrderID = OS.OrderID
在@BeginningDate和@EndingDate之间的O.ShippedDate
GO
如你所见，我需要给出两个参数我回馈了一些数据。
当然我搜索要求，但这是我的第一次;）所以真的很好
don''知道，如何建立这部分代码。
所以我需要用''年'和''年销售'参数调用程序。
我''我发起了这样的呕吐：
objCom.CommandType =
objCom.CommandText =
objCom.Execute
感谢您的帮助。
mw
Of course you''re right.
Below there is code of procedure which a want to call:
CREATE PROCEDURE dbo.[Year to Year Sales]
@BeginningDate DateTime, @EndingDate DateTime
AS
IF @BeginningDate IS NULL OR @EndingDate IS NULL
BEGIN
RAISERROR(''NULL values are not allowed'', 14, 1)
RETURN
END
SELECT O.ShippedDate,
O.OrderID,
OS.Subtotal,
DATENAME(yy,ShippedDate) AS Year
FROM ORDERS O INNER JOIN [Order Subtotals] OS
ON O.OrderID = OS.OrderID
WHERE O.ShippedDate BETWEEN @BeginningDate AND @EndingDate
GO
As you see, I need to give two parametrs and I give back a some data.
Of course I search befor asking, but it is my first time ;) so a realy
don''t know, how build this part of code.
So I need call the procedure with ''Year'' and ''Year Sales'' parametrs.
I''ve founded somting like that:
objCom.CommandType =
objCom.CommandText =
objCom.Execute
Thanks for your help.
mw
" Marcin Wasilewski" <毫安******************** @ gmail.com>在消息中写道
news：11 ********************** @ g43g2000cwa.googlegr oups.com ...
"Marcin Wasilewski" <ma********************@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
当然你是对的。
下面是想要打电话的程序代码：
创建程序dbo。[年销售额]
@BeginningDate DateTime，@ EndingDate DateTime
AS
如果@BeginningDate为空或@EndingDate为空
BEGIN
RAISERROR（'''NULL值不允许''，14,11 ）
返回
结束
选择O.ShippedDate，
O.OrderID，
OS.Subtotal，
DATENAME（yy，ShippedDate）AS年
FROM ORDERS O INNER JOIN [Order Subtotals] OS
ON O.OrderID = OS.OrderID
在@BeginningDate和@EndingDate之间的O.ShippedDate
GO
如你所见，我需要提供两个参数，然后我会回馈一些数据。
当然我搜索要求，但这是我的第一次;）所以真的好吗？不知道，如何构建这部分代码。
所以我需要调用程序w ith''Year''和''Year Sales''参数。
我已经创建了这样的内容：
objCom.CommandType =
objCom.CommandText =
objCom。执行
感谢您的帮助。
mw
Of course you''re right.
Below there is code of procedure which a want to call:
CREATE PROCEDURE dbo.[Year to Year Sales]
@BeginningDate DateTime, @EndingDate DateTime
AS
IF @BeginningDate IS NULL OR @EndingDate IS NULL
BEGIN
RAISERROR(''NULL values are not allowed'', 14, 1)
RETURN
END
SELECT O.ShippedDate,
O.OrderID,
OS.Subtotal,
DATENAME(yy,ShippedDate) AS Year
FROM ORDERS O INNER JOIN [Order Subtotals] OS
ON O.OrderID = OS.OrderID
WHERE O.ShippedDate BETWEEN @BeginningDate AND @EndingDate
GO
As you see, I need to give two parametrs and I give back a some data.
Of course I search befor asking, but it is my first time ;) so a realy
don''t know, how build this part of code.
So I need call the procedure with ''Year'' and ''Year Sales'' parametrs.
I''ve founded somting like that:
objCom.CommandType =
objCom.CommandText =
objCom.Execute
Thanks for your help.
mw
我不确定您的程序名称是否为年到年销售额匹配
它做什么。如果想要创建一个基于一年的记录集，
那么也许你只需要一个输入参数 - 年份。你还需要
小心在...之间当使用日期作为订单日期时，也可能包含时间部分。我也会避免在
存储过程的名称中加入空格，因为这意味着你必须使用愚蠢的括号。
我不知道是否如果要将其包装在vba函数中，我会费心在存储的
过程中引发错误。您可以使用
vba编码来检查您是否有开始日期和结束日期。如果你要开始提出错误，那么也许你应该考虑让你的
存储过程提供一个返回值来显示它是否成功。
然后你必须得到你的vba编码来获得这个输出的价值
参数。我这里没有这样做，但是这给了你一般的想法：
Private Sub cmdTest_Click（）
错误GoTo Err_Handler
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rst作为ADODB.Recordset
Dim strConn As String
Dim dteStart As Date
Dim dteEnd As Date
dteStart = DateSerial（2006,1,1）
dteEnd = DateSerial（2006,2,1）
strConn ="提供商= SQLOLEDB;" &安培; _
" Data Source = MyServer;" &安培; _
" Initial Catalog = MyDatabase;" &安培; _
集成安全性= SSPI
设置cnn =新ADODB.Connection
cnn.Open strConn
设置cmd =新ADODB.Command
cmd.ActiveConnection = cnn
cmd .CommandText =" [年度销售额]"
cmd.CommandType = adCmdStoredProc
设置prm = cmd.CreateParameter（" ; @ BeginningDate"，adDate，adParamInput ,,
dteStart）
cmd.Parameters.Append prm
设置prm = cmd .CreateParameter（" @EndingDate"，adDate，adParamInput ,,
dteEnd）
cmd.Parameters.Append prm
设置rst = cmd.Execute
虽然不是rst.EOF
Debug.Print rst.Fields（" OrderID"）
rst.MoveNext
Wend
MsgBox" Done"，vbInformation
Exit_Handler：
如果不是第一个那么
如果rst.State<> adStateClosed然后
rst.Close
结束如果
设置rst = Nothing
结束如果
设置prm = Nothing
设置cmd = Nothing
如果不是cnn则没有那么
如果cnn.State<> adStateClosed然后
cnn.Close
结束如果
设置cnn = Nothing
结束如果
退出Sub
Err_Handler：
MsgBox Err.Description，vbExclamation，"错误号：" &安培; Err.Number
恢复Exit_Handler
结束子
I''m not sure whether the name of your procedure "Year to Year Sales" matches
what it does. If the idea is to create a recordset based on a single year,
then perhaps you only need one input parameter - the year. You also need to
be careful with "between" when using dates as the order date may also
contain a time portion. I would also avoid putting spaces in the names of
stored procedures as it means you have to use the silly brackets.
I don''t know whether I would bother to raise an error in the stored
procedure if you are going to wrap it in a vba function. You could use the
vba coding to check whether you have a start and end date. If you are going
to start raising errors, then perhaps you should think about getting your
stored procedure to provide a return value to show if it was successful.
You then have to get your vba coding to get the value of this output
parameter. I have not done this here, but this gives you the general idea:
Private Sub cmdTest_Click()
On Error GoTo Err_Handler
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rst As ADODB.Recordset
Dim strConn As String
Dim dteStart As Date
Dim dteEnd As Date
dteStart = DateSerial(2006, 1, 1)
dteEnd = DateSerial(2006, 2, 1)
strConn = "Provider=sqloledb;" & _
"Data Source=MyServer;" & _
"Initial Catalog=MyDatabase;" & _
"Integrated Security=SSPI"
Set cnn = New ADODB.Connection
cnn.Open strConn
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "[Year to Year Sales]"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("@BeginningDate", adDate, adParamInput, ,
dteStart)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@EndingDate", adDate, adParamInput, ,
dteEnd)
cmd.Parameters.Append prm
Set rst = cmd.Execute
While Not rst.EOF
Debug.Print rst.Fields("OrderID")
rst.MoveNext
Wend
MsgBox "Done", vbInformation
Exit_Handler:
If Not rst Is Nothing Then
If rst.State <> adStateClosed Then
rst.Close
End If
Set rst = Nothing
End If
Set prm = Nothing
Set cmd = Nothing
If Not cnn Is Nothing Then
If cnn.State <> adStateClosed Then
cnn.Close
End If
Set cnn = Nothing
End If
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Sub
这篇关于如何通话程序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
