none
Visual basic 10 Guardar en una base de datos Accsess data grid view RRS feed

  • Pregunta

  • Hola buenas tardes, tengo un problema, el codigo que les presentaré  funciana bien para guardar en una base de datos acces desde los texbox; pero quiero guardar tanto los texbox colo lo que muestro en en un data grid view al mismo tiemo. por fabor ayudenme. El código es el siguiente:

    Imports System.Data.OleDb 'Permite Manejo de Base de datos Accses
    Public Class FACTURA
        Dim cnn As New OleDb.OleDbConnection
        Dim StrConexion As String
        Dim StrComando As String
        Dim adapter As New OleDbDataAdapter
        Dim Cb As OleDbCommandBuilder
        Dim copia As New DataSet
        Private Sub FACTURA_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dt.Columns.Clear()
            Limpiar_Formulario()
            'Guardar desde datagrid en access
            Dt.Columns.Add(DcClasificacion)
            Dt.Columns.Add(DcExamen)
            ' Dt.Columns.Add(DcCantidad)
            Dt.Columns.Add(DcPrecio)
            Dt.Columns.Add(DcImporte)
            Me.DataGridView2.DataSource = Dt
        End Sub
        Private Sub intefase_Entrada()
            'Se Ejecuta cuando se carga el formulario
            'HABILITADOS
            TXTCODIGO.Enabled = True
            BTNMODIFICA.Enabled = True
            BTNSALIR.Enabled = True
            BTNBUSCAR.Enabled = True
            'DESABILITADOS
            TXTNOMBRE.Enabled = False
            TXTAPELLIDO.Enabled = False
            TXTCI.Enabled = False
            TXTTELEFONO.Enabled = False
            TXTDIRECCION.Enabled = False
            TXTDESCUENTO.Enabled = False
            BTNNUEVO.Enabled = False
        End Sub
        Private Sub Interfase_Datos()
            'Se Ejecuta cuando se INGRESA NUEVOS DATOS
            'DESABILITADOS
            TXTCODIGO.Enabled = False
            BTNMODIFICA.Enabled = False
            BTNSALIR.Enabled = False
            BTNBUSCAR.Enabled = False
            'HABILITADOS
            TXTNOMBRE.Enabled = True
            TXTAPELLIDO.Enabled = True
            TXTCI.Enabled = True
            TXTTELEFONO.Enabled = True
            TXTDIRECCION.Enabled = True
            TXTDESCUENTO.Enabled = True
            BTNNUEVO.Enabled = True
        End Sub
        Private Sub BTNBUSCAR_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNBUSCAR.Click
            'CONTADOR  EXPEDIENTE AL PULSAR PACIENTE
            Dim exp As Double
            Dim exo2 As Double
            exp = Val(TXTEXPEDIENTE.Text)
            exo2 = exp + 1
            TXTEXPEDIENTE.Text = exo2
            ' NÚMERO FACTURA A PARTIR DE UNA EXISTENTE 
            Dim numerofactuta As Double
            Dim numerofactura2 As Double
            numerofactuta = Val(TXTNUMEROFACTURA.Text)
            numerofactura2 = TXTEXPEDIENTE.Text + 421
            TXTNUMEROFACTURA.Text = numerofactura2
            'BUSCANDO PACIENTE
            If Buscar_Registro(TXTCODIGO.Text) = True Then
                'Mostrar mensaje  Registro existente
                MessageBox.Show("El Registro ya Existe")
                TXTCODIGO.Focus()
                Interfase_Datos()
                TXTDR.Focus()
            Else
                MessageBox.Show("El Paciente no existe, Registreo por favor")
                Limpiar_Formulario()
                Form2.ShowDialog()
                ' restando expediente por no encontralo
                exp = Val(TXTEXPEDIENTE.Text)
                exo2 = exp - 1
                TXTEXPEDIENTE.Text = exo2
                ' restando número de factura  por no encontralo
                numerofactuta = Val(TXTNUMEROFACTURA.Text)
                numerofactura2 = TXTEXPEDIENTE.Text + 421
                TXTNUMEROFACTURA.Text = numerofactura2
            End If
            TXTDR.Focus()
        End Sub
        Function Buscar_Registro(ByVal xid As String) As Boolean
            If xid = "" Then
                MessageBox.Show("INGRESE EL CODIGO A BUSCAR POR FAVOR")
                xid = 0
                TXTCODIGO.Enabled = True
                TXTCODIGO.Focus()
            End If
            ' 1) Covertir  Cadena en número
            Dim Id As Double
            Id = Convert.ToInt32(xid)
            ' 2) Conección
            Dim Conexion As New OleDbConnection
            Conexion.ConnectionString = "Provider= Microsoft.ACE.OLEDB.12.0; Data Source= C:\TECNILABBASE2014.accdb; Persist Security info= False"
            ' 3) Cadena SQL
            Dim cadenasql As String = "SELECT * FROM Afiliados WHERE codigo = " & Id
            ' 4) Adaptador
            Dim Adaptador As New OleDbDataAdapter(cadenasql, Conexion)
            ' 5) Data Set
            Dim Ds As New DataSet
            ' 6) Llenar Data Set
            Conexion.Open() 'Abre conexion
            Adaptador.Fill(Ds) 'El Adaptador llena de datos al DATA SET
            Conexion.Close()
            ' 7) Contar Registro
            If (Ds.Tables(0).Rows.Count = 0) Then
                'No encontro el registro
                Return False
            Else
                'Si encontro Registro
                'Carga los texbox del formulario con la información del registro encontrado
                TXTNOMBRE.Text = Ds.Tables(0).Rows(0)("nombres").ToString()
                TXTAPELLIDO.Text = Ds.Tables(0).Rows(0)("apellidos").ToString()
                TXTCI.Text = Ds.Tables(0).Rows(0)("ci").ToString()
                TXTTELEFONO.Text = Ds.Tables(0).Rows(0)("telefono").ToString()
                TXTDIRECCION.Text = Ds.Tables(0).Rows(0)("direccion").ToString()
                TXTDESCUENTO.Text = Ds.Tables(0).Rows(0)("descuento").ToString()
                Ds.Dispose()
                Return (True)
            End If
        End Function
        Private Sub Limpiar_Formulario()
            'Limpia los textos del formulario
            TXTNOMBRE.Clear()
            TXTAPELLIDO.Clear()
            TXTCI.Clear()
            TXTTELEFONO.Clear()
            TXTDIRECCION.Clear()
            TXTDESCUENTITO.Clear()
            TXTSUBTOTAL.Clear()
            TXTDESCUENTO.Clear()
            TXTTOTAL.Clear()
            TXTIVA.Clear()
            TXTVALORTOTAL.Clear()
            TXTSALDO.Clear()
            TXTABONO.Clear()
            TXTINGRESAIVA.Clear()
            TXTDESCUENTO.Clear()
            ExamenTextBox.Clear()
            preciotxt.Clear()
            txtclasificacion.Clear()
            TXTBUSCARCLASIF.Clear()
            TXTCANTIDAD.Clear()
        End Sub
        Dim Dt As New DataTable
        Dim Dr As DataRow
        Dim DcClasificacion As New DataColumn("Clasificación", GetType(System.String))
        Dim DcExamen As New DataColumn("Examen", GetType(System.String))
        Dim DcCantidad As New DataColumn("Cantidad", GetType(System.Int16))
        Dim DcPrecio As New DataColumn("Precio", GetType(System.Int16))
        Dim DcImporte As New DataColumn("Importe", GetType(System.String))
        Private Sub TXTBUSCARCLASIF_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TXTBUSCARCLASIF.TextChanged
            Filtrar()
        End Sub
        Sub CARGARDATOS()
        End Sub
        'Buscar 
        Sub Filtrar()
            ' 2) Conección
            Dim Conexion As New OleDbConnection
            Conexion.ConnectionString = "Provider= Microsoft.ACE.OLEDB.12.0; Data Source= C:\TECNILABBASE2014.accdb; Persist Security info= False"
            ' 3) Cadena SQL
            Dim CARGA As New OleDbDataAdapter("SELECT * FROM EXAMEN_CON WHERE EXAMEN LIKE'" & TXTBUSCARCLASIF.Text & "%'", Conexion)
            Dim DS As New DataSet
            CARGA.Fill(DS, " EXAMEN_CON")
            DataGridView1.DataSource = DS.Tables(" EXAMEN_CON")
        End Sub
        Private Sub BTNEXAMEN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNEXAMEN.Click
            'Validando Datos vacios
            Dim texto As String = ""
            If TXTABONO.Text = "" Then
                texto = "    ABONO" & Chr(13) & texto
            End If
            If TXTINGRESAIVA.Text = "" Then
                texto = "    IVA" & Chr(13) & texto
            End If
            If TXTBUSCARCLASIF.Text = "" Then
                texto = "    BUCAR UNA CLASIFICACIÓN" & Chr(13) & texto
            End If
            If ExamenTextBox.Text = "" Then
                texto = "    NOMBRE DEL EXAMEN" & Chr(13) & texto
            End If
            If preciotxt.Text = "" Then
                texto = "     PRECIO DEL EXAMEN" & Chr(13)
            End If
            If txtclasificacion.Text = "" Then
                texto = "     CLASIFICACIÓN" & Chr(13) & texto
            End If
            If texto <> "" Then
                MsgBox("No se a podido guardar los datos. Porfavor revise los siguientes puntos : " & Chr(13) & Chr(13) & texto, MsgBoxStyle.Information, Application.ProductName)
                Exit Sub
            End If
            'Pasando datos al Datagrid view2 
            Dim NumFila As Integer = Me.DataGridView1.CurrentCell.RowIndex
            Dim numcolum As Integer = Me.DataGridView1.CurrentCell.ColumnIndex
            Dim dato As String = Me.DataGridView1(numcolum, NumFila).Value
            ExamenTextBox.Text = dato
            Dr = Dt.NewRow
            Dr = Dt.NewRow
            Dr(DcClasificacion) = Me.txtclasificacion.Text
            Dr(DcExamen) = Me.ExamenTextBox.Text
            Dr(DcPrecio) = Me.preciotxt.Text
            Dt.Rows.Add(Dr)
            Dim filas As Integer = Me.DataGridView2.Rows.Count - 1
            If filas = 0 Then Me.LBLCANTIDAD.Text = filas & "    Registro encontrado.....!"
            If filas = 1 Then Me.LBLCANTIDAD.Text = filas & "    Registro encontrado.....!"
            If filas >= 1 Then Me.LBLCANTIDAD.Text = filas & "   Registro encontrados.....!"
            'Sumar una Columna
            Dim Total As Single
            Dim Col As Integer = Me.DataGridView1.CurrentCell.ColumnIndex
            For Each row As DataGridViewRow In Me.DataGridView2.Rows
                Total += Val(row.Cells(Col).Value)
            Next
            'Calcula el Descuento
            TXTSUBTOTAL.Text = Total
            TXTDESCUENTITO.Text = Val(TXTDESCUENTO.Text * TXTSUBTOTAL.Text / 100)
            TXTTOTAL.Text = Val(TXTSUBTOTAL.Text - TXTDESCUENTITO.Text)
            'Calcula Iva
            TXTIVA.Text = Val(TXTINGRESAIVA.Text * TXTSUBTOTAL.Text / 100)
            'Calcula Valor Total a Cancelar
            Dim tot As Double
            Dim desc As Double
            tot = TXTTOTAL.Text
            desc = TXTIVA.Text
            TXTVALORTOTAL.Text = (tot + desc)
            'Calcula Saldo
            TXTSALDO.Text = TXTVALORTOTAL.Text - TXTABONO.Text
        End Sub
        Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
            If e.RowIndex > -1 Then
                txtclasificacion.Text = DataGridView1("Clasificacion", e.RowIndex).Value.ToString()
                ExamenTextBox.Text = DataGridView1("Examen", e.RowIndex).Value.ToString()
                preciotxt.Text = DataGridView1("Precio", e.RowIndex).Value.ToString()
            End If
        End Sub
        Private Sub CALCULAR_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CALCULAR.Click
            'Sumar una Columna
            Dim Total As Single
            Dim Col As Integer = Me.DataGridView1.CurrentCell.ColumnIndex
            For Each row As DataGridViewRow In Me.DataGridView2.Rows
                Total += Val(row.Cells(Col).Value)
            Next
            'Calcula el Descuento
            TXTSUBTOTAL.Text = Total
            TXTDESCUENTITO.Text = Val(TXTDESCUENTO.Text * TXTSUBTOTAL.Text / 100)
            TXTTOTAL.Text = Val(TXTSUBTOTAL.Text - TXTDESCUENTITO.Text)
            'Calcula Iva
            TXTIVA.Text = Val(TXTINGRESAIVA.Text * TXTSUBTOTAL.Text / 100)
            'Calcula Valor Total a Cancelar
            Dim tot As Double
            Dim desc As Double
            tot = TXTTOTAL.Text
            desc = TXTIVA.Text
            TXTVALORTOTAL.Text = (tot + desc)
            'Calcula Saldo
            TXTSALDO.Text = TXTVALORTOTAL.Text - TXTABONO.Text
        End Sub
        'Borrar fila del Data Grid
        Private Sub cdm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cdm.Click
            If MsgBox("Desea borrar la fila seleccionada?.", MsgBoxStyle.YesNo Or MsgBoxStyle.Information Or MsgBoxStyle.DefaultButton2, "Eliminar Fila") = MsgBoxResult.Yes Then
                DataGridView2.Rows.RemoveAt(DataGridView2.CurrentRow.Index)
                DataGridView2.Refresh()
                'Sumar una Columna
                Dim Total As Single
                Dim Col As Integer = Me.DataGridView1.CurrentCell.ColumnIndex
                For Each row As DataGridViewRow In Me.DataGridView2.Rows
                    Total += Val(row.Cells(Col).Value)
                Next
                'Calcula el Descuento
                TXTSUBTOTAL.Text = Total
                TXTDESCUENTITO.Text = Val(TXTDESCUENTO.Text * TXTSUBTOTAL.Text / 100)
                TXTTOTAL.Text = Val(TXTSUBTOTAL.Text - TXTDESCUENTITO.Text)
                'Calcula Iva
                TXTIVA.Text = Val(TXTINGRESAIVA.Text * TXTSUBTOTAL.Text / 100)
                'Calcula Valor Total a Cancelar
                Dim tot As Double
                Dim desc As Double
                tot = TXTTOTAL.Text
                desc = TXTIVA.Text
                TXTVALORTOTAL.Text = (tot + desc)
                'Calcula Saldo
                TXTSALDO.Text = TXTVALORTOTAL.Text - TXTABONO.Text
            End If
        End Sub
        Function Agrego_Registro(ByVal Idd As String, ByVal expediente As String, ByVal clasificacion As String, ByVal examen As String, ByVal precio As String, ByVal subtotal As String, ByVal ivaporciento As String, ByVal ivaciento As String,
                                 ByVal descuento As String, ByVal descuentito As String, ByVal saldo As String, ByVal abono As String, ByVal total As String, ByVal ci As String, ByVal fecha As String, ByVal codigofactura As String) As Boolean
            'Sirve para guardar la fecha del data time picker 
            Dim Control As String
            Control = FechaDateTimePicker.Value
            TextBox1.Text = Control ' 
            'Convertir
            Dim Cod As Integer = Convert.ToInt32(Idd)
            Dim EXPED As Integer = Convert.ToInt32(expediente)
            'Dim CANTI As Integer = Convert.ToDouble(cantidad)
            Dim PRECI As Integer = Convert.ToDouble(precio)
            Dim SUBTO As Integer = Convert.ToDouble(subtotal)
            Dim IV As Integer = Convert.ToDouble(ivaporciento)
            Dim IVACIENT As Integer = Convert.ToDouble(ivaciento)
            Dim DESCUEN As Integer = Convert.ToDouble(descuento)
            Dim DESCUENTIT As Integer = Convert.ToDouble(descuentito)
            Dim SALD As Integer = Convert.ToDouble(saldo)
            Dim ABON As Integer = Convert.ToDouble(abono)
            Dim TOTA As Integer = Convert.ToDouble(total)
            Dim CId As Integer = Convert.ToInt32(ci)
            Dim COFACT As Integer = Convert.ToInt32(codigofactura)
            'Conexion
            Dim Conexion As New OleDbConnection
            Conexion.ConnectionString = "Provider= Microsoft.ACE.OLEDB.12.0; Data Source= C:\TECNILABBASE2014.accdb; Persist Security info= False"
            'Instruccion SQL
            Dim cadenaSQL As String = " INSERT INTO factura (codigo,expediente,clasificacion,examen,precio,subtotal,ivaporciento,ivaciento,descuento,descuentito,saldo,abono,total,ci,fecha,codigofactura)"
            cadenaSQL = cadenaSQL + " VALUES (" & Cod & ","
            cadenaSQL = cadenaSQL + "             " & EXPED & ","
            ' cadenaSQL = cadenaSQL + "             " & CANTI & ","
            cadenaSQL = cadenaSQL + "            '" & clasificacion & "',"
            cadenaSQL = cadenaSQL + "            '" & examen & "',"
            cadenaSQL = cadenaSQL + "             " & PRECI & ","
            cadenaSQL = cadenaSQL + "            " & SUBTO & ","
            cadenaSQL = cadenaSQL + "            " & IV & ","
            cadenaSQL = cadenaSQL + "            " & IVACIENT & ","
            cadenaSQL = cadenaSQL + "            " & DESCUEN & ","
            cadenaSQL = cadenaSQL + "            " & DESCUENTIT & ","
            cadenaSQL = cadenaSQL + "             " & SALD & ","
            cadenaSQL = cadenaSQL + "            " & abono & ","
            cadenaSQL = cadenaSQL + "            " & TOTA & ","
            cadenaSQL = cadenaSQL + "            " & CId & ","
            cadenaSQL = cadenaSQL + "           '" & Control & "',"
            cadenaSQL = cadenaSQL + "            " & COFACT & ")"
            Dim dt As New DataTable
           
            'Crear un comando
            Dim comando As OleDbCommand = Conexion.CreateCommand()
            'ASIGNAMOS LA CADENA SQL
            comando.CommandText = cadenaSQL
            'Ejecutar la consulta de acción
            Conexion.Open()
            comando.ExecuteNonQuery()
            Conexion.Close()
            Return True
        End Function
        'Guarda en la base de datos
        Private Sub BTNGUARDAR_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNGUARDAR.Click
            Dim texto As String = ""
            If TXTABONO.Text = "" Then
                texto = "    ABONO" & Chr(13) & texto
            End If
            If TXTINGRESAIVA.Text = "" Then
                texto = "    IVA" & Chr(13) & texto
            End If
            If texto <> "" Then
                MsgBox("No se a podido guardar los datos. Porfavor revise los siguientes puntos : " & Chr(13) & Chr(13) & texto, MsgBoxStyle.Information, Application.ProductName)
                'ValidarDatos = False
                Exit Sub
            End If
            Agrego_Registro(TXTCODIGO.Text, TXTEXPEDIENTE.Text, txtclasificacion.Text, ExamenTextBox.Text, preciotxt.Text, TXTSUBTOTAL.Text, TXTIVA.Text, TXTINGRESAIVA.Text, TXTDESCUENTO.Text, TXTDESCUENTITO.Text, TXTSALDO.Text, TXTABONO.Text, TXTVALORTOTAL.Text, TXTCI.Text, TextBox1.Text, TXTNUMEROFACTURA.Text)
            Filtrar()
        End Sub
        Private Sub ContadoRadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ContadoRadioButton1.CheckedChanged
            If ContadoRadioButton1.Checked = True Then
                TXTABONO.Text = TXTVALORTOTAL.Text
            End If
        End Sub
        Private Sub TXTDR_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TXTDR.KeyPress
            If Char.IsLetter(e.KeyChar) Then
                e.Handled = False
            ElseIf Char.IsControl(e.KeyChar) Then
                e.Handled = False
            ElseIf Char.IsSeparator(e.KeyChar) Then
                e.Handled = False
            Else
                e.Handled = True
            End If
            'Al puulsar ente pasa al siguiente texbox
            If e.KeyChar = ChrW(Keys.Enter) Then
                TXTABONO.Focus()
            End If
        End Sub
        Private Sub BTNNUEVO_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNNUEVO.Click
            TXTCODIGO.Enabled = True
            TXTCODIGO.Clear()
            BTNBUSCAR.Enabled = True
        End Sub
        Private Sub TXTINGRESAIVA_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TXTINGRESAIVA.KeyPress
            If e.KeyChar = ChrW(Keys.Enter) Then
                TXTBUSCARCLASIF.Focus()
            End If
        End Sub
        Private Sub TXTABONO_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TXTABONO.KeyPress
            If e.KeyChar = ChrW(Keys.Enter) Then
                TXTINGRESAIVA.Focus()
            End If
        End Sub
        Private Sub BTNEXAMEN_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles BTNEXAMEN.KeyPress
            If e.KeyChar = ChrW(Keys.Enter) Then
                BTNGUARDAR.Focus()
            End If
        End Sub
    End Class

    domingo, 18 de agosto de 2013 19:55

Todas las respuestas

  • hola

    lo primero que deberias cambiar en tu codigo es la forma en que asignas los valores en la query, concatener en un string es una pesima idea, para es estan los parametros

    algo como ser

    Using conn As New SqlConnection("<connection string>")
    	conn.Open()
    
    	Dim query As String = "INSERT INTO NombreTabla (nombre, apellido) VALUES (@nombre, @apellido)"
    	Dim cmd As New SqlCommand(query, conn)
    
    
    	For Each row As DataGridViewRow In dataGridView1.Rows
    		cmd.Parameters.Clear()
    
    		cmd.Parameters.AddWithValue("@nombre", Convert.ToString(row.Cells("Nombre").Value))
    		cmd.Parameters.AddWithValue("@apellido", Convert.ToInt32(row.Cells("Apellido").Value))
    
    		cmd.ExecuteNonQuery()
    	Next
    End Using

    la idea es usar el cmd.Parameters

    para asignar los valores a las queries y no concatener en un string

    alli se recorre las rows del grid y se van insertando, algo como eso deberias replicar en tu desarrollo

    saludos


    Leandro Tuttini

    Blog
    Buenos Aires
    Argentina

    lunes, 19 de agosto de 2013 12:20