data.vb

Imports Microsoft.Win32
Imports System.Data.OracleClient
Imports System.Collections.Generic
Imports System.Security.Cryptography
Imports System.IO
Imports System.Net

Module Data

    'Public CONN_KZR As String = Registry.LocalMachine.OpenSubKey("SOFTWARE"). _
    '                                                  OpenSubKey("GXVAIS_REG"). _
    '                                                  OpenSubKey("CON_STRINGS").GetValue("CONN_KZR")

    'Public CONN_KIOSK As String = Registry.LocalMachine.OpenSubKey("SOFTWARE"). _
    '                                                  OpenSubKey("GXVAIS_REG"). _
    '                                                  OpenSubKey("CON_STRINGS").GetValue("CONN_KIOSK")

    'Public hesabat_path As String = Registry.LocalMachine.OpenSubKey("SOFTWARE"). _
    '                                                      OpenSubKey("GXVAIS_REG"). _
    '                                                      OpenSubKey("REPORTS_PATH").GetValue("RP_EDOC")

    'Public Folder As String = Registry.LocalMachine.OpenSubKey("SOFTWARE"). _
    '                                                OpenSubKey("GXVAIS_REG"). _
    '                                                OpenSubKey("VPDGK"). _
    '                                                OpenSubKey("parametr").GetValue("log_folder")

    ''''''''''''''''''''''''''''''''''
    'Public CONN_KZR As String = "Data Source=DGKDB;Persist Security Info=True;User ID=KARGUZAR;Password=c@2Kr!3_GK;"
    'Public CONN_KADR As String = "Data Source=DGKDB;Persist Security Info=True;User ID=KADR;Password=c@2Hr!3_GK;"
    'Public CONN_WEB_KIOSK As String = "Data Source=DGK_WEBDB;Persist Security Info=True;User ID=KIOSK;Password=c@2Kk!3_WD;"
    'Public CONN_KIOSK As String = "Data Source=DGKDB;Persist Security Info=True;User ID=KIOSK;Password=c@2Kk!3_GK;"

    'Public CONN_VP As String = "Data Source=DGKDB;Persist Security Info=True;User ID=VPDGK;Password=c@2Vk!3_GK;"
    'Public CONN_CONTR = "Data Source=DGKDB;Persist Security Info=True;User ID=CONTRABAND;Password=c@2Cd!3_GK;"
    'Public CONN_DICT As String = "Data Source=DGKDB;Persist Security Info=True;User ID=VPDGK;Password=c@2Vk!3_GK;"

    ''''''''''''''''''

    Public Folder As String = "c:\logs\EDOC"
    Public Max_olcu As String = "5"
    Public hesabat_path As String = "D:\inetpub\wwwroot\Hesabatlar\EDOC\"

    Public SERVER_IP As String = Registry.LocalMachine.OpenSubKey("SOFTWARE").OpenSubKey("GXVAIS_REG").OpenSubKey("OTHERS").GetValue("SERVER_IP")
    Public sender_email As String = Registry.LocalMachine.OpenSubKey("SOFTWARE").OpenSubKey("gomruk").GetValue("sender_email").ToString
    Public inventar_email As String = Registry.LocalMachine.OpenSubKey("SOFTWARE").OpenSubKey("gomruk").GetValue("inventar_email").ToString

    Public FTPSettings_IP As String = Registry.LocalMachine.OpenSubKey("SOFTWARE").OpenSubKey("GXVAIS_REG"). _
                                                       OpenSubKey("VPDGK").OpenSubKey("ftp").GetValue("IP")

    Public FTPSettings_UserID As String = Registry.LocalMachine.OpenSubKey("SOFTWARE").OpenSubKey("GXVAIS_REG"). _
                                                OpenSubKey("VPDGK").OpenSubKey("ftp").GetValue("UserID")

    Public FTPSettings_Password As String = Registry.LocalMachine.OpenSubKey("SOFTWARE").OpenSubKey("GXVAIS_REG"). _
                                                  OpenSubKey("VPDGK").OpenSubKey("ftp").GetValue("Password")

    Public DBConnectionS As Dictionary(Of String, String) = GetDBConnection("R", {"CONN_KZR", "CONN_KADR", "CONN_VP", "CONN_KIOSK", "CONN_CONTR", "CONN_INT_KIOSK", "CONN_DICT"})
    Public CONN_KZR As String = DBConnectionS.Item("CONN_KZR").ToString
    Public CONN_KADR As String = DBConnectionS.Item("CONN_KADR").ToString
    Public CONN_VP As String = DBConnectionS.Item("CONN_VP").ToString
    Public CONN_KIOSK As String = DBConnectionS.Item("CONN_KIOSK").ToString
    Public CONN_CONTR As String = DBConnectionS.Item("CONN_CONTR").ToString
    Public CONN_WEB_KIOSK As String = DBConnectionS.Item("CONN_INT_KIOSK").ToString
    Public CONN_DICT As String = DBConnectionS.Item("CONN_DICT").ToString

    Function GetDBConnection(DBStatus As Char, ByVal dbusername() As String) As Dictionary(Of String, String)
        Dim oDBCons As New DBCons.DBConsClient
        Dim callerInfo As New DBCons.CallerSystemInfo
        callerInfo.SubSystemName = "VAIS"
        If DBStatus = "R" Then
            callerInfo.DBStatus = DBCons.Databasestatus.REALDB
        ElseIf DBStatus = "T" Then
            callerInfo.DBStatus = DBCons.Databasestatus.TESTDB
        ElseIf DBStatus = "H" Then
            callerInfo.DBStatus = DBCons.Databasestatus.REPORTDB
        End If

        Dim CONS(dbusername.Length - 1) As String
        For i As Integer = 0 To dbusername.Length - 1
            CONS(i) = dbusername(i)
        Next
        callerInfo.DBaseUserName = CONS
        callerInfo.Password = "!^G^*ght^C*(Chetr^*("
        Dim res As New DBCons.ResultDBC
        res = oDBCons.GetSCString(callerInfo)
        Return res.dbcons
    End Function

    'Public CONN_KZR As String = "Data Source=KARGUZAR;Persist Security Info=True;User ID=KARGUZAR;Password=c@2Kr!3_GK;"
    'Public CONN_KADR As String = "Data Source=DGKDB;Persist Security Info=True;User ID=KADR;Password=c@2Hr!3_GK;"
    'Public CONN_VP As String = "Data Source=DGKDB;Persist Security Info=True;User ID=VPDGK;Password=c@2Vk!3_GK;"
    'Public CONN_KIOSK As String = "Data Source=DGKDB;Persist Security Info=True;User ID=KIOSK;Password=c@2Kk!3_GK;"
    'Public CONN_CONTR As String = "Data Source=DGKDB;Persist Security Info=True;User ID=CONTRABAND;Password=c@2Cd!3_GK;"
    'Public CONN_WEB_KIOSK As String = "Data Source=DGK_WEBDB;Persist Security Info=True;User ID=KIOSK;Password=c@2Kk!3_WD;"
    'Public CONN_DICT As String = "Data Source=DGKDB;Persist Security Info=True;User ID=DICTIONARIES;Password=c@2Ds!3_GK;"

    Public nCONN_KZR As New OracleConnection(CONN_KZR)
    Public nCONN_KADR As New OracleClient.OracleConnection(CONN_KADR)
    Public nCONN_VP As New OracleConnection(CONN_VP)
    Public nCONN_KIOSK As New OracleClient.OracleConnection(CONN_KIOSK)
    Public nCONN_WEB_KIOSK As New OracleClient.OracleConnection(CONN_WEB_KIOSK)
    Public nCONN_CONTR As New OracleClient.OracleConnection(CONN_CONTR)
    Public nCONN_DICT As New OracleClient.OracleConnection(CONN_DICT)

    Public Enum MessageType
        Errors = 1
        Info = 2
        Success = 3
        Warning = 4
        SignVerified = 5
        SignNotVerified = 6
        NoneSign = 7
    End Enum

#Region "MSG"
    Public Sub Show(ByVal MessageBox As UI.HtmlControls.HtmlTable, ByVal messageType As MessageType, ByVal message As String, _
                    ByVal CL_BTN As HyperLink, ByVal litMessage As Label, ByVal ShowButton As String)
        If ShowButton = 1 Then
            CL_BTN.Visible = True
        Else
            CL_BTN.Visible = False
        End If
        MessageBox.Visible = True
        litMessage.Text = message
        Select Case messageType
            Case messageType.Errors
                MessageBox.Attributes.Add("Class", "error")
            Case messageType.Info
                MessageBox.Attributes.Add("Class", "info")
            Case messageType.Success
                MessageBox.Attributes.Add("Class", "success")
            Case messageType.Warning
                MessageBox.Attributes.Add("Class", "warning")
        End Select
    End Sub
#End Region
#Region "ANTI DOUBLE CLICK"
    Public Sub AntiDoubleClick(ByVal ParamArray Controls() As WebControl)
        For i As Integer = 0 To UBound(Controls, 1)
            If TypeOf Controls(i) Is Button Then
                CType(Controls(i), Button).UseSubmitBehavior = False
                CType(Controls(i), Button).Attributes.Add("onclick", "this.disabled='true';this.value = 'Gözləyin...!'")
                CType(Controls(i), Button).Attributes.Add("onload", "this.disabled='false';")
            End If
        Next i
    End Sub
#End Region

    Public Class CmdParameter
        Public Parameter As OracleParameter
        Public Sub New(ByVal name As String, ByVal type As OracleType, ByVal value As Object)
            Parameter = New OracleParameter(name, type)
            Parameter.Value = value
        End Sub
    End Class
    Function Encrypt(ByVal str As String) As String
        Dim hashedDataBytes As Byte()
        Dim ret As String = ""
        Dim encoder As New UTF8Encoding()
        Dim md5Hasher As New MD5CryptoServiceProvider()
        hashedDataBytes = md5Hasher.ComputeHash(encoder.GetBytes(str))
        Dim b As Byte
        For Each b In hashedDataBytes
            ret = ret + b.ToString
        Next b
        Return ret
    End Function

    Public Function Insert(ByVal Table As String, ByRef err As Label, ByVal ParamArray Controls() As Web.UI.WebControls.WebControl) As Boolean
        Dim Columns As String = "IDN"
        Dim Values As String = "SEQ_" + Table + ".NEXTVAL"
        For i As Integer = 0 To UBound(Controls, 1)
            If Mid(Controls(i).ID.ToUpper, 1, 4) = "DATE" Then
                Columns += ", " + Mid(Controls(i).ID.ToUpper, 5)
            Else
                Columns += ", " + Controls(i).ID.ToUpper
            End If
        Next i

        For i As Integer = 0 To UBound(Controls, 1)
            Dim Value As String = ""
            If TypeOf Controls(i) Is TextBox Then
                If Mid(Controls(i).ID.ToUpper, 1, 4) = "DATE" Then
                    Value += " TO_DATE('" & CType(Controls(i), TextBox).Text & "', 'DD.MM.YYYY  hh24:mi:ss')"
                Else
                    Value = "N'" + CType(Controls(i), TextBox).Text.ToUpper + "'"
                End If

            ElseIf TypeOf Controls(i) Is DropDownList Then
                Value = "'" + CType(Controls(i), DropDownList).SelectedValue + "'"
            ElseIf TypeOf Controls(i) Is RadioButtonList Then
                Value = "'" + CType(Controls(i), RadioButtonList).SelectedValue + "'"
            ElseIf TypeOf Controls(i) Is Label Then
                If Mid(Controls(i).ID.ToUpper, 1, 4) = "DATE" Then
                    Value += " TO_DATE('" & CType(Controls(i), Label).Text & "', 'DD.MM.YYYY  hh24:mi:ss')"
                Else
                    Value = "N'" + CType(Controls(i), Label).Text.ToUpper + "'"
                End If
            End If
            Values += ", " + Value
        Next i
        Dim sql = "INSERT INTO " + Table + "(" + Columns + ") VALUES (" + Values + ")"
        If ExecuteQuery(sql, err) Then
            err.ForeColor = Drawing.Color.Black
            err.Text = "Düzəliş uğurla bazaya yazıldı!"
            Return True
        Else
            err.ForeColor = Drawing.Color.Red
            err.Text += "Xəta: Düzəliş bazaya yazılmadı."
            Return False
        End If
    End Function
    Public Function Update(ByVal Table As String, ByRef err As Label, ByVal SERT As String, ByVal ParamArray Controls() As WebControl) As Boolean
        Dim KeyValues As String = ""
        For i As Integer = 0 To UBound(Controls, 1)
            If Mid(Controls(i).ID.ToUpper, 1, 4) = "DATE" Then
                KeyValues += Mid(Controls(i).ID.ToString, 5) + " = "
            Else
                KeyValues += Controls(i).ID.ToString + " = "
            End If

            Dim Value As String = ""
            If TypeOf Controls(i) Is TextBox Then
                If Mid(Controls(i).ID.ToUpper, 1, 4) = "DATE" Then
                    Value += " TO_DATE('" & CType(Controls(i), TextBox).Text & "', 'DD.MM.YYYY  hh24:mi:ss')"
                Else
                    Value = "N'" + CType(Controls(i), TextBox).Text.ToUpper + "'"
                End If
            ElseIf TypeOf Controls(i) Is DropDownList Then
                Value = "'" + CType(Controls(i), DropDownList).SelectedValue + "'"
            ElseIf TypeOf Controls(i) Is RadioButtonList Then
                Value = "'" + CType(Controls(i), RadioButtonList).SelectedValue + "'"
            ElseIf TypeOf Controls(i) Is Label Then
                If Mid(Controls(i).ID.ToUpper, 1, 4) = "DATE" Then
                    Value += " TO_DATE('" & CType(Controls(i), Label).Text & "', 'DD.MM.YYYY  hh24:mi:ss')"
                Else
                    Value = "N'" + CType(Controls(i), Label).Text.ToUpper + "'"
                End If
            End If
            KeyValues += Value
            If i <> UBound(Controls, 1) Then
                KeyValues += ", "
            End If
        Next i

        Dim sql = "UPDATE " & Table & " SET " & KeyValues & SERT
        If ExecuteQuery(sql, err) Then
            err.ForeColor = Drawing.Color.Black
            err.Text = "Düzəliş uğurla bazaya yazıldı!"
            Return True
        Else
            err.ForeColor = Drawing.Color.Red
            err.Text = "Xəta: Düzəliş bazaya yazılmadı. " & err.Text
            Return False
        End If
    End Function
    Function RecordCount(ByVal sert As String, ByVal FIELD As String) As String
        Dim connect As New OracleConnection(nCONN_KZR.ConnectionString)
        If Not connect.State = ConnectionState.Open Then
            connect.Open()
        End If
        Try
            Dim s As String = Replace(sert, "*", "count(" & FIELD & ") as say")
            Dim myCommand As New System.Data.OracleClient.OracleCommand(s, connect)
            Dim SQLReader As System.Data.OracleClient.OracleDataReader
            SQLReader = myCommand.ExecuteReader()
            SQLReader.Read()
            RecordCount = Trim(SQLReader.Item("say")).ToString
            SQLReader.Close()
        Catch ex As Exception
            'WriteToLog(ex.Message & vbNewLine & sert & vbNewLine & "-----", "recordcount" & Format(Date.Today, "dd.MM.yyyy"))
        End Try
        If Not connect.State = ConnectionState.Closed Then
            connect.Close()
        End If
    End Function
    Function IFSelect(ByVal SqlStr As String) As String
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim da As OracleDataAdapter = New OracleDataAdapter(SqlStr, conn)
        Dim dt As DataTable = New DataTable()
        Try
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            da.Fill(dt)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "IFSelect FUNCTION IN Data.vb", SqlStr)
            conn.Close()
            da.Dispose()
            Return False
        Finally
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        If dt.Rows.Count > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
    Sub fillComboWithBind(ByVal DDL As DropDownList, ByVal SqlStr As String, ByVal parm As CmdParameter(), ByVal field1 As String, ByVal field2 As String, ByVal showtext As String, ByVal mode As Short)
        Dim connect As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim Command As New OracleCommand(SqlStr, connect)
        Dim SQLadapter As OracleDataAdapter = New OracleDataAdapter(Command)
        Dim DataSet_tm As New DataSet
        Dim cp As CmdParameter
        Dim count As Integer
        Try
            If Not connect.State = ConnectionState.Open Then
                connect.Open()
            End If
            For Each cp In parm
                SQLadapter.SelectCommand.Parameters.Add(cp.Parameter)
            Next
            SQLadapter.Fill(DataSet_tm)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "fillComboWithBind FUNCTION IN Data.vb", SqlStr)
            If Not connect.State = ConnectionState.Closed Then
                connect.Close()
            End If
        Finally
            If Not connect.State = ConnectionState.Closed Then
                connect.Close()
            End If
        End Try
        DDL.Items.Clear()
        count = DataSet_tm.Tables(0).Rows.Count
        If mode = 1 Then
            DDL.Items.Add(New ListItem(showtext, 0))
        End If
        Dim IJ As Integer = 0
        While IJ < count
            Dim NewListItem As ListItem = New ListItem(DataSet_tm.Tables(0).Rows(IJ).Item(field1).ToString, DataSet_tm.Tables(0).Rows(IJ).Item(field2).ToString)
            DDL.Items.Add(NewListItem)
            IJ = IJ + 1
        End While
        'While count > 0
        '    Dim NewListItem As ListItem = New ListItem(DataSet_tm.Tables(0).Rows(count - 1).Item(field1).ToString, DataSet_tm.Tables(0).Rows(count - 1).Item(field2).ToString)
        '    DDL.Items.Add(NewListItem)
        '    count = count - 1
        'End While
        DataSet_tm.Dispose()
    End Sub
    Sub MultyFillComboWithBind(ByVal SqlStr As String, ByVal parm As CmdParameter(), ByVal field1 As String, ByVal field2 As String, ByVal showtext As String, ByVal mode As Short, ByVal ParamArray Controls() As WebControl)
        Dim connect As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim Command As New OracleCommand(SqlStr, connect)
        Dim SQLadapter As OracleDataAdapter = New OracleDataAdapter(Command)
        Dim DataSet_tm As New DataSet
        Dim cp As CmdParameter
        Dim count As Integer
        Try
            For Each cp In parm
                SQLadapter.SelectCommand.Parameters.Add(cp.Parameter)
            Next
            If Not connect.State = ConnectionState.Open Then
                connect.Open()
            End If
            SQLadapter.Fill(DataSet_tm)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "MultyFillComboWithBind FUNCTION IN Data.vb", SqlStr)
        Finally
            Command.Dispose()
            If Not connect.State = ConnectionState.Closed Then
                connect.Close()
            End If
        End Try
        For i As Integer = 0 To UBound(Controls, 1)
            CType(Controls(i), DropDownList).Items.Clear()
            count = DataSet_tm.Tables(0).Rows.Count
            If mode = 1 Then
                CType(Controls(i), DropDownList).Items.Add(New ListItem(showtext, 0))
            End If
            Dim IJ As Integer = 0
            While IJ < count
                Dim NewListItem As ListItem = New ListItem(DataSet_tm.Tables(0).Rows(IJ).Item(field1).ToString, DataSet_tm.Tables(0).Rows(IJ).Item(field2).ToString)
                CType(Controls(i), DropDownList).Items.Add(NewListItem)
                IJ = IJ + 1
            End While
        Next
    End Sub
    Sub fillComboFromDataTable(ByVal DDL As DropDownList, ByVal dt As DataTable, ByVal field1 As String, ByVal field2 As String, ByVal showtext As String, ByVal mode As Short)
        DDL.Items.Clear()
        Dim count As Integer = dt.Rows.Count
        If mode = 1 Then
            DDL.Items.Add(New ListItem(showtext, 0))
        End If
        Dim IJ As Integer = 0
        While IJ < count
            Dim NewListItem As ListItem = New ListItem(dt.Rows(IJ).Item(field1).ToString, dt.Rows(IJ).Item(field2).ToString)
            DDL.Items.Add(NewListItem)
            IJ = IJ + 1
        End While
    End Sub
    Sub fillListBFromDataTable(ByVal DDL As ListBox, ByVal dt As DataTable, ByVal field1 As String, ByVal field2 As String)
        DDL.Items.Clear()
        Dim count As Integer = dt.Rows.Count
        Dim IJ As Integer = 0
        While IJ < count
            Dim NewListItem As ListItem = New ListItem(dt.Rows(IJ).Item(field1).ToString, dt.Rows(IJ).Item(field2).ToString)
            DDL.Items.Add(NewListItem)
            IJ = IJ + 1
        End While
    End Sub
    Sub fillListDFromDataTable(ByVal DDL As DevExpress.Web.ASPxEditors.ASPxListBox, ByVal dt As DataTable, ByVal field1 As String, ByVal field2 As String)
        DDL.Items.Clear()
        Dim count As Integer = dt.Rows.Count
        Dim IJ As Integer = 0
        While IJ < count
            DDL.Items.Add(dt.Rows(IJ).Item(field1).ToString, dt.Rows(IJ).Item(field2).ToString)
            IJ = IJ + 1
        End While
    End Sub
    Sub fillListBFromDT(ByVal DDL As ListBox, ByVal dt As DataTable, ByVal field1 As String, ByVal field2 As String)
        DDL.Items.Clear()
        Dim count As Integer = dt.Rows.Count
        Dim IJ As Integer = 0
        While IJ < count
            Dim IJ2 As Integer = 0
            Dim IJ1 As Integer = 0
            Dim count1 As Integer = DDL.Items.Count
            While IJ1 < count1
                If dt.Rows(IJ).Item(field2).ToString = DDL.Items(IJ1).Value Then
                    IJ2 = 1
                End If
                IJ1 = IJ1 + 1
            End While
            If IJ2 = 0 Then
                Dim NewListItem As ListItem = New ListItem(dt.Rows(IJ).Item(field1).ToString, dt.Rows(IJ).Item(field2).ToString)
                DDL.Items.Add(NewListItem)
            End If
            IJ = IJ + 1
        End While
    End Sub
    Function ExecuteQuery_WCONN(ByVal db_conn As OracleConnection, ByVal sert As String) As Boolean
        ExecuteQuery_WCONN = True
        Dim myTrans As OracleTransaction
        Dim connect As New OracleConnection(db_conn.ConnectionString)
        Dim intCommand As New OracleCommand
        intCommand.Connection = connect
        If Not connect.State = ConnectionState.Open Then
            connect.Open()
        End If
        myTrans = connect.BeginTransaction()
        intCommand.Transaction = myTrans
        Try
            intCommand.CommandText = sert
            intCommand.ExecuteNonQuery()
            myTrans.Commit()
        Catch ex As Exception
            WriteToLog(ex.ToString, "ExecuteQuery_WCONN IN DATA.VB", sert)
            myTrans.Rollback()
            ExecuteQuery_WCONN = False
        Finally
            If Not connect.State = ConnectionState.Closed Then
                connect.Close()
            End If
        End Try
    End Function
    Function ExecuteQuery_K(ByVal sert As String, ByVal err As Label) As Boolean
        ExecuteQuery_K = True
        Dim myTrans As OracleTransaction
        Dim connect As New OracleConnection(nCONN_KZR.ConnectionString)
        If Not connect.State = ConnectionState.Open Then
            connect.Open()
        End If
        myTrans = connect.BeginTransaction()
        Dim intCommand As New OracleCommand
        intCommand.Transaction = myTrans
        intCommand.Connection = connect
        Try
            intCommand.CommandText = sert
            intCommand.ExecuteNonQuery()
            myTrans.Commit()
        Catch ex As Exception
            err.Text = ex.Message
            WriteToLog(err.Text, "ExecuteQuery IN DATA.VB", sert)
            myTrans.Rollback()
            ExecuteQuery_K = False
        End Try
        If Not connect.State = ConnectionState.Closed Then
            connect.Close()
        End If
    End Function
    Function ExecuteQuery(ByVal sert As String, ByVal err As Label) As Boolean
        ExecuteQuery = True
        Dim myTrans As OracleTransaction
        Dim connect As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim intCommand As New OracleCommand
        intCommand.Connection = connect
        If Not connect.State = ConnectionState.Open Then
            connect.Open()
        End If
        myTrans = connect.BeginTransaction()
        intCommand.Transaction = myTrans
        Try
            intCommand.CommandText = sert
            intCommand.ExecuteNonQuery()
            myTrans.Commit()
        Catch ex As Exception
            err.Text = ex.Message
            WriteToLog(err.Text, "ExecuteQuery IN DATA.VB", sert)
            myTrans.Rollback()
            ExecuteQuery = False
        Finally
            If Not connect.State = ConnectionState.Closed Then
                connect.Close()
            End If
        End Try
    End Function
    Public Function RtnStrUpdForBind(ByVal Table As String, ByVal Term As String, ByVal ParamArray Controls() As Web.UI.WebControls.WebControl) As String
        Dim Values As String = ""
        For i As Integer = 0 To UBound(Controls, 1)
            Values += "," & Controls(i).ID.ToUpper & " = :" & Controls(i).ID.ToUpper
        Next i
        Values = Mid(Values, 2)
        Dim SqlStr = "UPDATE " + Table + "SET" + Values + " " + Term
        Return SqlStr
    End Function
    Public Function InsWithBind(ByVal Table As String, ByRef err As Label, ByVal ParamArray Controls() As Web.UI.WebControls.WebControl) As Boolean
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim theInt As Integer
        Dim theDate As Date
        Dim theNum As Decimal

        Dim Columns As String = ""
        Dim Values As String = ""
        Dim cmd As OracleCommand = New OracleCommand("", conn)

        For i As Integer = 0 To UBound(Controls, 1)
            If Columns.Length = 0 Then
                Columns = Controls(i).ID.ToUpper
            Else
                Columns += ", " + Controls(i).ID.ToUpper
            End If
            If Values.Length = 0 Then
                Values += ":" + Controls(i).ID.ToUpper
            Else
                Values += ",:" + Controls(i).ID.ToUpper
            End If

            Dim OrclParm As OracleParameter = New OracleParameter
            Dim Value As String = ""
            If TypeOf Controls(i) Is TextBox Then
                If Integer.TryParse(CType(Controls(i), TextBox).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), TextBox).Text
                ElseIf Decimal.TryParse(CType(Controls(i), TextBox).Text, theNum) Then
                    OrclParm.OracleType = OracleType.Double
                    OrclParm.Value = CType(Controls(i), TextBox).Text
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), TextBox).Text.ToUpper
                End If
            ElseIf TypeOf Controls(i) Is DropDownList Then
                If Integer.TryParse(CType(Controls(i), DropDownList).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), DropDownList).SelectedValue
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), DropDownList).SelectedValue.ToUpper
                End If
            ElseIf TypeOf Controls(i) Is RadioButtonList Then
                Value = "'" + CType(Controls(i), RadioButtonList).SelectedValue + "'"
            ElseIf TypeOf Controls(i) Is Label Then
                If Integer.TryParse(CType(Controls(i), Label).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), Label).Text
                ElseIf Date.TryParse(CType(Controls(i), Label).Text, theDate) Then
                    OrclParm.OracleType = OracleType.DateTime
                    OrclParm.Value = CType(Controls(i), Label).Text
                ElseIf Decimal.TryParse(CType(Controls(i), Label).Text, theNum) Then
                    OrclParm.OracleType = OracleType.Double
                    OrclParm.Value = CType(Controls(i), Label).Text
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), Label).Text
                End If
            ElseIf TypeOf Controls(i) Is CheckBox Then
                OrclParm.OracleType = OracleType.Int32
                OrclParm.Value = Math.Abs(CType(CType(Controls(i), CheckBox).Checked, Integer))
            ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxGridLookup.ASPxGridLookup Then
                If Integer.TryParse(CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value
                Else
                    If CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value Then
                        OrclParm.OracleType = OracleType.NVarChar
                        OrclParm.Value = CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value
                    End If
                End If
            End If
            OrclParm.ParameterName = ":" + Controls(i).ID.ToUpper
            cmd.Parameters.Add(OrclParm)
        Next i

        Dim SqlStr = "INSERT INTO " + Table + "(" + Columns + ") VALUES (" + Values + ")"
        cmd.CommandText = SqlStr
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If
        Dim myTrans As OracleTransaction
        myTrans = conn.BeginTransaction()
        cmd.Transaction = myTrans
        Try
            cmd.ExecuteNonQuery()
            myTrans.Commit()
            err.ForeColor = Drawing.Color.Black
            err.Text = "Uğurla bazaya yazıldı!"
        Catch ex As Exception
            myTrans.Rollback()
            err.ForeColor = Drawing.Color.Red
            err.Text += "Xəta: Bazaya yazılmadı.  " & ex.ToString()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            cmd.Dispose()
            Return False
        Finally
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            cmd.Dispose()
        End Try
        Return True
    End Function
    Public Function UpdWithBind(ByVal Table As String, ByVal Term As String, ByVal parm As CmdParameter(), ByRef err As Label, ByVal ParamArray Controls() As Web.UI.WebControls.WebControl) As Boolean
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim theInt As Integer
        Dim theDate As Date
        Dim theNum As Decimal

        Dim Values As String = ""
        Dim cmd As OracleCommand = New OracleCommand("", conn)
        Dim cp As CmdParameter
        For Each cp In parm
            cmd.Parameters.Add(cp.Parameter)
        Next
        For i As Integer = 0 To UBound(Controls, 1)

            Values += "," & Controls(i).ID.ToUpper & " = :" & Controls(i).ID.ToUpper

            Dim OrclParm As OracleParameter = New OracleParameter
            Dim Value As String = ""
            If TypeOf Controls(i) Is TextBox Then
                If Integer.TryParse(CType(Controls(i), TextBox).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), TextBox).Text
                ElseIf Decimal.TryParse(CType(Controls(i), TextBox).Text, theNum) Then
                    OrclParm.OracleType = OracleType.Double
                    OrclParm.Value = CType(Controls(i), TextBox).Text
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), TextBox).Text.ToUpper
                End If
            ElseIf TypeOf Controls(i) Is DropDownList Then
                If Integer.TryParse(CType(Controls(i), DropDownList).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), DropDownList).SelectedValue
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), DropDownList).SelectedValue.ToUpper
                End If
            ElseIf TypeOf Controls(i) Is RadioButtonList Then
                Value = "'" + CType(Controls(i), RadioButtonList).SelectedValue + "'"
            ElseIf TypeOf Controls(i) Is Label Then
                If Integer.TryParse(CType(Controls(i), Label).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), Label).Text
                ElseIf Date.TryParse(CType(Controls(i), Label).Text, theDate) Then
                    OrclParm.OracleType = OracleType.DateTime
                    OrclParm.Value = CType(Controls(i), Label).Text
                ElseIf Decimal.TryParse(CType(Controls(i), Label).Text, theNum) Then
                    OrclParm.OracleType = OracleType.Double
                    OrclParm.Value = CType(Controls(i), Label).Text
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), Label).Text
                End If
            ElseIf TypeOf Controls(i) Is CheckBox Then
                OrclParm.OracleType = OracleType.Int32
                OrclParm.Value = Math.Abs(CType(CType(Controls(i), CheckBox).Checked, Integer))
            ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxEditors.ASPxComboBox Then
                If Integer.TryParse(CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).Value, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).SelectedItem.Value
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).SelectedItem.Value
                End If
            ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxEditors.ASPxDateEdit Then
                If CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxDateEdit).Value Is Nothing Then
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = DBNull.Value
                Else
                    OrclParm.OracleType = OracleType.DateTime
                    OrclParm.Value = Formatdate(CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxDateEdit).Value, "dd.MM.yyyy")
                    'OrclParm.Value = DateTime.ParseExact(Formatdate(CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxDateEdit).Value, "dd.MM.yyyy"), "dd.MM.yyyy", Nothing)
                End If
            ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxGridLookup.ASPxGridLookup Then
                If Integer.TryParse(CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value
                Else
                    If CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value Then
                        OrclParm.OracleType = OracleType.NVarChar
                        OrclParm.Value = CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value
                    End If
                End If
            End If
            OrclParm.ParameterName = ":" + Controls(i).ID.ToUpper
            cmd.Parameters.Add(OrclParm)
        Next i

        Values = Mid(Values, 2)
        Dim SqlStr = "UPDATE " + Table + " SET " + Values + " " + Term
        cmd.CommandText = SqlStr
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If
        Dim myTrans As OracleTransaction
        myTrans = conn.BeginTransaction()
        cmd.Transaction = myTrans
        Try
            cmd.ExecuteNonQuery()
            myTrans.Commit()
            err.ForeColor = Drawing.Color.Black
            err.Text = "Düzəliş uğurla bazaya yazıldı!"
        Catch ex As Exception
            myTrans.Rollback()
            err.ForeColor = Drawing.Color.Red
            err.Text += "Xəta: Düzəliş bazaya yazılmadı."
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            WriteToLog(ex.Message.ToString, "UpdWithBind IN DATA.VB", Table)
            Return False
        Finally
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            cmd.Dispose()
        End Try
        Return True
    End Function
    Sub WriteToLog(ByVal ERR_TEXT As String, ByVal FUNC_NAME As String, ByVal TERM As String)
        Dim ps As CmdParameter() = {New CmdParameter(":func_name", OracleType.NVarChar, FUNC_NAME), New CmdParameter(":ERR_TEXT", OracleType.NVarChar, ERR_TEXT), _
                                    New CmdParameter(":term", OracleType.NVarChar, TERM)}
        Dim SqlStr As String = "Insert into TERR_LOG (FUNC_NAME, ERR_TEXT, TERM) Values(:FUNC_NAME, :ERR_TEXT, :TERM)"
        ExecBindNonSql(SqlStr, ps)
    End Sub
    Public Function ExecuteBindNonSql(ByVal SqlStr As String, ByVal parm As CmdParameter(), ByRef Err As Label) As Boolean
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim cmd As New OracleCommand(SqlStr, conn)
        Dim cp As CmdParameter
        Try
            For Each cp In parm
                cmd.Parameters.Add(cp.Parameter)
            Next
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "ExecuteBindNonSql FUNCTION IN Data.vb", SqlStr)
            Err.ForeColor = Drawing.Color.Red
            Err.Text = "XƏTA baş verdi!  " & ex.Message.ToString
            conn.Close()
            Return False
        Finally
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            cmd.Dispose()
        End Try
        Return True
    End Function
    Public Function ExecBindNonSql_Double(ByVal SqlStr1 As String, ByVal parm1 As CmdParameter(), ByVal SqlStr2 As String, ByVal parm2 As CmdParameter(), ByRef Err As Label) As Boolean
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim cmd1 As New OracleCommand(SqlStr1, conn)
        Dim cmd2 As New OracleCommand(SqlStr2, conn)
        Dim cp1 As CmdParameter
        Dim cp2 As CmdParameter
        Dim myTrans As OracleTransaction
        For Each cp1 In parm1
            cmd1.Parameters.Add(cp1.Parameter)
        Next
        For Each cp2 In parm2
            cmd2.Parameters.Add(cp2.Parameter)
        Next
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If
        myTrans = conn.BeginTransaction()
        cmd1.Transaction = myTrans
        cmd2.Transaction = myTrans
        Try
            cmd1.ExecuteNonQuery()
            cmd2.ExecuteNonQuery()
            myTrans.Commit()
            Err.ForeColor = Drawing.Color.Black
            Err.Text = "Uğurla bazaya yazıldı!"
        Catch ex As Exception
            myTrans.Rollback()
            WriteToLog(ex.Message.ToString, "ExecBindNonSql_Double FUNCTION IN Data.vb", SqlStr1)
            Err.ForeColor = Drawing.Color.Red
            Err.Text += "Xəta: Bazaya yazılmadı." & ex.Message.ToString
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            Return False
        Finally
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            cmd1.Dispose()
            cmd2.Dispose()
        End Try
        Return True
    End Function
    Public Function ExecBindNonSql_Tree(ByVal SqlStr1 As String, ByVal parm1 As CmdParameter(), ByVal SqlStr2 As String, ByVal parm2 As CmdParameter(), ByVal SqlStr3 As String, ByVal parm3 As CmdParameter(), ByRef Err As Label) As Boolean
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim cmd1 As New OracleCommand(SqlStr1, conn)
        Dim cmd2 As New OracleCommand(SqlStr2, conn)
        Dim cmd3 As New OracleCommand(SqlStr3, conn)
        Dim cp1 As CmdParameter
        Dim cp2 As CmdParameter
        Dim cp3 As CmdParameter
        Dim myTrans As OracleTransaction
        For Each cp1 In parm1
            cmd1.Parameters.Add(cp1.Parameter)
        Next
        For Each cp2 In parm2
            cmd2.Parameters.Add(cp2.Parameter)
        Next
        For Each cp3 In parm3
            cmd3.Parameters.Add(cp3.Parameter)
        Next
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If
        myTrans = conn.BeginTransaction()
        cmd1.Transaction = myTrans
        cmd2.Transaction = myTrans
        cmd3.Transaction = myTrans
        Try
            cmd1.ExecuteNonQuery()
            cmd2.ExecuteNonQuery()
            cmd3.ExecuteNonQuery()
            myTrans.Commit()
            Err.ForeColor = Drawing.Color.Black
            Err.Text = "Uğurla bazaya yazıldı!"
        Catch ex As Exception
            myTrans.Rollback()
            WriteToLog(ex.Message.ToString, "ExecBindNonSql_Double FUNCTION IN Data.vb", SqlStr1)
            Err.ForeColor = Drawing.Color.Red
            Err.Text += "Xəta: Bazaya yazılmadı." & ex.Message.ToString
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            Return False
        Finally
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            cmd1.Dispose()
            cmd2.Dispose()
            cmd3.Dispose()
        End Try
        Return True
    End Function
    Public Function ExecBindNonSql(ByVal SqlStr As String, ByVal parm As CmdParameter()) As Boolean
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim cmd As New OracleCommand(SqlStr, conn)
        Dim cp As CmdParameter
        Try
            For Each cp In parm
                cmd.Parameters.Add(cp.Parameter)
            Next
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "ExecuteBindNonSql FUNCTION IN Data.vb", SqlStr)
            conn.Close()
            Return False
        Finally
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            cmd.Dispose()
        End Try
        Return True
    End Function
    Public Sub SelectRow(ByVal SERT As String, ByRef err As String, ByVal ParamArray Controls() As Control)
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If
        Dim Adapter As New OracleDataAdapter
        Dim DataSet As New DataSet()
        Dim ReadRow As DataRow
        Dim theInt As Integer
        Try
            Dim Command As New OracleCommand(SERT, conn)
            Adapter = New OracleDataAdapter(Command)
            Adapter.Fill(DataSet)
            If DataSet.Tables(0).Rows.Count = 0 Then
                err = "Məlumat tapılmadı"
            Else
                ReadRow = DataSet.Tables(0).Rows(0)
                For i As Integer = 0 To UBound(Controls, 1)
                    Dim Value As String = Controls(i).ID.ToUpper
                    If Mid(Controls(i).ID.ToUpper, 1, 4) = "DATE" Then
                        If ReadRow(Mid(Controls(i).ID.ToString, 5)).ToString = "" Then
                            Value = ""
                        Else
                            Value = Formatdate(ReadRow(Mid(Controls(i).ID.ToString, 5)).ToString, "dd.MM.yyyy HH:mm")
                        End If
                    Else
                        Value = ReadRow(Controls(i).ID.ToUpper).ToString
                    End If

                    If TypeOf Controls(i) Is TextBox Then
                        CType(Controls(i), TextBox).Text = Value.ToString

                    ElseIf TypeOf Controls(i) Is DropDownList Then
                        Try
                            CType(Controls(i), DropDownList).SelectedValue = Value.ToString
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is RadioButtonList Then
                        Try
                            CType(Controls(i), RadioButtonList).SelectedValue = Value.ToString
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is Label Then
                        CType(Controls(i), Label).Text = Value.ToString
                    ElseIf TypeOf Controls(i) Is CheckBox Then
                        Try
                            CType(Controls(i), CheckBox).Checked = Value.ToString
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is LinkButton Then
                        Try
                            CType(Controls(i), LinkButton).Text = Value.ToString
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is HiddenField Then
                        CType(Controls(i), HiddenField).Value = Value.ToString
                    ElseIf TypeOf Controls(i) Is HtmlInputHidden Then
                        CType(Controls(i), HtmlInputHidden).Value = Value.ToString
                    ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxEditors.ASPxComboBox Then
                        Try
                            If Integer.TryParse(Value, theInt) Then
                                CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).Value = CType(Value.ToString, Integer)
                            Else
                                CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).Value = Value.ToString
                            End If
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxEditors.ASPxDateEdit Then
                        Try
                            CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxDateEdit).Value = CDate(Value.ToString)
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxGridLookup.ASPxGridLookup Then
                        Try
                            CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value = CType(Value.ToString, Integer)
                        Catch ex As Exception
                        End Try
                    End If
                Next i
            End If
        Catch Exc As Exception
            WriteToLog(Exc.Message.ToString, "SelectRow IN DATA.VB", SERT)
            err = ("Xəta baş verdi:" & Exc.Message)
        End Try
        If Not conn.State = ConnectionState.Closed Then
            conn.Close()
        End If
    End Sub
    Public Sub SelectRowFromDB(ByVal NAME_STPROC As String, ByVal data() As String, ByVal params() As String, ByVal types() As String, ByRef err As Label, ByVal ParamArray Controls() As WebControl)
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        'ByVal parm As CmdParameter(),
        Dim theDate As Date
        Dim j As Integer = 0
        Dim reader As OracleDataReader
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        OrclCmd.CommandType = CommandType.StoredProcedure
        'For Each cp In parm
        '    OrclCmd.Parameters.Add(cp.Parameter)
        'Next
        For j = 0 To params.Length - 1
            OrclCmd.Parameters.Add(New OracleParameter(params(j), types(j))).Value = data(j)
        Next
        OrclCmd.Parameters.Add("data_set", OracleType.Cursor).Direction = ParameterDirection.Output
        Try
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            reader = OrclCmd.ExecuteReader()
            If Not reader.HasRows Then
                err.Text = "Məlumat tapılmadı"
            Else
                reader.Read()
                For i As Integer = 0 To UBound(Controls, 1)
                    Dim Value As String = reader.Item(Controls(i).ID.ToUpper).ToString
                    'Dim Value As String = Controls(i).ID.ToUpper
                    'If reader.Item(Controls(i).ID.ToString).ToString.Length = 10 And Date.TryParse(reader.Item(Controls(i).ID.ToString).ToString, theDate) Then
                    '    Value = Formatdate(reader.Item(Controls(i).ID.ToString).ToString, "dd.MM.yyyy")
                    'Else
                    '    Value = reader.Item(Controls(i).ID.ToUpper).ToString
                    'End If
                    If TypeOf Controls(i) Is TextBox Then
                        CType(Controls(i), TextBox).Text = Value.ToString
                    ElseIf TypeOf Controls(i) Is DropDownList Then
                        Try
                            CType(Controls(i), DropDownList).SelectedValue = Value.ToString
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is RadioButtonList Then
                        Try
                            CType(Controls(i), RadioButtonList).SelectedValue = Value.ToString
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is Label Then
                        CType(Controls(i), Label).Text = Value.ToString
                    ElseIf TypeOf Controls(i) Is CheckBox Then
                        Try
                            CType(Controls(i), CheckBox).Checked = Value.ToString
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is LinkButton Then
                        Try
                            CType(Controls(i), LinkButton).Text = Value.ToString
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxEditors.ASPxComboBox Then
                        Try
                            CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).Value = CType(Value.ToString, Integer)
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxEditors.ASPxDateEdit Then
                        Try
                            CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxDateEdit).Value = CDate(Value.ToString)
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxGridLookup.ASPxGridLookup Then
                        Try
                            CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value = CType(Value.ToString, Integer)
                        Catch ex As Exception
                        End Try
                    End If
                Next i
                reader.Close()
            End If
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "SelectRowFromDB IN DATA.VB", NAME_STPROC)
        Finally
            OrclCmd.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
    End Sub
    Public Sub SelectRowWithBind(ByVal SERT As String, ByVal parm As CmdParameter(), ByRef err As Label, ByVal ParamArray Controls() As WebControl)
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim cmd As New OracleCommand(SERT, conn)
        Dim reader As OracleDataReader
        Dim theDate As Date
        Try
            Dim cp As CmdParameter
            For Each cp In parm
                cmd.Parameters.Add(cp.Parameter)
            Next
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            reader = cmd.ExecuteReader()
            If Not reader.HasRows Then
                err.Text = "Məlumat tapılmadı"
            Else
                reader.Read()
                For i As Integer = 0 To UBound(Controls, 1)
                    Dim Value As String = reader.Item(Controls(i).ID.ToUpper).ToString
                    'If Date.TryParse(reader.Item(Controls(i).ID.ToString).ToString, theDate) Then
                    '    Value = Formatdate(reader.Item(Controls(i).ID.ToString).ToString, "dd.MM.yyyy")
                    'Else
                    '    Value = reader.Item(Controls(i).ID.ToUpper).ToString
                    'End If
                    If TypeOf Controls(i) Is TextBox Then
                        CType(Controls(i), TextBox).Text = Value.ToString
                    ElseIf TypeOf Controls(i) Is DropDownList Then
                        Try
                            CType(Controls(i), DropDownList).SelectedValue = Value.ToString
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is RadioButtonList Then
                        Try
                            CType(Controls(i), RadioButtonList).SelectedValue = Value.ToString
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is Label Then
                        CType(Controls(i), Label).Text = Value.ToString
                    ElseIf TypeOf Controls(i) Is CheckBox Then
                        Try
                            CType(Controls(i), CheckBox).Checked = Value.ToString
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is LinkButton Then
                        CType(Controls(i), LinkButton).Text = Value.ToString
                    ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxEditors.ASPxComboBox Then
                        Try
                            CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).Value = CType(Value.ToString, Integer)
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxEditors.ASPxDateEdit Then
                        Try
                            CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxDateEdit).Value = CDate(Formatdate(Value.ToString, "dd.MM.yyyy"))
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxGridLookup.ASPxGridLookup Then
                        Try
                            CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value = CType(Value.ToString, Integer)
                        Catch ex As Exception
                        End Try
                    End If
                Next i
            End If
            reader.Close()
        Catch Exc As Exception
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            WriteToLog(Exc.Message, "SelectRowWithBind IN DATA.VB", SERT)
            err.Text = ("Xəta baş verdi:" & Exc.Message)
        Finally
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
    End Sub
    Function OraProcRtnInt(ByVal NAME_STPROC As String, ByVal data() As String, ByVal params() As String, ByVal types() As OracleType) As Integer
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        Dim NUM As Integer = 0
        OrclCmd.CommandType = CommandType.StoredProcedure
        Dim i As Integer
        For i = 0 To params.Length - 1
            OrclCmd.Parameters.Add(New OracleParameter(params(i), types(i))).Value = data(i)
        Next
        OrclCmd.Parameters.Add("RTN_ID", OracleType.Int32).Direction = ParameterDirection.Output
        Dim myTrans As OracleTransaction
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If
        myTrans = conn.BeginTransaction()
        Try
            OrclCmd.Transaction = myTrans
            OrclCmd.ExecuteNonQuery()
            NUM = CType(OrclCmd.Parameters("RTN_ID").Value.ToString(), Integer)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "ReturnProcInt IN DATA.VB", NAME_STPROC)
            NUM = -1
        Finally
            myTrans.Dispose()
            OrclCmd.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return NUM
    End Function
    Function OraProcRtnInt(ByVal db_conn As OracleConnection, ByVal NAME_STPROC As String, ByVal data() As String, ByVal params() As String, ByVal types() As OracleType) As Integer
        Dim conn As New OracleConnection(db_conn.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        Dim NUM As Integer = 0
        OrclCmd.CommandType = CommandType.StoredProcedure
        Dim i As Integer
        For i = 0 To params.Length - 1
            OrclCmd.Parameters.Add(New OracleParameter(params(i), types(i))).Value = data(i)
        Next
        OrclCmd.Parameters.Add("RTN_ID", OracleType.Int32).Direction = ParameterDirection.Output
        Dim myTrans As OracleTransaction
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If
        myTrans = conn.BeginTransaction()
        Try
            OrclCmd.Transaction = myTrans
            OrclCmd.ExecuteNonQuery()
            NUM = CType(OrclCmd.Parameters("RTN_ID").Value.ToString(), Integer)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "ReturnProcInt IN DATA.VB", NAME_STPROC)
            NUM = -1
        Finally
            myTrans.Dispose()
            OrclCmd.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return NUM
    End Function
    Function OraFuncRtnInt(ByVal NAME_STPROC As String, ByVal data() As String, ByVal params() As String, ByVal types() As String) As Integer
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        Dim NUM As Integer = 0
        OrclCmd.CommandType = CommandType.StoredProcedure
        Dim RTN_NUM As OracleParameter = New OracleParameter("RTN_ID", OracleType.Int32)
        RTN_NUM.Direction = ParameterDirection.ReturnValue
        OrclCmd.Parameters.Add(RTN_NUM)
        Dim i As Integer
        For i = 0 To params.Length - 1
            OrclCmd.Parameters.Add(New OracleParameter(params(i), types(i))).Value = data(i)
        Next
        Try
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            OrclCmd.ExecuteNonQuery()
            NUM = CType(OrclCmd.Parameters("RTN_ID").Value.ToString(), Integer)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "ReturnProcInt IN DATA.VB", NAME_STPROC)
            NUM = -1
        Finally
            OrclCmd.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return NUM
    End Function
    Function OraFuncRtnInt(ByVal db_conn As OracleConnection, ByVal NAME_STPROC As String, ByVal data() As String, ByVal params() As String, ByVal types() As String) As Integer
        Dim conn As New OracleConnection(db_conn.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        Dim NUM As Integer = 0
        OrclCmd.CommandType = CommandType.StoredProcedure
        Dim RTN_NUM As OracleParameter = New OracleParameter("RTN_ID", OracleType.Int32)
        RTN_NUM.Direction = ParameterDirection.ReturnValue
        OrclCmd.Parameters.Add(RTN_NUM)
        Dim i As Integer
        For i = 0 To params.Length - 1
            OrclCmd.Parameters.Add(New OracleParameter(params(i), types(i))).Value = data(i)
        Next
        Try
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            OrclCmd.ExecuteNonQuery()
            NUM = CType(OrclCmd.Parameters("RTN_ID").Value.ToString(), Integer)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "ReturnProcInt IN DATA.VB", NAME_STPROC)
            NUM = -1
        Finally
            OrclCmd.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return NUM
    End Function
    Function OraFuncRtnStr(ByVal NAME_STPROC As String, ByVal data() As String, ByVal params() As String, ByVal types() As String) As String
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        Dim Str As String = ""
        OrclCmd.CommandType = CommandType.StoredProcedure
        Dim RTN_STR As OracleParameter = New OracleParameter("RTN_STR", OracleType.NVarChar, 2000)
        RTN_STR.Direction = ParameterDirection.ReturnValue
        OrclCmd.Parameters.Add(RTN_STR)
        Dim i As Integer
        For i = 0 To params.Length - 1
            OrclCmd.Parameters.Add(New OracleParameter(params(i), types(i))).Value = data(i)
        Next
        Try
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            OrclCmd.ExecuteNonQuery()
            Str = OrclCmd.Parameters("RTN_STR").Value.ToString()
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "OraFuncRtnStr IN DATA.VB", NAME_STPROC)
        Finally
            OrclCmd.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return Str
    End Function
    Public Function InsOrUpdWithOra_MAS(ByVal db_conn As OracleConnection, ByVal NAME_STPROC As String, ByVal Int_Str As String, ByVal Up_Low As String, ByVal ParamsType() As OracleType, ByVal ParamArray Controls() As Web.UI.WebControls.WebControl) As String
        Dim conn As New OracleConnection(db_conn.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        Dim NUM As String = "0"
        OrclCmd.CommandType = CommandType.StoredProcedure
        For i As Integer = 0 To UBound(Controls, 1)
            If TypeOf Controls(i) Is TextBox Then
                If Up_Low = "Up" Then
                    OrclCmd.Parameters.Add(New OracleParameter("P_" & Controls(i).ID.ToUpper, ParamsType(i))).Value = CType(Controls(i), TextBox).Text.ToUpper
                Else
                    OrclCmd.Parameters.Add(New OracleParameter("P_" & Controls(i).ID.ToUpper, ParamsType(i))).Value = CType(Controls(i), TextBox).Text
                End If
            ElseIf TypeOf Controls(i) Is DropDownList Then
                OrclCmd.Parameters.Add(New OracleParameter("P_" & Controls(i).ID.ToUpper, ParamsType(i))).Value = CType(Controls(i), DropDownList).SelectedValue
            ElseIf TypeOf Controls(i) Is RadioButtonList Then
                OrclCmd.Parameters.Add(New OracleParameter("P_" & Controls(i).ID.ToUpper, ParamsType(i))).Value = CType(Controls(i), RadioButtonList).SelectedValue
            ElseIf TypeOf Controls(i) Is Label Then
                OrclCmd.Parameters.Add(New OracleParameter("P_" & Controls(i).ID.ToUpper, ParamsType(i))).Value = CType(Controls(i), Label).Text
            ElseIf TypeOf Controls(i) Is CheckBox Then
                OrclCmd.Parameters.Add(New OracleParameter("P_" & Controls(i).ID.ToUpper, ParamsType(i))).Value = Math.Abs(CType(CType(Controls(i), CheckBox).Checked, Integer))
            End If
        Next i
        If Int_Str = "Int" Then
            OrclCmd.Parameters.Add("RTN_ID", OracleType.Int32).Direction = ParameterDirection.Output
        Else
            OrclCmd.Parameters.Add("RTN_STR", OracleType.NVarChar).Direction = ParameterDirection.Output
        End If

        Dim myTrans As OracleTransaction
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If
        myTrans = conn.BeginTransaction()
        Try
            OrclCmd.Transaction = myTrans
            OrclCmd.ExecuteNonQuery()
            If Int_Str = "Int" Then
                NUM = CType(OrclCmd.Parameters("RTN_ID").Value.ToString(), Integer)
            Else
                NUM = OrclCmd.Parameters("RTN_STR").Value.ToString()
            End If
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "InsOrUpdWithOra_MAS FUNCTION IN Data.vb", NAME_STPROC)
            Return "0"
        Finally
            myTrans.Dispose()
            OrclCmd.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return NUM
    End Function
    Public Function InsOrUpdWithOrac(ByVal NAME_STPROC As String, ByRef err As Label, ByVal ParamArray Controls() As Web.UI.WebControls.WebControl) As Integer
        Dim theInt As Integer
        Dim theDate As DateTime
        Dim theNum As Decimal

        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        Dim NUM As Integer = 0
        OrclCmd.CommandType = CommandType.StoredProcedure
        For i As Integer = 0 To UBound(Controls, 1)
            Dim OrclParm As OracleParameter = New OracleParameter
            Dim Value As String = ""
            If TypeOf Controls(i) Is TextBox Then
                If Integer.TryParse(CType(Controls(i), TextBox).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), TextBox).Text
                ElseIf Decimal.TryParse(CType(Controls(i), TextBox).Text, theNum) Then
                    OrclParm.OracleType = OracleType.Double
                    OrclParm.Value = CType(Controls(i), TextBox).Text
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), TextBox).Text.ToUpper
                End If
            ElseIf TypeOf Controls(i) Is DropDownList Then
                If Integer.TryParse(CType(Controls(i), DropDownList).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), DropDownList).SelectedValue
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), DropDownList).SelectedValue.ToUpper
                End If
            ElseIf TypeOf Controls(i) Is RadioButtonList Then
                Value = "'" + CType(Controls(i), RadioButtonList).SelectedValue + "'"
            ElseIf TypeOf Controls(i) Is Label Then
                If Integer.TryParse(CType(Controls(i), Label).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), Label).Text
                ElseIf Date.TryParse(CType(Controls(i), Label).Text, theDate) Then
                    OrclParm.OracleType = OracleType.DateTime
                    OrclParm.Value = CType(Controls(i), Label).Text
                ElseIf Decimal.TryParse(CType(Controls(i), Label).Text, theNum) Then
                    OrclParm.OracleType = OracleType.Double
                    OrclParm.Value = CType(Controls(i), Label).Text
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), Label).Text
                End If
            ElseIf TypeOf Controls(i) Is CheckBox Then
                OrclParm.OracleType = OracleType.Int32
                OrclParm.Value = Math.Abs(CType(CType(Controls(i), CheckBox).Checked, Integer))
            End If
            OrclParm.ParameterName = "P_" & Controls(i).ID.ToUpper
            OrclCmd.Parameters.Add(OrclParm)
        Next i
        OrclCmd.Parameters.Add("RTN_ID", OracleType.Int32).Direction = ParameterDirection.Output
        Dim myTrans As OracleTransaction
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If
        myTrans = conn.BeginTransaction()
        Try
            OrclCmd.Transaction = myTrans
            OrclCmd.ExecuteNonQuery()
            NUM = CType(OrclCmd.Parameters("RTN_ID").Value.ToString(), Integer)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "InsOrUpdWithOrac FUNCTION IN Data.vb", NAME_STPROC)
            err.ForeColor = Drawing.Color.Red
            err.Text += "Xəta: Bazaya yazılmadı.  " & ex.ToString()
            Return 0
        Finally
            myTrans.Dispose()
            OrclCmd.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return NUM
    End Function
    Public Function InsOrUpdWithOra(ByVal NAME_STPROC As String, ByRef err As Label, ByVal Poz As Integer, ByVal ParamArray Controls() As Web.UI.WebControls.WebControl) As Integer
        Dim theInt As Integer
        Dim theDate As Date
        Dim theNum As Decimal

        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        Dim NUM As Integer = 0
        OrclCmd.CommandType = CommandType.StoredProcedure
        For i As Integer = 0 To UBound(Controls, 1)
            Dim OrclParm As OracleParameter = New OracleParameter
            Dim Value As String = ""
            If TypeOf Controls(i) Is TextBox Then
                If Integer.TryParse(CType(Controls(i), TextBox).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), TextBox).Text
                ElseIf Date.TryParse(CType(Controls(i), TextBox).Text, Nothing, "dd.MM.yyyy", theDate) Then
                    OrclParm.OracleType = OracleType.DateTime
                    OrclParm.Value = CType(Controls(i), TextBox).Text
                ElseIf Decimal.TryParse(CType(Controls(i), TextBox).Text, theNum) Then
                    OrclParm.OracleType = OracleType.Double
                    OrclParm.Value = CType(Controls(i), TextBox).Text
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), TextBox).Text.ToUpper
                End If
            ElseIf TypeOf Controls(i) Is DropDownList Then
                If Integer.TryParse(CType(Controls(i), DropDownList).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), DropDownList).SelectedValue
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), DropDownList).SelectedValue.ToUpper
                End If
            ElseIf TypeOf Controls(i) Is RadioButtonList Then
                Value = "'" + CType(Controls(i), RadioButtonList).SelectedValue + "'"
            ElseIf TypeOf Controls(i) Is Label Then
                If Integer.TryParse(CType(Controls(i), Label).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), Label).Text
                ElseIf Date.TryParse(CType(Controls(i), Label).Text, theDate) Then
                    OrclParm.OracleType = OracleType.DateTime
                    OrclParm.Value = CType(Controls(i), Label).Text
                ElseIf Decimal.TryParse(CType(Controls(i), Label).Text, theNum) Then
                    OrclParm.OracleType = OracleType.Double
                    OrclParm.Value = CType(Controls(i), Label).Text
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), Label).Text
                End If
            ElseIf TypeOf Controls(i) Is CheckBox Then
                OrclParm.OracleType = OracleType.Int32
                OrclParm.Value = Math.Abs(CType(CType(Controls(i), CheckBox).Checked, Integer))
            End If
            OrclParm.ParameterName = "P_" & Controls(i).ID.ToUpper.Substring(Poz)
            OrclCmd.Parameters.Add(OrclParm)
        Next i
        OrclCmd.Parameters.Add("RTN_ID", OracleType.Int32).Direction = ParameterDirection.Output
        Dim myTrans As OracleTransaction
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If
        myTrans = conn.BeginTransaction()
        Try
            OrclCmd.Transaction = myTrans
            OrclCmd.ExecuteNonQuery()
            NUM = CType(OrclCmd.Parameters("RTN_ID").Value.ToString(), Integer)
        Catch ex As Exception
            err.ForeColor = Drawing.Color.Red
            err.Text += "Xəta: Bazaya yazılmadı.  " & ex.ToString()
            WriteToLog(ex.Message.ToString, "InsOrUpdWithOra FUNCTION IN Data.vb", NAME_STPROC)
            Return 0
        Finally
            myTrans.Dispose()
            OrclCmd.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return NUM
    End Function
    '''COMPONENT
    Public Sub Controls_oper(ByVal mode As String, ByVal ParamArray Controls() As WebControl)
        Dim md As Boolean
        If mode = 1 Then
            md = False
        Else
            md = True
        End If
        For i As Integer = 0 To UBound(Controls, 1)
            If TypeOf Controls(i) Is TextBox Then
                CType(Controls(i), TextBox).ReadOnly = md
            ElseIf TypeOf Controls(i) Is DropDownList Then
                CType(Controls(i), DropDownList).Enabled = Not md
            ElseIf TypeOf Controls(i) Is Button Then
                CType(Controls(i), Button).Enabled = Not md
            ElseIf TypeOf Controls(i) Is RadioButton Then
                CType(Controls(i), RadioButton).Enabled = Not md
            ElseIf TypeOf Controls(i) Is RadioButtonList Then
                CType(Controls(i), RadioButtonList).Enabled = Not md
            ElseIf TypeOf Controls(i) Is CheckBox Then
                CType(Controls(i), CheckBox).Enabled = Not md
            ElseIf TypeOf Controls(i) Is ImageButton Then
                CType(Controls(i), ImageButton).Enabled = Not md
            End If
        Next i
    End Sub
    Public Sub ContrlClear_Lock_Unlock(ByVal mode As Integer, ByVal ParamArray Controls() As WebControl)
        Dim md As Boolean
        If mode = 1 Then
            md = False
        Else
            md = True
        End If
        For i As Integer = 0 To UBound(Controls, 1)
            If TypeOf Controls(i) Is TextBox Then
                CType(Controls(i), TextBox).Text = ""
                CType(Controls(i), TextBox).ReadOnly = md
                CType(Controls(i), TextBox).BackColor = System.Drawing.Color.White
            ElseIf TypeOf Controls(i) Is DropDownList Then
                CType(Controls(i), DropDownList).SelectedIndex = 0
                CType(Controls(i), DropDownList).BackColor = System.Drawing.Color.White
                CType(Controls(i), DropDownList).Enabled = Not md
            ElseIf TypeOf Controls(i) Is RadioButton Then
                CType(Controls(i), RadioButton).Checked = False
                CType(Controls(i), RadioButton).Enabled = Not md
            ElseIf TypeOf Controls(i) Is RadioButtonList Then
                CType(Controls(i), RadioButtonList).SelectedIndex = -1
                CType(Controls(i), RadioButtonList).Enabled = Not md
            ElseIf TypeOf Controls(i) Is ListBox Then
                CType(Controls(i), ListBox).Items.Clear()
            ElseIf TypeOf Controls(i) Is Label Then
                CType(Controls(i), Label).Text = ""
            ElseIf TypeOf Controls(i) Is CheckBox Then
                CType(Controls(i), CheckBox).Checked = False
                CType(Controls(i), CheckBox).Enabled = Not md
            ElseIf TypeOf Controls(i) Is ImageButton Then
                CType(Controls(i), ImageButton).Enabled = Not md
            ElseIf TypeOf Controls(i) Is Button Then
                CType(Controls(i), Button).Enabled = Not md
            End If
        Next i
    End Sub
    Public Sub ControlsClear_(ByVal str As String, ByVal ParamArray Controls() As Control)
        For i As Integer = 0 To UBound(Controls, 1)
            If TypeOf Controls(i) Is TextBox Then
                CType(Controls(i), TextBox).Text = str
                CType(Controls(i), TextBox).BackColor = System.Drawing.Color.White
            ElseIf TypeOf Controls(i) Is DropDownList Then
                CType(Controls(i), DropDownList).SelectedIndex = 0
                CType(Controls(i), DropDownList).BackColor = System.Drawing.Color.White
            ElseIf TypeOf Controls(i) Is RadioButton Then
                CType(Controls(i), RadioButton).Checked = False
            ElseIf TypeOf Controls(i) Is RadioButtonList Then
                CType(Controls(i), RadioButtonList).SelectedIndex = -1
            ElseIf TypeOf Controls(i) Is ListBox Then
                CType(Controls(i), ListBox).Items.Clear()
            ElseIf TypeOf Controls(i) Is Label Then
                CType(Controls(i), Label).Text = ""
            ElseIf TypeOf Controls(i) Is CheckBox Then
                CType(Controls(i), CheckBox).Checked = False
            ElseIf TypeOf Controls(i) Is LinkButton Then
                CType(Controls(i), LinkButton).Text = ""
            ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxEditors.ASPxComboBox Then
                CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).Value = ""
                CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).BackColor = System.Drawing.Color.White
            ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxEditors.ASPxDateEdit Then
                CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxDateEdit).Value = str
                CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxDateEdit).BackColor = System.Drawing.Color.White
            End If
        Next i
    End Sub
    Public Function CheckForEmpty(ByVal ParamArray Controls() As WebControl) As Boolean
        Dim count As Integer
        For i As Integer = 0 To UBound(Controls, 1)
            If TypeOf Controls(i) Is TextBox Then
                If CType(Controls(i), TextBox).Text.Equals("") Then
                    CType(Controls(i), TextBox).BackColor = System.Drawing.Color.Lavender
                    count = count + 1
                Else
                    CType(Controls(i), TextBox).BackColor = System.Drawing.Color.White
                End If
            ElseIf TypeOf Controls(i) Is DropDownList Then
                If CType(Controls(i), DropDownList).SelectedValue = 0 Then
                    CType(Controls(i), DropDownList).BackColor = System.Drawing.Color.Lavender
                    count = count + 1
                Else
                    CType(Controls(i), DropDownList).BackColor = System.Drawing.Color.White
                End If
            ElseIf TypeOf Controls(i) Is RadioButtonList Then
                If CType(Controls(i), RadioButtonList).SelectedIndex = -1 Then
                    CType(Controls(i), RadioButtonList).BackColor = System.Drawing.Color.Lavender
                    count = count + 1
                Else
                    CType(Controls(i), RadioButtonList).BackColor = System.Drawing.Color.White
                End If
            ElseIf TypeOf Controls(i) Is ListBox Then
                If CType(Controls(i), ListBox).Items.Count = 0 Then
                    CType(Controls(i), ListBox).BackColor = System.Drawing.Color.Lavender
                    count = count + 1
                Else
                    CType(Controls(i), ListBox).BackColor = System.Drawing.Color.White
                End If
            ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxEditors.ASPxComboBox Then
                If CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).SelectedIndex = -1 Then
                    CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).BackColor = System.Drawing.Color.Lavender
                    count = count + 1
                Else
                    CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).BackColor = System.Drawing.Color.White
                End If
            ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxEditors.ASPxDateEdit Then
                If CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxDateEdit).Value Is Nothing Then
                    CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxDateEdit).BackColor = System.Drawing.Color.Lavender
                    count = count + 1
                Else
                    CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxDateEdit).BackColor = System.Drawing.Color.White
                End If
            ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxGridLookup.ASPxGridLookup Then
                If CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value Is Nothing Then
                    CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).BackColor = System.Drawing.Color.Lavender
                    count = count + 1
                Else
                    CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).BackColor = System.Drawing.Color.White
                End If
            End If
        Next i

        If Not count = 0 Then
            Return CheckForEmpty = True
        Else
            Return CheckForEmpty = False
        End If
    End Function
    Function Multi_fillComboBGI(ByVal field1 As String, ByVal field2 As String, ByVal sert As String, ByVal showtext As String, ByVal mode As Short, ByVal ParamArray Controls() As WebControl) As Boolean
        Dim connect As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim Command As New OracleCommand("", connect)
        Dim SQLadapter As New OracleDataAdapter
        Dim DataSet_tm As New DataSet
        Dim count As Integer
        Command.CommandText = sert
        SQLadapter = New OracleDataAdapter(Command)
        If Not connect.State = ConnectionState.Open Then
            connect.Open()
        End If
        SQLadapter.Fill(DataSet_tm)
        For i As Integer = 0 To UBound(Controls, 1)
            CType(Controls(i), DropDownList).Items.Clear()
            count = DataSet_tm.Tables(0).Rows.Count
            If mode = 1 Then
                CType(Controls(i), DropDownList).Items.Add(New ListItem(showtext, 0))
            End If
            Dim IJ As Integer = 0
            While IJ < count
                Dim NewListItem As ListItem = New ListItem(DataSet_tm.Tables(0).Rows(IJ).Item(field1).ToString, DataSet_tm.Tables(0).Rows(IJ).Item(field2).ToString)
                CType(Controls(i), DropDownList).Items.Add(NewListItem)
                IJ = IJ + 1
            End While
            'While count > 0
            '    Dim NewListItem As ListItem = New ListItem(DataSet_tm.Tables(0).Rows(count - 1).Item(field1).ToString, DataSet_tm.Tables(0).Rows(count - 1).Item(field2).ToString)
            '    CType(Controls(i), DropDownList).Items.Add(NewListItem)
            '    count = count - 1
            'End While
        Next
        If Not connect.State = ConnectionState.Closed Then
            connect.Close()
        End If
        SQLadapter.Dispose()
    End Function
    Sub fillComboBGI(ByVal DDL As DropDownList, ByVal field1 As String, ByVal field2 As String, ByVal sert As String, ByVal showtext As String, ByVal mode As Short)
        Dim connect As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim Command As New OracleCommand(sert, connect)
        Dim SQLadapter As OracleDataAdapter = New OracleDataAdapter(Command)
        Dim DataSet_tm As New DataSet("Table01")
        Dim count As Integer
        If Not connect.State = ConnectionState.Open Then
            connect.Open()
        End If

        SQLadapter.Fill(DataSet_tm)
        DDL.Items.Clear()
        count = DataSet_tm.Tables(0).Rows.Count
        If mode = 1 Then
            DDL.Items.Add(New ListItem(showtext, 0))
        End If
        Dim IJ As Integer = 0
        While IJ < count
            Dim NewListItem As ListItem = New ListItem(DataSet_tm.Tables(0).Rows(IJ).Item(field1).ToString, DataSet_tm.Tables(0).Rows(IJ).Item(field2).ToString)
            DDL.Items.Add(NewListItem)
            IJ = IJ + 1
        End While
        DataSet_tm.Dispose()
        If Not connect.State = ConnectionState.Closed Then
            connect.Close()
        End If
    End Sub
    Sub fillListBGI(ByVal DDL As ListBox, ByVal field1 As String, ByVal field2 As String, ByVal sert As String)
        Dim connect As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim Command As New OracleCommand(sert, connect)
        Dim SQLadapter As OracleDataAdapter = New OracleDataAdapter(Command)
        Dim DataSet_tm As New DataSet
        Dim count As Integer
        If Not connect.State = ConnectionState.Open Then
            connect.Open()
        End If
        SQLadapter.Fill(DataSet_tm)
        DDL.Items.Clear()
        count = DataSet_tm.Tables(0).Rows.Count
        Dim IJ As Integer = 0
        While IJ < count
            Dim NewListItem As ListItem = New ListItem(DataSet_tm.Tables(0).Rows(IJ).Item(field1).ToString, DataSet_tm.Tables(0).Rows(IJ).Item(field2).ToString)
            DDL.Items.Add(NewListItem)
            IJ = IJ + 1
        End While
        DataSet_tm.Dispose()
        If Not connect.State = ConnectionState.Closed Then
            connect.Close()
        End If
    End Sub
    Function Formatdate(ByVal s As String, ByVal formats As String) As String
        If s.Length > 0 Then
            Formatdate = Format(DateTime.Parse(s), formats)
        Else
            Formatdate = Nothing
        End If

    End Function
    Public Function ccExecuteParamQuery(ByVal sert As String, ByVal params() As OracleParameter, ByRef outParam As OracleParameter, ByVal err As Label) As Integer
        Dim result As Integer = 0
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim myTrans As OracleTransaction
        conn.Open()
        myTrans = conn.BeginTransaction()
        Dim intCommand As New OracleCommand
        intCommand.Transaction = myTrans
        intCommand.Connection = conn
        Dim j As Integer
        For j = 0 To params.Length - 1
            intCommand.Parameters.Add(params(j))
        Next j
        If Not outParam Is Nothing Then
            outParam.Direction = ParameterDirection.Output
            intCommand.Parameters.Add(outParam)
        End If
        Try
            intCommand.CommandText = sert
            result = intCommand.ExecuteNonQuery()
            myTrans.Commit()
        Catch ex As Exception
            err.Text = ex.Message
            result = 0
            myTrans.Rollback()
        Finally
            conn.Close()
        End Try
        Return result
    End Function
    Public Function ccExecuteParamQuery(ByVal sert As String, ByVal params() As OracleParameter, ByRef outParam As OracleParameter) As Integer
        Dim result As Integer = 0
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim myTrans As OracleTransaction
        conn.Open()
        myTrans = conn.BeginTransaction()
        Dim intCommand As New OracleCommand
        intCommand.Transaction = myTrans
        intCommand.Connection = conn
        Dim j As Integer
        For j = 0 To params.Length - 1
            intCommand.Parameters.Add(params(j))
        Next j
        If Not outParam Is Nothing Then
            outParam.Direction = ParameterDirection.Output
            intCommand.Parameters.Add(outParam)
        End If
        Try
            intCommand.CommandText = sert
            result = intCommand.ExecuteNonQuery()
            myTrans.Commit()
        Catch ex As Exception
            result = 0
            myTrans.Rollback()
        Finally
            conn.Close()
        End Try
        Return result
    End Function

    Sub WriteToLogR(ByVal erors_str As String, ByVal file_name As String)
        Dim il As String
        Dim ay As String

        file_name = file_name & ".rq"

        Dim DateOfCreation As DateTime = Date.Today
        il = DateOfCreation.Year.ToString
        ay = DateOfCreation.Month.ToString
        Dim myfolderil As New System.IO.DirectoryInfo(Folder & "\" & il)
        If myfolderil.Exists Then
            Dim myfolderay As New System.IO.DirectoryInfo(Folder & "\" & il & "\" & ay)
            If myfolderay.Exists Then
                Try
                    Dim sw As StreamWriter
                    If File.Exists(Folder & "\" & il & "\" & ay & "\" & Replace(file_name, ":", "")) Then
                        sw = File.AppendText(Folder & "\" & il & "\" & ay & "\" & Replace(file_name, ":", ""))
                    Else
                        sw = New StreamWriter(Folder & "\" & il & "\" & ay & "\" & Replace(file_name, ":", ""))
                    End If
                    sw.Write(erors_str)
                    sw.Close()
                Catch ex As Exception

                End Try
            Else
                myfolderay.Create()
                Try
                    Dim sw As StreamWriter = New StreamWriter(Folder & "\" & il & "\" & ay & "\" & Replace(file_name, ":", ""))
                    sw.Write(erors_str)
                    sw.Close()
                Catch ex As Exception

                End Try
            End If
        Else
            myfolderil.Create()
            Dim myfolderay As New System.IO.DirectoryInfo(Folder & "\" & il & "\" & ay)
            If myfolderay.Exists Then
                Try
                    Dim sw As StreamWriter = New StreamWriter(Folder & "\" & il & "\" & ay & "\" & Replace(file_name, ":", ""))
                    sw.Write(erors_str)
                    sw.Close()
                Catch ex As Exception

                End Try
            Else
                myfolderay.Create()
                Try
                    Dim sw As StreamWriter = New StreamWriter(Folder & "\" & il & "\" & ay & "\" & Replace(file_name, ":", ""))
                    sw.Write(erors_str)
                    sw.Close()
                Catch ex As Exception

                End Try
            End If
        End If
    End Sub
    Public Sub Show_Sertificate(ByVal MessageBox As UI.HtmlControls.HtmlTable, ByVal messageType As MessageType, ByVal message As String, ByVal litMessage As LinkButton)
        MessageBox.Visible = True
        litMessage.Text = message
        Select Case messageType
            Case messageType.Errors
                MessageBox.Attributes.Add("Class", "error")
            Case messageType.Info
                MessageBox.Attributes.Add("Class", "info")
            Case messageType.Success
                MessageBox.Attributes.Add("Class", "success")
            Case messageType.Warning
                MessageBox.Attributes.Add("Class", "warning")
            Case messageType.SignVerified
                MessageBox.Attributes.Add("Class", "SignVerified")
            Case messageType.SignNotVerified
                MessageBox.Attributes.Add("Class", "SignNotVerified")
            Case messageType.NoneSign
                MessageBox.Attributes.Add("Class", "NoneSign")
        End Select
    End Sub
    Public Sub Show_Sertificate(ByVal MessageBox As UI.HtmlControls.HtmlTable, ByVal messageTyp As MessageType, ByVal message As String)
        MessageBox.Visible = True
        Select Case messageTyp
            Case MessageType.Errors
                MessageBox.Attributes.Add("Class", "error")
            Case MessageType.Info
                MessageBox.Attributes.Add("Class", "info")
            Case MessageType.Success
                MessageBox.Attributes.Add("Class", "success")
            Case MessageType.Warning
                MessageBox.Attributes.Add("Class", "warning")
            Case MessageType.SignVerified
                MessageBox.Attributes.Add("Class", "SignVerified")
            Case MessageType.SignNotVerified
                MessageBox.Attributes.Add("Class", "SignNotVerified")
            Case MessageType.NoneSign
                MessageBox.Attributes.Add("Class", "NoneSign")
        End Select
    End Sub
    Public Sub ShowDoc(ByVal Sys_NO As String)
        If Sys_NO <> "" Then
            If Sys_NO.Substring(0, 3) = "CVB" Then
                Redirect("~/CVB_VIEW.aspx", "_BLANK", "status=no,top=50,left=50,width=550,height=300,scrollbars=no,status=1")
            ElseIf Sys_NO.Substring(0, 3) = "XDM" Or Sys_NO.Substring(0, 3) = "VMT" Then
                Redirect("~/XDM_VIEW.aspx", "_BLANK", "status=no,top=50,left=50,height=800,scrollbars=yes,status=1")
            ElseIf Sys_NO.Substring(0, 3) = "OUT" Then
                Redirect("~/OUT_VIEW.aspx", "_BLANK", "status=no,top=50,left=50,height=800,scrollbars=yes,status=1")
            ElseIf Sys_NO.Substring(0, 3) = "DXL" Then
                Redirect("~/DXLVIEW.aspx", "_BLANK", "status=no,top=50,left=50,height=800,scrollbars=yes,status=1")
            ElseIf Sys_NO.Substring(0, 3) = "CMD" Or Sys_NO.Substring(0, 3) = "QRN" Or Sys_NO.Substring(0, 3) = "GSR" Then
                Redirect("~/CMDVIEW.aspx", "_BLANK", "status=no,top=50,left=50,height=800,scrollbars=yes,status=1")
            ElseIf Sys_NO.Substring(0, 3) = "CMS" Then
                Redirect("~/CMSVIEW.aspx", "_BLANK", "status=no,top=50,left=50,height=800,scrollbars=yes,status=1")
            ElseIf Sys_NO.Substring(0, 3) = "ARS" Then
                Redirect("~/ARS_VIEW.aspx", "_BLANK", "status=no,top=50,left=50,height=800,scrollbars=yes,status=1")
            ElseIf Sys_NO.Substring(0, 3) = "TBR" Then
                Redirect("~/TBR_VIEW.aspx", "_BLANK", "status=no,top=50,left=50,height=800,scrollbars=yes,status=1")
            End If
        End If
    End Sub
    ''Yeni

    Public Function RemoveDuplicateRows(ByVal dTable As DataTable, ByVal colName As String) As DataTable
        Dim hTable As New Hashtable()
        Dim duplicateList As New ArrayList()
        For Each drow__1 As DataRow In dTable.Rows
            If hTable.Contains(drow__1(colName)) Then
                duplicateList.Add(drow__1)
            Else
                hTable.Add(drow__1(colName), String.Empty)
            End If
        Next
        For Each dRow__2 As DataRow In duplicateList
            dTable.Rows.Remove(dRow__2)
        Next
        Return dTable
    End Function
    Public Function OperWithOraProc(ByVal db_conn As OracleConnection, ByVal NAME_STPROC As String, ByVal Int_Str As String, ByVal Up_Low As String, ByVal ParamArray Controls() As WebControl) As String
        Dim theInt As Integer
        Dim theDate As DateTime
        Dim theNum As Decimal

        Dim conn As New OracleConnection(db_conn.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        Dim RTN As String = "0"
        OrclCmd.CommandType = CommandType.StoredProcedure
        For i As Integer = 0 To UBound(Controls, 1)
            Dim OrclParm As OracleParameter = New OracleParameter
            Dim Value As String = ""
            If TypeOf Controls(i) Is TextBox Then
                If Integer.TryParse(CType(Controls(i), TextBox).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), TextBox).Text
                ElseIf Decimal.TryParse(CType(Controls(i), TextBox).Text, theNum) Then
                    OrclParm.OracleType = OracleType.Double
                    OrclParm.Value = CType(Controls(i), TextBox).Text
                Else
                    If Up_Low = "Up" Then
                        OrclParm.OracleType = OracleType.NVarChar
                        OrclParm.Value = CType(Controls(i), TextBox).Text.ToUpper
                    Else
                        OrclParm.OracleType = OracleType.NVarChar
                        OrclParm.Value = CType(Controls(i), TextBox).Text
                    End If
                End If
            ElseIf TypeOf Controls(i) Is DropDownList Then
                If Integer.TryParse(CType(Controls(i), DropDownList).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), DropDownList).SelectedValue
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), DropDownList).SelectedValue.ToUpper
                End If
            ElseIf TypeOf Controls(i) Is RadioButtonList Then
                Value = "'" + CType(Controls(i), RadioButtonList).SelectedValue + "'"
            ElseIf TypeOf Controls(i) Is Label Then
                If Integer.TryParse(CType(Controls(i), Label).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), Label).Text
                ElseIf Date.TryParse(CType(Controls(i), Label).Text, theDate) Then
                    OrclParm.OracleType = OracleType.DateTime
                    OrclParm.Value = CType(Controls(i), Label).Text
                ElseIf Decimal.TryParse(CType(Controls(i), Label).Text, theNum) Then
                    OrclParm.OracleType = OracleType.Double
                    OrclParm.Value = CType(Controls(i), Label).Text
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), Label).Text
                End If
            ElseIf TypeOf Controls(i) Is CheckBox Then
                OrclParm.OracleType = OracleType.Int32
                OrclParm.Value = Math.Abs(CType(CType(Controls(i), CheckBox).Checked, Integer))
            ElseIf TypeOf Controls(i) Is ListBox Then
                If CType(Controls(i), ListBox).Items.Count = 1 Then
                    If Integer.TryParse(CType(Controls(i), ListBox).Items(0).Value, theInt) Then
                        OrclParm.OracleType = OracleType.Int32
                        OrclParm.Value = CType(Controls(i), ListBox).Items(0).Value
                    Else
                        If Up_Low = "Up" Then
                            OrclParm.OracleType = OracleType.NVarChar
                            OrclParm.Value = CType(Controls(i), ListBox).Items(0).Value.ToUpper
                        Else
                            OrclParm.OracleType = OracleType.NVarChar
                            OrclParm.Value = CType(Controls(i), ListBox).Items(0).Value
                        End If
                    End If
                End If
                If CType(Controls(i), ListBox).Items.Count > 1 Then
                    Dim str_str As String
                    For j As Integer = 0 To CType(Controls(i), ListBox).Items.Count - 1
                        str_str += "," & CType(Controls(i), ListBox).Items(j).Value
                    Next
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = str_str.Substring(1)
                End If
                If CType(Controls(i), ListBox).Items.Count = 0 Then
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = DBNull.Value
                End If
            ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxEditors.ASPxComboBox Then
                If Integer.TryParse(CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).Value, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).SelectedItem.Value
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).SelectedItem.Value
                End If
            ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxEditors.ASPxDateEdit Then
                If CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxDateEdit).Value Is Nothing Then
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = DBNull.Value
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = DateTime.ParseExact(Formatdate(CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxDateEdit).Value, "dd.MM.yyyy"), "dd.MM.yyyy", Nothing)
                End If
            ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxGridLookup.ASPxGridLookup Then
                If Integer.TryParse(CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value
                End If
            End If

            OrclParm.ParameterName = "P_" & Controls(i).ID.ToUpper
            OrclCmd.Parameters.Add(OrclParm)
        Next i
        If Int_Str = "Int" Then
            OrclCmd.Parameters.Add("RTN_ID", OracleType.Int32).Direction = ParameterDirection.Output
        Else
            OrclCmd.Parameters.Add("RTN_STR", OracleType.NVarChar).Direction = ParameterDirection.Output
        End If
        Dim myTrans As OracleTransaction
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If
        myTrans = conn.BeginTransaction()
        Try
            OrclCmd.Transaction = myTrans
            OrclCmd.ExecuteNonQuery()
            If Int_Str = "Int" Then
                RTN = OrclCmd.Parameters("RTN_ID").Value.ToString()
            Else
                RTN = OrclCmd.Parameters("RTN_STR").Value.ToString()
            End If
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "OperWithOraProc FUNCTION IN Data.vb", NAME_STPROC)
            Return "0"
        Finally
            myTrans.Dispose()
            OrclCmd.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return RTN
    End Function
    Public Function OperWithOraPro(ByVal db_conn As OracleConnection, ByVal NAME_STPROC As String, ByVal Int_Str As String, ByVal Up_Low As String, ByVal data() As String, ByVal params() As String, ByVal types() As String, ByVal ParamArray Controls() As WebControl) As String
        Dim theInt As Integer
        Dim theDate As DateTime
        Dim theNum As Decimal

        Dim conn As New OracleConnection(db_conn.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        Dim RTN As String = "0"
        OrclCmd.CommandType = CommandType.StoredProcedure

        For i As Integer = 0 To UBound(Controls, 1)
            Dim OrclParm As OracleParameter = New OracleParameter
            Dim Value As String = ""
            If TypeOf Controls(i) Is TextBox Then
                If Integer.TryParse(CType(Controls(i), TextBox).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), TextBox).Text
                ElseIf Decimal.TryParse(CType(Controls(i), TextBox).Text, theNum) Then
                    OrclParm.OracleType = OracleType.Double
                    OrclParm.Value = CType(Controls(i), TextBox).Text
                Else
                    If Up_Low = "Up" Then
                        OrclParm.OracleType = OracleType.NVarChar
                        OrclParm.Value = CType(Controls(i), TextBox).Text.ToUpper
                    Else
                        OrclParm.OracleType = OracleType.NVarChar
                        OrclParm.Value = CType(Controls(i), TextBox).Text
                    End If
                End If
            ElseIf TypeOf Controls(i) Is DropDownList Then
                If Integer.TryParse(CType(Controls(i), DropDownList).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), DropDownList).SelectedValue
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), DropDownList).SelectedValue.ToUpper
                End If
            ElseIf TypeOf Controls(i) Is RadioButtonList Then
                Value = "'" + CType(Controls(i), RadioButtonList).SelectedValue + "'"
            ElseIf TypeOf Controls(i) Is Label Then
                If Integer.TryParse(CType(Controls(i), Label).Text, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), Label).Text
                ElseIf Date.TryParse(CType(Controls(i), Label).Text, theDate) Then
                    OrclParm.OracleType = OracleType.DateTime
                    OrclParm.Value = CType(Controls(i), Label).Text
                ElseIf Decimal.TryParse(CType(Controls(i), Label).Text, theNum) Then
                    OrclParm.OracleType = OracleType.Double
                    OrclParm.Value = CType(Controls(i), Label).Text
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), Label).Text
                End If
            ElseIf TypeOf Controls(i) Is CheckBox Then
                OrclParm.OracleType = OracleType.Int32
                OrclParm.Value = Math.Abs(CType(CType(Controls(i), CheckBox).Checked, Integer))
            ElseIf TypeOf Controls(i) Is ListBox Then
                If CType(Controls(i), ListBox).Items.Count = 1 Then
                    If Integer.TryParse(CType(Controls(i), ListBox).Items(0).Value, theInt) Then
                        OrclParm.OracleType = OracleType.Int32
                        OrclParm.Value = CType(Controls(i), ListBox).Items(0).Value
                    Else
                        If Up_Low = "Up" Then
                            OrclParm.OracleType = OracleType.NVarChar
                            OrclParm.Value = CType(Controls(i), ListBox).Items(0).Value.ToUpper
                        Else
                            OrclParm.OracleType = OracleType.NVarChar
                            OrclParm.Value = CType(Controls(i), ListBox).Items(0).Value
                        End If
                    End If
                End If
                If CType(Controls(i), ListBox).Items.Count > 1 Then
                    Dim str_str As String
                    For k As Integer = 0 To CType(Controls(i), ListBox).Items.Count - 1
                        str_str += "," & CType(Controls(i), ListBox).Items(k).Value
                    Next
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = str_str.Substring(1)
                End If
                If CType(Controls(i), ListBox).Items.Count = 0 Then
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = DBNull.Value
                End If
            ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxEditors.ASPxComboBox Then
                If Integer.TryParse(CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).Value, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).SelectedItem.Value
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxComboBox).SelectedItem.Value
                End If
            ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxEditors.ASPxDateEdit Then
                If CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxDateEdit).Value Is Nothing Then
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = DBNull.Value
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = Formatdate(CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxDateEdit).Value, "dd.MM.yyyy")
                    'OrclParm.Value = DateTime.ParseExact(Formatdate(CType(Controls(i), DevExpress.Web.ASPxEditors.ASPxDateEdit).Value, "dd.MM.yyyy"), "dd.MM.yyyy", Nothing)
                End If
            ElseIf TypeOf Controls(i) Is DevExpress.Web.ASPxGridLookup.ASPxGridLookup Then
                If Integer.TryParse(CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value, theInt) Then
                    OrclParm.OracleType = OracleType.Int32
                    OrclParm.Value = CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value
                Else
                    OrclParm.OracleType = OracleType.NVarChar
                    OrclParm.Value = CType(Controls(i), DevExpress.Web.ASPxGridLookup.ASPxGridLookup).Value
                End If
            End If

            OrclParm.ParameterName = "P_" & Controls(i).ID.ToUpper
            OrclCmd.Parameters.Add(OrclParm)
        Next i

        Dim j As Integer
        For j = 0 To params.Length - 1
            If types(j) = "OracleType.Clob" Then
                OrclCmd.Parameters.Add(New OracleParameter(params(j), OracleType.Clob)).Value = data(j)
            Else
                OrclCmd.Parameters.Add(New OracleParameter(params(j), types(j))).Value = data(j)

            End If
        Next

        If Int_Str = "Int" Then
            OrclCmd.Parameters.Add("RTN_ID", OracleType.Int32).Direction = ParameterDirection.Output
        Else
            OrclCmd.Parameters.Add("RTN_STR", OracleType.NVarChar).Direction = ParameterDirection.Output
        End If
        Dim myTrans As OracleTransaction
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If
        myTrans = conn.BeginTransaction()
        Try
            OrclCmd.Transaction = myTrans
            OrclCmd.ExecuteNonQuery()
            If Int_Str = "Int" Then
                RTN = OrclCmd.Parameters("RTN_ID").Value.ToString()
            Else
                RTN = OrclCmd.Parameters("RTN_STR").Value.ToString()
            End If
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "OperWithOraPro FUNCTION IN Data.vb", NAME_STPROC)
            RTN = 0
        Finally
            myTrans.Dispose()
            OrclCmd.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return RTN
    End Function
    Function OraProcRtnCursor(ByVal NAME_STPROC As String, ByVal data() As String, ByVal params() As String, ByVal types() As String) As DataTable
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        OrclCmd.CommandType = CommandType.StoredProcedure
        Dim i As Integer
        For i = 0 To params.Length - 1
            OrclCmd.Parameters.Add(New OracleParameter(params(i), types(i))).Value = data(i)
        Next
        OrclCmd.Parameters.Add("data_set", OracleType.Cursor).Direction = ParameterDirection.Output
        Dim OrclDA As New OracleDataAdapter(OrclCmd)
        Dim RtnTable As DataTable = New DataTable()
        Try
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            OrclDA.Fill(RtnTable)
        Catch ex As Exception
            WriteToLogR(ex.Message.ToString & " " & NAME_STPROC, "data")
            ' WriteToLog(ex.Message.ToString, "ReturnCursor IN DATA.VB", NAME_STPROC)
        Finally
            OrclDA.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return RtnTable
    End Function
    Function OraProcRtnInt(ByVal NAME_STPROC As String, ByVal data() As String, ByVal params() As String, ByVal types() As String) As Integer
        Dim conn As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        Dim NUM As Integer = 0
        OrclCmd.CommandType = CommandType.StoredProcedure
        Dim i As Integer
        For i = 0 To params.Length - 1
            If types(i) = "OracleType.Clob" Then
                OrclCmd.Parameters.Add(New OracleParameter(params(i), OracleType.Clob)).Value = data(i)
            Else
                OrclCmd.Parameters.Add(New OracleParameter(params(i), types(i))).Value = data(i)
            End If
        Next
        OrclCmd.Parameters.Add("RTN_ID", OracleType.Int32).Direction = ParameterDirection.Output
        Dim myTrans As OracleTransaction
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If
        myTrans = conn.BeginTransaction()
        Try
            OrclCmd.Transaction = myTrans
            OrclCmd.ExecuteNonQuery()
            NUM = CType(OrclCmd.Parameters("RTN_ID").Value.ToString(), Integer)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "ReturnProcInt IN DATA.VB", NAME_STPROC)
            NUM = 0
        Finally
            myTrans.Dispose()
            OrclCmd.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return NUM
    End Function
    Function OraProcRtnInt(ByVal db_conn As OracleConnection, ByVal NAME_STPROC As String, ByVal data() As String, ByVal params() As String, ByVal types() As String) As Integer
        Dim conn As New OracleConnection(db_conn.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        Dim NUM As Integer = 0
        OrclCmd.CommandType = CommandType.StoredProcedure
        Dim i As Integer
        For i = 0 To params.Length - 1
            If types(i) = "OracleType.Clob" Then
                OrclCmd.Parameters.Add(New OracleParameter(params(i), OracleType.Clob)).Value = data(i)
            Else
                OrclCmd.Parameters.Add(New OracleParameter(params(i), types(i))).Value = data(i)
            End If
        Next
        OrclCmd.Parameters.Add("RTN_ID", OracleType.Int32).Direction = ParameterDirection.Output
        Dim myTrans As OracleTransaction
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If
        myTrans = conn.BeginTransaction()
        Try
            OrclCmd.Transaction = myTrans
            OrclCmd.ExecuteNonQuery()
            NUM = CType(OrclCmd.Parameters("RTN_ID").Value.ToString(), Integer)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "ReturnProcInt IN DATA.VB", NAME_STPROC)
            NUM = 0
        Finally
            myTrans.Dispose()
            OrclCmd.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return NUM
    End Function
    Function OraProRtnCursor(ByVal db_conn As OracleConnection, ByVal NAME_STPROC As String, ByVal data() As String, ByVal params() As String, ByVal types() As String) As DataTable
        Dim conn As New OracleConnection(db_conn.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        OrclCmd.CommandType = CommandType.StoredProcedure
        Dim i As Integer
        For i = 0 To params.Length - 1
            OrclCmd.Parameters.Add(New OracleParameter(params(i), types(i))).Value = data(i)
        Next
        OrclCmd.Parameters.Add("data_set", OracleType.Cursor).Direction = ParameterDirection.Output
        Dim OrclDA As New OracleDataAdapter(OrclCmd)
        Dim RtnTable As DataTable = New DataTable()
        Try
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            OrclDA.Fill(RtnTable)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "ReturnCursor IN DATA.VB", NAME_STPROC)
        Finally
            OrclDA.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return RtnTable
    End Function
    Function OraPrcRtnInt(ByVal db_conn As OracleConnection, ByVal NAME_STPROC As String, ByVal data() As String, ByVal params() As String, ByVal types() As String) As Integer
        Dim conn As New OracleConnection(db_conn.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        Dim NUM As Integer = 0
        OrclCmd.CommandType = CommandType.StoredProcedure
        Dim i As Integer
        For i = 0 To params.Length - 1
            If types(i) = "OracleType.Clob" Then
                OrclCmd.Parameters.Add(New OracleParameter(params(i), OracleType.Clob)).Value = data(i)
            Else
                OrclCmd.Parameters.Add(New OracleParameter(params(i), types(i))).Value = data(i)
            End If
        Next
        OrclCmd.Parameters.Add("RTN_ID", OracleType.Int32).Direction = ParameterDirection.Output
        Dim myTrans As OracleTransaction
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If
        myTrans = conn.BeginTransaction()
        Try
            OrclCmd.Transaction = myTrans
            OrclCmd.ExecuteNonQuery()
            NUM = CType(OrclCmd.Parameters("RTN_ID").Value.ToString(), Integer)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "ReturnProcInt IN DATA.VB", NAME_STPROC)
            NUM = 0
        Finally
            myTrans.Dispose()
            OrclCmd.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return NUM
    End Function
    Function OraFncRtnInt(ByVal db_conn As OracleConnection, ByVal NAME_STPROC As String, ByVal data() As String, ByVal params() As String, ByVal types() As String) As Integer
        Dim conn As New OracleConnection(db_conn.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        Dim NUM As Integer = 0
        OrclCmd.CommandType = CommandType.StoredProcedure
        Dim RTN_NUM As OracleParameter = New OracleParameter("RTN_ID", OracleType.Int32)
        RTN_NUM.Direction = ParameterDirection.ReturnValue
        OrclCmd.Parameters.Add(RTN_NUM)
        Dim i As Integer
        For i = 0 To params.Length - 1
            OrclCmd.Parameters.Add(New OracleParameter(params(i), types(i))).Value = data(i)
        Next
        Try
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            OrclCmd.ExecuteNonQuery()
            NUM = CType(OrclCmd.Parameters("RTN_ID").Value.ToString(), Integer)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "ReturnProcInt IN DATA.VB", NAME_STPROC)
            NUM = 1
        Finally
            OrclCmd.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return NUM
    End Function
    Function OraFncRtnStr(ByVal db_conn As OracleConnection, ByVal NAME_STPROC As String, ByVal data() As String, ByVal params() As String, ByVal types() As String) As String
        Dim conn As New OracleConnection(db_conn.ConnectionString)
        Dim OrclCmd As New OracleCommand(NAME_STPROC, conn)
        Dim Str As String = ""
        OrclCmd.CommandType = CommandType.StoredProcedure
        Dim RTN_STR As OracleParameter = New OracleParameter("RTN_STR", OracleType.NVarChar, 2000)
        RTN_STR.Direction = ParameterDirection.ReturnValue
        OrclCmd.Parameters.Add(RTN_STR)
        Dim i As Integer
        For i = 0 To params.Length - 1
            OrclCmd.Parameters.Add(New OracleParameter(params(i), types(i))).Value = data(i)
        Next
        Try
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            OrclCmd.ExecuteNonQuery()
            Str = OrclCmd.Parameters("RTN_STR").Value.ToString()
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "OraFncRtnStr IN DATA.VB", NAME_STPROC)
        Finally
            OrclCmd.Dispose()
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return Str
    End Function
    Function ExecuteQuery_CON(ByVal db_conn As OracleConnection, ByVal sert As String) As Boolean
        ExecuteQuery_CON = True
        Dim myTrans As OracleTransaction
        Dim connect As New OracleConnection(db_conn.ConnectionString)
        Dim intCommand As New OracleCommand
        intCommand.Connection = connect
        If Not connect.State = ConnectionState.Open Then
            connect.Open()
        End If
        myTrans = connect.BeginTransaction()
        intCommand.Transaction = myTrans
        Try
            intCommand.CommandText = sert
            intCommand.ExecuteNonQuery()
            myTrans.Commit()
        Catch ex As Exception
            WriteToLog(ex.ToString, "ExecuteQuery IN DATA.VB", sert)
            myTrans.Rollback()
            ExecuteQuery_CON = False
        Finally
            If Not connect.State = ConnectionState.Closed Then
                connect.Close()
            End If
        End Try
    End Function
    Public Function ExecutBindNonSql(ByVal db_conn As OracleConnection, ByVal SqlStr As String, ByVal parm As CmdParameter()) As Boolean
        Dim conn As New OracleConnection(db_conn.ConnectionString)
        Dim cmd As New OracleCommand(SqlStr, conn)
        Dim cp As CmdParameter
        Try
            For Each cp In parm
                cmd.Parameters.Add(cp.Parameter)
            Next
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "ExecBindNonSql FUNCTION IN Data.vb", SqlStr)
            conn.Close()
            Return False
        Finally
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            cmd.Dispose()
        End Try
        Return True
    End Function
    Function ExecuteParamQuery(ByVal sert As String, ByVal params() As OracleParameter, ByVal err As Label) As Integer
        Dim result As Integer = 0
        Dim myTrans As OracleTransaction
        nCONN_VP.Open()
        myTrans = nCONN_VP.BeginTransaction()
        Dim intCommand As New OracleCommand
        intCommand.Transaction = myTrans
        intCommand.Connection = nCONN_VP
        Dim j As Integer
        For j = 0 To params.Length - 1
            intCommand.Parameters.Add(params(j))
        Next j

        Try
            intCommand.CommandText = sert
            result = intCommand.ExecuteNonQuery()
            myTrans.Commit()
        Catch ex As Exception
            err.Text = ex.Message
            result = 0
            myTrans.Rollback()
        End Try
        nCONN_VP.Close()
        Return result
    End Function
    Function RTN_STR(ByVal str As String) As String
        Dim connect As New OracleConnection(nCONN_KZR.ConnectionString)
        Dim myCommand As New System.Data.OracleClient.OracleCommand(str, connect)
        Dim SQLReader As System.Data.OracleClient.OracleDataReader
        Try
            If Not connect.State = ConnectionState.Open Then
                connect.Open()
            End If
            SQLReader = myCommand.ExecuteReader()
            SQLReader.Read()
            If SQLReader.HasRows Then
                RTN_STR = SQLReader.GetValue(0).ToString()
            Else
                RTN_STR = ""
            End If
            SQLReader.Close()
        Catch ex As Exception
            RTN_STR = "-1"
            WriteToLog(ex.Message.ToString, "RTN_STR FUNCTION IN Data.vb", str)
            SQLReader.Close()
        End Try
        If Not connect.State = ConnectionState.Closed Then
            connect.Close()
        End If
    End Function
    Function RTN_STR(ByVal db_conn As OracleConnection, ByVal str As String) As String
        Dim connect As New OracleConnection(db_conn.ConnectionString)
        Dim myCommand As New System.Data.OracleClient.OracleCommand(str, connect)
        Dim SQLReader As System.Data.OracleClient.OracleDataReader
        Try
            If Not connect.State = ConnectionState.Open Then
                connect.Open()
            End If
            SQLReader = myCommand.ExecuteReader()
            SQLReader.Read()
            If SQLReader.HasRows Then
                RTN_STR = SQLReader.GetValue(0).ToString()
            Else
                RTN_STR = ""
            End If
            SQLReader.Close()
        Catch ex As Exception
            RTN_STR = "-1"
            WriteToLog(ex.Message.ToString, "RTN_STR FUNCTION IN Data.vb", str)
            SQLReader.Close()
        End Try
        If Not connect.State = ConnectionState.Closed Then
            connect.Close()
        End If
    End Function
    Function RTN_VALUE_WITH_PS(ByVal db_conn As OracleConnection, ByVal SqlStr As String, ByVal parm As CmdParameter())
        Dim connect As New OracleConnection(db_conn.ConnectionString)
        Dim myCommand As New OracleCommand(SqlStr, connect)
        Dim SQLReader As OracleDataReader
        Dim cp As CmdParameter
        Try
            For Each cp In parm
                myCommand.Parameters.Add(cp.Parameter)
            Next
            If Not connect.State = ConnectionState.Open Then
                connect.Open()
            End If
            SQLReader = myCommand.ExecuteReader()
            SQLReader.Read()
            RTN_VALUE_WITH_PS = SQLReader.GetValue(0).ToString()
            SQLReader.Close()
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "RTN_VALUE_WITH_PS FUNCTION IN Data.vb", SqlStr)
            RTN_VALUE_WITH_PS = "-1"
            SQLReader.Close()
        End Try
        If Not connect.State = ConnectionState.Closed Then
            connect.Close()
        End If
    End Function
    Function RTN_VALUE_WITH_BIND_VALUE(ByVal db_conn As OracleConnection, ByVal SqlStr As String, ByVal value_name As String, ByVal value_type As String, ByVal value As String)
        Dim connect As New OracleConnection(db_conn.ConnectionString)
        Dim myCommand As New OracleCommand(SqlStr, connect)
        Dim SQLReader As OracleDataReader
        Try
            myCommand.Parameters.Add(New OracleParameter(value_name, value_type)).Value = value
            If Not connect.State = ConnectionState.Open Then
                connect.Open()
            End If
            SQLReader = myCommand.ExecuteReader()
            SQLReader.Read()
            RTN_VALUE_WITH_BIND_VALUE = SQLReader.GetValue(0).ToString()
            SQLReader.Close()
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "RTN_VALUE_WITH_BIND_VALUE FUNCTION IN Data.vb", SqlStr & " - " & value)
            RTN_VALUE_WITH_BIND_VALUE = "-1"
            SQLReader.Close()
        End Try
        If Not connect.State = ConnectionState.Closed Then
            connect.Close()
        End If
    End Function
    Function RTN_VALUE_WITH_BIND_MAS(ByVal db_conn As OracleConnection, ByVal SqlStr As String, ByVal data() As String, ByVal params() As String, ByVal types() As String)
        Dim connect As New OracleConnection(db_conn.ConnectionString)
        Dim myCommand As New OracleCommand(SqlStr, connect)
        Dim SQLReader As OracleDataReader
        Try
            Dim i As Integer = 0
            For i = 0 To params.Length - 1
                myCommand.Parameters.Add(New OracleParameter(params(i), types(i))).Value = data(i)
            Next
            If Not connect.State = ConnectionState.Open Then
                connect.Open()
            End If
            SQLReader = myCommand.ExecuteReader()
            SQLReader.Read()
            RTN_VALUE_WITH_BIND_MAS = SQLReader.GetValue(0).ToString()
            SQLReader.Close()
        Catch ex As Exception
            RTN_VALUE_WITH_BIND_MAS = "-1"
            SQLReader.Close()
        End Try
        If Not connect.State = ConnectionState.Closed Then
            connect.Close()
        End If
    End Function
    Function RtnTable(ByVal db_conn As OracleConnection, ByVal SqlStr As String) As DataTable
        Dim conn As New OracleConnection(db_conn.ConnectionString)
        Dim da As OracleDataAdapter = New OracleDataAdapter(SqlStr, conn)
        Dim dt As DataTable = New DataTable()
        Try
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            da.Fill(dt)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "RetTable FUNCTION IN Data.vb", SqlStr)
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            da.Dispose()
        Finally
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            da.Dispose()
        End Try
        Return dt
    End Function
    Function RtnTable_BIND_VALUE(ByVal db_conn As OracleConnection, ByVal SqlStr As String, ByVal value_name As String, ByVal value_type As String, ByVal value As String) As DataTable
        Dim conn As New OracleConnection(db_conn.ConnectionString)
        Dim da As OracleDataAdapter = New OracleDataAdapter(SqlStr, conn)
        Dim dt As DataTable = New DataTable()
        Dim cp As CmdParameter
        Try
            da.SelectCommand.Parameters.Add(New OracleParameter(value_name, value_type)).Value = value
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            da.Fill(dt)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "RtnTable_BIND_VALUE FUNCTION IN Data.vb", SqlStr)
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            da.Dispose()
        Finally
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            da.Dispose()
        End Try
        Return dt
    End Function
    Function RtnTable_BIND_MAS(ByVal db_conn As OracleConnection, ByVal SqlStr As String, ByVal data() As String, ByVal params() As String, ByVal types() As String) As DataTable
        Dim conn As New OracleConnection(db_conn.ConnectionString)
        Dim da As OracleDataAdapter = New OracleDataAdapter(SqlStr, conn)
        Dim dt As DataTable = New DataTable()
        Try
            Dim i As Integer = 0
            For i = 0 To params.Length - 1
                da.SelectCommand.Parameters.Add(New OracleParameter(params(i), types(i))).Value = data(i)
            Next
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            da.Fill(dt)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "RtnTable_BIND_MAS FUNCTION IN Data.vb", SqlStr)
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            da.Dispose()
        Finally
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            da.Dispose()
        End Try
        Return dt
    End Function
    Function RtnTable_WITH_PS(ByVal db_conn As OracleConnection, ByVal SqlStr As String, ByVal parm As CmdParameter()) As DataTable
        Dim conn As New OracleConnection(db_conn.ConnectionString)
        Dim da As OracleDataAdapter = New OracleDataAdapter(SqlStr, conn)
        Dim dt As DataTable = New DataTable()
        Dim cp As CmdParameter
        Try
            For Each cp In parm
                da.SelectCommand.Parameters.Add(cp.Parameter)
            Next
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            da.Fill(dt)
        Catch ex As Exception
            WriteToLog(ex.Message.ToString, "RtnTable_WITH_PS FUNCTION IN Data.vb", SqlStr)
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            da.Dispose()
        Finally
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
            da.Dispose()
        End Try
        Return dt
    End Function
    Function CON_Multi_fillComboBGI(ByVal conn As OracleConnection, ByVal field1 As String, ByVal field2 As String, ByVal sert As String, ByVal showtext As String, ByVal mode As Short, ByVal ParamArray Controls() As WebControl) As Boolean
        Dim DataSet_tm As DataSet = ccGetDataSet(sert, conn)
        Dim count As Integer
        For i As Integer = 0 To UBound(Controls, 1)
            CType(Controls(i), DropDownList).Items.Clear()
            count = DataSet_tm.Tables(0).Rows.Count
            If mode = 1 Then
                CType(Controls(i), DropDownList).Items.Add(New ListItem(showtext, "-1"))
            ElseIf mode = 2 Then
                CType(Controls(i), DropDownList).Items.Add(New ListItem(showtext, "0"))
            End If
            While count > 0
                Dim NewListItem As ListItem = New ListItem(DataSet_tm.Tables(0).Rows(count - 1).Item(field1).ToString, DataSet_tm.Tables(0).Rows(count - 1).Item(field2).ToString)
                CType(Controls(i), DropDownList).Items.Add(NewListItem)
                count = count - 1
            End While
        Next
    End Function
    Public Function ccGetDataSet(ByVal sql As String, ByVal NCONN As OracleConnection) As DataSet
        Dim ds As New DataSet
        Dim Command As New OracleCommand("", NCONN)
        Dim SQLadapter As New OracleDataAdapter
        Command.CommandText = sql
        SQLadapter = New OracleDataAdapter(Command)
        SQLadapter.Fill(ds)
        Return ds
    End Function
    Sub WriteToLog(ByVal erors_str As String, ByVal file_name As String)
        Dim il As String
        Dim ay As String

        file_name = file_name & ".rq"

        Dim DateOfCreation As DateTime = Date.Today
        il = DateOfCreation.Year.ToString
        ay = DateOfCreation.Month.ToString
        Dim myfolderil As New System.IO.DirectoryInfo(Folder & "\" & il)

        If myfolderil.Exists Then
            Dim myfolderay As New System.IO.DirectoryInfo(Folder & "\" & il & "\" & ay)

            If myfolderay.Exists Then
                Try
                    Dim sw As StreamWriter
                    If File.Exists(Folder & "\" & il & "\" & ay & "\" & Replace(file_name, ":", "")) Then
                        sw = File.AppendText(Folder & "\" & il & "\" & ay & "\" & Replace(file_name, ":", ""))
                    Else
                        sw = New StreamWriter(Folder & "\" & il & "\" & ay & "\" & Replace(file_name, ":", ""))
                    End If
                    sw.Write(erors_str)
                    sw.Close()
                Catch ex As Exception

                End Try

            Else
                myfolderay.Create()
                Try
                    Dim sw As StreamWriter = New StreamWriter(Folder & "\" & il & "\" & ay & "\" & Replace(file_name, ":", ""))
                    sw.Write(erors_str)
                    sw.Close()
                Catch ex As Exception

                End Try

            End If
        Else
            myfolderil.Create()
            Dim myfolderay As New System.IO.DirectoryInfo(Folder & "\" & il & "\" & ay)
            If myfolderay.Exists Then
                Try
                    Dim sw As StreamWriter = New StreamWriter(Folder & "\" & il & "\" & ay & "\" & Replace(file_name, ":", ""))
                    sw.Write(erors_str)
                    sw.Close()
                Catch ex As Exception

                End Try
            Else
                myfolderay.Create()
                Try
                    Dim sw As StreamWriter = New StreamWriter(Folder & "\" & il & "\" & ay & "\" & Replace(file_name, ":", ""))
                    sw.Write(erors_str)
                    sw.Close()
                Catch ex As Exception

                End Try

            End If
        End If

    End Sub
    Public Function ccSelectRow(ByVal conn As OracleConnection, ByVal SERT As String, ByRef err As String, ByVal ParamArray Controls() As Control) As Integer
        conn.Open()
        Dim Adapter As New OracleDataAdapter
        Dim DataSet As New DataSet()
        Dim ReadRow As DataRow
        Dim res As Integer = 0
        Try
            Dim Command As New OracleCommand(SERT, conn)
            Adapter = New OracleDataAdapter(Command)
            Adapter.Fill(DataSet)
            res = DataSet.Tables(0).Rows.Count
            If res = 0 Then
                err = "Məlumat tapılmadı"
                For i As Integer = 0 To UBound(Controls, 1)
                    If TypeOf Controls(i) Is TextBox Then
                        CType(Controls(i), TextBox).Text = ""
                    ElseIf TypeOf Controls(i) Is DropDownList Then
                        Try
                            CType(Controls(i), DropDownList).SelectedIndex = 0
                        Catch ex As Exception
                        End Try

                    ElseIf TypeOf Controls(i) Is Label Then
                        CType(Controls(i), Label).Text = ""
                    End If
                Next i
            Else
                ReadRow = DataSet.Tables(0).Rows(0)
                For i As Integer = 0 To UBound(Controls, 1)
                    Dim Value As String = Controls(i).ID.ToUpper
                    If Mid(Controls(i).ID.ToUpper, 1, 4) = "DATE" Then
                        If ReadRow(i).ToString = "" Then
                            Value = ""
                        Else
                            Value = Formatdate(ReadRow(i).ToString, "dd.MM.yyyy")
                        End If

                    Else
                        Value = ReadRow(i).ToString
                    End If

                    If TypeOf Controls(i) Is TextBox Then
                        CType(Controls(i), TextBox).Text = Value.ToString

                    ElseIf TypeOf Controls(i) Is DropDownList Then
                        Try
                            CType(Controls(i), DropDownList).SelectedValue = Value.ToString
                        Catch ex As Exception
                        End Try
                    ElseIf TypeOf Controls(i) Is HiddenField Then
                        CType(Controls(i), HiddenField).Value = Value.ToString
                    ElseIf TypeOf Controls(i) Is Label Then
                        CType(Controls(i), Label).Text = Value.ToString
                    ElseIf TypeOf Controls(i) Is RadioButtonList Then
                        Try
                            CType(Controls(i), RadioButtonList).SelectedValue = Value.ToString
                        Catch ex As Exception
                        End Try
                    End If
                Next i
            End If
        Catch Exc As Exception
            err = ("Xəta baş verdi:" & Exc.Message)
        End Try
        conn.Close()
        Return res
    End Function
    Function getdat_conn(ByVal sorgu As String, ByVal name As String, ByVal conn As OracleConnection)
        Dim str As String
        Dim s As Boolean = False
        Dim db_conn As New OracleConnection(conn.ConnectionString)
        If Not db_conn.State = ConnectionState.Open Then
            db_conn.Open()
        End If
        Try
            Dim Command As New OracleCommand
            Command.Connection = db_conn
            Dim SQLReader As System.Data.OracleClient.OracleDataReader
            Command.CommandText = sorgu
            SQLReader = Command.ExecuteReader()
            While SQLReader.Read
                str = SQLReader.Item(name).ToString
                s = True
            End While
            If s = False Then
                str = ""
            End If
            SQLReader.Close()
        Catch ex As Exception

        End Try
        If Not db_conn.State = ConnectionState.Closed Then
            db_conn.Close()
        End If
        Return str
    End Function
    Function ExecuteInsUpd_Eks(ByVal sert() As String, ByVal lbl As Label) As Boolean
        Dim i, CNT As Short
        ExecuteInsUpd_Eks = True
        Dim myTrans As OracleTransaction
        Dim connect As New OracleConnection(nCONN_VP.ConnectionString)
        If Not connect.State = ConnectionState.Open Then
            connect.Open()
        End If
        myTrans = connect.BeginTransaction()
        Dim intCommand As New OracleCommand
        intCommand.Transaction = myTrans
        intCommand.Connection = connect
        Try
            For i = 0 To sert.Length - 1
                If sert(i) <> "Nothing" Then
                    intCommand.CommandText = sert(i)
                    CNT = intCommand.ExecuteNonQuery()
                    If CNT = 0 Then
                        WriteToLog(Format(Date.Now, "dd.MM.yyyy hh:mm:ss") & vbNewLine & sert(i) & vbNewLine, "ExecuteInsUpd_Eks_RES" & Format(Date.Today, "MM.yyyy"))
                    End If
                End If

            Next
            lbl.ForeColor = Drawing.Color.Black
            lbl.Text = "Məlumat uğurla bazaya yazıldı."
            myTrans.Commit()
        Catch ex As Exception
            ExecuteInsUpd_Eks = False
            myTrans.Rollback()
            lbl.ForeColor = Drawing.Color.Red
            lbl.Text = "Xəta: " & ex.Message
            WriteToLog(ex.Message & vbNewLine & sert(i) & vbNewLine, "ExecuteInsUpd_Eks" & Format(Date.Today, "MM.yyyy"))
        End Try
        If Not connect.State = ConnectionState.Closed Then
            connect.Close()
        End If
    End Function
    Public Function GetIamasAddresStr(ByVal result As Object, ByRef k As Integer) As String
        Dim oDataList As New DataListIamas
        Dim iamas_doc_dict_type As New DropDownList
        Dim COUNTRY_N_DDL As New DropDownList

        BindControlList(oDataList.IAMAS_DOC_LIST, iamas_doc_dict_type)
        BindControlList(oDataList.COUNTRY_DDL, COUNTRY_N_DDL)


        Dim address As String = ""

        Try
            Try
                If k = 2 Then
                    If result.country <> 0 Then
                        COUNTRY_N_DDL.SelectedValue = result.country.ToString
                        address = COUNTRY_N_DDL.SelectedItem.Text + ", "
                    End If
                End If
            Catch ex As Exception

            End Try

            If result.province <> 0 Then
                If k = 2 Then
                    iamas_doc_dict_type.SelectedValue = result.province.ToString + "#9"
                    address = address + iamas_doc_dict_type.SelectedItem.Text + ", "
                Else
                    iamas_doc_dict_type.SelectedValue = result.province.ToString + "#4"
                    address = address + iamas_doc_dict_type.SelectedItem.Text + ", "
                End If

            End If

            Try
                If k = 2 Then
                    If result.foreignCity <> 0 Then
                        address = address + result.foreignCity + " şəh., "
                    End If
                End If
            Catch ex As Exception

            End Try
            If result.city <> 0 Then
                iamas_doc_dict_type.SelectedValue = result.city.ToString + "#2"
                address = address + iamas_doc_dict_type.SelectedItem.Text + " şəh., "
            End If




            If result.district <> 0 Then
                iamas_doc_dict_type.SelectedValue = result.district.ToString + "#3"
                address = address + iamas_doc_dict_type.SelectedItem.Text + " ray., "
            End If

            If Not result.place.ToString = "" Then
                address = address + result.place.ToString + ", "
            End If

            If k = 1 Then
                If Not result.street.ToString = "" Then
                    address = address + result.street.ToString + ", "
                End If
                If Not result.block.ToString = "" Then
                    address = address + "Məh. " + result.block.ToString + ", "
                End If

                If Not result.building.ToString = "" Then
                    address = address + "ev " + result.building.ToString + ", "
                End If

                If Not result.apartment.ToString = "" Then
                    address = address + "m. " + result.apartment.ToString + ", "
                End If

            End If

            Dim idx As Integer
            idx = address.LastIndexOf(",")
            GetIamasAddresStr = address.Substring(0, idx)
        Catch ex As Exception

        End Try

    End Function
    Sub BindControlList(ByVal _Data_list As List(Of Data_list), ByVal ParamArray Controls() As Control)
        For i As Integer = 0 To UBound(Controls, 1)
            CType(Controls(i), DropDownList).DataSource = _Data_list
            CType(Controls(i), DropDownList).DataValueField = "Code"
            CType(Controls(i), DropDownList).DataTextField = "Name"
            CType(Controls(i), DropDownList).DataBind()
        Next
    End Sub
    Function GetDocType(ByVal connect As OracleConnection, ByVal field1 As String, ByVal field2 As String, ByVal sert As String, ByVal showtext As String, ByVal mode As Short) As List(Of Data_list)
        Dim oCountry_list As New List(Of Data_list)
        'oDocType.Add(New Country_list("0", "-"))
        'Return oDocType


        If Not connect.State = ConnectionState.Open Then
            connect.Open()
        End If
        Dim Command As New OracleCommand("", connect)
        Dim SQLadapter As New OracleDataAdapter
        Dim DataSet_tm As New DataSet
        Dim count As Integer
        Command.CommandText = sert
        SQLadapter = New OracleDataAdapter(Command)
        SQLadapter.Fill(DataSet_tm)

        count = DataSet_tm.Tables(0).Rows.Count
        If mode = 1 Then
            oCountry_list.Add(New Data_list("0", showtext))
        End If
        While count > 0
            oCountry_list.Add(New Data_list(DataSet_tm.Tables(0).Rows(count - 1).Item(field2).ToString, DataSet_tm.Tables(0).Rows(count - 1).Item(field1).ToString))
            count = count - 1
        End While

        If Not connect.State = ConnectionState.Closed Then
            connect.Close()
        End If
        Return oCountry_list
    End Function



End Module