Code:
' Wochenplan - Fehlzeitsliste
' Programmierer: Michael Spelter
' Option Strict On
Imports Microsoft.VisualBasic.DateAndTime
Imports System
Imports System.Data
Imports System.Data.Common
Imports System.Data.OleDb
Imports System.Windows.Forms
Public Class FrmAZCheck
Private connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Datenbank\LVV.mdb;"
Private query_zpl As String = "SELECT MANR, DATUM, FZART, ARBEITSZEIT, STATUS FROM ARBEITSZEIT"
Private query_stamm As String = "SELECT MANR, NVNAME, KZ_AKTIV FROM STAMM"
Private dtS As New DataTable
Protected Overrides Sub OnLoad(ByVal e As System.EventArgs)
Dim con As DbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\Datenbank\LVV.mdb;")
Dim cmd As DbCommand = New OleDbCommand("SELECT MANR, NVNAME, KZ_AKTIV FROM STAMM")
Dim cmdZPL As DbCommand = New OleDbCommand("SELECT MANR, DATUM, FZART FROM ARBEITSZEIT")
Dim ds As New DataSet
Dim dsZ As New DataSet
cmd.Connection = con
cmdZPL.Connection = con
REM Kalender
dgvKalender.ReadOnly = True
dgvKalender.Columns.Add("PNR", "Pers.-Nr.")
dgvKalender.Columns.Add("MA", "Mitarbeiter")
dgvKalender.Columns(0).Width = 40
dgvKalender.Columns(1).Width = 150
For i = 1 To 31
dgvKalender.Columns.Add("FZ" & Format(i, "00"), Format(i, "00"))
Next i
For i = 1 To 31
dgvKalender.Columns(1 + i).Width = 28
Next
'Erläuterung zu Fehlzeitsabkürzung zu Farben
'grüne Feld = Urlaub
'rote Feld = Krankmeldung
'rote Feld = unentschuldigte und entschuldigte Fehlzeiten
'blaue Feld = Dienst-/Geschäftsreise
Try
con.Open()
Dim dr As DbDataReader = cmd.ExecuteReader()
Dim drZ As DbDataReader = cmdZPL.ExecuteReader()
Dim dt As DataTable = dr.GetSchemaTable()
Dim dtZ As DataTable = drZ.GetSchemaTable()
Dim i As Integer = 0 ' Zeile
Dim tag As Integer = 0 ' Tag im Kalender
dgvKalender.Rows.Add(dt.Rows.Count + 3) ' Zeilen eingeführt
i = 0 ' Zeile ist auf Null
'Do While dr.Read()
'dgvKalender.Rows(i).Cells(1).Value = dr("NVNAME")
'dgvKalender.Rows(i).Cells(0).Value = dr("MANR")
'dr.NextResult()
'Do While drZ.Read()
'tag = Convert.ToDateTime(drZ("DATUM")).Day
'dgvKalender.Rows(i).Cells(1 + tag).Value = drZ("FZART")
'drZ.NextResult()
'Loop
'i = i + 1
'Loop
Do While dr.Read()
dgvKalender.Rows(i).Cells(0).Value = dr("MANR")
dgvKalender.Rows(i).Cells(1).Value = dr("NVNAME")
i = i + 1
If CStr(drZ("MANR")) = CStr(dr("MANR")) Then
tag = Convert.ToDateTime(drZ("DATUM")).Day
dgvKalender.Rows(i).Cells(1 + tag).Value = drZ("FZART")
End If
Loop
Catch ex As Exception
MsgBox("Fehler: " & ex.Message)
Finally
con.Close()
End Try
MyBase.OnLoad(e)
End Sub
End Class
Bitte um prüfen, ob Tabelle leider nur 1 Zeile und Fehlermeldung "Keine Daten für die Zeile/Spalte"
MfG
Michael
Zuletzt bearbeitet: