Imports System.Data.SqlClient


Public Class Form1
    Dim myConnection As SqlConnection
    Dim myCommand As SqlCommand

 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        myConnection = New SqlConnection("Data Source=S1800DW01\SQLEXPRESS;Initial Catalog=VBTest;Integrated Security=SSPI;")
        Try
            'Testweise Verbindung öffnen:
            myConnection.Open()
            myConnection.Close()
        Catch ex As Exception
            MsgBox("Fehler bei 'myConnection.Open()'")
            Status("Fehler bei 'myConnection.Open()'", False)

        End Try
    End Sub

    Private Sub Btnfill_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnFill.Click
        ClearTable_UE_Data()
        AddRecord("EU-XXX-AAA", "Irgendwo", 1.1, 1.0)
        AddRecord("AB-XXX-YYY", "Überall", 1.0, 2.1)
        ReadTable()
    End Sub

    Private Sub BtnReadTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnReadTable.Click
        ReadTable()
    End Sub

    Private Sub BtnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnExit.Click
        End
    End Sub

    Sub ClearTable_UE_Data()
        Dim ra As Integer
        Try
            myConnection.Open()
            'opening the connection
            myCommand = New SqlCommand("Delete from UE_DATA", myConnection)
            'executing the command and assigning it to connection
            ra = myCommand.ExecuteNonQuery()
            Status("'ClearTable_UE_Data' ausgeführt")
        Catch ex As Exception
            Status("Fehler bei 'ClearTable_UE_Data'", False)
        End Try
        Try
            myConnection.Close()
        Catch ex As Exception
        End Try

    End Sub
    Sub ReadTable()
        'declaring the objects
        Dim dr As SqlDataReader
        Dim myInd As Integer = 0
        Dim myRow As String() = {"", "", "", ""}
        DataGridView1.Rows.Clear()

        Try
            myConnection.Open()
            'opening the connection
            myCommand = New SqlCommand("Select * from UE_DATA", myConnection)
            'executing the command and assigning it to connection
            dr = myCommand.ExecuteReader()
            While dr.Read()
                'reading from the datareader
                'displaying the data from the table
                For myInd = 0 To dr.FieldCount - 1
                    myRow(myInd) = dr(myInd).ToString
                Next
                DataGridView1.Rows.Add(myRow)
            End While
            dr.Close()
            Status("'ReadTable' OK")
        Catch e As Exception
            Status("Fehler bei 'ReadTable'", False)
        End Try

        Try
            myConnection.Close()
        Catch ex As Exception
        End Try

    End Sub

    Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd.Click
        AddRecord("EG-XXX-YYY", "ALLES", 1.5, 0.5)
    End Sub

    Sub AddRecord(ByVal Nummer As String, ByVal Verwendung As String, ByVal Wert1 As Double, ByVal Wert2 As Double)
        Dim ra As Integer

        'command-String zusammensetzen, bei float-Werten muss das Komma durch den Punkt ersetzt werden, Strings müssen in
        ' einfache Anführungszeichen gesetzt werden ...
        Dim cmdString As String = "'" & Nummer & "','" & Verwendung & "'," & Wert1.ToString.Replace(",", ".") & "," & Wert2.ToString.Replace(",", ".")
        cmdString = "INSERT INTO UE_DATA(Nummer,Verwendung,Wert1,Wert2) values(" & cmdString & ")"
        Try
            myConnection.Open()

            myCommand = New SqlCommand(cmdString, myConnection)
            ra = myCommand.ExecuteNonQuery()
            Status("'AddRecord' OK")
        Catch ex As Exception
            Status("Fehler bei 'AddRecord'", False)

        End Try

        Try
            myConnection.Close()
        Catch ex As Exception
        End Try

    End Sub

    Sub Status(ByVal txt As String, Optional ByVal ok As Boolean = True)
        If ok Then
            lblStatus.BackColor = Color.LightGreen
        Else
            lblStatus.BackColor = Color.Red
        End If
        lblStatus.Text = txt
    End Sub

End Class
