Imports System.Data.SqlClient
Imports System.IO
Public Class clsDatahandling

    Public Connectionstring As String

    Sub New()
        Me.Connectionstring = Get_Connectionstring()
    End Sub

    Public Function Get_Connectionstring()
        Dim rootWebConfig As System.Configuration.Configuration
        rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~/MyWebSiteRoot")
        Dim connString As System.Configuration.ConnectionStringSettings
        If (rootWebConfig.ConnectionStrings.ConnectionStrings.Count > 0) Then
            connString = rootWebConfig.ConnectionStrings.ConnectionStrings("LPConnectionString")
            If Not (connString.ConnectionString = Nothing) Then
                Return connString.ConnectionString
            Else
                MsgBox("Kein Connetionstring vorhanden")
                Return ""
            End If
        End If
    End Function

    Public Function Get_Tabledata(ByVal Tablename As String, Optional StoredProc As Boolean = False, Optional is_SQL_String As Boolean = False) As DataSet
        Dim sqlconnect As New SqlConnection
        Dim ds As New DataSet
        ds.Tables.Clear()
        sqlconnect.ConnectionString = Me.Connectionstring
        sqlconnect.Open()
        Dim da As New SqlDataAdapter("", sqlconnect)
        Dim sqlcmd As New SqlCommand
        sqlcmd.Connection = sqlconnect
        If StoredProc = True Then
            sqlcmd.CommandType = CommandType.StoredProcedure
            sqlcmd.CommandText = Tablename
        Else
            sqlcmd.CommandType = CommandType.Text
            sqlcmd.CommandText = "Select * from " + Tablename
        End If
        If is_SQL_String = True Then
            sqlcmd.CommandText = Tablename
        End If
        '        sqlcmd.CommandType = CommandType.StoredProcedure
        '       sqlcmd.CommandText = "Berufsliste"
        da.SelectCommand = sqlcmd
        da.Fill(ds, "Daten")
        Return ds
    End Function

    Public Function Run_Statement(ByVal Statement As String) As Boolean
        Try
            Dim sqlconnect As New SqlConnection
            Dim ds As New DataSet
            ds.Tables.Clear()
            sqlconnect.ConnectionString = Me.Connectionstring
            sqlconnect.Open()
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = sqlconnect
            sqlcmd.CommandText = Statement
            sqlcmd.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

#Region "DBKey"
    Public Function Get_NewDBKey(ByVal Tablename As String) As Integer
        Dim sqlconnect As New SqlConnection
        Dim ds As New DataSet
        ds.Tables.Clear()
        sqlconnect.ConnectionString = Me.Connectionstring
        sqlconnect.Open()
        Dim da As New SqlDataAdapter("", sqlconnect)
        Dim sqlcmd As New SqlCommand
        sqlcmd.Connection = sqlconnect
        sqlcmd.CommandType = CommandType.Text
        sqlcmd.CommandText = "Select key_wert from key_tabelle where beschreibung='" + Tablename + "'"
        da.SelectCommand = sqlcmd
        da.Fill(ds, "Daten")
        Dim keyvalue = ds.Tables(0).Rows(0).Item(0)
        keyvalue = keyvalue + 1
        sqlcmd.CommandText = "Update key_tabelle set key_wert = " + keyvalue.ToString + " where beschreibung='" + Tablename + "'"
        sqlcmd.ExecuteNonQuery()
        Return keyvalue
    End Function

    Public Function Get_LastKey(ByVal Tablename As String, ByVal fieldname As String) As Integer
        Dim SQLconnect As New SqlConnection()
        Dim ds As New DataSet
        ds.Tables.Clear()
        SQLconnect.ConnectionString = Me.Connectionstring
        SQLconnect.Open()
        Dim da As New SqlDataAdapter("", SQLconnect)
        Dim sqlcmd As New SqlCommand
        sqlcmd.Connection = SQLconnect
        sqlcmd.CommandType = CommandType.Text
        sqlcmd.CommandText = "Select top 1 " + fieldname + " from " + Tablename + " order by " + fieldname + " desc"
        da.SelectCommand = sqlcmd
        da.Fill(ds, "Daten")
        Dim keyvalue As Integer = 0
        Try
            keyvalue = ds.Tables(0).Rows(0).Item(0)
            keyvalue = keyvalue + 1
        Catch ex As Exception
            keyvalue = 0
        End Try
        sqlcmd.Dispose()
        SQLconnect.Close()
        ds.Dispose()
        Return keyvalue
    End Function

#End Region


#Region "Menu"
    Public Function Get_Menu(ByVal Menutype As Integer) As DataTable
        Dim SQLconnect As New SqlConnection()
        Dim ds As New DataSet
        ds.Tables.Clear()
        SQLconnect.ConnectionString = Me.Connectionstring
        SQLconnect.Open()
        Dim da As New SqlDataAdapter("", SQLconnect)
        Dim sqlcmd As New SqlCommand
        sqlcmd.Connection = SQLconnect
        sqlcmd.CommandType = CommandType.Text
        If Get_Option(11) = "1/2" And Menutype = 3 Then
            sqlcmd.CommandText = "Select * from web_menu where aktiv=1 and menutext<>'Schüler' and ( menutype=1 or menutype = " + Menutype.ToString + ") order by menutype, sort"
        Else
            sqlcmd.CommandText = "Select * from web_menu where aktiv=1 and ( menutype=1 or menutype = " + Menutype.ToString + ") order by menutype, sort"
        End If

        da.SelectCommand = sqlcmd

        da.Fill(ds, "Daten")
        sqlcmd.Dispose()
        SQLconnect.Close()
        Return ds.Tables(0)
    End Function
#End Region

