Connect to Sybase ASE Database with Visual Basic 2015

Hello all, my gears are grinding for some weeks now. My company uses Sybase ASE, but I can't figure out how to connect to the database with Visual Studio 2015 (Visual Basic).

Where I need help with: - Connect to Database (we usually do that with Aqua Data Studio) - execute a stored procedure with parameters in textboxes - display the output in a textbox.

I would really appreciate any help. (Sorry for my bad english) Best regards from Germany, Brian

0
edit delete
are you sure?
asked Brian Loose

2 answers

I have the same problem. Anybody has any solutions? This question was created more than 4 months ago.

Kenneth, Essay help company

0
edit delete
are you sure?
answered Kenneth Beck

Hello Kenneth, I figured out now.

You need the following resources: (.dll) - Sybase.AdoNet4.AseClient - Sybase.AdoNet4.AseDestination - Sybase.AdoNet4.AseReportingServices - Sybase.AdoNet4.EnterpriseLibrary

I'll just post the whole code below.

Imports Sybase

Imports Sybase.AdoNet4 Imports Sybase.AdoNet4.AseDestination Imports Sybase.Data Imports Sybase.Data.AseClient Imports Sybase.Data.AseReportingServices Imports SQLExecuter.My.Resources

Public Class Executer

Private Sub Executer_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
    Dim login As New AseConnection("Data Source=*CENSORED*;Port=5000;UID=" + _tbUSER.Text + ";PWD=" + _tbPASSWORD.Text + ";Database=worker;")

    Try
        Saver(_tbLOG.Text)
        Form1.Close()
        'login.Close()

    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

End Sub

Sub Saver(Wert As String)
    Try
        Dim logname As String = _tbUSER.Text + "_" + Date.Today + ".log"
        Dim sr3 As IO.StreamWriter = New IO.StreamWriter(logname, True)
        If Wert = Nothing Then
            sr3.Close()
            Exit Sub
        End If
        sr3.WriteLine(Wert)
        sr3.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub



Private Sub Executer_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    _tbUSER.Text = Form1._tbUID.Text
    _tbMACHINE.Text = Environment.MachineName
    _tbPASSWORD.Text = Form1._tbPWD.Text
    _tbDOMAIN.Text = Environment.UserDomainName
    _tbDATETODAY.Text = Date.Today.ToString("yyyy-MM-dd")
    Form1.Hide()
    Try
        Dim wclav As New Net.WebClient
        Dim mrv As String = wclav.DownloadString("*CENSORED*/SQLExecuter_Version.mrv")
        _tbAVAILABLEVERSION.Text = mrv
    Catch ex As Exception
        MsgBox("Couldn't check the most recent version!")
    End Try

    _tbLOG.Text = "[" + Date.Today.ToString("yyyy-MM-dd") + "][" + TimeOfDay + "]: Logged in as " + _tbUSER.Text + "@" + _tbMACHINE.Text + vbNewLine + vbNewLine



    If _tbCURRENTVERSION.Text = _tbAVAILABLEVERSION.Text Then
        PictureBox1.Image = My.Resources.utd


    Else
        PictureBox1.Image = My.Resources.otd

        Dim result As Integer = MessageBox.Show("Sorry to interrupt, but this is important." + vbNewLine + vbNewLine + vbNewLine + "Your PC is running an outdated version of SQLExecuter." + vbNewLine + vbNewLine + "There may be changed SP's that can harm or crash the database." + vbNewLine + "It's recommended to upgrade to the newest version." + vbNewLine + "Update now?", "Update", MessageBoxButtons.YesNo)


        If result = DialogResult.No Then
            Me.Show()

        ElseIf result = DialogResult.Yes Then

            Try
                '#########################################WICHTIG#######DROPDOWNLOAD


            Catch ex As Exception
                MsgBox("An error occurred while trying to download the new version." + vbNewLine + "Please check your internet connection and try again.", MsgBoxStyle.Critical)

            End Try
        End If
    End If

    Dim login As New AseConnection("Data Source=*CENSORED*;Port=5000;UID=" + _tbUSER.Text + ";PWD=" + _tbPASSWORD.Text + ";Database=worker;")

    Try
        login.Open()
        _tbLOG.Text = _tbLOG.Text + "[" + Date.Today.ToString("yyyy-MM-dd") + "][" + TimeOfDay + "]: Connected to database 'worker' on '*CENSORED*:5000'" + vbNewLine
    Catch ex As Exception
        _tbLOG.Text = _tbLOG.Text + "[" + Date.Today.ToString("yyyy-MM-dd") + "][" + TimeOfDay + "]: Connection to database failed! ERROR: " + ex.ToString + vbNewLine
    End Try
    login.Close()
End Sub

Private Sub PictureBox1_Click(sender As Object, e As EventArgs) Handles PictureBox1.Click
    If _tbCURRENTVERSION.Text = _tbAVAILABLEVERSION.Text Then
        MsgBox("You are up-to-date!")
    Else
        Dim result As Integer = MessageBox.Show("Sorry to interrupt, but this is important." + vbNewLine + vbNewLine + vbNewLine + "Your PC is running an outdated version of SQLHelper." + vbNewLine + vbNewLine + "There may be changed SP's that can harm or crash the database." + vbNewLine + "It's recommended to upgrade to the newest version." + vbNewLine + "Update now?", "Update", MessageBoxButtons.YesNo)


        If result = DialogResult.No Then
            Me.Show()

        ElseIf result = DialogResult.Yes Then

            Try
                '#########################################WICHTIG#######DROPDOWNLOAD

            Catch ex As Exception
                MsgBox("An error occurred while trying to download the new version." + vbNewLine + "Please check your internet connection and try again.", MsgBoxStyle.Critical)

            End Try
        End If
    End If
