Wie die Rückgabe von Werten aus einer Gespeicherten Prozedur von SQL Server und Nutzen Sie in Access VBA

Ich habe eine Gespeicherte Prozedur in SQL Server, funktioniert gut. Jetzt kann ich es aufrufen aus VBA, aber wollen, um einen Wert zurückzugeben, um zu wissen, ob es irgendwelche Fehler etc. Der Letzte parameter bei meinem SP ist als AUSGABE:

@DataSetID int = 0,
@Destination char(1)='-',
@errStatusOK bit OUTPUT

Meine VBA-Aufruf der SP ist unten, aber es wird nicht funktionieren, jetzt, nach dem hinzufügen der neuen parameter, und ich bin mir nicht sicher, wohin ich gehe falsch, ich bekomme 3708 - Parameter object is improperly defined. Inconsistent or incomplete information was provided.:

Set cnn = New adodb.Connection
cnn.ConnectionString = 
   "DRIVER=SQL Server;SERVER=SERVER\SERVER;DATABASE=a_db;Trusted_Connection=Yes"

cnn.Open cnn.ConnectionString

Set cmd = New adodb.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stprMoveDataSet"

Set param = cmd.CreateParameter
               ("@DataSetID", adInteger, adParamInput, , stDataSet)
cmd.Parameters.Append param
Set param = cmd.CreateParameter
               ("@Destination", adChar, adParamInput, 1, stDestination)
cmd.Parameters.Append param
Set param = cmd.CreateParameter
               ("@errStatusOK", adBit, adParamReturnValue)
cmd.Parameters.Append param

rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Open cmd

Wie bekomme ich die vba arbeiten mit der OUTPUT-parameter und stellen Sie den return-Wert 'lesbar' durch die vba.

BEARBEITEN - ich habe mich verändert um die Frage, insbesondere über die Rückgabe der Werte und nicht nur über die Verwendung von OUTPUT-Parametern.

InformationsquelleAutor aSystemOverload | 2012-09-13

Set cnn = New adodb.Connection
cnn.ConnectionString = 
   "DRIVER=SQL Server;SERVER=SERVER\SERVER;DATABASE=a_db;Trusted_Connection=Yes"

cnn.Open cnn.ConnectionString

Set cmd = New adodb.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stprMoveDataSet"

Set param1 = cmd.CreateParameter
               ("@DataSetID", adInteger, adParamInput, , stDataSet)
cmd.Parameters.Append param
Set param2 = cmd.CreateParameter
               ("@Destination", adChar, adParamInput, 1, stDestination)
cmd.Parameters.Append param
Set param3 = cmd.CreateParameter
               ("@errStatusOK", adBit, adParamOutput, , adParamReturnValue)
cmd.Parameters.Append param

rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Open cmd

Sie müssen dies geschrieben, während ich mich die Veröffentlichung meiner eigenen Antwort, ich verstehe, wie ich schief gelaufen mit der CreateParameter jetzt. Vielen Dank, vielleicht verwenden, dass in die Zukunft.

InformationsquelleAutor AnandPhadke

Mehrere Wege möglich sind, um Werte mit Hilfe von VBA.

Recordset
Anzahl der betroffenen Datensätze (nur bei Insert/Update/Delete ansonsten -1)
Output-parameter
Rückgabewert

Mein code zeigt alle vier. Hier ist eine gespeicherte Prozedur, die einen Wert zurückgibt:

Create PROCEDURE CheckExpedite
    @InputX  varchar(10),
    @InputY int,
    @HasExpedite int out
AS
BEGIN
    Select @HasExpedite = 9 from <Table>
    where Column2 = @InputX and Column3 = @InputY

    If @HasExpedite = 9
        Return 2
    Else
        Return 3
End

Hier ist die sub, die ich in Excel VBA. Sie benötigen Verweis auf Microsoft ActiveX Data Objects 2.8 Library.

