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 log in first

Post Answer
  1. Please log in to post answer

Similar Questions

Tagsfor this question

Forumall questions