End Sub



Private Sub _tbCUSTOMERID_Click(sender As Object, e As EventArgs) Handles _tbCUSTOMERID.Click
    _tbCUSTOMERID.Text = My.Computer.Clipboard.GetText


End Sub

Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
    _tbSHOWCLIPPED.Text = My.Computer.Clipboard.GetText


End Sub



'##############################################################################################################################
'######################################## SHOW CONTRACT INFO ##################################################################
'##############################################################################################################################

Private Function readString(reader As AseDataReader, int As Integer) As String
    Dim data As String = "(null)"

    If Not reader.IsDBNull(int) Then
        data = reader.GetString(int)
    End If

    Return data
End Function

Private Sub readResultSet1(reader As AseDataReader, resultSetCount As Integer)
    While reader.Read()
        If (resultSetCount = 0) Then
            Dim in0 As String = "Vertrag-ID: " + readString(reader, 0) + vbNewLine
            Dim in1 As String = "Vertrag-BZ: " + readString(reader, 1) + vbNewLine
            Dim in2 As String = "Tech-Status: " + readString(reader, 2) + vbNewLine
            Dim in3 As String = "Kunde-ID: " + readString(reader, 3) + vbNewLine
            Dim in4 As String = "Produkt-ID: " + readString(reader, 4) + vbNewLine
            Dim in5 As String = "Produkt: " + readString(reader, 5) + vbNewLine


            _tbSQLINFO.Text = "##### TECHNISCH #####" + vbNewLine + in0 + in1 + in2 + in3 + in4 + in5
        End If

        If (resultSetCount = 1) Then

            Dim si0 As String = "Vertrag-ID: " + readString(reader, 0) + vbNewLine
            Dim si1 As String = "Vertragsstatus: " + readString(reader, 1) + vbNewLine
            Dim si2 As String = "SPAM-Status: " + readString(reader, 2) + vbNewLine
            Dim si3 As String = "Kunde-ID: " + readString(reader, 3) + vbNewLine
            Dim si4 As String = "Produkt-ID: " + readString(reader, 4) + vbNewLine
            Dim si5 As String = "Produkt: " + readString(reader, 5) + vbNewLine
            Dim si7 As String = "Kündigungsdatum: " + readString(reader, 7) + vbNewLine

            _tbSQLINFO.Text = _tbSQLINFO.Text + vbNewLine + "##### KAUFMÄNNISCH ##### " + vbNewLine + si0 + si1 + si2 + si3 + si4 + si5 + si7 + vbNewLine + "##### POSITIONEN #####" + vbNewLine
        End If

        If (resultSetCount = 2) Then
            Dim pi0 As String = "VP: " + readString(reader, 0) + "   "
            Dim pi1 As String = "Status: " + readString(reader, 1) + "   "
            Dim pi5 As String = "Bezeichnung: " + readString(reader, 5) + vbNewLine

            _tbSQLINFO.Text = _tbSQLINFO.Text + pi0 + pi1 + pi5
        End If



        If (resultSetCount = 4) Then
            Dim ka0 As String = readString(reader, 0) + "     "
            Dim ka1 As String = "Wirkdatum: " + readString(reader, 1) + "   "
            Dim ka2 As String = readString(reader, 2) + "     "
            Dim ka3 As String = "Beschreibung: " + readString(reader, 3) + "   "
            Dim ka5 As String = "Status: " + readString(reader, 5) + "   "
            Dim ka7 As String = "Position: " + readString(reader, 7) + vbNewLine

            _tbSQLINFO.Text = _tbSQLINFO.Text + ka0 + ka1 + ka2 + ka3 + ka5 + ka7
        End If

        If (resultSetCount = 5) Then
            Dim sr0 As String = readString(reader, 0) + "     "
            Dim sr1 As String = "Wirkdatum: " + readString(reader, 1) + "   "
            Dim sr2 As String = readString(reader, 2) + "     "
            Dim sr3 As String = "Beschreibung: " + readString(reader, 3) + "   "
            Dim sr5 As String = "Status: " + readString(reader, 5) + "   "
            Dim sr7 As String = "Position: " + readString(reader, 7) + vbNewLine

            _tbSQLINFO.Text = _tbSQLINFO.Text + sr0 + sr1 + sr2 + sr3 + sr5 + sr7
        End If
    End While
End Sub

Private Function getCUSINFO()

    Dim login As New AseConnection("Data Source=*CENSORED*;Port=5000;UID=" + _tbUSER.Text + ";PWD=" + _tbPASSWORD.Text + ";Database=worker;")


    login.Open()

    Dim cmd As New AseCommand("utl_contract_data2", login)
    cmd.CommandType = CommandType.StoredProcedure

    Dim param As AseParameter = cmd.CreateParameter()
    param.ParameterName = "@vertrag_id"
    param.AseDbType = AseDbType.Integer
    param.Direction = ParameterDirection.Input
    param.Value = _tbCONTRACTID.Text
    cmd.Parameters.Add(param)

    Dim reader As AseDataReader = cmd.ExecuteReader()

    Dim resultSetCount = 0

    readResultSet1(reader, resultSetCount)

    While reader.NextResult
        resultSetCount = resultSetCount + 1
        readResultSet1(reader, resultSetCount)


    End While


    reader.Close()
    login.Close()


    Return 0
End Function


Private Sub _tbCONTRACTID_Click(sender As Object, e As EventArgs) Handles _tbCONTRACTID.Click
    _tbCONTRACTID.Text = My.Computer.Clipboard.GetText
    _tbSQLINFO.Text = ""
    Try
        getCUSINFO()
        _tbLOG.Text = _tbLOG.Text + "[" + Date.Today.ToString("yyyy-MM-dd") + "][" + TimeOfDay + "]: Showing information for contract #" + _tbCONTRACTID.Text + vbNewLine
    Catch ex As Exception
        _tbLOG.Text = _tbLOG.Text + "!!! ERROR !!! " + ex.ToString + vbNewLine
    End Try

