Access SQL-Abfrage mit Rechenoperation (Rückgabe von errechnetem Wert)

dive26 · 30. Juni 2014, 11:33

Hallo Leute,

ich stehe vor der Aufgabe einen Wert in einer Access-Tabelle hochzuzählen (und in der Tabelle auch zu aktualisieren) und gleichzeitig den hochgezählten Wert auszulesen.

Den Wert hochzählen kann ich ja schon mal:

VB.NET-Quellcode

''' <summary>
''' Übergibt die nächste Rechnungsnummer und setzt diese als letzte Rechnungsnummer in der Datenbank
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Function GetNextRechNr() As Long
Dim conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & FlexX_Datenverzeichnis + "Umsaetze.mdb" & "; Jet OLEDB:Database Password=" & DatenbankPasswort & ";")
Dim cmd As New OleDbCommand
Dim sql As String = ""
cmd.Connection = conn
conn.Open()
sql = "UPDATE Rechnungsnummer SET Rechnungsnummer = Rechnungsnummer +1"
'Der neue Wert von 'Rechnungsnummer" soll auch gleich abgefragt werden
'wie wird das gehandhabt?
cmd = New OleDbCommand(sql, conn) 'Return CLng(cmd.ExecuteScalar())
cmd.ExecuteNonQuery()
conn.Close()
Return 111
End Function

Wie bekomme ich aber den Wert in eine Variable übergeben und gleichzeitig hochgezählt. Geht das überhaupt oder muss ich dazu zwei SQL Abfragen hintereinander machen?

Ich vermute es gibt dazu eine kombinierte SQL-Abfrage, bin aber bis jetzt nicht drauf gekommen wie die lauten sollte.

LG Roland

petaod · 30. Juni 2014, 11:43

Ich würde über das Datenmodell nochmals nachdenken.

Du hast eine eigene Tabelle mit einer Zeile und einer Spalte, die du für das Verwalten einer Rechnungsnummer verwendest.
Mach das lieber über die Autoincrement-Eigenschaft in der Rechnungstabelle.

dive26 · 30. Juni 2014, 11:51

Das geht leider nicht. In der Datenbank befinden sich 3 Tabellen:

1. Artikelumsätze
2. Rechnungsabschlüsse mit Detailinformationen
3. Tabelle mit der zuletzt verwendeten Rechnungsnummer und ein paar anderen Werten die auf jedem Client verfügbar sein müssen

Das Problem:
Zwischen dem Ermitteln der Rechnungsnummer und Schreiben der Datensätze in die anderen Spalten kann schon mal eine oder mehrere Sekunden vergehen. Meine erste Idee wäre auch gewesen aus der Tabelle mit den Rechnungsabschlüssen einfach die nächste freie Rechnungsnummer zu ermitteln. Wenn das aber 10 Kassen zur selben Zeit machen, dann kann schon mal eine Rechnungsnummer doppelt vergeben werden.

Deshalb eine eigene Tabelle mit einem Wert, der sofort bei der Abfrage erhöht wird. Dann ist es egal wie lange die einzelne Kasse braucht um die restlichen Daten in der Tabelle zu verbuchen.

Also brauche ich nur eine Funktion die den Wert um eins erhöht, speichert und den neuen Wert zurückgibt.

Dieser Code hier würde zwar funktionieren, aber ich glaube ich habe da eine SQL-Abfrage unnötig zu viel (soll ja nicht all zu viel Rechenzeit und nicht unötig viele Datenbankzugriffe erfordern):

VB.NET-Quellcode

''' <summary>
''' Übergibt die nächste Rechnungsnummer und setzt diese als letzte Rechnungsnummer in der Datenbank
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Function GetNextRechNr() As Long
Dim RecNu As Long = 0
Dim conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & FlexX_Datenverzeichnis + "Umsaetze.mdb" & "; Jet OLEDB:Database Password=" & DatenbankPasswort & ";")
Dim cmd As New OleDbCommand
Dim sql As String = ""
cmd.Connection = conn
conn.Open()
sql = "UPDATE Rechnungsnummer SET Rechnungsnummer = Rechnungsnummer +1"
cmd = New OleDbCommand(sql, conn)
cmd.ExecuteNonQuery()
sql = "SELECT Rechnungsnummer FROM Rechnungsnummer"
cmd = New OleDbCommand(sql, conn)
RecNu = CLng(cmd.ExecuteScalar())
conn.Close()
Return RecNu
End Function