Sub CheckValue()

    Dim InputX As String: InputX = "6000"
    Dim InputY As Integer: InputY = 2014

    'open connnection
    Dim ACon As New Connection
    'ACon.Open ("Provider=SQLOLEDB;Data Source=<SqlServer>;" & _
    '    "Initial Catalog=<Table>;Integrated Security=SSPI")

    'set command
    Dim ACmd As New Command
    Set ACmd.ActiveConnection = ACon
    ACmd.CommandText = "CheckExpedite"
    ACmd.CommandType = adCmdStoredProc

    'Return value must be first parameter else you'll get error from too many parameters
    'Procedure or function "Name" has too many arguments specified.
    ACmd.Parameters.Append ACmd.CreateParameter("ReturnValue", adInteger, adParamReturnValue)
    ACmd.Parameters.Append ACmd.CreateParameter("InputX", adVarChar, adParamInput, 10, InputX)
    ACmd.Parameters.Append ACmd.CreateParameter("InputY", adInteger, adParamInput, 6, InputY)
    ACmd.Parameters.Append ACmd.CreateParameter("HasExpedite", adInteger, adParamOutput)

    Dim RS As Recordset
    Dim RecordsAffected As Long

    'execute query that returns value
    Call ACmd.Execute(RecordsAffected:=RecordsAffected, Options:=adExecuteNoRecords)

    'execute query that returns recordset
    'Set RS = ACmd.Execute(RecordsAffected:=RecordsAffected)

    'get records affected, return value and output parameter
    Debug.Print "Records affected: " & RecordsAffected
    Debug.Print "Return value: " & ACmd.Parameters("ReturnValue")
    Debug.Print "Output param: " & ACmd.Parameters("HasExpedite")

    'use record set here
    '...

    'close
    If Not RS Is Nothing Then RS.Close
    ACon.Close

End Sub

InformationsquelleAutor D_Bester

Ich würde zunächst sah OUTPUT-Parameter, konnte aber nicht herausfinden, wie man Sie zurück zu Access (VBA), um dann feedback für den Benutzer. Ein Kollege schlug ein WÄHLEN Sie in der Gespeicherten Prozedur und diese zu nutzen.

GESPEICHERTE PROZEDUR:
Hinzugefügt die folgenden am Ende:

SELECT @errStatusOK as errStatusOK, @countCurrent as countCurrent, @countHistorical as countHistorical

VBA:

Dim cnn As ADODB.Connection
Dim cmd As New ADODB.Command, rs As New ADODB.Recordset, param As New ADODB.Parameter
Dim fld As ADODB.Field
Dim stMessage As String

Set cnn = New ADODB.Connection
cnn.ConnectionString = "DRIVER=SQL Server;SERVER=SERVER\SERVER;DATABASE=a_db;Trusted_Connection=Yes"

cnn.Open cnn.ConnectionString

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stprMoveDataSet"

Set param = cmd.CreateParameter("@DataSetID", adInteger, adParamInput, , stDataSet)
cmd.Parameters.Append param
Set param = cmd.CreateParameter("@Destination", adChar, adParamInput, 1, stDestination)
cmd.Parameters.Append param

rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
'rs.Open cmd
Set rs = cmd.Execute
If rs!errstatusok = True Then
    stMessage = "Operation appears to have been successful, check the DataSets Listing..." & Chr(13) & "Also, the Server returned the following information: ["
Else
    stMessage = "Operation appears to have failed, check the DataSets Listing..." & Chr(13) & "Also, the Server returned the following information: ["
End If
For Each fld In rs.Fields
    stMessage = stMessage & "| " & fld.Name & " /" & fld.Value & " |"
Next fld
stMessage = stMessage & "]"

MsgBox stMessage

Dieser gibt die folliwing:
Operation appears to have failed, check the DataSets Listing... Also, the Server returned the following information: [| errStatusOK /False || countCurrent /0 || countHistorical /10 |]

Natürlich können Sie die Ausgabe-Parameter als auch als Rückgabewert oder betroffene Datensätze. Siehe meine Antwort.

InformationsquelleAutor aSystemOverload

2

Unter die anderen parameter, Aufzählungen, von denen die "adParamInput" genommen wird, ein anderes ist "adParamOutput", die an einen out-parameter einer gespeicherten Prozedur, und "adParamInputOutput" für einen parameter, der geht "beide Richtungen", wie Sie es waren. In deinem Fall glaube ich, dass "adParamOutput" angemessen wäre. Ich hoffe, das ist das, was du bist suchen für.
- Nicht das, was ich suchte, aber nützlich. Ein Kollege schlug vor, dass ich nur die Ausgabe über eine SELECT @errStatusOK etc und manipulieren es in der VBA, die funktionierte. Dank
InformationsquelleAutor David W

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.