Daten aus stored Proc nach VBA Access

[MartinHan]

Hallo,

ich habe da offenbar noch ein fundamentales Verständnisproblem...
Ich habe eine STP die einen Insert macht und möchte den neuen Identity Wert in VBA zurück haben. Eigentlich kein Hexenwerk, sollte man meinen.
Der Insert funktioniert auch, nur ich bekommen nix zurück...
Wo hakt es?

Hier der VBa Code

Public Function umsatz_add(KontoId As Long _
                        , Betrag As Currency _
                        , Buchungstext As String _
                        , UmsatzDatum As Date _
                        , umsatzart As Long _
                        , befreit As Boolean _
                        , Optional batch As Boolean) As Integer


    Dim strsql As String
    Dim bef_int As Integer
    Dim rc As Integer
   
    On Error GoTo myerror
   
    bef_int = 0
    If befreit = True Then bef_int = 1
   
    ' Construct the SQL for the pass-through query to call the stored procedure
     
    strsql = "EXEC [dbo].[umsatz_add] " _
                                                                     & KontoId _
                                                             & "," & Betrag _
                                                             & ",'" & Buchungstext & "'" _
                                                             & ",'" & year(UmsatzDatum) & "/" & Month(UmsatzDatum)          & "/" & Day(UmsatzDatum) & "'" _
                                                             & "," & umsatzart _
                                                             & "," & bef_int _
                                                             & ",1;"
                                                         
    rc = call_stp(strsql, Forms![start menue]!gbl_connstr, "umsatz_add")
   
    If rc > 0 Then
       
        DoCmd.OpenForm "Errorlogtab"
     
        GoTo myerror
       
    End If
   
myexit:
   
    Exit Function
   
myerror:
    MsgBox "Der Umsatz wurde nicht eingefügt", vbCritical, "Umsatz einfügen"
    Resume myexit
   
    End Function
   Public Function call_stp(strsql As String, strconn As String, strstpname As String) As Long
   
   '
   ' parameter:
   '
   ' strsql: sql string mit Exec stpname und parameter
   ' strconn: Connention Sting
   ' strstpname: Name der STP
   '
       
    On Error GoTo myerror

    Dim db1 As database
    Dim qdf As QueryDef
       
    call_stp = 0
   
    Set db1 = CurrentDb
   
    ' Create or reuse a pass-through query
   
   Set qdf = db1.CreateQueryDef("")
     
    ' Configure the pass-through query
    With qdf
   
       .Connect = strconn
       .sql = strsql
       .ReturnsRecords = False   ' No records are returned for a backup operation
   
       ' Execute the pass-through query
       .Execute
       
    End With
   
       
CleanUp:
    ' Clean up objects
    If Not qdf Is Nothing Then Set qdf = Nothing
    If Not db1 Is Nothing Then Set db1 = Nothing
    Exit Function
   
myerror:
    MsgBox "Fehler in STP: " & strstpname, vbCritical, "Fehler in STP"
    MsgBox Err.Description
    call_stp = 8
    Resume CleanUp
                       
End Function

Hier die STP:

alter procedure [dbo].[umsatz_add]
   -- Add the parameters for the stored procedure here
    (@kontoid int
    ,@Betrag money
    ,@Buchungstext nvarchar(100)
    ,@UmsatzDatum date
    ,@umsatzart int
   ,@newflag bit
    ,@befreit bit)
   

AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   begin try
       SET NOCOUNT ON;

       declare @new_identity as int
      
       --declare @new_identity int output   
   
       insert into [dbo].[umsatz] (kontoid
                        ,betrag
                        ,buchungstext
                        ,umsatzdatum
                        ,umsatzart
                        ,newflag
                        ,befreit)
                        values
                       (@kontoid
                              ,@Betrag
                              ,@Buchungstext
                              ,@UmsatzDatum
                              ,@umsatzart
                              ,@befreit
                       ,@newflag)
      

       SELECT @new_identity = SCOPE_IDENTITY()
         return @new_identity
      
   end try