End Sub



'#################################################################################################################################
'####################################### END SHOWING CONTRACT INFO ###############################################################
'#################################################################################################################################



'#################################################################################################################################
'####################################### START TERMINATE CONTRACT NOW ############################################################
'#################################################################################################################################

Private Function readStringtermconnow(reader As AseDataReader, int As Integer) As String
    Dim data As String = "(null)"

    If Not reader.IsDBNull(int) Then
        data = reader.GetString(int)
    End If

    Return data
End Function


Private Function termCONnow()
    Dim login As New AseConnection("Data Source=*CENSORED*;Port=5000;UID=" + _tbUSER.Text + ";PWD=" + _tbPASSWORD.Text + ";Database=vertrag;")

    login.Open()

    Dim cmd As New AseCommand("utl_TerminateContractNow", login)
    cmd.CommandType = CommandType.StoredProcedure

    Dim param1 As AseParameter = cmd.CreateParameter()
    param1.ParameterName = "@contract_id"
    param1.AseDbType = AseDbType.Integer
    param1.Direction = ParameterDirection.Input
    param1.Value = _tbCONTRACTID.Text

    Dim param2 As AseParameter = cmd.CreateParameter()
    param2.ParameterName = "@by_reason"
    param2.AseDbType = AseDbType.Integer
    param2.Direction = ParameterDirection.Input
    param2.Value = "26"

    Dim param3 As AseParameter = cmd.CreateParameter()
    param3.ParameterName = "@debug"
    param3.AseDbType = AseDbType.Integer
    param3.Direction = ParameterDirection.Input
    param3.Value = "1"

    cmd.Parameters.Add(param1)
    cmd.Parameters.Add(param2)
    cmd.Parameters.Add(param3)



    Dim reader As AseDataReader = cmd.ExecuteReader()

    Dim resultSetCount = 0

    readResultSet2(reader, resultSetCount)

    While reader.NextResult
        resultSetCount = resultSetCount + 1
        readResultSet2(reader, resultSetCount)
    End While


    reader.Close()
    login.Close()

    Return 0
End Function

Private Sub readResultSet2(reader As AseDataReader, resultSetCount As Integer)
    While reader.Read()
        If (resultSetCount = 0) Then
            Dim tcn0 As String = readStringtermconnow(reader, 0)

            _tbSQLINFO.Text = tcn0
        End If
    End While
End Sub

Private Sub _btnTERMCONNOW_Click(sender As Object, e As EventArgs) Handles _btnTERMCONNOW.Click
    Try
        termCONnow()
        _tbLOG.Text = _tbLOG.Text + "[" + Date.Today.ToString("yyyy-MM-dd") + "][" + TimeOfDay + "]: ##### EXECUTE ##### Terminating contract #" + _tbCONTRACTID.Text + " by now!" + vbNewLine
    Catch ex As Exception
        _tbLOG.Text = _tbLOG.Text + "!!! ERROR !!! " + ex.ToString + vbNewLine
    End Try
End Sub


'#################################################################################################################################
'####################################### END TERMINATE CONTRACT NOW ##############################################################
'#################################################################################################################################


'#################################################################################################################################
'####################################### START TERMINATE CONTRACT PER DATE #######################################################
'#################################################################################################################################

Private Function readStringtermconDATE(reader As AseDataReader, int As Integer) As String
    Dim data As String = "(null)"

    If Not reader.IsDBNull(int) Then
        data = reader.GetString(int)
    End If

    Return data
End Function


Private Function termCONdate()
    Dim login As New AseConnection("Data Source=*CENSORED*;Port=5000;UID=" + _tbUSER.Text + ";PWD=" + _tbPASSWORD.Text + ";Database=vertrag;")

    login.Open()

    Dim cmd As New AseCommand("utl_TerminateContract", login)
    cmd.CommandType = CommandType.StoredProcedure

    Dim param1 As AseParameter = cmd.CreateParameter()
    param1.ParameterName = "@contract_id"
    param1.AseDbType = AseDbType.Integer
    param1.Direction = ParameterDirection.Input
    param1.Value = _tbCONTRACTID.Text

    Dim param2 As AseParameter = cmd.CreateParameter()
    param2.ParameterName = "@by_reason"
    param2.AseDbType = AseDbType.Integer
    param2.Direction = ParameterDirection.Input
    param2.Value = "26"

    Dim param3 As AseParameter = cmd.CreateParameter()
    param3.ParameterName = "@termination_date"
    param3.AseDbType = AseDbType.SmallDateTime
    param3.Direction = ParameterDirection.Input
    param3.Value = _tbDATEOFAFFECT.Text

    Dim param4 As AseParameter = cmd.CreateParameter()
    param4.ParameterName = "@effect_date"
    param4.AseDbType = AseDbType.SmallDateTime
    param4.Direction = ParameterDirection.Input
    param4.Value = _tbDATEOFAFFECT.Text

    Dim param5 As AseParameter = cmd.CreateParameter()
    param5.ParameterName = "@debug"
    param5.AseDbType = AseDbType.Integer
    param5.Direction = ParameterDirection.Input
    param5.Value = "1"

    Dim param6 As AseParameter = cmd.CreateParameter()
    param6.ParameterName = "@extern_info"
    param6.AseDbType = AseDbType.TinyInt
    param6.Direction = ParameterDirection.Input
    param6.Value = "1"


    cmd.Parameters.Add(param1)
    cmd.Parameters.Add(param2)
    cmd.Parameters.Add(param3)
    cmd.Parameters.Add(param4)
    cmd.Parameters.Add(param5)
    cmd.Parameters.Add(param6)



    Dim reader As AseDataReader = cmd.ExecuteReader()

    Dim resultSetCount = 0

    readResultSet3(reader, resultSetCount)

    While reader.NextResult
        resultSetCount = resultSetCount + 1
        readResultSet3(reader, resultSetCount)
    End While


    reader.Close()
    login.Close()

    Return 0