#Region "Firma"
    Public Function Insert_Firma(ByVal keyvalue As Integer) As Boolean
        Try
            Dim SQLconnect As New SqlConnection()
            Dim sql As String = ""
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "Insert into firma (firmanr, aktiv, erstellt_am, mutiert_am, mutierer) values(" + keyvalue.ToString + ",1,getdate(),getdate(),1)"
            sqlcmd.CommandText = sql
            sqlcmd.ExecuteNonQuery()
            sqlcmd.Dispose()
            SQLconnect.Close()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function Firma_aktivieren(ByVal firmanr As Integer, userid As Integer)
        Try
            Dim SQLconnect As New SqlConnection()
            Dim sql As String = ""
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "update firma set aktuell=1, mutiert_am=getdate(), mutierer=" + userid.ToString + " where firmanr=" + firmanr.ToString
            sqlcmd.CommandText = sql
            sqlcmd.ExecuteNonQuery()
            sqlcmd.Dispose()
            SQLconnect.Close()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function Firmen_Inaktivieren(userid As Integer)
        Try
            Dim SQLconnect As New SqlConnection()
            Dim sql As String = ""
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "update firma set aktuell=0, mutiert_am=getdate(), mutierer=" + userid.ToString + " where aktiv=1 and aktuell=1"
            sqlcmd.CommandText = sql
            sqlcmd.ExecuteNonQuery()
            sqlcmd.Dispose()
            SQLconnect.Close()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function Inaktive_Berufsangebote_loeschen(userid As Integer)
        Try
            Dim SQLconnect As New SqlConnection()
            Dim sql As String = ""
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "update firmaberuf set aktiv=0, mutiert_am=getdate(),mutierer=" + userid.ToString + " where firmaberuf.firmanr in (select firmanr from firma where aktiv=0 or aktuell=0)"
            sqlcmd.CommandText = sql
            sqlcmd.ExecuteNonQuery()
            sqlcmd.Dispose()
            SQLconnect.Close()
            Return True
        Catch ex As Exception
            Return False
        End Try

    End Function
    Public Function Get_Firma(Firmanr As Integer) As DataTable
        Try
            Dim SQLconnect As New SqlConnection()
            Dim ds As New DataSet
            ds.Tables.Clear()
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim da As New SqlDataAdapter("", SQLconnect)
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sqlcmd.CommandText = "Select * from firma where firmanr=" + Firmanr.ToString
            da.SelectCommand = sqlcmd
            da.Fill(ds, "Daten")
            sqlcmd.Dispose()
            SQLconnect.Close()
            Return ds.Tables(0)
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function Get_Lehrer(Klassenr As Integer) As String
        Dim ds As New DataSet
        Try
            Dim SQLconnect As New SqlConnection()
            ds.Tables.Clear()
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim da As New SqlDataAdapter("", SQLconnect)
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sqlcmd.CommandText = "SELECT dbo.web_Lehrer.Name +' '+ dbo.web_Lehrer.Vorname as Lehrername FROM dbo.Klasse INNER JOIN  dbo.web_Lehrer ON dbo.Klasse.Lehrernr = dbo.web_Lehrer.LehrerNr where dbo.klasse.klassenr=" + Klassenr.ToString
            da.SelectCommand = sqlcmd
            da.Fill(ds, "Daten")
            sqlcmd.Dispose()
            SQLconnect.Close()
            Try
                Return ds.Tables(0).Rows(0).Item(0)
            Catch ex As Exception
                Return ""
            End Try

        Catch ex As Exception
            Return Nothing
        Finally
            ds.Dispose()
        End Try

    End Function

    Public Function Get_Firmenliste() As DataTable
        Try
            Dim SQLconnect As New SqlConnection()
            Dim ds As New DataSet
            ds.Tables.Clear()
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim da As New SqlDataAdapter("", SQLconnect)
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sqlcmd.CommandText = "Select FirmaNr, NameZ1 from firma "
            da.SelectCommand = sqlcmd
            da.Fill(ds, "Daten")
            sqlcmd.Dispose()
            SQLconnect.Close()
            Return ds.Tables(0)
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Insert_Ansprechpartner(Firmanr As Integer, ByVal name As String, ByVal vorname As String, ByVal telefon As String, ByVal email As String, ByVal anrede As String)
        Try
            Dim SQLconnect As New SqlConnection()
            Dim sql As String = ""
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "Insert into Ansprechpartner (firmanr, name, vorname, tel, email, anrede, erstellt_am, mutiert_am, aktiv)"
            sql = sql + " values(" + Firmanr.ToString + ", '" + name + "','" + vorname + "','" + telefon + "','" + email + "','" + anrede + "',getdate(),getdate(),1)"
            sqlcmd.CommandText = sql
            sqlcmd.ExecuteNonQuery()
            sqlcmd.Dispose()
            SQLconnect.Close()
            Return True
        Catch ex As Exception
            Return False
        End Try

    End Function

    Public Function Insert_FirmaBeruf(ByVal Firmanr As Integer, ByVal berufnr As Integer, ByVal ansprechpartnernr As Integer, ByVal zeitnr As Integer, ByVal anzahl As Integer, ByVal klassenr As Integer, userid As Integer)
        Try
            Dim ds As New DataSet
            ds = Get_Tabledata("Select KlasseNr from Beruf where berufnr=" + berufnr.ToString, False, True)
            'Dim klassenr As Integer = ds.Tables(0).Rows(0).Item(0)
            ds.Dispose()
            Dim newkey As Integer
            newkey = Get_NewDBKey("FirmaBeruf")
            Dim SQLconnect As New SqlConnection()
            Dim sql As String = ""
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "Insert into FirmaBeruf ( FirmaBerufnr, FirmaNr, Berufnr, AnsprechpartnerNr, ZeitNr, Bemerkung, Aktiv, Erstellt_am, Mutiert_am, Mutierer, Anzahl, klassenr)"
            sql = sql + " values(" + newkey.ToString + ", " + Firmanr.ToString + "," + berufnr.ToString + "," + ansprechpartnernr.ToString + "," + zeitnr.ToString + ",''," + "1" + ",'" + Now + "','" + Now + "'," + userid.ToString + "," + anzahl.ToString + "," + klassenr.ToString + ")"
            sqlcmd.CommandText = sql
            sqlcmd.ExecuteNonQuery()
            sqlcmd.Dispose()
            SQLconnect.Close()
            Return True
        Catch ex As Exception
            Return False
        End Try

    End Function