begin catch
    DECLARE @ErrorMessage NVARCHAR(MAX);
     DECLARE @ErrorNumber INT;
     DECLARE @ErrorSeverity INT;
     DECLARE @ErrorState INT;
     DECLARE @ErrorLine INT;
     DECLARE @ErrorProcedure NVARCHAR(MAX);
     DECLARE @ErrorDateTime DateTime;
 
     SELECT
       @ErrorMessage = ERROR_MESSAGE(),
       @ErrorNumber = ERROR_NUMBER(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE(),
       @ErrorLine = ERROR_LINE(),
       @ErrorProcedure = ERROR_PROCEDURE(),
       @ErrorDateTime = GETDATE();

   Insert into [dbo].[ErrorLog] (ErrorNumber, ErrorState, ErrorSeverity, ErrorLine, ErrorProcedure, ErrorMessage, ErrorDateTime)
   Values (@ErrorNumber, @ErrorState, @ErrorSeverity, @ErrorLine, @ErrorProcedure, @ErrorMessage, @ErrorDateTime)

   RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
 END CATCH;
END

Danke!

Martin

[PhilS]

Output-Parameter lassen sich über Pass-Through-Abfrage nur umständlich auslesen.Ich empfehle das "return @new_identity" durch ein "SELECT @new_identity AS New_Id" zu ersetzen und dann in VBA ein Recordset zu öffen, um diesen Wert auszulesen.

Nebenbei: Bei der Übergabe von Betrag in VBA an die Pass-Through-Abfrage musst du sicherstellen, dass der Punkt als Dezimaltrennzeichen verwendet wird. Z.B. mit Str().

[markusxy]

Ich würd mir mal die Möglichkeiten mit ADO ansehen. Da kann man dann auch Parameter einsetzen und hat generell einige zusätzliche Möglichkeiten.

Wie immer findet man dann auch Beispiele im Web:

https://stackoverflow.com/questions/3268865/return-value-indicating-update-success-failure-of-sql-server-stored-procedure-vi

[MartinHan]

Hi,

das mit dem ADO gefallt mir ganz gut-

Ich habe jetzt mal ein ganz einfaches Beispiel entwckelt, nur um das Prinzip zu verstehen:

VBA:

Public Sub teststp()

On Error GoTo myerror

     Dim cn As New ADODB.connection
     Dim cmd As New ADODB.Command
     Dim rs As New ADODB.Recordset
     Dim p1, p2, summe As Long
     
     
     p1 = 10
     p2 = 20

     cn.ConnectionString = Forms![start menue]!tab_connstr
     
    cn.Open
     
     With cmd

             .ActiveConnection = cn
             .CommandType = adCmdStoredProc
             .CommandText = "teststp"
             .Parameters.Refresh
             .NamedParameters = True
             .Parameters("@p1").Value = p1
             .Parameters("@p2").Value = p2
             
    End With

     Set rs = cmd.Execute
     'rs.Close

     summe = cmd.Parameters("@summe").Value
     MsgBox "summe von " & p1 & "," & p2 & ": " & summe, vbInformation, "Summe aus STP"
     
    Set cmd = Nothing
    cn.Close

myexit:
   Exit Sub

myerror:
    MsgBox "Fehler in Testspt", vbCritical
    MsgBox Err.Description
    Resume myexit


End Sub

Stp:

ALTER PROCEDURE [dbo].[teststp]
   -- Add the parameters for the stored procedure here
   @p1 int
   ,@p2 int
   ,@su int output

AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

    -- Insert statements for procedure here
   set @su = @p1 + @p2

   return 0
END

Zwei Fragen dazu:

ich habe gelesen, das man erst auf OUTPUT-werte zugreifen kann, wenn das Recordset geschlossen ist-
Wenn ich aber rs.close ausführe kommt der Fehler das rs schon geschlossen ist...
Die Summe wird aber trotzdem richtig berechnet...

Wie frage ich in VBA den Returncode ab? (er ist jetzt der Einfachheit halber auf 0 gesetzt, im wahren Leben sind die Statemnets natürlich komplexer).

Danke für Hilfe

Martin

[PhilS]

ich habe gelesen, das man erst auf OUTPUT-werte zugreifen kann, wenn das Recordset geschlossen ist-

[...]

Wie frage ich in VBA den Returncode ab? (er ist jetzt der Einfachheit halber auf 0 gesetzt, im wahren Leben sind die Statemnets natürlich komplexer).

Das man erst auf Output-Parameter zugreifen kann, wenn ein (alle?) Recordsets aus der Stored Procedure geschlossen sind habe ich so nicht im Kopf. Evtl. könnte es sein, dass man abwarten muss bis alle Recordsets überhaupt erstmal erstellt sind, wenn man die SP asynchron ausführt.

Der Return-Wert einer Stored Procedure ist immer der erste Parameter:

Code [Auswählen] Erweitern

MsgBox cmd.Parameters(0).Value

[MartinHan]

Hi,

manchmal kann alles so einfach sein...

Wunderbar!

Martin

[MartinHan]

Hier jetzt der gesamte Code des VBA Codings.
Vielleicht hilft sie jetzt manchen als Grundlage. Es ist wie gesagt ein simples Beispiel, bei dem es nur darum geht, ein Prinzip zu klären.
Wichtig war für mich eben die Unterscheidung zwischen Ergebnis und Returncode, die aus meiner Sicht zwei verschiedene Dinge sind. Natürlich kann man das Ergebnis auch dementsprechend interpretieren....Geschmacksache.
Das mit dem Close von rs bleibt für mich immer noch rätselhaft und ich wie´auch nicht, wie man das rausfinden soll.
Aber...works as desinged!

Public Sub teststp()

On Error GoTo myerror

     Dim cn As New ADODB.connection
     Dim cmd As New ADODB.Command
     Dim rs As New ADODB.Recordset
     Dim p1, p2, summe As Long
     Dim rc As Long
     
     
     p1 = 10
     p2 = 20
     
           cn.ConnectionString = Forms![start menue]!tab_connstr
           cn.Open
           
 
     With cmd

             .ActiveConnection = cn
             .CommandType = adCmdStoredProc
             .CommandText = "teststp"
             .Parameters.Append cmd.CreateParameter("ReturnValue", adInteger, adParamReturnValue)
             .Parameters.Refresh
             .NamedParameters = True
             .Parameters("@p1").Value = p1
             .Parameters("@p2").Value = p2
             
    End With

     Set rs = cmd.Execute
 
     'rs.Close

     summe = cmd.Parameters("@su").Value
     MsgBox "summe von " & p1 & "," & p2 & ": " & summe, vbInformation, "Summe aus STP"
         
     MsgBox "returncode ist: " & cmd.Parameters(0).Value, vbInformation, " Returncode aus STP"
     
     Set cmd = Nothing
     cn.Close

myexit:
   Exit Sub

myerror:
   
    MsgBox "Fehler in Testspt", vbCritical
    MsgBox Err.Description
    Resume myexit


End Sub

[PhilS]

Das mit dem Close von rs bleibt für mich immer noch rätselhaft und ich wie´auch nicht, wie man das rausfinden soll.

Für den Anfang des Herausfindens wäre es dringend empfohlen eine Stored Procedure zu verwenden, die auch ein Recordset erstellt.

[MartinHan]

Da hast du wohl recht...Das werde ich mir dann noch mal ansehen.
Aber fürs erste klappt es jetzt auch ohne RS. Die Prozedur umsatz_add macht was sie soll.
Die Parameter werden geprüft, der Insert wird gemacht und es gibt output Parameter, im positiv Fall die neu eingefügte ID oder es gibt Meldungen im Fehlerfall, entweder bei Userfehlern (Daten nicht da oder falsch) oder Systemfehlern,
am Returncode kann man es ablesen.
Mann kann sich jetzt darüber streiten, od die Prüfung der Daten in der STP oder in Access gemacht werden sollte, doppelt wäre natürlich Unsinn.

Falls wir uns nicht mehr sehen, allen einen guten Rutsch!

Martin

[Beaker s.a.]

Nur am Rande

Code [Auswählen] Erweitern

Dim p1, p2, summe As Longhier wird nur Summe als Long deklariert, p1 und p2 sind Variant.

[Bitsqueezer]

Hallo,

für den Rückgabewert (der immer nur ein Long sein kann) gibt es einen definierten Namen: @RETURN_VALUE, darunter kann man den Wert aus der Parameter-Collection auslesen.

Solange die SP kein Recordset zurückgibt, benötigt man auch kein Recordset zur Ausführung. Wenn man also nur einen INSERT ausführen will, genügt ein ADO-Command.

OUTPUT-Variablen kann man nach der Ausführung aus der Parameters-Collection auslesen.

Wenn man mehrere Zeilen mit einem INSERT einfügen will, dann hilft Scope_Identity nicht, da es nur den letzten INSERT betrifft. In dem Fall hilft es, eine Tabelle mit CREATE TABLE entweder als Temp-Tabelle oder als Speichertabelle (als Tabellenvariable) zu erstellen und mit OUTPUT TO im INSERT kann man dann alles, was geschrieben wurde, in die Tabelle einfügen. Die kann man am Ende mit SELECT auslesen und an den Aufrufer zurückgeben. In dem Fall benötigt man natürlich auch ein Recordset.

"Parameters.Refresh" ist eine bequeme Methode, die Parameter vom SQL Server anzufordern, das ist einfach und funktioniert auch sehr gut. Leider nicht in jedem Fall - wenn der Benutzer der Datenbank nicht die entsprechenden Rechte auf dem Server hat, die Parameter auszulesen, dann geht das leider daneben.
Es ist zwar umständlicher, aber sicherer, Parameter selbst zu bilden und mit "Parameters.Append" an die Collection anzuhängen. Nachteil ist, bei Änderung der Parameter muß man den Code dazu auch anpassen.

Ich hatte mir dazu mal eine umfangreiche Klasse geschrieben, die das mit eigenen Funktionen vom SQL Server liest, aber das wäre hier wahrscheinlich ein wenig viel...

Hier mal die Function auf dem SQL Server, mit der ich die Informationen an VBA weitergereicht habe:

Code [Auswählen] Erweitern

-- =============================================
-- Author:      Christian Coppes
-- Create date: 06.01.2014
-- Description: Returns column information for a specified view object
-- =============================================
ALTER FUNCTION [dbo].[fnCCADOColumnInfos]
(
   -- Add the parameters for the function here
   @strSchema nvarchar(128),
   @strObject nvarchar(128)
)
RETURNS
@tblParameters TABLE
(
   F_Position          int PRIMARY KEY NOT NULL,
   F_ColName           nvarchar(128) NOT NULL,
   F_ColType           nvarchar(128) NOT NULL,
   F_ColDefault        nvarchar(4000) NULL,
   F_ColNullable       bit NOT NULL,
   F_ColSize           int NULL,
   F_NumPrecision      tinyint NULL,
   F_NumPrecRadix      smallint NULL,
   F_NumScale          int NULL
)
WITH EXECUTE AS 'UserWithOwnerRights'  -- or user with rights to view definitions
AS
BEGIN
   -- As this is able to load informations about views AND tables, add further code to make sure only views can be used if you want to secure this
   INSERT INTO @tblParameters(F_Position,F_ColName,F_ColType,F_ColDefault, F_ColNullable,
                              F_ColSize,F_NumPrecision,F_NumPrecRadix,F_NumScale)
   SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, (CASE IS_NULLABLE WHEN 'NO' THEN 0 WHEN 'YES' THEN 1 END) AS IsNullable,
          CHARACTER_MAXIMUM_LENGTH , NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE
     FROM INFORMATION_SCHEMA.Columns
    WHERE TABLE_SCHEMA = @strSchema
      AND TABLE_NAME = @strObject
    ORDER BY ORDINAL_POSITION;
   RETURN;
END

Vorteil ist, es ist damit eine eigene Funktion und man kann die Rechte entsprechend vergeben, diese auszuführen, ohne dem User zuviele Rechte zu geben. Außerdem kann man mit "EXECUTE AS" einen User angeben, der die Rechte hat, die Informationen auszulesen, so daß die Function mit höheren Rechten ausgeführt werden kann, als der User eigentlich hat.

Du gibst in Deiner 1. SP einen Fehler über ein SELECT zurück. Das würde ich nicht machen. Das Problem ist, daß Du u.U. etwas in der SP ausliest und als Recordset in VBA erwartest. Wenn ein Fehler auftritt, hast Du am Ende im Recordset auf einmal die Fehlertabelle und nicht das erwartete Recordset. Außerdem kann es Dir passieren, daß Du dann mehr als ein Recordset zurückerhältst und das Fehlerlog ist das letzte davon. Zwar kann ADO mit multiplen Recordsets umgehen, das mußt Du dann aber jedesmal berücksichtigen und selbst umschalten.

Meine Empfehlung:
In einem großen DB-Projekt für ein ERP-System habe ich das einfach so gemacht, daß es immer einen OUTPUT-Parameter "@strError" gab, über den ich selbsterstellte Fehlermeldungen ausgeben konnte, die dann einfach beim Ausführen der SP ausgelesen werden konnten.
RAISERROR habe ich nie verwendet, weil das in Access immer problematisch ist, abzufangen.
Den Fehler in eine Errorlog-Tabelle zu schreiben, habe ich ebenfalls so gemacht, allerdings über eine SP, um das zentral zu machen (das funktioniert, der Fehler geht beim Aufruf nicht verloren).
Als Return-Code habe ich dann einen negativen Wert mit der ID im Fehlerlog zurückgegeben, so daß man das Fehlerlog mit der ID (ohne das Minus) aus der Fehlertabelle auslesen konnte, wenn man den Fehler in VBA anzeigen will. Da der Return-Code immer ein Long-Wert ist, ist das kein Problem. Positive oder 0-Werte dann für andere Returncodes, je nach SP.

Somit kann man einen Fehler grundsätzlich über den Return-Code ermitteln, der dann einfach immer negativ ist und den Fehler dann außerdem aus dem Errorlog auslesen und anzeigen. Eigene Fehlertexte, die nicht SQL Server generiert hat, kann man dann aus @strError auslesen, hier dann auch z.B. HTML-formatiert.

Gruß

Christian

[MartinHan]

Hallo Christian,

danke für deine Nachricht! ich muss die erstmal verarbeiten...
Es ist wie gesagt ein kleines Projekt und ich bin hier Einzelkämpfer, aber hoch motiviert!
Ich habe viele Jahre Erfahrung in Access und fange jetzt erst an mit Stp.
Ich muss aber sagen gegenüber VBA ist die Programmierung aber doch eher simple.
Ansonsten liegen meine EDV-Erfahrungen bei Metadaten-Repositories und Host-CCM.
Das wichtigste ist an dieser Anwendung, das sie robust ist und der Dialog mehr so eine Art Wizzard ist. Es darf keinen Button geben, auf den man nicht klicken darf.

So sieht mein STP jetzt aus, ich denke, da ist schon viel von dem drin, was du mir empfohlen hasT:

ALTER procedure [dbo].[umsatz_add]
    -- Add the parameters for the stored procedure here
    (@kontoid int
    ,@Betrag money
    ,@Buchungstext nvarchar(100)
    ,@UmsatzDatum date
    ,@umsatzart int   
    ,@befreit bit
    ,@newflag bit
    ,@new_identity int output
    ,@errmsg nvarchar(100) output)
   

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
   
    begin try

        SET NOCOUNT ON;           
     
        set @new_identity = 0

        set @errmsg = ''
       
        -- Check der Parameter
               
        IF(@kontoid IS NULL or @kontoid = 0)           
        begin
            SET @errmsg = N'Die Kontonummer fehlt'             
            return 8
        end

        IF(@betrag IS NULL)
        begin
            SET @errmsg = N'Der Betrag fehlt'
            return 8
        end
       
        IF(@buchungstext IS NULL or @buchungstext = '')
        begin
            SET @errmsg = N'Bu-txt fehlt'             
            return 8           
        end

        IF(@umsatzdatum IS NULL )
        begin
            SET @umsatzdatum = N'umsatzdatum fehlt'           
            return 8
           
        end             
       
        IF(@umsatzart IS NULL OR @umsatzart = 0 )
        begin
            SET @errmsg = N'Umsatzart fehlt'           
            return 8           
            end

        IF(@befreit IS NULL )
        begin
            SET @errmsg = N'befreit  fehlt'           
            return 8           
        end       

          insert into [dbo].[umsatz] (kontoid
                                          ,betrag
                                      ,buchungstext
                                      ,umsatzdatum
                                        ,umsatzart
                                      ,newflag
                                      ,befreit)
                                      values
                                      (@kontoid
                                      ,@Betrag
                                      ,@Buchungstext
                                      ,@UmsatzDatum
                                      ,@umsatzart
                                      ,@befreit
                                        ,@newflag)
            SELECT @new_identity = SCOPE_IDENTITY()             
    end try

begin catch
   
  set @errmsg = ERROR_MESSAGE();
 
  Return 8
 
END CATCH;

END

Wie gesagt möchte ich mittelfristig die Anwendung in andere Hände übergeben, mit 80 möchte ich nicht mehr entwickeln.
Als ich noch gearbeitet habe, haben wir in unserer Abteilung auch Anwendungen für die Kollegen entwickelt. Wir hatten dann mal ein Expertin für Dialoge eingeladen, die unsere Dialog beurteilen sollte.
Sie war sehr höflich, lächhelte aber angesichts unserer Machenschaften....
Sie sagte, bei Daimler z.B. haben sie Versuchspersonen, die die Dialog testen sollen. Denen werden spezielle Brillen aufgesetzt  um auf jeder Seite die Augenbewegung zu messen. Dieses dient dann als Basis für Optimierungen...
Soweit werde ich aber nicht gehen...

Bis dahn! Und Danke!

Martin

[Bitsqueezer]

Hallo Martin,

das ist allerdings, wie mehrfach erwähnt, großer Aufwand, zumal bei Fehlern in der EIngabe ständig die SP neu aufgerufen werden muß.

Validierung ist i.d.R. die Hauptaufgabe des Frontends. Einfach "Form_BeforeUpdate" verwenden und dort erst mal alles prüfen und bei Fehlern eine Meldung ausgeben und Cancel=True setzen - fertig.
Natürlich in einem gebundenen Formular.

Du ersparst Dir dann den ganzen Aufwand vom Schreiben der SP über Aufrufen usw. mit allen möglichen Extra-Fehlerquellen.

Tip für Identity: Wenn Du nur einen einzigen Wert wie die ID zurückgeben willst, genügt es, diese mit RETURN zurückzugeben. Der SELECT am Ende bewirkt, daß Du die Daten nur mit einem Recordset wieder auslesen kannst.

Mit RETURN kannst Du den Wert einfach aus den Parametern auslesen, wie oben beschrieben. Alternativ, wenn Du eine OUTPUT für @new_identity verwendest, kannst Du auch dort den Wert auslesen und RETURN für andere Dinge verwenden.

SELECT ist in einer SP i.d.R. nur notwendig, wenn Du mehr als einen Wert zurückgeben willst.

Ich find's cool, daß Du mit 79 noch aktiv sowas machst. Einfach immer dranbleiben, hält den Geist fit.

Gruß

Christian

[MartinHan]

bin 73!

[Bitsqueezer]

Oh, sorry, hatte irgendwie in Erinnerung, 79 gelesen zu haben irgendwo...
Ja, dann! Jungspund!...