End Function

Private Sub readResultSet3(reader As AseDataReader, resultSetCount As Integer)
    While reader.Read()
        If (resultSetCount = 0) Then
            Dim tcd0 As String = readStringtermconDATE(reader, 0)

            _tbSQLINFO.Text = tcd0
        End If
    End While
End Sub

Private Sub _btnTERMCONDATE_Click(sender As Object, e As EventArgs) Handles _btnTERMCONDATE.Click
    Try
        termCONdate()
        _tbLOG.Text = _tbLOG.Text + "[" + Date.Today.ToString("yyyy-MM-dd") + "][" + TimeOfDay + "]: ##### EXECUTE ##### Termination for contract #" + _tbCONTRACTID.Text + " set to " + _tbDATEOFAFFECT.Text + vbNewLine
    Catch ex As Exception
        _tbLOG.Text = _tbLOG.Text + "!!! ERROR !!! " + ex.ToString + vbNewLine
    End Try
End Sub

'#################################################################################################################################
'####################################### END TERMINATE CONTRACT PER DATE #########################################################
'#################################################################################################################################

'#################################################################################################################################
'####################################### START TERMINATE POSITION NOW ############################################################
'#################################################################################################################################

Private Function readStringtermposNOW(reader As AseDataReader, int As Integer) As String
    Dim data As String = "(null)"

    If Not reader.IsDBNull(int) Then
        data = reader.GetString(int)
    End If

    Return data
End Function


Private Function termPOSnow()
    Dim login As New AseConnection("Data Source=*CENSORED*;Port=5000;UID=" + _tbUSER.Text + ";PWD=" + _tbPASSWORD.Text + ";Database=vertrag;")

    login.Open()

    Dim cmd As New AseCommand("utl_TerminateContractItem", login)
    cmd.CommandType = CommandType.StoredProcedure

    Dim param1 As AseParameter = cmd.CreateParameter()
    param1.ParameterName = "@contract_id"
    param1.AseDbType = AseDbType.Integer
    param1.Direction = ParameterDirection.Input
    param1.Value = _tbCONTRACTID.Text

    Dim param2 As AseParameter = cmd.CreateParameter()
    param2.ParameterName = "@item"
    param2.AseDbType = AseDbType.Integer
    param2.Direction = ParameterDirection.Input
    param2.Value = _tbPOSITION.Text

    Dim param3 As AseParameter = cmd.CreateParameter()
    param3.ParameterName = "@effect_date"
    param3.AseDbType = AseDbType.SmallDateTime
    param3.Direction = ParameterDirection.Input
    param3.Value = _tbDATETODAY.Text

    cmd.Parameters.Add(param1)
    cmd.Parameters.Add(param2)
    cmd.Parameters.Add(param3)


    Dim reader As AseDataReader = cmd.ExecuteReader()

    Dim resultSetCount = 0

    readResultSet4(reader, resultSetCount)

    While reader.NextResult
        resultSetCount = resultSetCount + 1
        readResultSet4(reader, resultSetCount)
    End While


    reader.Close()
    login.Close()

    Return 0
End Function

Private Sub readResultSet4(reader As AseDataReader, resultSetCount As Integer)
    While reader.Read()
        If (resultSetCount = 0) Then
            Dim tpn0 As String = readStringtermposNOW(reader, 0)

            _tbSQLINFO.Text = tpn0
        End If
    End While
End Sub

Private Sub _btnTERMPOSNOW_Click(sender As Object, e As EventArgs) Handles _btnTERMPOSNOW.Click
    Try
        termPOSnow()
        _tbLOG.Text = _tbLOG.Text + "[" + Date.Today.ToString("yyyy-MM-dd") + "][" + TimeOfDay + "]: ##### EXECUTE ##### Position " + _tbPOSITION.Text + " cancelled! Contract #" + _tbCONTRACTID.Text + vbNewLine
    Catch ex As Exception
        _tbLOG.Text = _tbLOG.Text + "!!! ERROR !!! " + ex.ToString + vbNewLine
    End Try
End Sub

'#################################################################################################################################
'####################################### END TERMINATE POSITION NOW ##############################################################
'#################################################################################################################################

'#################################################################################################################################
'####################################### START TERMINATE POSITION DATE ###########################################################
'#################################################################################################################################

Private Function readStringtermposDATE(reader As AseDataReader, int As Integer) As String
    Dim data As String = "(null)"

    If Not reader.IsDBNull(int) Then
        data = reader.GetString(int)
    End If

    Return data
End Function