ErfinderDesRades · 30. Juni 2014, 11:58

ich hab nicht verstanden, wozu das gut sein soll, aber diese Frage kann ich dir beantworten:

VB.NET-Quellcode

'Der neue Wert von 'Rechnungsnummer" soll auch gleich abgefragt werden
'wie wird das gehandhabt?

mit einem Select-Command - wie sonst?

aber ich empfehle auch, erstmal DB-Grundlagen zu lernen.
(Scheint mir jdfs. ein grober Fehler, dass dein Update-Command keine Where-Einschränkung hat. Da werden dann wohl alle Rechnungsnummern hochgezählt. Vlt. irre ich mich - wie gesagt: ich peile nicht, wozu das überhaupt gut sein soll)

dive26 · 30. Juni 2014, 12:07

@ErfinderDesRades
Es gibt in der Tabelle "Rechnungsnummern" nur eine Zeile mit mehreren Spalten. Also nur einen Datenbankeintrag.
Daher kein WHERE, da sowieso immer nur eine Zeile da drin steht.

derzeit steht zwar nur noch AnzahlPersonen dabei, es wird aber auch noch ein paar andere globale Werte geben
Ich könnte die Rechnungsnummer und die anderen Werte zwar auch in eine simple Datei speichern, jedoch hätte ich die Umsatzdaten gerne in einer einzigen Datenbank zusammengefasst. Hat fiskalische Gründe (Unveränderbarkeit der Daten) - mit Datenbankpasswort (ja ja, kann man alles knacken).