#End Region

#Region "Schueler"
    Public Function verify_klasse(ByVal klassenr As Integer) As String
        Dim sqlconnect As New SqlConnection
        Dim ds As New DataSet
        ds.Tables.Clear()
        sqlconnect.ConnectionString = Me.Connectionstring
        sqlconnect.Open()
        Dim da As New SqlDataAdapter("", sqlconnect)
        Dim sqlcmd As New SqlCommand
        sqlcmd.Connection = sqlconnect
        sqlcmd.CommandType = CommandType.StoredProcedure
        sqlcmd.CommandText = "verify_klasse"
        sqlcmd.Parameters.Add("@klassenr", SqlDbType.Int)
        sqlcmd.Parameters(0).Value = klassenr
        da.SelectCommand = sqlcmd
        da.Fill(ds, "Daten")
        Return ds.Tables(0).Rows(0).Item(0)
    End Function

    Public Function Insert_Schueler(ByVal klassenr As Integer, ByVal klassentyp As Integer) As Integer
        Try
            Dim SQLconnect As New SqlConnection()
            Dim sql As String = ""
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "Insert into schueler (name, vorname, klassenr, klassentyp, bemerkung, aktiv, erstellt_am, mutiert_am, mutierer) values('*** Neuer Schüler',''," + klassenr.ToString + "," + klassentyp.ToString + ",'',1,getdate(),getdate(),1)"
            sqlcmd.CommandText = sql
            sqlcmd.ExecuteNonQuery()
            sqlcmd.Dispose()
            SQLconnect.Close()

            Return Get_Last_Schueler()
        Catch ex As Exception
            Return -1
        End Try
    End Function
    Public Function Get_Last_Schueler() As Integer
        Try
            Dim SQLconnect As New SqlConnection()
            Dim ds As New DataSet
            Dim sql As String = ""

            ds.Tables.Clear()
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim da As New SqlDataAdapter("", SQLconnect)
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "SELECT top 1 SchuelerNr "
            sql = sql + "FROM Schueler "
            sql = sql + "ORDER BY Schuelernr desc"
            da.SelectCommand.CommandText = sql

            da.Fill(ds, "Daten")

            sqlcmd.Dispose()
            SQLconnect.Close()
            Return ds.Tables(0).Rows(0).Item(0)
        Catch ex As Exception
            Return Nothing
        End Try
    End Function


    Public Function Get_Schueler_By_Schueler() As DataTable
        Try
            Dim SQLconnect As New SqlConnection()
            Dim ds As New DataSet
            Dim sql As String = ""

            ds.Tables.Clear()
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim da As New SqlDataAdapter("", SQLconnect)
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "SELECT Schueler.SchuelerNr, Schueler.KlasseNr, Schueler.Name, Schueler.Vorname, "
            sql = sql + "Schueler.Bemerkung, Schueler.Aktiv, Schueler.Erstellt_am, Schueler.Mutiert_am, Schueler.Mutierer "
            sql = sql + "FROM Schueler "
            sql = sql + "ORDER BY Schueler.Name, Schueler.Vorname"
            da.SelectCommand.CommandText = sql

            da.Fill(ds, "Daten")
            sqlcmd.Dispose()
            SQLconnect.Close()
            Return ds.Tables(0)
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Get_Last_Schulerberuf_Priority_By_Schuler(ByVal Schulernrnr As Integer) As Integer
        Try
            Dim SQLconnect As New SqlConnection()
            Dim ds As New DataSet
            Dim sql As String = ""

            ds.Tables.Clear()
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim da As New SqlDataAdapter("", SQLconnect)
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "SELECT top 1 prioritaet from schuelerberuf where aktiv=1 and schuelernr=" + Schulernrnr.ToString + " order by prioritaet desc"
            da.SelectCommand.CommandText = sql
            da.Fill(ds, "Daten")
            sqlcmd.Dispose()
            SQLconnect.Close()
            Try
                Return ds.Tables(0).Rows(0).Item(0) + 1
            Catch ex As Exception
                Return 1
            End Try
            ds.Dispose()
        Catch ex As Exception
            Return 1
        End Try
    End Function

    Public Function Get_Schueler_By_Schuelernr(ByVal nr As Integer) As DataTable
        Try
            Dim SQLconnect As New SqlConnection()
            Dim ds As New DataSet
            Dim sql As String = ""

            ds.Tables.Clear()
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim da As New SqlDataAdapter("", SQLconnect)
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "SELECT SchuelerNr, KlasseNr, Name, Vorname, "
            sql = sql + "Bemerkung, Aktiv, Erstellt_am, Mutiert_am, Mutierer "
            sql = sql + "FROM Schueler "
            sql = sql + "where schuelernr=" & nr.ToString
            da.SelectCommand.CommandText = sql
            da.Fill(ds, "Daten")
            sqlcmd.Dispose()
            SQLconnect.Close()
            Return ds.Tables(0)
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Get_Schuelerberuf(ByVal Schuelernr As Integer) As DataTable
        Try
            Dim SQLconnect As New SqlConnection()
            Dim ds As New DataSet
            Dim sql As String = ""

            ds.Tables.Clear()
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim da As New SqlDataAdapter("", SQLconnect)
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "SELECT SchuelerBeruf.SchuelerBerufNr, SchuelerBeruf.SchuelerNr, SchuelerBeruf.BerufNr, SchuelerBeruf.Bemerkung, SchuelerBeruf.Aktiv, SchuelerBeruf.Erstellt_am, "
            sql = sql + "SchuelerBeruf.Mutiert_am, SchuelerBeruf.Mutierer, SchuelerBeruf.Prioritaet, Beruf.Bezeichnung AS Beruf FROM SchuelerBeruf INNER JOIN "
            sql = sql + "Beruf ON SchuelerBeruf.BerufNr = Beruf.Berufnr WHERE SchuelerBeruf.SchuelerNr = " + Schuelernr.ToString
            da.SelectCommand.CommandText = sql
            da.Fill(ds, "Daten")
            sqlcmd.Dispose()
            SQLconnect.Close()
            Return ds.Tables(0)
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Verify_Schueler(ByVal schuelernr As Integer) As String
        Dim sqlconnect As New SqlConnection
        Dim ds As New DataSet
        ds.Tables.Clear()
        sqlconnect.ConnectionString = Me.Connectionstring
        sqlconnect.Open()
        Dim da As New SqlDataAdapter("", sqlconnect)
        Dim sqlcmd As New SqlCommand
        sqlcmd.Connection = sqlconnect
        sqlcmd.CommandType = CommandType.StoredProcedure
        sqlcmd.CommandText = "verify_schueler"
        sqlcmd.Parameters.Add("@schuelernr", SqlDbType.Int)
        sqlcmd.Parameters(0).Value = schuelernr
        da.SelectCommand = sqlcmd
        da.Fill(ds, "Daten")
        Return ds.Tables(0).Rows(0).Item(0)
    End Function