Private Function termPOSdate()
    Dim login As New AseConnection("Data Source=*CENSORED*;Port=5000;UID=" + _tbUSER.Text + ";PWD=" + _tbPASSWORD.Text + ";Database=vertrag;")

    login.Open()

    Dim cmd As New AseCommand("utl_TerminateContractItem", login)
    cmd.CommandType = CommandType.StoredProcedure

    Dim param1 As AseParameter = cmd.CreateParameter()
    param1.ParameterName = "@contract_id"
    param1.AseDbType = AseDbType.Integer
    param1.Direction = ParameterDirection.Input
    param1.Value = _tbCONTRACTID.Text

    Dim param2 As AseParameter = cmd.CreateParameter()
    param2.ParameterName = "@item"
    param2.AseDbType = AseDbType.Integer
    param2.Direction = ParameterDirection.Input
    param2.Value = _tbPOSITION.Text

    Dim param3 As AseParameter = cmd.CreateParameter()
    param3.ParameterName = "@effect_date"
    param3.AseDbType = AseDbType.SmallDateTime
    param3.Direction = ParameterDirection.Input
    param3.Value = _tbDATEOFAFFECT.Text

    cmd.Parameters.Add(param1)
    cmd.Parameters.Add(param2)
    cmd.Parameters.Add(param3)


    Dim reader As AseDataReader = cmd.ExecuteReader()

    Dim resultSetCount = 0

    readResultSet5(reader, resultSetCount)

    While reader.NextResult
        resultSetCount = resultSetCount + 1
        readResultSet5(reader, resultSetCount)
    End While


    reader.Close()
    login.Close()

    Return 0
End Function

Private Sub readResultSet5(reader As AseDataReader, resultSetCount As Integer)
    While reader.Read()
        If (resultSetCount = 0) Then
            Dim tpd0 As String = readStringtermposDATE(reader, 0)

            _tbSQLINFO.Text = tpd0
        End If
    End While
End Sub

Private Sub _btnTERMPOSDATE_Click(sender As Object, e As EventArgs) Handles _btnTERMPOSDATE.Click
    Try
        termPOSdate()
        _tbLOG.Text = _tbLOG.Text + "[" + Date.Today.ToString("yyyy-MM-dd") + "][" + TimeOfDay + "]: ##### EXECUTE ##### Cancellation of position " + _tbPOSITION.Text + " set to " + _tbDATEOFAFFECT.Text + vbNewLine
    Catch ex As Exception
        _tbLOG.Text = _tbLOG.Text + "!!! ERROR !!! " + ex.ToString + vbNewLine
    End Try
End Sub

'#################################################################################################################################
'####################################### END TERMINATE POSITION DATE #############################################################
'#################################################################################################################################

'#################################################################################################################################
'####################################### START ACTIVATE CONTRACT NOW #############################################################
'#################################################################################################################################

Private Function readStringACTIconNOW(reader As AseDataReader, int As Integer) As String
    Dim data As String = "(null)"

    If Not reader.IsDBNull(int) Then
        data = reader.GetString(int)
    End If

    Return data
End Function

Private Function actiCONnow()
    Dim login As New AseConnection("Data Source=*CENSORED*;Port=5000;UID=" + _tbUSER.Text + ";PWD=" + _tbPASSWORD.Text + ";Database=vertrag;")

    login.Open()

    Dim cmd As New AseCommand("utl_ActivateContract", login)
    cmd.CommandType = CommandType.StoredProcedure

    Dim param1 As AseParameter = cmd.CreateParameter()
    param1.ParameterName = "@contract_id"
    param1.AseDbType = AseDbType.Integer
    param1.Direction = ParameterDirection.Input
    param1.Value = _tbCONTRACTID.Text

    Dim param2 As AseParameter = cmd.CreateParameter()
    param2.ParameterName = "@activationdate"
    param2.AseDbType = AseDbType.SmallDateTime
    param2.Direction = ParameterDirection.Input
    param2.Value = _tbDATETODAY.Text

    Dim param3 As AseParameter = cmd.CreateParameter()
    param3.ParameterName = "@debug"
    param3.AseDbType = AseDbType.TinyInt
    param3.Direction = ParameterDirection.Input
    param3.Value = "1"

    cmd.Parameters.Add(param1)
    cmd.Parameters.Add(param2)
    cmd.Parameters.Add(param3)


    Dim reader As AseDataReader = cmd.ExecuteReader()

    Dim resultSetCount = 0

    readResultSet6(reader, resultSetCount)

    While reader.NextResult
        resultSetCount = resultSetCount + 1
        readResultSet6(reader, resultSetCount)
    End While


    reader.Close()
    login.Close()

    Return 0
End Function

Private Sub readResultSet6(reader As AseDataReader, resultSetCount As Integer)
    While reader.Read()
        If (resultSetCount = 0) Then
            Dim acn0 As String = readStringACTIconNOW(reader, 0)

            _tbSQLINFO.Text = acn0
        End If
    End While
End Sub

Private Sub _btnACTICON_Click(sender As Object, e As EventArgs) Handles _btnACTICON.Click
    Try
        actiCONnow()
        _tbLOG.Text = _tbLOG.Text + "[" + Date.Today.ToString("yyyy-MM-dd") + "][" + TimeOfDay + "]: ##### EXECUTE ##### Contract #" + _tbCONTRACTID.Text + " activated successfully!" + vbNewLine
    Catch ex As Exception
        _tbLOG.Text = _tbLOG.Text + "!!! ERROR !!! " + ex.ToString + vbNewLine
    End Try
End Sub

'#################################################################################################################################
'####################################### END ACTIVATE CONTRACT NOW ###############################################################
'#################################################################################################################################

'#################################################################################################################################
'####################################### START ACTIVATE POSITION NOW #############################################################
'#################################################################################################################################

Private Function readStringACTIposNOW(reader As AseDataReader, int As Integer) As String
    Dim data As String = "(null)"

    If Not reader.IsDBNull(int) Then
        data = reader.GetString(int)
    End If

    Return data
End Function