Dass ich eine SELECT-Abfrage brauche ist mir schon klar (habe ich ja im zweiten Codebeispiel gepostet und das funktioniert soweit auch. Aber wie ich eine UPDATE und eine SELECT Anweisung in einen einzigen Befehl verkapseln kann weis ich nicht. Oder macht das dann schon keinen eklatanten Geschwindigkeitsunterschied mehr aus ob eine Abfrage oder zwei hintereinander?

petaod · 30. Juni 2014, 12:10

dive26 schrieb:

Dieser Code hier würde zwar funktionieren, aber ich glaube ich habe da eine SQL-Abfrage unnötig zu viel

Der funktioniert nur dann, wenn sich zwischen die beiden SQL-Befehlen nicht ein anderer Client mit demselben Anliegen drängt.
Plötzlich verarbeitet dann der eine Client den Satz vom anderen.

dive26 schrieb:

soll ja nicht all zu viel Rechenzeit und nicht unötig viele Datenbankzugriffe erfordern

Ich würde mir viel mehr Sorgen um die Datenintegrität machen, als um ein paar verlorene Nanosekunden.

Schau dir mal diesen Beitrag zum Thema @@Identity an:
support.microsoft.com/kb/815629

ErfinderDesRades · 30. Juni 2014, 12:11

Ich nehme an, die RechnungsNummern sollen dann Primärschlüssel von Rechnung-Datensätzen werden?
Was spricht dagegen, den Rechnungen gleich eine Autowert-Spalte als Primkey angedeihen zu lassen?

dive26 · 30. Juni 2014, 12:18

@ErfinderDesRades
Prinzipiell schon, aber der interne Ablauf sieht so aus, dass zwischen Ermitteln der Rechnungsnummer, anschließendem Druck und dem speichern der Detailinfos etwas Zeit vergehen kann. Also ich brauche die Rechnungsnummer um diese am Formular andrucken zu lassen. Zusätzlich wird auch noch (fürs Finanzamt) eine zweite Protokolldatenbank beschrieben, dort brauche ich die ebenfalls.

Vom Ablauf her lässt sich hier leider nicht auf etwas anderes ausweichen.

@Pedaod
Ja, Datenintegrität ist mir auch das wichtigste. Auf die Nanosekunde kommt es auch nicht an (ein Test ergab unter einer ms beim Zugriff durch beide Abfragen).
Danke für den Link. Muss ich mir mal in Ruhe anschauen, auf den ersten Blick blick ich mich da noch nicht durch.

ErfinderDesRades · 30. Juni 2014, 12:32

also ich würde ganz normal eine Rechnung erstellen, und die auch schon unbearbeitet in die DB inserten. Beim Insert wird ja eh der DB-generierte neue Autowert-Primkey zurückgemeldet.
Ja, und alles paletti - dann kannste die Rechnung fertig machen und drucken und bla, und natürlich ein zweites mal speichern in der endgültigen Version.

dive26 · 30. Juni 2014, 13:12

@ErfinderDesRades
Das wäre sicher eine Möglichkeit, jedoch für mich leider in der Organisation extrem hinderlich (hängen zig Sachen hinten dran und alle Einträge müssten nochmals aktualisiert werden, das benötigt dann wiederum mehr Code und mehr Datenbankzeit). Auch wenn ich die Rechnungsnummer so wie von Dir vorgeschlagen handhaben würde, hätte ich immer noch ein paar Andere Werte wo ich vor dem selben Problem stünde. Aber danke trotzdem für die Idee.

Dann bleibe ich am Besten bei den zwei Abfragen und helfe mir mit einem Hilfswert um sicherzustellen, dass zwischen den beiden Abfragen kein anderer Client mit dem selben Anliegen zugegriffen hat. Dies ist wohl eher unwahrscheinlich bei einer Zugriffszeit unter einer ms, aber dennoch möglich. Folgender Code berücksichtigt auch diesen Umstand:

VB.NET-Quellcode

''' <summary>
''' Übergibt die nächste Rechnungsnummer und setzt diese als letzte Rechnungsnummer in der Datenbank
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Function GetNextRechNr() As Long
Dim RecNu As Long = 0
Dim conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & FlexX_Datenverzeichnis + "Umsaetze.mdb" & "; Jet OLEDB:Database Password=" & DatenbankPasswort & ";")
Dim cmd As New OleDbCommand
Dim sql As String = ""
Dim Kennung As String = My.Settings.Stationskennung + "_" + Now().ToString("ss.fff")
cmd.Connection = conn
conn.Open()
sql = "UPDATE Rechnungsnummer SET Rechnungsnummer = Rechnungsnummer +1 , LetzteStationImZugriff='" + Kennung + "'"
cmd = New OleDbCommand(sql, conn)
cmd.ExecuteNonQuery()
sql = "SELECT Rechnungsnummer FROM Rechnungsnummer WHERE LetzteStationImZugriff='" + Kennung + "'"
cmd = New OleDbCommand(sql, conn)
RecNu = CLng(cmd.ExecuteScalar())
'Wenn keine Rechnungsnummer (0) übergeben wird, dann hat inzwischen eine andere Station eine Rechnungsnummer angefordert.
'Da das Abfragefenster extrem klein ist, kann nicht mehr als eine Abfrage dazwischen gemacht worden sein und sogar diese ist eher unwahrscheinlich
'Daher, wenn dieser Fall dennoch eintritt einfach eine erneute Abfrage ohne Kennung durchführen und das Ergebnis mit 1 subtrahieren
If RecNu = 0 Then
sql = "SELECT Rechnungsnummer FROM Rechnungsnummer"
cmd = New OleDbCommand(sql, conn)
RecNu = CLng(cmd.ExecuteScalar()) - 1
ErrorLog("Doppelter Datenbankzugriff auf Rechnungsnummer " + RecNu.ToString + " wurde zugewiesen")
End If
conn.Close()
Return RecNu
End Function