#End Region

#Region "User"
    Public Function Insert_Web_User(ByVal Username As String, password As String, usertype As Integer, firmanr As Integer, lehrernr As Integer) As Integer
        Try
            Dim SQLconnect As New SqlConnection()
            Dim sql As String = ""
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "Insert into web_user (username, passwort, usertype, provisorisch, firmanr, lehrernr, erstellt_am, mutiert_am, aktiv, verifystring) values ("
            sql = sql + "'" + Username + "',"
            sql = sql + "'" + password + "',"
            sql = sql + usertype.ToString + ","
            sql = sql + "1,"
            sql = sql + firmanr.ToString + ","
            sql = sql + lehrernr.ToString + ","
            sql = sql + "'" + Now + "',"
            sql = sql + "'" + Now + "',"
            sql = sql + "1" + ",'')"
            sqlcmd.CommandText = sql
            sqlcmd.ExecuteNonQuery()
            sqlcmd.Dispose()
            SQLconnect.Close()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function Check_User(ByVal Userid As String, ByVal UserPassword As String) As Integer
        Dim SQLconnect As New SqlConnection()
        Dim ds As New DataSet
        ds.Tables.Clear()
        SQLconnect.ConnectionString = Me.Connectionstring
        SQLconnect.Open()
        Dim da As New SqlDataAdapter("", SQLconnect)
        Dim sqlcmd As New SqlCommand
        sqlcmd.Connection = SQLconnect
        sqlcmd.CommandType = CommandType.Text
        sqlcmd.CommandText = "Select eintragnr from web_user where provisorisch=0 and aktiv=1 and username='" + Userid + "' and passwort='" + UserPassword + "'"
        da.SelectCommand = sqlcmd
        da.Fill(ds, "Daten")
        Dim keyvalue As Integer
        If ds.Tables(0).Rows.Count = 0 Then
            keyvalue = -1
        Else
            keyvalue = ds.Tables(0).Rows(0).Item(0)
        End If
        sqlcmd.Dispose()
        SQLconnect.Close()
        ds.Dispose()
        Return keyvalue
    End Function

    Public Function User_Activation(ByVal key As String) As Boolean
        Dim SQLconnect As New SqlConnection()
        Dim ds As New DataSet
        ds.Tables.Clear()
        SQLconnect.ConnectionString = Me.Connectionstring
        SQLconnect.Open()
        Dim da As New SqlDataAdapter("", SQLconnect)
        Dim sqlcmd As New SqlCommand
        sqlcmd.Connection = SQLconnect
        sqlcmd.CommandType = CommandType.Text
        sqlcmd.CommandText = "Select * from web_user where verifystring='" + key + "'"

        da.SelectCommand = sqlcmd
        da.Fill(ds, "Daten")
        Dim keyvalue As Integer
        If ds.Tables(0).Rows.Count = 0 Then
            Return False
        End If
        If ds.Tables(0).Rows(0).Item("Provisorisch") = False Then
            Return False
        End If
        Me.UpdateTable("web_user", "provisorisch", "0", False, "Eintragnr", ds.Tables(0).Rows(0).Item(0))
        Return True
    End Function

    Public Function Get_UserUsertype(ByVal Eintragnr As Integer) As DataTable
        Dim SQLconnect As New SqlConnection()
        Dim ds As New DataSet
        ds.Tables.Clear()
        SQLconnect.ConnectionString = Me.Connectionstring
        SQLconnect.Open()
        Dim da As New SqlDataAdapter("", SQLconnect)
        Dim sqlcmd As New SqlCommand
        sqlcmd.Connection = SQLconnect
        sqlcmd.CommandType = CommandType.Text
        sqlcmd.CommandText = "Select Firmanr, lehrernr, usertype from web_user where eintragnr = " + Eintragnr.ToString
        da.SelectCommand = sqlcmd
        da.Fill(ds, "Daten")
        sqlcmd.Dispose()
        SQLconnect.Close()
        ds.Dispose()
        Return ds.Tables(0)
    End Function