Private Function actiPOSnow()
    Dim login As New AseConnection("Data Source=*CENSORED*;Port=5000;UID=" + _tbUSER.Text + ";PWD=" + _tbPASSWORD.Text + ";Database=vertrag;")

    login.Open()

    Dim cmd As New AseCommand("utl_ActivateContractItem", login)
    cmd.CommandType = CommandType.StoredProcedure

    Dim param1 As AseParameter = cmd.CreateParameter()
    param1.ParameterName = "@contract_id"
    param1.AseDbType = AseDbType.Integer
    param1.Direction = ParameterDirection.Input
    param1.Value = _tbCONTRACTID.Text

    Dim param2 As AseParameter = cmd.CreateParameter()
    param2.ParameterName = "@item"
    param2.AseDbType = AseDbType.Integer
    param2.Direction = ParameterDirection.Input
    param2.Value = _tbPOSITION.Text

    Dim param3 As AseParameter = cmd.CreateParameter()
    param3.ParameterName = "@activationdate"
    param3.AseDbType = AseDbType.SmallDateTime
    param3.Direction = ParameterDirection.Input
    param3.Value = _tbDATETODAY.Text

    Dim param4 As AseParameter = cmd.CreateParameter()
    param4.ParameterName = "@debug"
    param4.AseDbType = AseDbType.TinyInt
    param4.Direction = ParameterDirection.Input
    param4.Value = "1"

    cmd.Parameters.Add(param1)
    cmd.Parameters.Add(param2)
    cmd.Parameters.Add(param3)
    cmd.Parameters.Add(param4)


    Dim reader As AseDataReader = cmd.ExecuteReader()

    Dim resultSetCount = 0

    readResultSet7(reader, resultSetCount)

    While reader.NextResult
        resultSetCount = resultSetCount + 1
        readResultSet7(reader, resultSetCount)
    End While


    reader.Close()
    login.Close()

    Return 0
End Function

Private Sub readResultSet7(reader As AseDataReader, resultSetCount As Integer)
    While reader.Read()
        If (resultSetCount = 0) Then
            Dim apn0 As String = readStringACTIposNOW(reader, 0)

            _tbSQLINFO.Text = apn0
        End If
    End While
End Sub

Private Sub _btnACTIPOS_Click(sender As Object, e As EventArgs) Handles _btnACTIPOS.Click
    Try
        actiPOSnow()
        _tbLOG.Text = _tbLOG.Text + "[" + Date.Today.ToString("yyyy-MM-dd") + "][" + TimeOfDay + "]: ##### EXECUTE ##### Activation of position " + _tbPOSITION.Text + " success!" + vbNewLine
    Catch ex As Exception
        _tbLOG.Text = _tbLOG.Text + "!!! ERROR !!! " + ex.ToString + vbNewLine
    End Try
End Sub

'#################################################################################################################################
'####################################### END ACTIVATE POSITION NOW ###############################################################
'#################################################################################################################################

'#################################################################################################################################
'####################################### START REACTIVATE CONTRACT less2w ########################################################
'#################################################################################################################################

Private Function readStringREACTconl2w(reader As AseDataReader, int As Integer) As String
    Dim data As String = "(null)"

    If Not reader.IsDBNull(int) Then
        data = reader.GetString(int)
    End If

    Return data
End Function

Private Function reactCONl2w()
    Dim login As New AseConnection("Data Source=*CENSORED*;Port=5000;UID=" + _tbUSER.Text + ";PWD=" + _tbPASSWORD.Text + ";Database=vertrag;")

    login.Open()

    Dim cmd As New AseCommand("utl_reactivateContract", login)
    cmd.CommandType = CommandType.StoredProcedure

    Dim param1 As AseParameter = cmd.CreateParameter()
    param1.ParameterName = "@contract_id"
    param1.AseDbType = AseDbType.Integer
    param1.Direction = ParameterDirection.Input
    param1.Value = _tbCONTRACTID.Text

    Dim param2 As AseParameter = cmd.CreateParameter()
    param2.ParameterName = "@debug"
    param2.AseDbType = AseDbType.Integer
    param2.Direction = ParameterDirection.Input
    param2.Value = "1"

    cmd.Parameters.Add(param1)
    cmd.Parameters.Add(param2)

    Dim reader As AseDataReader = cmd.ExecuteReader()

    Dim resultSetCount = 0

    readResultSet8(reader, resultSetCount)

    While reader.NextResult
        resultSetCount = resultSetCount + 1
        readResultSet8(reader, resultSetCount)
    End While


    reader.Close()
    login.Close()

    Return 0
End Function

Private Sub readResultSet8(reader As AseDataReader, resultSetCount As Integer)
    While reader.Read()
        If (resultSetCount = 0) Then
            Dim rcl2w0 As String = readStringREACTconl2w(reader, 0)

            _tbSQLINFO.Text = rcl2w0
        End If
    End While
End Sub

Private Sub _btnREACTCONLESS_Click(sender As Object, e As EventArgs) Handles _btnREACTCONLESS.Click
    Try
        reactCONl2w()
        _tbLOG.Text = _tbLOG.Text + "[" + Date.Today.ToString("yyyy-MM-dd") + "][" + TimeOfDay + "]: ##### EXECUTE ##### Contract #" + _tbCONTRACTID.Text + " reactivated!" + vbNewLine
    Catch ex As Exception
        _tbLOG.Text = _tbLOG.Text + "!!! ERROR !!! " + ex.ToString + vbNewLine
    End Try
End Sub

'#################################################################################################################################
'####################################### END REACTIVATE CONTRACT less2w ##########################################################
'#################################################################################################################################

'#################################################################################################################################
'####################################### START REACTIVATE CONTRACT more2w ########################################################
'#################################################################################################################################

Private Function readStringREACTconm2w(reader As AseDataReader, int As Integer) As String
    Dim data As String = "(null)"

    If Not reader.IsDBNull(int) Then
        data = reader.GetString(int)
    End If

    Return data
End Function