Sollte der unwahrscheinliche Fall eintreten, dann sehe ich das im Logfile. Kann das ja die nächsten Monaten bei meinem BETA-Test und beim Entwickeln beobachten ob der Fall überhaupt eintritt.
In meiner alten Software (VB6) hatte ich das auch so gehandhabt, jedoch ohne diesen Sicherheitsgriff. Bis jetzt hat es auch im Netzwkerkbetrieb mit mehreren Clients ohne eine fehlende Rechnungsnummer geklappt (und das seit 2005).

Danke für Eure Antworten.
Zumindest weis ich jetzt, dass es keine Kombi-SQL Anweisung gibt die Rechnen und Rückgeben kann.

LG Roland

petaod · 30. Juni 2014, 13:30

dive26 schrieb:

Kann das ja die nächsten Monaten bei meinem BETA-Test und beim Entwickeln beobachten ob der Fall überhaupt eintritt.

Das wird im Beta-Test und beim Entwickeln nicht auftauchen, sondern nur im produktiven Betrieb.
Und da auch nur ganz selten.
Aber wenn, ist die Kacke am Dampfen.

dive26 schrieb:

Zumindest weis ich jetzt, dass es keine Kombi-SQL Anweisung gibt die Rechnen und Rückgeben kann.

Es gibt so eine Art Kombi-Anweisung (nachgeschaltet).
SELECT @@IDENTITY gibt dir die letzte ID aus deiner DB-Session zurück.
Aber das kannst du nur verwenden, wenn du tatsächlich mit Autoincrement beim Insert arbeitest, also wirklich einen echten Primary Key verwendest.
Dein Ansatz mit der ausgelagerten Tabelle, in der du das selbst verwaltest, muss über kurz oder lang schief gehen.

dive26 · 30. Juni 2014, 13:38

Kann man denn eine Primary Autoincrement ID von z.B 1,2,3 auf 20140001,20140002 etc. setzen ohne Datensätze dazwischen? So weit ich weis nicht (kann mich aber auch irren).
Der User soll aber die Möglichkeit haben die Rechnungsnummer individuell zu ändern.
Die Finanz schreibt nur vor, dass diese Fortlaufend ist. Aber es spricht nichts dagegen, dass jedes Monat oder jedes Jahr der Nummernkreis geändert wird (also z.B. von 2014xxxxx auf 2015xxxxx).
Von Daher ist der Ansatz mit der letzten ID nicht praktikabel.

Danke Euch allen für die hilfreichen Antworten.
LG Roland

petaod · 30. Juni 2014, 14:31

ErfinderDesRades schrieb:

Beim Insert wird ja eh der DB-generierte neue Autowert-Primkey zurückgemeldet.

Wie du sehr häufig beobachten kannst, wird das Fixum des Nummernkreises einfach vorangestellt, so dass die angezeigte Rechnungsnummer aus Jahr + DatabaseID besteht.
Bei solchen Verfahren ist die erste Rechnungsnummer des Jahres nicht 2014000001 sondern z.B. 2014056842.

Ansonsten:
Ja, du kannst den Grundwert des Autoincrement erhöhen.
- leere Kopie der Tabelle erzeugen (ohne Daten)
- Feld-Typ von Autowert auf Zahl ändern
- Einen Datensatz erstellen mit dem Wert deines neuen Nummernkreises (z.B. 2014000000)
- eine Anfügeabfrage erstellen, in der du die neue Tabelle (mit dem einzelnen Datensatz) der alten Tabelle hinzufügst.
Dein nächster erzeugter Eintrag erhält jetzt die Nummer 2014000001

Access SQL-Abfrage mit Rechenoperation (Rückgabe von errechnetem Wert)

Access SQL-Abfrage mit Rechenoperation (Rückgabe von errechnetem Wert)

VB.NET-Quellcode

VB.NET-Quellcode

VB.NET-Quellcode

dive26 schrieb:

dive26 schrieb:

VB.NET-Quellcode

dive26 schrieb:

dive26 schrieb:

ErfinderDesRades schrieb:

Ähnliche Themen

3 Benutzer haben hier geschrieben