#End Region

#Region "Generelle_Funktionen"
    Public Function UpdateTable(ByVal Tablename As String, ByVal Fieldname As String, ByVal fieldvalue As String, ByVal stringvalue As Boolean, ByVal KeyField As String, ByVal KeyValue As Integer) As Boolean
        Try
            Dim SQLconnect As New SqlConnection()
            Dim sql As String = ""
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "Update " + Tablename + " set " + Fieldname + "="
            If stringvalue = True Then
                sql = sql + "'" + fieldvalue + "'"
            Else
                sql = sql + fieldvalue
            End If
            sql = sql + " where " + KeyField + " = " + KeyValue.ToString
            sqlcmd.CommandText = sql
            sqlcmd.ExecuteNonQuery()
            sqlcmd.Dispose()
            SQLconnect.Close()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function



#End Region

    Public Function ConvertDate(ByVal indate As DateTime) As String
        Return Microsoft.VisualBasic.Format(indate, "yyyy-MM-dd HH:MM:SS")
    End Function

#Region "Diverse_Stammdaten"
    Public Function Insert_new_record(ByVal Tablename As String, ByVal Keyfield As String) As Integer
        Dim newkey As Integer = Me.Get_LastKey(Tablename, Keyfield)
        Try
            Dim SQLconnect As New SqlConnection()
            Dim sql As String = ""
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "Insert " + Tablename + "(" + Keyfield + ",erstellt_am,mutiert_am, mutierer, aktiv) values(" + newkey.ToString + ",'" + Now + "','" + Now + "',1,1)"
            sqlcmd.CommandText = sql
            sqlcmd.ExecuteNonQuery()
            sqlcmd.Dispose()
            SQLconnect.Close()
            Return newkey
        Catch ex As Exception
            Return False
        End Try


    End Function


#End Region

#Region "Zuteilung"

    Public Function Get_Reservierte_Plaetze(ByVal Berufnr As Integer) As DataTable
        Dim sql As String
        sql = "SELECT Schueler.SchuelerNr,Schueler.Name +' '+ Schueler.Vorname AS Schueler_Name,   Beruf.Bezeichnung,  SchuelerBeruf.Prioritaet, Zeiten.Bezeichnung as Zeit "
        sql = sql + " FROM Zuteilung INNER JOIN SchuelerBeruf ON (Zuteilung.SchuelerBerufNr = SchuelerBeruf.SchuelerBerufNr) INNER JOIN Schueler ON (SchuelerBeruf.SchuelerNr = Schueler.SchuelerNr) "
        sql = sql + "  INNER JOIN Zeiten ON (Zuteilung.Zeitnr = Zeiten.ZeitNr) INNER JOIN Beruf ON (SchuelerBeruf.BerufNr = Beruf.Berufnr) "
        sql = sql + "WHERE zuteilung.aktiv=1 and schuelerberuf.aktiv=1 and schuelerberuf.berufnr = " + Berufnr.ToString
        Dim ds As New DataSet
        Dim dtAngebot As New DataTable
        Dim SQLconnect As New SqlConnection()
        SQLconnect.ConnectionString = Me.Connectionstring
        SQLconnect.Open()
        Dim sqlcmd As New SqlCommand
        Dim da As New SqlDataAdapter("", SQLconnect)
        sqlcmd.Connection = SQLconnect
        Try
            ds.Tables.Clear()
            sqlcmd.CommandType = CommandType.Text
            sqlcmd.CommandText = sql
            da.SelectCommand = sqlcmd
            da.Fill(ds, "Reserviert ")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return ds.Tables(0)
    End Function
    Public Function Get_Freie_Plätze(ByVal Berufnr As Integer) As DataTable
        'Angebot auslesen
        Dim ds As New DataSet
        Dim dtAngebot As New DataTable
        Dim sql As String = "Select * from FirmaBeruf where aktiv=1 and Berufnr=" + Berufnr.ToString
        Dim SQLconnect As New SqlConnection()
        SQLconnect.ConnectionString = Me.Connectionstring
        SQLconnect.Open()
        Dim sqlcmd As New SqlCommand
        Dim da As New SqlDataAdapter("", SQLconnect)
        sqlcmd.Connection = SQLconnect
        Try
            ds.Tables.Clear()
            sqlcmd.CommandType = CommandType.Text
            sqlcmd.CommandText = sql
            da.SelectCommand = sqlcmd
            da.Fill(ds, "Angebot")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        dtAngebot = ds.Tables(0)

        Dim dtZugeteilt As New DataTable
        sql = "SELECT  Zuteilung.*, FirmaBeruf.FirmaBerufnr FROM zuteilung INNER JOIN firmaberuf ON (zuteilung.firmaberufnr = firmaberuf.FirmaBerufNr)  WHERE zuteilung.aktiv = 1 AND  firmaberuf.berufnr=" + Berufnr.ToString
        Try
            ds.Tables.Clear()
            sqlcmd.CommandType = CommandType.Text
            sqlcmd.CommandText = sql
            da.SelectCommand = sqlcmd
            da.Fill(ds, "Zugeteilt")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        dtZugeteilt = ds.Tables(0)

        For Each r As DataRow In dtZugeteilt.Rows
            For Each rr As DataRow In dtAngebot.Rows
                If r.Item("FirmaBerufNr") = rr.Item("Firmaberufnr") Then
                    If r.Item("ZeitNr") = rr.Item("Zeitnr") Then
                        If r.Item("Ansprechpartnernr") = rr.Item("Ansprechpartnernr") Then rr.Item("Anzahl") = rr.Item("Anzahl") - 1
                    End If
                End If
            Next
        Next

        For Each r As DataRow In dtAngebot.Rows
            If r.Item("Anzahl") = 0 Then r.Delete()
        Next
        dtAngebot.AcceptChanges()
        Dim dtFreiePlätze As New DataTable
        Dim first As Boolean = True
        For Each r As DataRow In dtAngebot.Rows
            sql = "SELECT Firma.FirmaNr,Firma.NameZ1,FirmaBeruf.ZeitNr, firmaberuf.firmaberufnr,Zeiten.Bezeichnung, 0 as Anzahl, Ansprechpartner.AnsprechpartnerNr,  Ansprechpartner.Name +' ' + Ansprechpartner.Vorname As Ansprechpartner FROM Firma INNER JOIN FirmaBeruf ON (Firma.FirmaNr = FirmaBeruf.FirmaNr)"
            sql = sql + "   INNER JOIN Zeiten ON (FirmaBeruf.ZeitNr = Zeiten.ZeitNr) INNER JOIN Ansprechpartner ON (Firma.FirmaNr = Ansprechpartner.FirmaNr) where firmaberuf.firmaberufnr=" + r.Item("FirmaBerufnr").ToString + " and ansprechpartner.ansprechpartnernr=" + r.Item("Ansprechpartnernr").ToString
            Try
                ds.Tables.Clear()
                sqlcmd.CommandType = CommandType.Text
                sqlcmd.CommandText = sql
                da.SelectCommand = sqlcmd
                da.Fill(ds, "Angebot")
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            If first Then
                dtFreiePlätze = ds.Tables(0).Copy
                first = False
            Else

                For Each r1 As DataRow In ds.Tables(0).Rows
                    Dim datar As DataRow
                    datar = dtFreiePlätze.NewRow
                    For Each s As DataColumn In dtFreiePlätze.Columns
                        datar.Item(s.ColumnName) = r1.Item(s.ColumnName)
                    Next
                    dtFreiePlätze.Rows.Add(datar)
                Next
            End If

            For Each r2 As DataRow In dtFreiePlätze.Rows
                If r2.Item("FirmaBerufnr") = r.Item("FirmaBerufnr") Then
                    r2.Item("Anzahl") = r.Item("Anzahl")
                End If
            Next

        Next
        SQLconnect.Close()
        Return dtFreiePlätze
    End Function
    Public Function Insert_Zuteilung(ByVal Schuelerberufnr As Integer, ByVal Firmaberufnr As Integer, ByVal usernr As Integer) As Boolean
        Try
            Dim SQLconnect As New SqlConnection()
            Dim sql As String = ""
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "insert zuteilung (firmaberufnr, schuelerberufnr, mutierer) values(" + Firmaberufnr.ToString + "," + Schuelerberufnr.ToString + "," + usernr.ToString + ")"
            sqlcmd.CommandText = sql
            sqlcmd.ExecuteNonQuery()
            sqlcmd.Dispose()
            SQLconnect.Close()
            Return True
        Catch ex As Exception
            Return False
        End Try

    End Function


    Public Function Zuteilen(ByVal typ As Integer) As String
        Dim sqlconnect As New SqlConnection
        Dim ds As New DataSet
        ds.Tables.Clear()
        sqlconnect.ConnectionString = Me.Connectionstring
        sqlconnect.Open()
        Dim da As New SqlDataAdapter("", sqlconnect)
        Dim sqlcmd As New SqlCommand
        sqlcmd.Connection = sqlconnect
        sqlcmd.CommandType = CommandType.StoredProcedure
        sqlcmd.CommandText = "dbo.zuteilung_durchfuehren"
        sqlcmd.Parameters.Add("@typ", SqlDbType.Int)
        sqlcmd.Parameters(0).Value = typ
        sqlcmd.ExecuteNonQuery()
        Return ""
    End Function