Private Function reactCONm2w()
    Dim login As New AseConnection("Data Source=*CENSORED*;Port=5000;UID=" + _tbUSER.Text + ";PWD=" + _tbPASSWORD.Text + ";Database=worker;")

    login.Open()

    Dim cmd As New AseCommand("srh_reactivateContract", login)
    cmd.CommandType = CommandType.StoredProcedure

    Dim param1 As AseParameter = cmd.CreateParameter()
    param1.ParameterName = "@contract_id"
    param1.AseDbType = AseDbType.Integer
    param1.Direction = ParameterDirection.Input
    param1.Value = _tbCONTRACTID.Text

    Dim param2 As AseParameter = cmd.CreateParameter()
    param2.ParameterName = "@debug"
    param2.AseDbType = AseDbType.Integer
    param2.Direction = ParameterDirection.Input
    param2.Value = "1"

    cmd.Parameters.Add(param1)
    cmd.Parameters.Add(param2)

    Dim reader As AseDataReader = cmd.ExecuteReader()

    Dim resultSetCount = 0

    readResultSet9(reader, resultSetCount)

    While reader.NextResult
        resultSetCount = resultSetCount + 1
        readResultSet9(reader, resultSetCount)
    End While


    reader.Close()
    login.Close()

    Return 0
End Function

Private Sub readResultSet9(reader As AseDataReader, resultSetCount As Integer)
    While reader.Read()
        If (resultSetCount = 0) Then
            Dim rcm2w0 As String = readStringREACTconm2w(reader, 0)

            _tbSQLINFO.Text = rcm2w0
        End If
    End While
End Sub

Private Sub _btnREACTCONMORE_Click(sender As Object, e As EventArgs) Handles _btnREACTCONMORE.Click
    Try
        reactCONm2w()
        _tbLOG.Text = _tbLOG.Text + "[" + Date.Today.ToString("yyyy-MM-dd") + "][" + TimeOfDay + "]: ##### EXECUTE ##### Contract #" + _tbCONTRACTID.Text + " reactivated!" + vbNewLine
    Catch ex As Exception
        _tbLOG.Text = _tbLOG.Text + "!!! ERROR !!! " + ex.ToString + vbNewLine
    End Try
End Sub

'#################################################################################################################################
'####################################### END REACTIVATE CONTRACT more2w ##########################################################
'#################################################################################################################################

'#################################################################################################################################
'####################################### START WITHDRAW CANCELLATION #############################################################
'#################################################################################################################################

Private Function readStringWITHDRAWTERM(reader As AseDataReader, int As Integer) As String
    Dim data As String = "(null)"

    If Not reader.IsDBNull(int) Then
        data = reader.GetString(int)
    End If

    Return data
End Function

Private Function WITHDRAWTERM()
    Dim login As New AseConnection("Data Source=*CENSORED*;Port=5000;UID=" + _tbUSER.Text + ";PWD=" + _tbPASSWORD.Text + ";Database=vertrag;")

    login.Open()

    Dim cmd As New AseCommand("utl_WithdrawTermination", login)
    cmd.CommandType = CommandType.StoredProcedure

    Dim param1 As AseParameter = cmd.CreateParameter()
    param1.ParameterName = "@contract_id"
    param1.AseDbType = AseDbType.Integer
    param1.Direction = ParameterDirection.Input
    param1.Value = _tbCONTRACTID.Text

    Dim param2 As AseParameter = cmd.CreateParameter()
    param2.ParameterName = "@debug"
    param2.AseDbType = AseDbType.Integer
    param2.Direction = ParameterDirection.Input
    param2.Value = "1"

    cmd.Parameters.Add(param1)
    cmd.Parameters.Add(param2)

    Dim reader As AseDataReader = cmd.ExecuteReader()

    Dim resultSetCount = 0

    readResultSet10(reader, resultSetCount)

    While reader.NextResult
        resultSetCount = resultSetCount + 1
        readResultSet10(reader, resultSetCount)
    End While


    reader.Close()
    login.Close()

    Return 0
End Function

Private Sub readResultSet10(reader As AseDataReader, resultSetCount As Integer)
    While reader.Read()
        If (resultSetCount = 0) Then
            Dim wt0 As String = readStringWITHDRAWTERM(reader, 0)

            _tbSQLINFO.Text = wt0
        End If
    End While
End Sub

Private Sub _btnWITHDRAWTERM_Click(sender As Object, e As EventArgs) Handles _btnWITHDRAWTERM.Click
    Try
        WITHDRAWTERM()
        _tbLOG.Text = _tbLOG.Text + "[" + Date.Today.ToString("yyyy-MM-dd") + "][" + TimeOfDay + "]: ##### EXECUTE ##### Termination of contract #" + _tbCONTRACTID.Text + " withdrawn!" + vbNewLine
    Catch ex As Exception
        _tbLOG.Text = _tbLOG.Text + "!!! ERROR !!! " + ex.ToString + vbNewLine
    End Try
End Sub

'#################################################################################################################################
'####################################### END WITHDRAW CANCELLATION ###############################################################
'#################################################################################################################################

'#################################################################################################################################
'####################################### START UNLOCK CONTRACT ###################################################################
'#################################################################################################################################

Private Function readStringUNCON(reader As AseDataReader, int As Integer) As String
    Dim data As String = "(null)"

    If Not reader.IsDBNull(int) Then
        data = reader.GetString(int)
    End If

    Return data
End Function

