Inversiones/DAO/DAOGeneral.vb

Imports System.Data.SqlClient

Public Class DAOGeneral

    Dim da As SqlDataAdapter

    Public Function ListaBase() As DataTable
        Dim objCon As New Conexion

        Dim ds As New DataSet
        Dim dt As DataTable
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If


        da = New SqlDataAdapter("SP_ListaAnioBase", cn)
        da.Fill(ds, "AnioBase")
        dt = ds.Tables(0)


        da.Dispose()
        cn.Dispose()
        Return dt


    End Function




    Public Function ListaTiposImpuesto() As DataTable

        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If

        da = New SqlDataAdapter("SELECT [Codigo] ,[Nombre] 'Descripcion' FROM [dbo].[TIMP]", cn)
        da.Fill(ds)
        dt = ds.Tables(0)

        da.Dispose()
        cn.Dispose()
        Return dt


    End Function
    Public Function ListaTipoTasa() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If

        da = New SqlDataAdapter("SP_ListaTipoTasa", cn)
        da.Fill(ds)
        dt = ds.Tables(0)

        da.Dispose()
        cn.Dispose()
        Return dt


    End Function
    Public Function ListaTipoRenta() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If

        da = New SqlDataAdapter("SP_ListaTipoRenta", cn)
        da.Fill(ds)
        dt = ds.Tables(0)

        da.Dispose()
        cn.Dispose()
        Return dt



    End Function
    Public Function ListaEstadoInversion() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If

        da = New SqlDataAdapter("SP_ListaEstadoInversion", cn)
        da.Fill(ds)
        dt = ds.Tables(0)

        da.Dispose()
        cn.Dispose()
        Return dt


    End Function
    Public Function ListaEstadoDocumento() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If

        da = New SqlDataAdapter("SP_ListaEstadoDocumento", cn)
        da.Fill(ds)
        dt = ds.Tables(0)

        da.Dispose()
        cn.Dispose()
        Return dt


    End Function

    Public Function ListaTiposMercado() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_ListaTiposMercado", cn)
        da.Fill(ds, "ListaTiposMercado")
        dt = ds.Tables(0)

        da.Dispose()
        cn.Dispose()
        Return dt



    End Function
    Public Function ListaPaisesRelevanteInversion() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_ListaPaisesInv", cn)
        da.Fill(ds, "ListaPaisesInv")
        dt = ds.Tables(0)

        da.Dispose()
        cn.Dispose()
        Return dt



    End Function
    Public Function ListaEmpresas() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_ListaEmpresas", cn)
        da.Fill(ds)

        dt = ds.Tables(0)

        da.Dispose()
        cn.Dispose()
        Return dt




    End Function

    Public Function ListaEmpresasCheque()
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim diccionario As New Dictionary(Of String, String)
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        Dim sql = "SP_ListaEmpresas"


        Dim cmd As New SqlCommand(sql, cn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader
        Dim i As Integer = 1
        While dr.Read
            If String.IsNullOrEmpty(dr.GetName(0)) Then
                Return diccionario
            End If







            diccionario.Add(dr("Codigo"), dr("Descripcion"))
            i += 1
        End While
        cn.Close()
        Return diccionario



    End Function
    Public Function ListaEstadoAutorizacion() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_ListaEstadoAut", cn)
        da.Fill(ds, "ListaEstadoAut")

        dt = ds.Tables("ListaEstadoAut")

        da.Dispose()
        cn.Dispose()
        Return dt



    End Function


    Public Function ListaEstadoProceso() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_ListaEstadoProceso", cn)
        da.Fill(ds, "ListaEstadoProceso")

        dt = ds.Tables("ListaEstadoProceso")

        da.Dispose()
        cn.Dispose()
        Return dt



    End Function





    Public Function ListaTipoOperacion() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_ListaTipoOperacion", cn)
        da.Fill(ds)
        dt = ds.Tables(0)

        da.Dispose()
        cn.Dispose()
        Return dt



    End Function


    Public Function ListaInstrumentos(ByVal TipoFuncion As String) As DataTable
        Dim objCon As New Conexion

        Dim ds As New DataSet
        Dim dt As DataTable
        Dim cn As SqlConnection = objCon.Conectar

        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If

        Dim sql = "SP_ListaInstrumentosFinancieros"


        Dim cmd As New SqlCommand(sql, cn)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add("@TipoFuncion", SqlDbType.VarChar).Value = TipoFuncion

        da = New SqlDataAdapter(cmd)

        da.Fill(ds)
        dt = ds.Tables(0)

        da.Dispose()
        cn.Dispose()
        Return dt


    End Function



    Public Function ListaEmisores() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_ListaEmisores", cn)
        da.Fill(ds)
        dt = ds.Tables(0)


        da.Dispose()
        cn.Dispose()
        Return dt


    End Function

    Public Function ListaCalificacionesDeRiesgo() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_ListaCalificacionesDeRiesgo", cn)
        da.Fill(ds)
        dt = ds.Tables(0)

        da.Dispose()
        cn.Dispose()
        Return dt


    End Function

    Public Function ListaEmpresasCalificadoras() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_ListaEmpresasCalificadoras", cn)
        da.Fill(ds)
        dt = ds.Tables(0)

        da.Dispose()
        cn.Dispose()
        Return dt


    End Function

    Public Function ListaOrigenDeFondos() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_ListaOrigenDeFondos", cn)
        da.Fill(ds)
        dt = ds.Tables(0)

        da.Dispose()
        cn.Dispose()
        Return dt


    End Function

    Public Function ListaPlazosFactor() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_ListaPlazosFactor", cn)
        da.Fill(ds)
        dt = ds.Tables(0)

        da.Dispose()
        cn.Dispose()
        Return dt


    End Function
    Public Function ListaPeriodicidad() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_ListaPeriodos", cn)
        da.Fill(ds, "ListaPeriodos")
        dt = ds.Tables(0)


        da.Dispose()
        cn.Dispose()
        Return dt


    End Function

    Public Function ListaAnioBase() As DataSet
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_ListaAnioBase", cn)
        da.Fill(ds, "AnioBase")

        da.Dispose()
        cn.Dispose()
        Return ds


    End Function

    Public Function ResultadosDeBusqueda(Query As String) As DataSet
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter(Query, cn)
        da.Fill(ds, "Resultado")

        da.Dispose()
        cn.Dispose()
        Return ds


    End Function

    Public Function ListaCasasCorredoras() As DataSet
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_ListaCasasCorredoras", cn)
        da.Fill(ds, "CasasCorredoras")

        da.Dispose()
        cn.Dispose()
        Return ds



    End Function




    Public Function ListaImpuesto() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If

        da = New SqlDataAdapter("SELECT [CodImp],[Valor],[Impuesto]  'Descripcion' FROM [dbo].[IMPU]", cn)
        da.Fill(ds)
        dt = ds.Tables(0)

        da.Dispose()
        cn.Dispose()
        Return dt


    End Function

    Public Function ListaInversion(ByVal codigo As String) As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If

        da = New SqlDataAdapter("SELECT [codigo]  'Descripcion' FROM [dbo].[REP0] Where [CodInv]='" + codigo + "'", cn)
        da.Fill(ds)
        dt = ds.Tables(0)

        da.Dispose()
        cn.Dispose()
        Return dt

    End Function


    Public Function ObtenerInstrumento(ByVal Codigo As String)
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        If (Not String.IsNullOrEmpty(Codigo)) Then


            Dim sql As String
            Dim cmd As SqlCommand
            Dim Instrumento As String

            sql = "SELECT * FROM [dbo].[INV0] WHERE CodInv=@Codigo"


            cmd = New SqlCommand
            If cn Is Nothing Then
                cn = objCon.Conectar
            End If
            If cn.State = ConnectionState.Open Then
                cn.Close()
                cn.Dispose()
                cn = objCon.Conectar
                cn.Open()
            Else
                cn = objCon.Conectar
                cn.Open()
            End If

            cmd.CommandText = sql
            cmd.Parameters.Add("@Codigo", SqlDbType.VarChar).Value = Codigo
            cmd.Connection = cn

            ' Dim Datos = cmd.ExecuteScalar
            Dim Datos = cmd.ExecuteReader
            If Datos.HasRows Then
                'SE ENCONTRO EL REGISTRO


                If Datos.Read Then

                    Instrumento = Datos.Item("CodIF")



                    Return Instrumento
                End If
            End If
        End If


        If Codigo Is Nothing Then
            Codigo = String.Empty
        End If

        cn.Close()
        If Codigo.IndexOf("CINV") > -1 Then
            Return "CINV"
        ElseIf Codigo.IndexOf("BONO") > -1 Then
            Return "BONO"
        ElseIf Codigo.IndexOf("EURB") > -1 Then
            Return "EURB"
        ElseIf Codigo.IndexOf("TIT") > -1 Then
            Return "TIT"

        End If
    End Function


    Public Function ObtenerEmpresa(ByRef Codigo As String, ByRef Tabla As String)
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        If (Not String.IsNullOrEmpty(Codigo)) Then


            Dim sql As String
            Dim cmd As SqlCommand
            Dim Instrumento As String

            sql = "SELECT CodEmpr FROM " + Tabla + " WHERE CodInv=@Codigo"


            cmd = New SqlCommand
            If cn Is Nothing Then
                cn = objCon.Conectar
            End If
            If cn.State = ConnectionState.Open Then
                cn.Close()
                cn.Dispose()
                cn = objCon.Conectar
                cn.Open()
            Else
                cn = objCon.Conectar
                cn.Open()
            End If

            cmd.CommandText = sql
            cmd.Parameters.Add("@Codigo", SqlDbType.VarChar).Value = Codigo
            cmd.Connection = cn

            ' Dim Datos = cmd.ExecuteScalar
            Dim Datos = cmd.ExecuteReader
            cn.Close()
            If Datos.HasRows Then
                'SE ENCONTRO EL REGISTRO
                If Datos.Read Then
                    Instrumento = Datos.Item("CodEmpr")
                    Return Instrumento
                End If
            End If
        End If
        Return String.Empty
    End Function


    Public Function TraerImpuesto(ByVal Codigo As String, ByVal Tabla As String)
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        If (Not String.IsNullOrEmpty(Codigo)) Then


            Dim sql As String
            Dim cmd As SqlCommand
            Dim Impuesto As String

            sql = "SELECT * FROM " + Tabla + " WHERE CodInv=@Codigo"


            cmd = New SqlCommand
            If cn Is Nothing Then
                cn = objCon.Conectar
            End If
            If cn.State = ConnectionState.Open Then
                cn.Close()
                cn.Dispose()
                cn = objCon.Conectar
                cn.Open()
            Else
                cn = objCon.Conectar
                cn.Open()
            End If

            cmd.CommandText = sql
            cmd.Parameters.Add("@Codigo", SqlDbType.VarChar).Value = Codigo
            cmd.Connection = cn

            ' Dim Datos = cmd.ExecuteScalar
            Dim Datos = cmd.ExecuteReader
            cn.Close()
            If Datos.HasRows Then
                'SE ENCONTRO EL REGISTRO

                Try


                    If Datos.Read Then

                        Impuesto = Datos.Item("Impuestos")
                        Return Impuesto

                    Else
                        Return 0
                    End If

                Catch ex As Exception
                    Return 0
                End Try

            Else
                Return 0
            End If
        Else
            Return 0
        End If
    End Function




    Public Function AgregarImpuesto(ByVal CodigoInv As String, ByVal Tabla As String, ByVal Impues As String, ByVal flag As String)
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim retorno As Integer
        Dim sql As String
        Dim cmd As SqlCommand
        Dim Impuestos As String = TraerImpuesto(CodigoInv, Tabla)
        If (String.IsNullOrEmpty(Impuestos) Or Impuestos = "0") Then
            Impuestos = Impues
        Else
            If (flag = 0) Then


                If InStr(1, Impuestos, Impues) = 0 Then

                    Impuestos = Impuestos.ToString + "-" + Impues.ToString
                End If
            Else
                If Not InStr(1, Impuestos, Impues) = 0 Then

                    Dim QuitarImpuesto = Impuestos.Split("-")
                    Impuestos = String.Empty
                    For Each i In QuitarImpuesto
                        If (Not i = Impues) Then
                            If (String.IsNullOrEmpty(Impuestos)) Then
                                Impuestos = i
                            Else
                                Impuestos = Impuestos + "-" + i
                            End If
                        End If
                    Next



                End If

            End If
        End If




        sql = "UPDATE " + Tabla + " SET
           
           [Impuestos]=@Impuestos
           where [CodInv]=@CodInv

    "
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        cmd = New SqlCommand
        cmd.CommandText = sql

        With cmd.Parameters
            .Add("@CodInv", SqlDbType.VarChar).Value = CodigoInv
            .Add("@Impuestos", SqlDbType.VarChar).Value = Impuestos
        End With

        cmd.Connection = cn

        retorno = cmd.ExecuteNonQuery

        cn.Close()
        Return retorno

    End Function



    Public Function CargarImpuesto(ByVal codigo As String, ByVal Tabla As String)
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        Dim sql = "select * from " + Tabla + " where CodInv='" + codigo + "'"


        Dim cmd As New SqlCommand(sql, cn)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader

        Return dr
    End Function


    Public Function ObtenerImpuesto()
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar

        Dim diccionario As New Dictionary(Of String, String)
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        Dim sql = "select * from IMPU"


        Dim cmd As New SqlCommand(sql, cn)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader

        While dr.Read

            diccionario.Add(dr("CodImp").ToString, dr("Valor").ToString)


        End While

        cn.Close()
        Return diccionario
    End Function








    Public Function ExisteTitulo(ByVal CodTitulo As String, ByVal Tabla As String) As Boolean
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim valor As Integer = 0
        Dim Existe As Boolean = False
        Dim cmd As SqlCommand
        If Not String.IsNullOrEmpty(CodTitulo) Then

            If cn Is Nothing Then
                cn = objCon.Conectar
            End If
            If cn.State = ConnectionState.Open Then
                cn.Close()
                cn.Dispose()
                cn = objCon.Conectar
                cn.Open()
            Else
                cn = objCon.Conectar
                cn.Open()
            End If
            If Not String.IsNullOrEmpty(Tabla) Then



                cmd = New SqlCommand("select count(*) from " + Tabla + " where CodInv='" + CodTitulo.ToString + "'", cn)
                valor = CInt(cmd.ExecuteScalar)
                cmd.Dispose()
                If valor > 0 Then
                    Existe = True
                End If

            End If

            cn.Dispose()
        End If
        Return Existe
    End Function

    Public Function ExisteCMVTA(ByVal CodigoCompra As String) As Boolean
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim valor As Integer = 0
        Dim Existe As Boolean = False
        Dim cmd As SqlCommand
        Dim myTrans As SqlTransaction
        If Not String.IsNullOrEmpty(CodigoCompra) Then

            If cn Is Nothing Then
                cn = objCon.Conectar
            End If
            If cn.State = ConnectionState.Open Then
                cn.Close()
                cn.Dispose()
                cn = objCon.Conectar
                cn.Open()
            Else
                cn = objCon.Conectar
                cn.Open()
            End If
            myTrans = cn.BeginTransaction()
            Try



                cmd = New SqlCommand("select count(*) from OPERCMVTA where CodigoInversionCompra=@CodigoInversionCompra", cn)

                With cmd.Parameters
                    .Add("@CodigoInversionCompra", SqlDbType.VarChar).Value = CodigoCompra
                End With
                cmd.Transaction = myTrans
                valor = CInt(cmd.ExecuteScalar)
                cmd.Dispose()
                If valor > 0 Then
                    Existe = True
                Else

                    cmd = New SqlCommand("select count(*) from OPERCMVTA where CodigoInversionVenta=@CodigoInversionVenta", cn)

                    With cmd.Parameters
                        .Add("@CodigoInversionVenta", SqlDbType.VarChar).Value = CodigoCompra
                    End With
                    cmd.Transaction = myTrans
                    valor = CInt(cmd.ExecuteScalar)
                    cmd.Dispose()
                    If valor > 0 Then
                        Existe = True
                    End If
                End If
            Catch ex As Exception

                myTrans.Rollback()

                MsgBox(ex.Message)

            End Try

        End If

        cn.Dispose()

        Return Existe
    End Function

    Public Function ExisteVenta(ByVal CodTitulo As String) As Boolean
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim valor As Integer = 0
        Dim Existe As Boolean = False
        Dim cmd As SqlCommand
        If Not String.IsNullOrEmpty(CodTitulo) Then

            If cn Is Nothing Then
                cn = objCon.Conectar
            End If
            If cn.State = ConnectionState.Open Then
                cn.Close()
                cn.Dispose()
                cn = objCon.Conectar
                cn.Open()
            Else
                cn = objCon.Conectar
                cn.Open()
            End If




            cmd = New SqlCommand("select count(*) from  INV0 right join PIN0 on INV0.VCodInv=PIN0.VCodInv where ((INV0.VCodInv='" + CodTitulo + "' or PIN0.VCodInv='" + CodTitulo + "') and  (INV0.TipoOper='V' or PIN0.TipoOper='V')) ", cn)
            valor = CInt(cmd.ExecuteScalar)
            cmd.Dispose()
            If valor > 0 Then
                Existe = True
            End If



            cn.Dispose()
        End If
        Return Existe
    End Function




    Public Function IdPropuesta(ByVal CodTitulo As String)
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim valor As Integer = 0
        Dim Existe As Boolean = False
        Dim cmd As SqlCommand
        If Not String.IsNullOrEmpty(CodTitulo) Then

            If cn Is Nothing Then
                cn = objCon.Conectar
            End If
            If cn.State = ConnectionState.Open Then
                cn.Close()
                cn.Dispose()
                cn = objCon.Conectar
                cn.Open()
            Else
                cn = objCon.Conectar
                cn.Open()
            End If



            cmd = New SqlCommand("select DocId from PIN0 WHERE CodInv='" + CodTitulo + "'", cn)
            valor = CInt(cmd.ExecuteScalar)
            cmd.Dispose()




            cn.Dispose()
        End If
        Return valor
    End Function


    Public Function CantidadRegistros(ByVal Codigo As String, ByVal Tabla As String)
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim valor As Integer = 0
        Dim Existe As Boolean = False
        Dim cmd As SqlCommand
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        If (Not String.IsNullOrEmpty(Codigo)) Then
            cmd = New SqlCommand("select count(*) from " + Tabla + " where CodInv='" + Codigo + "'", cn)
            valor = CInt(cmd.ExecuteScalar)
            cmd.Dispose()


        End If

        cn.Dispose()


        Return valor
    End Function
    Public Function TipoDeInstrumento(ByVal pCodIF As String) As String
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim retorno As String
        Dim query As String
        Dim cmd As SqlCommand


        query = "SELECT T0.[Tipo] FROM [dbo].[INFI] T0 WHERE T0.CodIF=@CodIF"
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If

        cmd = New SqlCommand
        cmd.Connection = cn
        cmd.CommandText = query
        cmd.Parameters.Add("@CodIF", SqlDbType.VarChar).Value = "LETE"

        retorno = cmd.ExecuteScalar
        cn.Close()
        Return retorno
    End Function



    Public Function Periodicidad(ByVal Codigo As String, ByVal Tabla As String) As String
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim retorno As String
        Dim query As String
        Dim cmd As SqlCommand


        query = "SELECT T0.[Periodicidad] FROM " + Tabla + " T0 WHERE T0.CodInv=@CodInv"
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If

        cmd = New SqlCommand
        cmd.Connection = cn
        cmd.CommandText = query
        cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo

        Dim Datos = cmd.ExecuteScalar
        cn.Close()
        If Datos Is DBNull.Value Then
            Return Datos.ToString
        ElseIf Datos = Nothing Then

            Return String.Empty
        Else
            Return Datos
        End If





    End Function

    Public Function EstadoAutorizacionPro(ByVal Codigo As String) As String
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim retorno As String
        Dim query As String
        Dim cmd As SqlCommand


        query = "SELECT T0.[AutEstado] FROM PIN0 T0 WHERE T0.CodInv=@CodInv"
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If

        cmd = New SqlCommand
        cmd.Connection = cn
        cmd.CommandText = query
        cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo

        retorno = cmd.ExecuteScalar
        cn.Close()
        Return retorno
    End Function



    Public Function InstrumentosGrafica()
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As New DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_ConteoInstrumentos", cn)
        da.Fill(dt)
        cn.Close()
        Return dt


    End Function


    Public Function MontosInstrumentosGrafica()
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As New DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_CargarMonto", cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function

    Public Function RendimientosInstrumentoGrafica()
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As New DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_RendimientoGraficas", cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function


    Public Function RendimientoMontoGraficas()
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim dt As New DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        da = New SqlDataAdapter("SP_RendimientoMontoGraficas", cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function




    Public Function EstadoInversion(ByVal Codigo As String) As String
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim retorno As String = String.Empty
        Dim query As String
        Dim cmd As SqlCommand


        query = "SELECT EstadoProceso from INV0 where CodInv='" + Codigo + "'"
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If

        cmd = New SqlCommand
        cmd.Connection = cn
        cmd.CommandText = query
        cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
        Dim con = cmd.ExecuteScalar
        If Not con Is Nothing Then
            retorno = con.ToString
        End If

        cn.Close()
        Return retorno
    End Function


    Public Function AsociarReportoVenta(ByRef CodigoInstrumento As String, ByRef Tipo As String) As DataSet
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet

        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        Dim sql = "SP_AsociacionReportoVentas"


        Dim cmd As New SqlCommand(sql, cn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = CodigoInstrumento
        cmd.Parameters.Add("@Tipo", SqlDbType.VarChar).Value = Tipo
        da = New SqlDataAdapter(cmd)

        da.Fill(ds)
        cn.Close()
        Return ds
    End Function

    Public Function EstadoReporto(ByVal Codigo As String) As String
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim retorno As String = String.Empty
        Dim query As String
        Dim cmd As SqlCommand


        query = "SP_AsociacionReportoVentas"
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If

        cmd = New SqlCommand
        cmd = New SqlCommand(query, cn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@Tipo", SqlDbType.VarChar).Value = Codigo
        Dim con = cmd.ExecuteScalar
        If Not con Is Nothing Then
            retorno = con.ToString
        End If

        cn.Close()
        Return retorno
    End Function








    Public Sub GenerarCMVTA(ByVal CodigoCompra As String, ByVal CodigoVenta As String,
    ByVal Tabla As String, ByVal coleccion As Collection, ByVal ObjPropuesta As CEPropuestaInversion)
        Dim objCon As New Conexion
        Dim retorno As Integer
        Dim sql As String
        Dim cmd As SqlCommand
        Dim myTrans As SqlTransaction

        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        myTrans = cn.BeginTransaction()

        Try



            sql = "INSERT INTO [dbo].[PIN0]
           ([CodInv]
           ,[DocEst]
           ,[Asunto]
           ,[CodEmpr]
           ,[CodIF]
           ,[CodEmis]
           ,[CodCalRi]
           ,[CodECalRi]
           ,[OFon]
           ,[Coment]
           ,[Just]
           ,[TipoOper]
           ,[MontoInv]
           ,[Precio]
           ,[Rendimiento]
           ,[Plazo]
           ,[PlazoFact]
           ,[Ingresos]
           ,[FechaDoc]
           ,[AutEstado]
           ,[TipoMerc]
           ,[CodPais]
           ,[TipoRenta]
           ,[TipoTasa]
           ,[Periodicidad]
           ,[NombreManual]
           ,[EstadoReporto]

)
     VALUES
           (@CodInv
           ,@DocEst
           ,@Asunto
           ,@CodEmpr
           ,@CodIF
           ,@CodEmis
           ,@CodCalRi
           ,@CodECalRi
           ,@OFon
           ,@Coment
           ,@Just
           ,@TipoOper
           ,@MontoInv
           ,@Precio
           ,@Rendimiento
           ,@Plazo
           ,@PlazoFact
           ,@Ingresos
           ,@FechaDoc
           ,@AutEstado
           ,@TipoMerc
           ,@CodPais
           ,@TipoRenta
           ,@TipoTasa
           ,@Periodicidad
           ,@NombreManual
           ,@EstadoReporto
		   )"

            cmd = New SqlCommand
            cmd.CommandText = sql
            cmd.Connection = cn






            cmd.Transaction = myTrans

            With cmd.Parameters

                .Add("@CodInv", SqlDbType.VarChar).Value = ObjPropuesta.CodigoInversion.ToString
                .Add("@DocEst", SqlDbType.VarChar).Value = ObjPropuesta.EstadoDocumento.ToString
                .Add("@Asunto", SqlDbType.VarChar).Value = ObjPropuesta.Asunto.ToString
                .Add("@CodEmpr", SqlDbType.VarChar).Value = ObjPropuesta.CodigoEmpresa.ToString
                .Add("@CodIF", SqlDbType.VarChar).Value = ObjPropuesta.CodigoInstrumentoFinanciero.ToString
                .Add("@CodEmis", SqlDbType.Int).Value = ObjPropuesta.CodigoEmisor.ToString
                .Add("@CodCalRi", SqlDbType.VarChar).Value = ObjPropuesta.CodigoCalificacionDeRiesgo.ToString
                .Add("@CodECalRi", SqlDbType.VarChar).Value = ObjPropuesta.CodigoEmpresaCalificadora.ToString
                .Add("@OFon", SqlDbType.VarChar).Value = ObjPropuesta.OrigenDeFondos.ToString
                .Add("@Coment", SqlDbType.VarChar).Value = ObjPropuesta.Comentarios.ToString
                .Add("@Just", SqlDbType.VarChar).Value = ObjPropuesta.Justificacion.ToString
                .Add("@TipoOper", SqlDbType.VarChar).Value = ObjPropuesta.TipoOperacion.ToString
                .Add("@MontoInv", SqlDbType.Float).Value = ObjPropuesta.MontoInversion.ToString
                .Add("@Precio", SqlDbType.Float).Value = ObjPropuesta.Precio.ToString
                .Add("@Rendimiento", SqlDbType.Float).Value = ObjPropuesta.Rendimiento.ToString
                .Add("@Plazo", SqlDbType.Int).Value = ObjPropuesta.PlazoNumero.ToString
                .Add("@PlazoFact", SqlDbType.VarChar).Value = ObjPropuesta.PlazoFactor.ToString
                .Add("@Ingresos", SqlDbType.Float).Value = ObjPropuesta.Ingresos.ToString
                .Add("@FechaDoc", SqlDbType.Date).Value = ObjPropuesta.FechaDocumento.ToString
                .Add("@AutEstado", SqlDbType.VarChar).Value = ObjPropuesta.EstadoAutorizacion.ToString
                .Add("@TipoMerc", SqlDbType.VarChar).Value = ObjPropuesta.TipoMercado.ToString
                .Add("@CodPais", SqlDbType.VarChar).Value = ObjPropuesta.Pais.ToString

                .Add("@TipoRenta", SqlDbType.VarChar).Value = ObjPropuesta.TipoRenta.ToString
                .Add("@TipoTasa", SqlDbType.VarChar).Value = ObjPropuesta.TipoTasa.ToString
                .Add("@Periodicidad", SqlDbType.VarChar).Value = ObjPropuesta.Periodicidad.ToString
                .Add("@NombreManual", SqlDbType.VarChar).Value = ObjPropuesta.NombreManual.ToString
                .Add("@EstadoReporto", SqlDbType.VarChar).Value = "PRIMARIO"
            End With
            cmd.CommandText = sql

            retorno = cmd.ExecuteNonQuery

            ''Ingresar codigo


            '#####################################################################'


            cmd = New SqlCommand





            sql = "INSERT INTO [dbo].[Propuesta]
                   ([CodigoPropuesta]
                    ,[Nombre]
                    ,[Descrip]
                    ,[Estado]
        )
             VALUES
                   (@CodigoPropuesta
                    ,@Nombre
                    ,@Descrip
                    ,@Estado
        		   )"





            cmd.CommandText = sql
            cmd.Connection = cn
            cmd.Transaction = myTrans

            With cmd.Parameters
                .Add("@CodigoPropuesta", SqlDbType.VarChar).Value = ObjPropuesta.CodigoInversion.ToString
                .Add("@Nombre", SqlDbType.VarChar).Value = "ProCre"
                .Add("@Descrip", SqlDbType.VarChar).Value = "Propuesta de Inversion Inicial Creada"
                .Add("@Estado", SqlDbType.VarChar).Value = "En Propuesta"
            End With


            retorno = cmd.ExecuteNonQuery

            ''###############################################################
            ''###############################################################



            cmd = New SqlCommand
            sql = "INSERT INTO [dbo].[OPERCMVTA]
                   ([FechaOperacion]
                    ,[CodigoInversionCompra]
                    ,[CodigoInversionVenta]
                    ,[TipoFlujo]
        )
             VALUES
                   (@FechaOperacion
                    ,@CodigoInversionCompra
                    ,@CodigoInversionVenta
                    ,@TipoFlujo
        		   )"





            cmd.CommandText = sql
            cmd.Connection = cn
            cmd.Transaction = myTrans

            With cmd.Parameters
                .Add("@FechaOperacion", SqlDbType.Date).Value = Date.Now.Date
                .Add("@CodigoInversionCompra", SqlDbType.VarChar).Value = CodigoVenta
                .Add("@CodigoInversionVenta", SqlDbType.VarChar).Value = ObjPropuesta.CodigoInversion.ToString
                .Add("@TipoFlujo", SqlDbType.VarChar).Value = "C-V"
            End With


            retorno = cmd.ExecuteNonQuery















































            If Tabla = "OPC0" Or Tabla = "FUT0" Then

                ''Crear Nuevo Venta
                sql = "INSERT INTO " + Tabla + "
([CodInv]
,[FOperC]
,[FLiqC]
,[FVenC]
,[VaUnidadC]
,[ValorTransadoC]
,[CoBrokerC]
,[VaRecC]
,[IdContratoC]
,[CantContratoC]
,[UniContraC]

,[FOperV]
,[FLiqV]
,[FVenV]
,[VaUnidadV]
,[ValorTransadoV]
,[CoBrokerV]
,[VaRecV]
,[IdContratoV]
,[CantContratoV]
,[UniContraV]
,[GP]
,[Rendimiento]
,[TipoOperacion]

) VALUES(
           @CodInv
,@FOperC
,@FLiqC
,@FVenC
,@VaUnidadC
,@ValorTransadoC
,@CoBrokerC
,@VaRecC
,@IdContratoC
,@CantContratoC
,@UniContraC

,@FOperV
,@FLiqV
,@FVenV
,@VaUnidadV
,@ValorTransadoV
,@CoBrokerV
,@VaRecV
,@IdContratoV
,@CantContratoV
,@UniContraV
,@GP
,@Rendimiento
,@TipoOperacion


)
    "

                cmd = New SqlCommand
                cmd.CommandText = sql

                'Try



                With cmd.Parameters
                    .Add("@CodInv", SqlDbType.VarChar).Value = CodigoVenta
                    .Add("@FOperC", SqlDbType.DateTime).Value = CDate(coleccion(1))
                    .Add("@FLiqC", SqlDbType.DateTime).Value = CDate(coleccion(2))
                    .Add("@FVenC", SqlDbType.DateTime).Value = CDate(coleccion(3))
                    .Add("@VaUnidadC", SqlDbType.Float).Value = CDec(coleccion(4))
                    .Add("@ValorTransadoC", SqlDbType.Float).Value = CDec(coleccion(5))
                    .Add("@CoBrokerC", SqlDbType.Float).Value = CDec(coleccion(6))
                    .Add("@VaRecC", SqlDbType.Float).Value = CDec(coleccion(7))
                    .Add("@IdContratoC", SqlDbType.VarChar).Value = coleccion(8).ToString
                    .Add("@CantContratoC", SqlDbType.Int).Value = CInt(coleccion(9))
                    .Add("@UniContraC", SqlDbType.Int).Value = CInt(coleccion(10))


                    .Add("@FOperV", SqlDbType.DateTime).Value = CDate(coleccion(11))
                    .Add("@FLiqV", SqlDbType.DateTime).Value = CDate(coleccion(12))
                    .Add("@FVenV", SqlDbType.DateTime).Value = CDate(coleccion(13))
                    .Add("@VaUnidadV", SqlDbType.Float).Value = CDec(coleccion(14))
                    .Add("@ValorTransadoV", SqlDbType.Float).Value = CDec(coleccion(15))
                    .Add("@CoBrokerV", SqlDbType.Float).Value = CDec(coleccion(16))
                    .Add("@VaRecV", SqlDbType.Float).Value = CDec(coleccion(17))
                    .Add("@IdContratoV", SqlDbType.VarChar).Value = coleccion(18).ToString
                    .Add("@CantContratoV", SqlDbType.Int).Value = CInt(coleccion(19))
                    .Add("@UniContraV", SqlDbType.Int).Value = CInt(coleccion(20))

                    .Add("@GP", SqlDbType.Float).Value = CDec(coleccion(21))
                    .Add("@Rendimiento", SqlDbType.Float).Value = CDec(coleccion(22))
                    .Add("@TipoOperacion", SqlDbType.VarChar).Value = coleccion(23).ToString



                End With


                cmd.Connection = cn
                cmd.Transaction = myTrans
                retorno = cmd.ExecuteNonQuery






                ''Crear Nuevo Compra
                sql = "UPDATE " + Tabla + " SET

[FOperC]=@FOperC
,[FLiqC]=@FLiqC
,[FVenC]=@FVenC
,[VaUnidadC]=@VaUnidadC
,[ValorTransadoC]=@ValorTransadoC
,[CoBrokerC]=@CoBrokerC
,[VaRecC]=@VaRecC
,[IdContratoC]=@IdContratoC
,[CantContratoC]=@CantContratoC
,[UniContraC]=@UniContraC
,[FOperV]=@FOperV
,[FLiqV]=@FLiqV
,[FVenV]=@FVenV
,[VaUnidadV]=@VaUnidadV
,[ValorTransadoV]=@ValorTransadoV
,[CoBrokerV]=@CoBrokerV
,[VaRecV]=@VaRecV
,[IdContratoV]=@IdContratoV
,[CantContratoV]=@CantContratoV
,[UniContraV]=@UniContraV
,[GP]=@GP
,[Rendimiento]=@Rendimiento
,[TipoOperacion]=@TipoOperacion

where [CodInv]=@CodInv
    "

                cmd = New SqlCommand
                cmd.CommandText = sql

                'Try



                With cmd.Parameters
                    .Add("@CodInv", SqlDbType.VarChar).Value = CodigoCompra
                    .Add("@FOperC", SqlDbType.DateTime).Value = CDate(coleccion(1))
                    .Add("@FLiqC", SqlDbType.DateTime).Value = CDate(coleccion(2))
                    .Add("@FVenC", SqlDbType.DateTime).Value = CDate(coleccion(3))
                    .Add("@VaUnidadC", SqlDbType.Float).Value = CDec(coleccion(4))
                    .Add("@ValorTransadoC", SqlDbType.Float).Value = CDec(coleccion(5))
                    .Add("@CoBrokerC", SqlDbType.Float).Value = CDec(coleccion(6))
                    .Add("@VaRecC", SqlDbType.Float).Value = CDec(coleccion(7))
                    .Add("@IdContratoC", SqlDbType.VarChar).Value = coleccion(8).ToString
                    .Add("@CantContratoC", SqlDbType.Int).Value = CInt(coleccion(9))
                    .Add("@UniContraC", SqlDbType.Int).Value = CInt(coleccion(10))


                    .Add("@FOperV", SqlDbType.DateTime).Value = CDate(coleccion(11))
                    .Add("@FLiqV", SqlDbType.DateTime).Value = CDate(coleccion(12))
                    .Add("@FVenV", SqlDbType.DateTime).Value = CDate(coleccion(13))
                    .Add("@VaUnidadV", SqlDbType.Float).Value = CDec(coleccion(14))
                    .Add("@ValorTransadoV", SqlDbType.Float).Value = CDec(coleccion(15))
                    .Add("@CoBrokerV", SqlDbType.Float).Value = CDec(coleccion(16))
                    .Add("@VaRecV", SqlDbType.Float).Value = CDec(coleccion(17))
                    .Add("@IdContratoV", SqlDbType.VarChar).Value = coleccion(18).ToString
                    .Add("@CantContratoV", SqlDbType.Int).Value = CInt(coleccion(19))
                    .Add("@UniContraV", SqlDbType.Int).Value = CInt(coleccion(20))

                    .Add("@GP", SqlDbType.Float).Value = CDec(coleccion(21))
                    .Add("@Rendimiento", SqlDbType.Float).Value = CDec(coleccion(22))
                    .Add("@TipoOperacion", SqlDbType.VarChar).Value = coleccion(23).ToString



                End With


                cmd.Connection = cn
                cmd.Transaction = myTrans
                retorno = cmd.ExecuteNonQuery






                myTrans.Commit()

            ElseIf Tabla = "ACC0" Then
                ''Crear Nuevo Venta


                sql = "INSERT INTO " + Tabla + "
           ([CodInv]
           ,[PrecioLimpioC]
           ,[FOperC]
           ,[FVenC]
           ,[DiasTC]
           ,[GCapTotC]
           ,[GCapUnC]
           ,[RetAnBrC]
           ,[RetAnNetC]
           ,[UnC]
           ,[ValTransC]
           ,[MontoNetoC]
           ,[PrecSucioC]
           ,[PrecioLimpioV]
           ,[FOperV]
           ,[FVenV]
           ,[DiasTV]
           ,[GCapTotV]
           ,[GCapUnV]
           ,[RetAnBrV]
           ,[RetAnNetV]
           ,[UnV]
           ,[ValTransV]
           ,[MontoNetoV]
           ,[PrecSucioV]
           ,[ComisionCompraCasa]
           ,[ComisionCompraBolsa]
           ,[ComisionVentaCasa]
           ,[ComisionVentaBolsa]
           ,[IOF]
           ,[Renta]
           ,[Tipo]
           ,[ComisionCompraCasaValor]
           ,[ComisionCompraBolsaValor]
           ,[ComisionVentaCasaValor]
           ,[ComisionVentaBolsaValor]

)
     VALUES
           (@CodInv
           ,@PrecioLimpioC
           ,@FOperC
           ,@FVenC
           ,@DiasTC
           ,@GCapTotC
           ,@GCapUnC
           ,@RetAnBrC
           ,@RetAnNetC
           ,@UnC
           ,@ValTransC
           ,@MontoNetoC
           ,@PrecSucioC
           ,@PrecioLimpioV
           ,@FOperV
           ,@FVenV
           ,@DiasTV
           ,@GCapTotV
           ,@GCapUnV
           ,@RetAnBrV
           ,@RetAnNetV
           ,@UnV
           ,@ValTransV
           ,@MontoNetoV
           ,@PrecSucioV
           ,@ComisionCompraCasa
           ,@ComisionCompraBolsa
           ,@ComisionVentaCasa
           ,@ComisionVentaBolsa
           ,@IOF
           ,@Renta
           ,@Tipo
           ,@ComisionCompraCasaValor
           ,@ComisionCompraBolsaValor
           ,@ComisionVentaCasaValor
           ,@ComisionVentaBolsaValor
)"


                cmd = New SqlCommand
                cmd.CommandText = sql
                cmd.Connection = cn

                With cmd.Parameters
                    .Add("@CodInv", SqlDbType.VarChar).Value = CodigoVenta
                    .Add("@PrecioLimpioC", SqlDbType.Float).Value = CDec(coleccion(1))
                    .Add("@FOperC", SqlDbType.Date).Value = coleccion(2)
                    .Add("@FVenC", SqlDbType.Date).Value = coleccion(3)
                    .Add("@DiasTC", SqlDbType.Float).Value = CDec(coleccion(4))
                    .Add("@GCapTotC", SqlDbType.Float).Value = CDec(coleccion(5))
                    .Add("@GCapUnC", SqlDbType.Float).Value = CDec(coleccion(6))
                    .Add("@RetAnBrC", SqlDbType.Float).Value = CDec(coleccion(7))
                    .Add("@RetAnNetC", SqlDbType.Float).Value = CDec(coleccion(8))
                    .Add("@UnC", SqlDbType.Float).Value = CDec(coleccion(9))
                    .Add("@ValTransC", SqlDbType.Float).Value = CDec(coleccion(10))
                    .Add("@MontoNetoC", SqlDbType.Float).Value = CDec(coleccion(11))
                    .Add("@PrecSucioC", SqlDbType.Float).Value = CDec(coleccion(12))
                    .Add("@PrecioLimpioV", SqlDbType.Float).Value = CDec(coleccion(13))
                    .Add("@FOperV", SqlDbType.Date).Value = coleccion(14)
                    .Add("@FVenV", SqlDbType.Date).Value = coleccion(15)
                    .Add("@DiasTV", SqlDbType.Float).Value = CDec(coleccion(16))
                    .Add("@GCapTotV", SqlDbType.Float).Value = CDec(coleccion(17))
                    If ((coleccion(18).ToString = "NaN")) Then
                        .Add("@GCapUnV", SqlDbType.Float).Value = 0.0
                    Else
                        .Add("@GCapUnV", SqlDbType.Float).Value = CDec(coleccion(18))
                    End If

                    .Add("@RetAnBrV", SqlDbType.Float).Value = CDec(coleccion(19))
                    .Add("@RetAnNetV", SqlDbType.Float).Value = CDec(coleccion(20))
                    .Add("@UnV", SqlDbType.Float).Value = CDec(coleccion(21))
                    .Add("@ValTransV", SqlDbType.Float).Value = CDec(coleccion(22))
                    .Add("@MontoNetoV", SqlDbType.Float).Value = CDec(coleccion(23))

                    If ((coleccion(24).ToString = "NaN")) Then
                        .Add("@PrecSucioV", SqlDbType.Float).Value = 0.0

                    Else
                        .Add("@PrecSucioV", SqlDbType.Float).Value = CDec(coleccion(24))
                    End If



                    .Add("@ComisionCompraCasa", SqlDbType.Float).Value = CDec(coleccion(25))
                    .Add("@ComisionCompraBolsa", SqlDbType.Float).Value = CDec(coleccion(26))
                    .Add("@ComisionVentaCasa", SqlDbType.Float).Value = CDec(coleccion(27))
                    .Add("@ComisionVentaBolsa", SqlDbType.Float).Value = CDec(coleccion(28))


                    .Add("@IOF", SqlDbType.Bit).Value = CByte(coleccion(29))
                    .Add("@Renta", SqlDbType.Bit).Value = CByte(coleccion(30))
                    .Add("@Tipo", SqlDbType.VarChar).Value = coleccion(31)

                    .Add("@ComisionCompraCasaValor", SqlDbType.Float).Value = CDec(coleccion(32))
                    .Add("@ComisionCompraBolsaValor", SqlDbType.Float).Value = CDec(coleccion(33))
                    .Add("@ComisionVentaCasaValor", SqlDbType.Float).Value = CDec(coleccion(34))
                    .Add("@ComisionVentaBolsaValor", SqlDbType.Float).Value = CDec(coleccion(35))

                End With
                cmd.Transaction = myTrans
                retorno = cmd.ExecuteNonQuery



                ''Crear Nuevo Compra






                sql = "UPDATE " + Tabla + " SET
         
            [PrecioLimpioC]=@PrecioLimpioC
           ,[FOperC]=@FOperC
           ,[FVenC]=@FVenC
           ,[DiasTC]=@DiasTC
           ,[GCapTotC]=@GCapTotC
           ,[GCapUnC]=@GCapUnC
           ,[RetAnBrC]=@RetAnBrC
           ,[RetAnNetC]=@RetAnNetC
           ,[UnC]=@UnC
           ,[ValTransC]=@ValTransC
           ,[MontoNetoC]=@MontoNetoC
           ,[PrecSucioC]=@PrecSucioC
           ,[PrecioLimpioV]=@PrecioLimpioV
           ,[FOperV]=@FOperV
           ,[FVenV]=@FVenV
           ,[DiasTV]=@DiasTV
           ,[GCapTotV]=@GCapTotV
           ,[GCapUnV]=@GCapUnV
           ,[RetAnBrV]=@RetAnBrV
           ,[RetAnNetV]=@RetAnNetV
           ,[UnV]=@UnV
           ,[ValTransV]=@ValTransV
           ,[MontoNetoV]=@MontoNetoV
           ,[PrecSucioV]=@PrecSucioV
           ,[ComisionCompraCasa]=@ComisionCompraCasa
           ,[ComisionCompraBolsa]=@ComisionCompraBolsa
           ,[ComisionVentaCasa]=@ComisionVentaCasa
           ,[ComisionVentaBolsa]=@ComisionVentaBolsa
           ,[IOF]=@IOF
           ,[Renta]=@Renta
           ,[Tipo]=@Tipo
           ,[ComisionCompraCasaValor]=@ComisionCompraCasaValor
           ,[ComisionCompraBolsaValor]=@ComisionCompraBolsaValor
           ,[ComisionVentaCasaValor]=@ComisionVentaCasaValor
           ,[ComisionVentaBolsaValor]=@ComisionVentaBolsaValor
            where   [CodInv]=@CodInv
"


                cmd = New SqlCommand
                cmd.CommandText = sql
                cmd.Connection = cn

                With cmd.Parameters
                    .Add("@CodInv", SqlDbType.VarChar).Value = CodigoCompra
                    .Add("@PrecioLimpioC", SqlDbType.Float).Value = CDec(coleccion(1))
                    .Add("@FOperC", SqlDbType.Date).Value = coleccion(2)
                    .Add("@FVenC", SqlDbType.Date).Value = coleccion(3)
                    .Add("@DiasTC", SqlDbType.Float).Value = CDec(coleccion(4))
                    .Add("@GCapTotC", SqlDbType.Float).Value = CDec(coleccion(5))
                    .Add("@GCapUnC", SqlDbType.Float).Value = CDec(coleccion(6))
                    .Add("@RetAnBrC", SqlDbType.Float).Value = CDec(coleccion(7))
                    .Add("@RetAnNetC", SqlDbType.Float).Value = CDec(coleccion(8))
                    .Add("@UnC", SqlDbType.Float).Value = CDec(coleccion(9))
                    .Add("@ValTransC", SqlDbType.Float).Value = CDec(coleccion(10))
                    .Add("@MontoNetoC", SqlDbType.Float).Value = CDec(coleccion(11))
                    .Add("@PrecSucioC", SqlDbType.Float).Value = CDec(coleccion(12))
                    .Add("@PrecioLimpioV", SqlDbType.Float).Value = CDec(coleccion(13))
                    .Add("@FOperV", SqlDbType.Date).Value = coleccion(14)
                    .Add("@FVenV", SqlDbType.Date).Value = coleccion(15)
                    .Add("@DiasTV", SqlDbType.Float).Value = CDec(coleccion(16))
                    .Add("@GCapTotV", SqlDbType.Float).Value = CDec(coleccion(17))
                    If ((coleccion(18).ToString = "NaN")) Then
                        .Add("@GCapUnV", SqlDbType.Float).Value = 0.0
                    Else
                        .Add("@GCapUnV", SqlDbType.Float).Value = CDec(coleccion(18))
                    End If

                    .Add("@RetAnBrV", SqlDbType.Float).Value = CDec(coleccion(19))
                    .Add("@RetAnNetV", SqlDbType.Float).Value = CDec(coleccion(20))
                    .Add("@UnV", SqlDbType.Float).Value = CDec(coleccion(21))
                    .Add("@ValTransV", SqlDbType.Float).Value = CDec(coleccion(22))
                    .Add("@MontoNetoV", SqlDbType.Float).Value = CDec(coleccion(23))

                    If ((coleccion(24).ToString = "NaN")) Then
                        .Add("@PrecSucioV", SqlDbType.Float).Value = 0.0

                    Else
                        .Add("@PrecSucioV", SqlDbType.Float).Value = CDec(coleccion(24))
                    End If



                    .Add("@ComisionCompraCasa", SqlDbType.Float).Value = CDec(coleccion(25))
                    .Add("@ComisionCompraBolsa", SqlDbType.Float).Value = CDec(coleccion(26))
                    .Add("@ComisionVentaCasa", SqlDbType.Float).Value = CDec(coleccion(27))
                    .Add("@ComisionVentaBolsa", SqlDbType.Float).Value = CDec(coleccion(28))


                    .Add("@IOF", SqlDbType.Bit).Value = CByte(coleccion(29))
                    .Add("@Renta", SqlDbType.Bit).Value = CByte(coleccion(30))
                    .Add("@Tipo", SqlDbType.VarChar).Value = coleccion(31)

                    .Add("@ComisionCompraCasaValor", SqlDbType.Float).Value = CDec(coleccion(32))
                    .Add("@ComisionCompraBolsaValor", SqlDbType.Float).Value = CDec(coleccion(33))
                    .Add("@ComisionVentaCasaValor", SqlDbType.Float).Value = CDec(coleccion(34))
                    .Add("@ComisionVentaBolsaValor", SqlDbType.Float).Value = CDec(coleccion(35))

                End With
                cmd.Transaction = myTrans
                retorno = cmd.ExecuteNonQuery


                myTrans.Commit()


            ElseIf Tabla = "CIN0" Or Tabla = "BON0" Or Tabla = "EURB0" Or Tabla = "TIT" Then
                ''Crear Nueva Venta

                sql = "INSERT INTO " + Tabla + "
      ([CodInv]   
      ,[PeriodicC]
      ,[ValNomC]
      ,[UltFCupC]
      ,[SigFCupC]
      ,[FLiqC]
      ,[FVencC]
      ,[PorcCupC]
      ,[PrecVencC]
      ,[PrecioCompra]
      ,[DiasBaseC]
     
      
	  ,[PeriodicV]
      ,[ValNomV]
      ,[UltFCupV]
      ,[SigFCupV]
      ,[FLiqV]
      ,[FVencV]
      ,[PorcCupV]
      ,[PrecVencV]
      ,[PrecioVenta]
      ,[DiasBaseV]
   

      ,[ComisionPorCompraCasa]
      ,[ComisionPorCompraBolsa]
      ,[ComisionPorVentaCasa]
      ,[ComisionPorVentaBolsa]
      ,[YTMCompra]
      ,[YTMVenta]
        ,[DiasVenC]
        ,[DiasVenV]
        ,[DiasAcuC]
        ,[DiasAcuV]
        ,[YTMVenC]
        ,[YTMVenV]
        ,[IntAcuC]
        ,[IntAcuV]
        ,[IntPorAcuC]
        ,[IntPorAcuV]
        ,[PrecSucioC]
        ,[PrecSucioV]
        ,[ComisionCasaC]
        ,[ComisionCasaV]
        ,[ComisionBolsaC]
        ,[ComisionBolsaV]
        ,[ValTransC]
        ,[ValTransV]
        ,[MontoPagar]
        ,[MontoRecibir]
        ,[CostTransC]
        ,[CupRecibidos]

)
      VALUES(
       @CodInv
      ,@PeriodicC
      ,@ValNomC
      ,@UltFCupC
      ,@SigFCupC
      ,@FLiqC
      ,@FVencC
      ,@PorcCupC
      ,@PrecVencC
      ,@PrecioCompra
      ,@DiasBaseC
      
	  ,@PeriodicV
      ,@ValNomV
      ,@UltFCupV
      ,@SigFCupV
      ,@FLiqV
      ,@FVencV
      ,@PorcCupV
      ,@PrecVencV
      ,@PrecioVenta
      ,@DiasBaseV


      ,@ComisionPorCompraCasa
      ,@ComisionPorCompraBolsa
      ,@ComisionPorVentaCasa
      ,@ComisionPorVentaBolsa
      ,@YTMCompra
      ,@YTMVenta     



        ,@DiasVenC
        ,@DiasVenV
        ,@DiasAcuC
        ,@DiasAcuV
        ,@YTMVenC
        ,@YTMVenV
        ,@IntAcuC
        ,@IntAcuV
        ,@IntPorAcuC
        ,@IntPorAcuV
        ,@PrecSucioC
        ,@PrecSucioV
        ,@ComisionCasaC
        ,@ComisionCasaV
        ,@ComisionBolsaC
        ,@ComisionBolsaV
        ,@ValTransC
        ,@ValTransV
        ,@MontoPagar
        ,@MontoRecibir
        ,@CostTransC
        ,@CupRecibidos

)


 
"







                cmd = New SqlCommand
                cmd.CommandText = sql
                'Try

                With cmd.Parameters
                    If (coleccion(1) Is Nothing) Then
                        .Add("@PeriodicC", SqlDbType.VarChar).Value = "M"
                    Else
                        .Add("@PeriodicC", SqlDbType.VarChar).Value = coleccion(1).ToString()
                    End If

                    .Add("@ValNomC", SqlDbType.Float).Value = CDec(coleccion(2).ToString.Trim("%"))
                    .Add("@UltFCupC", SqlDbType.Date).Value = coleccion(3)
                    .Add("@SigFCupC", SqlDbType.Date).Value = coleccion(4)
                    .Add("@FLiqC", SqlDbType.Date).Value = coleccion(5)
                    .Add("@FVencC", SqlDbType.Date).Value = coleccion(6)
                    .Add("@PorcCupC", SqlDbType.Float).Value = CDec(coleccion(7).ToString.Trim("%"))
                    .Add("@PrecVencC", SqlDbType.Float).Value = CDec(coleccion(8).ToString.Trim("%"))
                    .Add("@PrecioCompra", SqlDbType.Float).Value = CDec(coleccion(9).ToString.Trim("%"))

                    If (coleccion(10) Is Nothing) Then
                        .Add("@DiasBaseC", SqlDbType.Int).Value = 365
                    Else
                        .Add("@DiasBaseC", SqlDbType.Int).Value = CInt(coleccion(10).ToString.Trim("%"))
                    End If


                    '.Add("@CostoTransC", SqlDbType.Float).Value = Trim(CDec(coleccion(11)))


                    If (coleccion(12) Is Nothing) Then
                        .Add("@PeriodicV", SqlDbType.VarChar).Value = "M"
                    Else
                        .Add("@PeriodicV", SqlDbType.VarChar).Value = coleccion(12).ToString()
                    End If



                    .Add("@ValNomV", SqlDbType.Float).Value = CDec(coleccion(13).ToString.Trim("%"))
                    .Add("@UltFCupV", SqlDbType.Date).Value = coleccion(14)
                    .Add("@SigFCupV", SqlDbType.Date).Value = coleccion(15)
                    .Add("@FLiqV", SqlDbType.Date).Value = coleccion(16)
                    .Add("@FVencV", SqlDbType.Date).Value = coleccion(17)
                    .Add("@PorcCupV", SqlDbType.Float).Value = CDec(coleccion(18).ToString.Trim("%"))
                    .Add("@PrecVencV", SqlDbType.Float).Value = CDec(coleccion(19).ToString.Trim("%"))
                    .Add("@PrecioVenta", SqlDbType.Float).Value = CDec(coleccion(20).ToString.Trim("%"))
                    If (coleccion(21) Is Nothing) Then
                        .Add("@DiasBaseV", SqlDbType.Int).Value = 365
                    Else
                        .Add("@DiasBaseV", SqlDbType.Int).Value = CInt(coleccion(21).ToString.Trim("%"))
                    End If

                    '.Add("@CostoTransV", SqlDbType.Float).Value = CDec(coleccion(22).ToString.Trim("%"))
                    .Add("@CodInv", SqlDbType.VarChar).Value = CodigoVenta

                    .Add("@ComisionPorCompraCasa", SqlDbType.Float).Value = coleccion(26).ToString()
                    .Add("@ComisionPorCompraBolsa", SqlDbType.Float).Value = coleccion(27).ToString()
                    .Add("@ComisionPorVentaCasa", SqlDbType.Float).Value = coleccion(28).ToString()
                    .Add("@ComisionPorVentaBolsa", SqlDbType.Float).Value = coleccion(29).ToString()

                    .Add("@YTMCompra", SqlDbType.Float).Value = coleccion(30).ToString()
                    .Add("@YTMVenta", SqlDbType.Float).Value = coleccion(31).ToString()




                    .Add("@DiasVenC", SqlDbType.Int).Value = coleccion(32)
                    .Add("@DiasVenV", SqlDbType.Int).Value = coleccion(33)
                    .Add("@DiasAcuC", SqlDbType.Int).Value = coleccion(34)
                    .Add("@DiasAcuV", SqlDbType.Int).Value = coleccion(35)
                    .Add("@YTMVenC", SqlDbType.Float).Value = coleccion(36)
                    .Add("@YTMVenV", SqlDbType.Float).Value = coleccion(37)
                    .Add("@IntAcuC", SqlDbType.Float).Value = coleccion(38)
                    .Add("@IntAcuV", SqlDbType.Float).Value = coleccion(39)
                    .Add("@IntPorAcuC", SqlDbType.Float).Value = coleccion(40)
                    .Add("@IntPorAcuV", SqlDbType.Float).Value = coleccion(41)
                    .Add("@PrecSucioC", SqlDbType.Float).Value = coleccion(42)
                    .Add("@PrecSucioV", SqlDbType.Float).Value = coleccion(43)
                    .Add("@ComisionCasaC", SqlDbType.Float).Value = coleccion(44)
                    .Add("@ComisionCasaV", SqlDbType.Float).Value = coleccion(45)
                    .Add("@ComisionBolsaC", SqlDbType.Float).Value = coleccion(46)
                    .Add("@ComisionBolsaV", SqlDbType.Float).Value = coleccion(47)
                    .Add("@ValTransC", SqlDbType.Float).Value = coleccion(48)
                    .Add("@ValTransV", SqlDbType.Float).Value = coleccion(49)
                    .Add("@MontoPagar", SqlDbType.Float).Value = coleccion(50)
                    .Add("@MontoRecibir", SqlDbType.Float).Value = coleccion(51)
                    .Add("@CostTransC", SqlDbType.Float).Value = coleccion(52)
                    .Add("@CupRecibidos", SqlDbType.Float).Value = coleccion(53)
                End With

                cmd.Connection = cn
                cmd.Transaction = myTrans
                retorno = cmd.ExecuteNonQuery

                ''Crear Nueva Compra


                sql = "UPDATE " + Tabla + " SET 

      [PeriodicC]=@PeriodicC
      ,[ValNomC]=@ValNomC
      ,[UltFCupC]=@UltFCupC
      ,[SigFCupC]=@SigFCupC
      ,[FLiqC]=@FLiqC
      ,[FVencC]=@FVencC
      ,[PorcCupC]=@PorcCupC
      ,[PrecVencC]=@PrecVencC
      ,[PrecioCompra]=@PrecioCompra
      ,[DiasBaseC]  =@DiasBaseC
	  ,[PeriodicV]=@PeriodicV
      ,[ValNomV]=@ValNomV
      ,[UltFCupV]=@UltFCupV
      ,[SigFCupV]=@SigFCupV
      ,[FLiqV]=@FLiqV
      ,[FVencV]=@FVencV
      ,[PorcCupV]=@PorcCupV
      ,[PrecVencV]=@PrecVencV
      ,[PrecioVenta]=@PrecioVenta
      ,[DiasBaseV]=@DiasBaseV
      ,[ComisionPorCompraCasa]=@ComisionPorCompraCasa
      ,[ComisionPorCompraBolsa]=@ComisionPorCompraBolsa
      ,[ComisionPorVentaCasa]=@ComisionPorVentaCasa
      ,[ComisionPorVentaBolsa]=@ComisionPorVentaBolsa
      ,[YTMCompra]=@YTMCompra
      ,[YTMVenta]=@YTMVenta
        ,[DiasVenC]=@DiasVenC
        ,[DiasVenV]=@DiasVenV
        ,[DiasAcuC]=@DiasAcuC
        ,[DiasAcuV]=@DiasAcuV
        ,[YTMVenC]=@YTMVenC
        ,[YTMVenV]=@YTMVenV
        ,[IntAcuC]=@IntAcuC
        ,[IntAcuV]=@IntAcuV
        ,[IntPorAcuC]=@IntPorAcuC
        ,[IntPorAcuV]=@IntPorAcuV
        ,[PrecSucioC]=@PrecSucioC
        ,[PrecSucioV]=@PrecSucioV
        ,[ComisionCasaC]=@ComisionCasaC
        ,[ComisionCasaV]=@ComisionCasaV
        ,[ComisionBolsaC]=@ComisionBolsaC
        ,[ComisionBolsaV]=@ComisionBolsaV
        ,[ValTransC]=@ValTransC
        ,[ValTransV]=@ValTransV
        ,[MontoPagar]=@MontoPagar
        ,[MontoRecibir]=@MontoRecibir
        ,[CostTransC]=@CostTransC
        ,[CupRecibidos]=@CupRecibidos
WHERE       [CodInv] = @CodInv

 
"







                cmd = New SqlCommand
                cmd.CommandText = sql
                'Try

                With cmd.Parameters
                    If (coleccion(1) Is Nothing) Then
                        .Add("@PeriodicC", SqlDbType.VarChar).Value = "M"
                    Else
                        .Add("@PeriodicC", SqlDbType.VarChar).Value = coleccion(1).ToString()
                    End If

                    .Add("@ValNomC", SqlDbType.Float).Value = CDec(coleccion(2).ToString.Trim("%"))
                    .Add("@UltFCupC", SqlDbType.Date).Value = coleccion(3)
                    .Add("@SigFCupC", SqlDbType.Date).Value = coleccion(4)
                    .Add("@FLiqC", SqlDbType.Date).Value = coleccion(5)
                    .Add("@FVencC", SqlDbType.Date).Value = coleccion(6)
                    .Add("@PorcCupC", SqlDbType.Float).Value = CDec(coleccion(7).ToString.Trim("%"))
                    .Add("@PrecVencC", SqlDbType.Float).Value = CDec(coleccion(8).ToString.Trim("%"))
                    .Add("@PrecioCompra", SqlDbType.Float).Value = CDec(coleccion(9).ToString.Trim("%"))

                    If (coleccion(10) Is Nothing) Then
                        .Add("@DiasBaseC", SqlDbType.Int).Value = 365
                    Else
                        .Add("@DiasBaseC", SqlDbType.Int).Value = CInt(coleccion(10).ToString.Trim("%"))
                    End If


                    '.Add("@CostoTransC", SqlDbType.Float).Value = Trim(CDec(coleccion(11)))


                    If (coleccion(12) Is Nothing) Then
                        .Add("@PeriodicV", SqlDbType.VarChar).Value = "M"
                    Else
                        .Add("@PeriodicV", SqlDbType.VarChar).Value = coleccion(12).ToString()
                    End If



                    .Add("@ValNomV", SqlDbType.Float).Value = CDec(coleccion(13).ToString.Trim("%"))
                    .Add("@UltFCupV", SqlDbType.Date).Value = coleccion(14)
                    .Add("@SigFCupV", SqlDbType.Date).Value = coleccion(15)
                    .Add("@FLiqV", SqlDbType.Date).Value = coleccion(16)
                    .Add("@FVencV", SqlDbType.Date).Value = coleccion(17)
                    .Add("@PorcCupV", SqlDbType.Float).Value = CDec(coleccion(18).ToString.Trim("%"))
                    .Add("@PrecVencV", SqlDbType.Float).Value = CDec(coleccion(19).ToString.Trim("%"))
                    .Add("@PrecioVenta", SqlDbType.Float).Value = CDec(coleccion(20).ToString.Trim("%"))
                    If (coleccion(21) Is Nothing) Then
                        .Add("@DiasBaseV", SqlDbType.Int).Value = 365
                    Else
                        .Add("@DiasBaseV", SqlDbType.Int).Value = CInt(coleccion(21).ToString.Trim("%"))
                    End If

                    '.Add("@CostoTransV", SqlDbType.Float).Value = CDec(coleccion(22).ToString.Trim("%"))
                    .Add("@CodInv", SqlDbType.VarChar).Value = CodigoCompra

                    .Add("@ComisionPorCompraCasa", SqlDbType.Float).Value = coleccion(26).ToString()
                    .Add("@ComisionPorCompraBolsa", SqlDbType.Float).Value = coleccion(27).ToString()
                    .Add("@ComisionPorVentaCasa", SqlDbType.Float).Value = coleccion(28).ToString()
                    .Add("@ComisionPorVentaBolsa", SqlDbType.Float).Value = coleccion(29).ToString()

                    .Add("@YTMCompra", SqlDbType.Float).Value = coleccion(30).ToString()
                    .Add("@YTMVenta", SqlDbType.Float).Value = coleccion(31).ToString()




                    .Add("@DiasVenC", SqlDbType.Int).Value = coleccion(32)
                    .Add("@DiasVenV", SqlDbType.Int).Value = coleccion(33)
                    .Add("@DiasAcuC", SqlDbType.Int).Value = coleccion(34)
                    .Add("@DiasAcuV", SqlDbType.Int).Value = coleccion(35)
                    .Add("@YTMVenC", SqlDbType.Float).Value = coleccion(36)
                    .Add("@YTMVenV", SqlDbType.Float).Value = coleccion(37)
                    .Add("@IntAcuC", SqlDbType.Float).Value = coleccion(38)
                    .Add("@IntAcuV", SqlDbType.Float).Value = coleccion(39)
                    .Add("@IntPorAcuC", SqlDbType.Float).Value = coleccion(40)
                    .Add("@IntPorAcuV", SqlDbType.Float).Value = coleccion(41)
                    .Add("@PrecSucioC", SqlDbType.Float).Value = coleccion(42)
                    .Add("@PrecSucioV", SqlDbType.Float).Value = coleccion(43)
                    .Add("@ComisionCasaC", SqlDbType.Float).Value = coleccion(44)
                    .Add("@ComisionCasaV", SqlDbType.Float).Value = coleccion(45)
                    .Add("@ComisionBolsaC", SqlDbType.Float).Value = coleccion(46)
                    .Add("@ComisionBolsaV", SqlDbType.Float).Value = coleccion(47)
                    .Add("@ValTransC", SqlDbType.Float).Value = coleccion(48)
                    .Add("@ValTransV", SqlDbType.Float).Value = coleccion(49)
                    .Add("@MontoPagar", SqlDbType.Float).Value = coleccion(50)
                    .Add("@MontoRecibir", SqlDbType.Float).Value = coleccion(51)
                    .Add("@CostTransC", SqlDbType.Float).Value = coleccion(52)
                    .Add("@CupRecibidos", SqlDbType.Float).Value = coleccion(53)
                End With

                cmd.Connection = cn
                cmd.Transaction = myTrans
                retorno = cmd.ExecuteNonQuery




                myTrans.Commit()


            Else Tabla = "Error"
                MsgBox("No se guardo el instrumento")
            End If

        Catch ex As Exception
            myTrans.Rollback()
            MsgBox("No se guardo el instrumento")
        End Try
    End Sub
End Class