#End Region

#Region "Optionen"
    Public Function Get_Option(ByVal onr As Integer) As String
        Try
            Dim sqlconnect As New SqlConnection
            Dim ds As New DataSet
            ds.Tables.Clear()
            sqlconnect.ConnectionString = Me.Connectionstring
            sqlconnect.Open()
            Dim da As New SqlDataAdapter("", sqlconnect)
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = sqlconnect
            sqlcmd.CommandType = CommandType.Text
            sqlcmd.CommandText = "Select * from optionen where optionnr=" + onr.ToString
            da.SelectCommand = sqlcmd
            da.Fill(ds, "Daten")
            Return ds.Tables(0).Rows(0).Item("Inhalt")
        Catch ex As Exception
            Return ""
            'MsgBox(ex.Message)
        End Try
    End Function
#End Region

#Region "RTF"
    Public Function Insert_Dokument(ByVal bezeichnung As String) As Integer
        Try
            Dim SQLconnect As New SqlConnection()
            Dim sql As String = ""
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "Insert into web_dokumenttexte (Bezeichnung, beschreibung, aktiv,erstellt_am,mutiert_am, mutierer, rtf) values('" + bezeichnung + "','',1,getdate(),getdate(),1,'')"
            sqlcmd.CommandText = sql
            sqlcmd.ExecuteNonQuery()
            sqlcmd.Dispose()
            SQLconnect.Close()
            Return Get_Last_Dokument()
        Catch ex As Exception
            Return -1
        End Try
    End Function

    Public Function Get_Last_Dokument() As Integer
        Try
            Dim SQLconnect As New SqlConnection()
            Dim ds As New DataSet
            Dim sql As String = ""

            ds.Tables.Clear()
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim da As New SqlDataAdapter("", SQLconnect)
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "SELECT top 1 eintragnr "
            sql = sql + "FROM web_dokumenttexte "
            sql = sql + "ORDER BY eintragnr desc"
            da.SelectCommand.CommandText = sql

            da.Fill(ds, "Daten")

            sqlcmd.Dispose()
            SQLconnect.Close()
            Return ds.Tables(0).Rows(0).Item(0)
        Catch ex As Exception
            Return Nothing
        End Try
    End Function


    Public Function Save_rtf(ByVal NrEintrag As Integer, ByVal Filename As String, irtf As String) As String
        Dim rtf As String
        If irtf = "" Then rtf = GetFileContents(Filename) Else rtf = irtf
        Dim Connection As New SqlConnection()
        Dim DA As New SqlDataAdapter("select * from Web_Dokumenttexte where EintragNr = " & Str(NrEintrag), Connection)
        Dim cb As SqlCommandBuilder = New SqlCommandBuilder(DA)
        Dim ds As New DataSet()
        Dim fs As New FileStream(Filename, FileMode.OpenOrCreate, FileAccess.Read)
        Dim mydata(fs.Length) As Byte
        fs.Read(mydata, 0, fs.Length)
        fs.Close()
        Try
            Connection.ConnectionString = Me.Connectionstring
            Connection.Open()
            DA.Fill(ds, "RichFile")
            Dim myRow As DataRow
            If ds.Tables(0).Rows.Count = 0 Then
                MsgBox("Datei kann nicht gespeichert werden.", MsgBoxStyle.Critical)
                Exit Function
            Else
                myRow = ds.Tables(0).Rows(0)
                myRow.Item(3) = mydata
                myRow.Item(8) = GetFileContents(Filename)
                myRow.Item(6) = Now
                'myRow.Item(3) = mydata
                'myRow.Item(5) = Now
                'myRow.Item(6) = 1
                DA.Update(ds, "RichFile")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
            Return False
        End Try
        fs = Nothing
        cb = Nothing
        ds = Nothing
        DA = Nothing
        Connection.Close()
        Connection = Nothing
        Return ""
    End Function

    Public Function Get_rtf(ByVal NrEintrag As Integer, ByVal filename As String) As String
        Dim Connection As New SqlConnection()
        Dim DA As New SqlDataAdapter("select * from Web_Dokumenttexte where EintragNr = " & Str(NrEintrag), Connection)
        Dim cb As SqlCommandBuilder = New SqlCommandBuilder(DA)
        Dim ds As New DataSet()

        Try
            Connection.ConnectionString = Me.Connectionstring
            Connection.Open()
            DA.Fill(ds, "RichFile")
            Dim myRow As DataRow
            If ds.Tables(0).Rows.Count = 0 Then
                Return ("Datei kann nicht gelesen werden.")
                Exit Function
            Else
                myRow = ds.Tables(0).Rows(0)
                Return myRow.Item(8)
            End If
        Catch ex As Exception
            Return MsgBox(ex.Message)
        End Try
        cb = Nothing
        ds = Nothing
        DA = Nothing
        Connection.Close()
        Connection = Nothing
    End Function

    Public Function GetFileContents(ByVal FullPath As String, _
    Optional ByRef ErrInfo As String = "") As String

        Dim strContents As String
        Dim objReader As StreamReader
        Try

            objReader = New StreamReader(FullPath)
            strContents = objReader.ReadToEnd()
            objReader.Close()
            Return strContents
        Catch Ex As Exception
            ErrInfo = Ex.Message
        End Try
    End Function