Private Function UNCON()
    Dim login As New AseConnection("Data Source=*CENSORED*;Port=5000;UID=" + _tbUSER.Text + ";PWD=" + _tbPASSWORD.Text + ";Database=vertrag;")

    login.Open()

    Dim cmd As New AseCommand("utl_UnlockContract", login)
    cmd.CommandType = CommandType.StoredProcedure

    Dim param1 As AseParameter = cmd.CreateParameter()
    param1.ParameterName = "@contract_id"
    param1.AseDbType = AseDbType.Integer
    param1.Direction = ParameterDirection.Input
    param1.Value = _tbCONTRACTID.Text

    Dim param2 As AseParameter = cmd.CreateParameter()
    param2.ParameterName = "@debug"
    param2.AseDbType = AseDbType.Integer
    param2.Direction = ParameterDirection.Input
    param2.Value = "1"

    Dim param3 As AseParameter = cmd.CreateParameter()
    param3.ParameterName = "@extern_info"
    param3.AseDbType = AseDbType.TinyInt
    param3.Direction = ParameterDirection.Input
    param3.Value = "0"

    cmd.Parameters.Add(param1)
    cmd.Parameters.Add(param2)
    cmd.Parameters.Add(param3)

    Dim reader As AseDataReader = cmd.ExecuteReader()

    Dim resultSetCount = 0

    readResultSet11(reader, resultSetCount)

    While reader.NextResult
        resultSetCount = resultSetCount + 1
        readResultSet11(reader, resultSetCount)
    End While


    reader.Close()
    login.Close()

    Return 0
End Function

Private Sub readResultSet11(reader As AseDataReader, resultSetCount As Integer)
    While reader.Read()
        If (resultSetCount = 0) Then
            Dim uc0 As String = readStringUNCON(reader, 0)

            _tbSQLINFO.Text = uc0
        End If
    End While
End Sub

Private Sub _btnUNLOCKCON_Click(sender As Object, e As EventArgs) Handles _btnUNLOCKCON.Click
    Try
        UNCON()
        _tbLOG.Text = _tbLOG.Text + "[" + Date.Today.ToString("yyyy-MM-dd") + "][" + TimeOfDay + "]: ##### EXECUTE ##### Contract #" + _tbCONTRACTID.Text + " unlocked!" + vbNewLine
    Catch ex As Exception
        _tbLOG.Text = _tbLOG.Text + "!!! ERROR !!! " + ex.ToString + vbNewLine
    End Try
End Sub

'#################################################################################################################################
'####################################### END UNLOCK CONTRACT #####################################################################
'#################################################################################################################################

Private Sub CheckBox1_CheckedChanged(sender As Object, e As EventArgs) Handles CheckBox1.CheckedChanged
    If CheckBox1.Checked = True Then
        TopMost = True
    Else
        TopMost = False
    End If
End Sub

Private Sub CheckBox2_CheckedChanged(sender As Object, e As EventArgs) Handles CheckBox2.CheckedChanged
    If CheckBox2.Checked = True Then
        _tbSQLINFO.BackColor = Color.White
        _tbSQLINFO.ForeColor = Color.Black
    Else
        _tbSQLINFO.BackColor = Color.Black
        _tbSQLINFO.ForeColor = Color.White
    End If
End Sub

Private Sub _tbLOG_TextChanged(sender As Object, e As EventArgs) Handles _tbLOG.TextChanged
    _tbLOG.SelectionStart = Len(_tbLOG.Text)

    _tbLOG.ScrollToCaret()
End Sub

Private Sub Timer2_Tick(sender As Object, e As EventArgs) Handles Timer2.Tick
    Label4.Text = TimeOfDay
    Try
        Label6.Text = My.Computer.Network.Ping("*CENSORED*.mt.einsundeins.de")
        If Label6.Text = True Then
            Label5.Text = "CONNECTED"
            Label5.ForeColor = Color.Green
        Else
            Label5.Text = "TIMEOUT - RECONNECTING..."
            Label5.ForeColor = Color.Red
        End If
    Catch ex As Exception
        _tbLOG.Text = _tbLOG.Text + "[" + Date.Today.ToString("yyyy-MM-dd") + "][" + TimeOfDay + "]: !!!!!!!!!! CONNECTION TO DATABASE LOST. RECONNECTING... !!!!!!!!!!" + vbNewLine
    End Try


End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        Dim wclav As New Net.WebClient
        Dim mrv As String = wclav.DownloadString("http://*CENSORED*/SWINFO/SQLExecuter_Version.mrv")
        _tbAVAILABLEVERSION.Text = mrv
    Catch ex As Exception
        MsgBox("Couldn't check the most recent version!")
    End Try
    If _tbCURRENTVERSION.Text = _tbAVAILABLEVERSION.Text Then
        PictureBox1.Image = My.Resources.utd
        MessageBox.Show("You are up-to-date!", "Update")

    Else
        PictureBox1.Image = My.Resources.otd

        Dim result As Integer = MessageBox.Show("Sorry to interrupt, but this is important." + vbNewLine + vbNewLine + vbNewLine + "Your PC is running an outdated version of SQLExecuter." + vbNewLine + vbNewLine + "There may be changed SP's that can harm or crash the database." + vbNewLine + "It's recommended to upgrade to the newest version." + vbNewLine + "Update now?", "Update", MessageBoxButtons.YesNo)


        If result = DialogResult.No Then
            Me.Show()

        ElseIf result = DialogResult.Yes Then

            Try
                '#########################################WICHTIG#######DROPDOWNLOAD


            Catch ex As Exception
                MsgBox("An error occurred while trying to download the new version." + vbNewLine + "Please check your internet connection and try again.", MsgBoxStyle.Critical)

            End Try
        End If
    End If
End Sub

End Class

I hope this helps you, sorry for Visual Basic ;)

0
edit delete
are you sure?
answered Brian Loose

Post an answer but please login first

Post Answer
  1. Please login to post answer

Similar Questions

Tagsfor this question

Forumall questions