#End Region

#Region "Reporting*"
    Public Function Get_SQL_Statement(ByVal Reportnr As Integer) As String
        Try
            Dim sqlconnect As New SqlConnection
            Dim ds As New DataSet
            ds.Tables.Clear()
            sqlconnect.ConnectionString = Me.Connectionstring
            sqlconnect.Open()
            Dim da As New SqlDataAdapter("", sqlconnect)
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = sqlconnect
            sqlcmd.CommandType = CommandType.Text
            sqlcmd.CommandText = "Select * from Report where Reportnr=" + Reportnr.ToString
            da.SelectCommand = sqlcmd
            da.Fill(ds, "Daten")
            Return ds.Tables(0).Rows(0).Item("SQL_Statement")
        Catch ex As Exception
            Return ""
            'MsgBox(ex.Message)
        End Try
    End Function

    Public Function Get_SQLAbfrage_Statement(ByVal SQLNr As Integer) As String
        Try
            Dim sqlconnect As New SqlConnection
            Dim ds As New DataSet
            ds.Tables.Clear()
            sqlconnect.ConnectionString = Me.Connectionstring
            sqlconnect.Open()
            Dim da As New SqlDataAdapter("", sqlconnect)
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = sqlconnect
            sqlcmd.CommandType = CommandType.Text
            sqlcmd.CommandText = "Select * from SQLAbfragen where sqlnr=" + SQLNr.ToString
            da.SelectCommand = sqlcmd
            da.Fill(ds, "Daten")
            Return ds.Tables(0).Rows(0).Item("SQLScript")
        Catch ex As Exception
            Return ""
            'MsgBox(ex.Message)
        End Try

    End Function

    Public Function Get_RptDatei(ByVal Auswertungnr As String, ByVal pfad As String, Optional ByVal fname As String = "") As String
        Dim connection As New SqlConnection()
        Dim DA As New SqlDataAdapter("select * from Report where Reportnr = " & Str(Auswertungnr), connection)
        Dim CB As SqlCommandBuilder = New SqlCommandBuilder(DA)
        Dim ds As New DataSet()
        Dim Filename As String = ""
        Try
            connection.ConnectionString = Me.Connectionstring
            connection.Open()
            DA.Fill(ds, "RptFile")
            Dim myRow As DataRow
            myRow = ds.Tables(0).Rows(0)
            Dim MyData() As Byte
            Filename = pfad + "\" + myRow.Item(1).ToString + ".frx"
            Return Filename
            Exit Try
            If fname <> "" Then
                Filename = fname
            End If
            MyData = myRow.Item(2)
            Dim K As Long
            K = UBound(MyData)
            'Dim fs As New FileStream(Filename, FileMode.OpenOrCreate, FileAccess.Write)
            'fs.Write(MyData, 0, K)
            'fs.Close()
            'fs = Nothing
        Catch ex As Exception
            Return ""
        End Try
        CB = Nothing
        ds = Nothing
        DA = Nothing
        connection.Close()
        connection = Nothing
        Return Filename
    End Function
#End Region

#Region "Lehrer"
    Public Function Insert_Lehrer(ByRef keyvalue As Integer) As Boolean
        Try
            Dim SQLconnect As New SqlConnection()
            Dim sql As String = ""
            SQLconnect.ConnectionString = Me.Connectionstring
            SQLconnect.Open()
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = SQLconnect
            sqlcmd.CommandType = CommandType.Text
            sql = "Insert into web_lehrer (aktiv, erstellt_am, mutiert_am, mutierer) values(1,getdate(),getdate(),1)"
            sqlcmd.CommandText = sql
            sqlcmd.ExecuteNonQuery()
            sqlcmd.Dispose()
            SQLconnect.Close()
            Dim key As Integer = Me.Get_LastKey("web_lehrer", "Lehrernr")
            key = key - 1
            keyvalue = key
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
#End Region

#Region "WebStatistik"
    Public Function Statistikdaten_Speichern(ByVal Jahr As String, ByVal Bezeichnung As String, ByVal userid As Integer) As String
        Dim sqlconnect As New SqlConnection
        Dim ds As New DataSet
        ds.Tables.Clear()
        sqlconnect.ConnectionString = Me.Connectionstring
        sqlconnect.Open()
        Dim da As New SqlDataAdapter("", sqlconnect)
        Dim sqlcmd As New SqlCommand
        sqlcmd.Connection = sqlconnect
        sqlcmd.CommandType = CommandType.StoredProcedure
        sqlcmd.CommandText = "dbo.SP_Web_Statistik"
        sqlcmd.Parameters.Add("@jahr", SqlDbType.Int)
        sqlcmd.Parameters.Add("@Beschreibung", SqlDbType.VarChar)
        sqlcmd.Parameters.Add("@Mutierer", SqlDbType.Int)
        sqlcmd.Parameters(0).Value = Jahr
        sqlcmd.Parameters(1).Value = Bezeichnung
        sqlcmd.Parameters(2).Value = userid

        sqlcmd.ExecuteNonQuery()
        Return ""
    End Function


    Public Function Get_Webstatistik_Data() As DataTable
        Dim ds As New DataSet
        Dim dt As New DataTable
        Dim sql As String = "Select Webstatistiknr,beschreibung from webstatistik where aktiv=1 order by erstellt_am desc"
        Dim SQLconnect As New SqlConnection()
        SQLconnect.ConnectionString = Me.Connectionstring
        SQLconnect.Open()
        Dim sqlcmd As New SqlCommand
        Dim da As New SqlDataAdapter("", SQLconnect)
        sqlcmd.Connection = SQLconnect
        Try
            ds.Tables.Clear()
            sqlcmd.CommandType = CommandType.Text
            sqlcmd.CommandText = sql
            da.SelectCommand = sqlcmd
            da.Fill(ds, "Daten")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return ds.Tables(0)
    End Function

    Public Function Delete_Webstatistik(WebStatNr As Integer) As DataTable

        Dim sqlconnect As New SqlConnection
        Dim ds As New DataSet
        ds.Tables.Clear()
        sqlconnect.ConnectionString = Me.Connectionstring
        sqlconnect.Open()
        Dim da As New SqlDataAdapter("", sqlconnect)
        Dim sqlcmd As New SqlCommand
        sqlcmd.Connection = sqlconnect
        sqlcmd.CommandType = CommandType.Text
        sqlcmd.CommandText = "update webstatistik set aktiv=0 where webstatistiknr=" + WebStatNr.ToString
        sqlcmd.ExecuteNonQuery()
    End Function
#End Region

#Region "Delete_Data"
    Public Sub Delete_Firmen()
        Exec_Query("Truncate table dbo.firma")
        Exec_Query("Delete from web_user where usertype=2")
        Delete_Ansprechpartner()
        Delete_Angebot()
    End Sub

    Public Sub Delete_Ansprechpartner()
        Exec_Query("Truncate table dbo.ansprechpartner")
        delete_angebot()
    End Sub

    Public Sub Delete_Angebot()
        Exec_Query("Truncate Table dbo.firmaberuf")
    End Sub

    Public Sub Delete_Lehrer()
        Exec_Query("Truncate Table dbo.web_lehrer")
        Exec_Query("Delete from web_user where usertype=3")
    End Sub

    Public Sub Delete_Klasse()
        Exec_Query("Truncate Table dbo.klasse")
    End Sub

    Public Sub Delete_Schueler()
        Exec_Query("Truncate Table dbo.Schueler")
        Exec_Query("Truncate Table dbo.schuelerberuf")
    End Sub

    Public Sub Delete_Zuteilung()
        Exec_Query("Truncate Table dbo.Zuteilung")
    End Sub

    Public Sub Exec_Query(ByVal sql As String)
        Dim sqlconnect As New SqlConnection
        Dim ds As New DataSet
        ds.Tables.Clear()
        sqlconnect.ConnectionString = Me.Connectionstring
        sqlconnect.Open()
        Dim da As New SqlDataAdapter("", sqlconnect)
        Dim sqlcmd As New SqlCommand
        sqlcmd.Connection = sqlconnect
        sqlcmd.CommandType = CommandType.Text
        sqlcmd.CommandText = sql
        sqlcmd.ExecuteNonQuery()
    End Sub
#End Region

End Class