ADO.NET CONSULTAS ADO.NET 1.‐Propiedades del ListBox y ComboBox.
Seleccionamos el Listbox.
RICHARD NAJARRO TALAVERANO
Página 1
ADO.NET Seleccionamos El ComboBox
CODIGO FUENTE: Imports System.Data Imports System.Data.SqlClient Public Class Form2 Dim cn As New SqlConnection("server=(local);integrated security=sspi;database=neptuno") Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load llenadoComboBox() llenadoListBox() End Sub Sub llenadoComboBox() Dim cad As String = "select * from clientes" Dim da As New SqlDataAdapter(cad, cn) Dim tbl As New DataTable da.Fill(tbl) ComboBox1.DataSource = tbl Me.ComboBox1.DisplayMember = "nombrecompañia" 'Campo a Mostar Me.ComboBox1.ValueMember = "Idcliente" 'Campo a evaluar End Sub Sub llenadoListBox() Dim cad As String = "select * from Clientes" Dim da As New SqlDataAdapter(cad, cn) Dim tbl As New DataTable da.Fill(tbl) ListBox1.DataSource = tbl Me.ListBox1.DisplayMember = "nombrecompañia" 'Campo a Mostar Me.ListBox1.ValueMember = "Idcliente" 'Campo a evaluar End Sub
RICHARD NAJARRO TALAVERANO
Página 2
ADO.NET
Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Try Me.lblmostar.Text = Me.ListBox1.Text Me.lblevaluar.Text = Me.ListBox1.SelectedValue Catch ex As Exception End Try End Sub Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged Try Me.lblmostar.Text = Me.ComboBox1.Text Me.lblevaluar.Text = Me.ComboBox1.SelectedValue Catch ex As Exception End Try End Sub End Class
RICHARD NAJARRO TALAVERANO
Página 3
ADO.NET
2.‐Crear una Aplicación que nos permita listar los Meses de Venta en un Listbox.
RICHARD NAJARRO TALAVERANO
Página 4
ADO.NET Luego de haber listado los meses de venta ,Seleccionar de uno a varios meses de venta ,listando en un DataGridview la Cantidad de pedidos del cliente por mes Seleccionado.
RICHARD NAJARRO TALAVERANO
Página 5
ADO.NET Seleccionar en el DataGridview o Haciendo un Check,luego presionar el boton Grabar ,Devera grabar uno varios clientes seleccionados en una Tabla Creada Vendidos.
Tabla Vendidos create table Vendidos( pedido int, precio money, cantidad int, total money, mes varchar(20)) RICHARD NAJARRO TALAVERANO
Página 6
ADO.NET Luego de haber grabado Mostrar el Ranking de Pedidos Vendidos de la Tabla Vendidos.
Claro que se devera mostrar en Tabla Vendidos. select * from vendidos
RICHARD NAJARRO TALAVERANO
Página 7
ADO.NET CODIGO FUENTE: Imports System.Data Imports System.Data.SqlClient Public Class Form1 Dim cn As New SqlConnection("server=(local);integrated security=sspi;database=neptuno") Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim cad As String = "select distinct datename(month,fechapedido) as Fecha from pedidos" Dim cmd As New SqlCommand(cad, cn) cn.Open() Dim dr As SqlDataReader = cmd.ExecuteReader While (dr.Read) Me.ListBox1.Items.Add(dr("Fecha")) End While cn.Close() End Sub Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Try Dim mes As String = "" For i As Integer = 0 To Me.ListBox1.Items.Count 1 If (Me.ListBox1.GetSelected(i)) Then mes = mes + "'" + Me.ListBox1.Items(i) + "'," End If Next mes = mes.Substring(0, mes.Length 1) Dim cadena As String = "select c.idcliente,nombrecompañia,count(p.idpedido) as Cantidad," & _ " sum(preciounidad*cantidad) as total,datename(month,fechapedido) as Fecha" & _ " from clientes c ,pedidos p,[Detalles de pedidos]d where c.idcliente=p.idcliente and " & _ " p.idpedido=d.idpedido and datename(month,fechapedido) in(" & mes & ")" & _ " group by c.idcliente,nombrecompañia,datename(month,fechapedido)" Dim cmd As New SqlCommand(cadena, cn) Dim da As New SqlDataAdapter(cmd) Dim tbl As New DataTable da.Fill(tbl) Me.DataGridView1.DataSource = tbl Catch ex As Exception End Try End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Try Dim cod As String = "" Dim mes As String = "" For Each row As DataGridViewRow In Me.DataGridView1.Rows Dim ch As DataGridViewCheckBoxCell = CType(row.Cells(0), DataGridViewCheckBoxCell)
RICHARD NAJARRO TALAVERANO
Página 8
ADO.NET If (ch.Value) Then cod = cod + "'" + row.Cells(1).Value.ToString() + "'," mes = mes + "'" + row.Cells(5).Value.ToString() + "'," End If Next cod = cod.Substring(0, cod.Length 1) mes = mes.Substring(0, mes.Length 1) Dim cadena As String = "select p.idpedido,preciounidad,cantidad,(preciounidad*cantidad) as total,datename(month,fechapedido) as Mes" & _ " from [detalles de pedidos]d, pedidos p where p.idpedido=d.idpedido and " & _ " idcliente in (" & cod & ") and datename(month,fechapedido) in (" & mes & ")" Dim cmd As New SqlCommand(cadena, cn) Dim da As New SqlDataAdapter(cmd) Dim tbl As New DataTable da.Fill(tbl) Dim j As Integer For i As Integer = 0 To tbl.Rows.Count 1 Dim cad As String = "insert into Vendidos (pedido,precio,cantidad,total,mes) values (@pedido,@precio,@cantidad,@total,@mes)" Dim cmd1 As New SqlCommand(cad, cn) cmd1.Parameters.Add("@pedido", SqlDbType.Int).Value = tbl.Rows(i)(0) cmd1.Parameters.Add("@precio", SqlDbType.Money).Value = tbl.Rows(i)(1) cmd1.Parameters.Add("@cantidad", SqlDbType.Int).Value = tbl.Rows(i)(2) cmd1.Parameters.Add("@total", SqlDbType.Money).Value = tbl.Rows(i)(3) cmd1.Parameters.Add("@mes", SqlDbType.VarChar, 20).Value = tbl.Rows(i)(4) cn.Open() j = cmd1.ExecuteNonQuery() cn.Close() Next If j > 0 Then MsgBox("Grabo Correctamente", MsgBoxStyle.YesNoCancel) ListadoVendidos() End If Catch ex As Exception End Try End Sub Sub ListadoVendidos() Dim cad As String = "select * from vendidos order by cantidad desc" Dim da As New SqlDataAdapter(cad, cn) Dim tbl As New DataTable da.Fill(tbl) Me.DataGridView2.DataSource = tbl Me.lbltotal.Text = tbl.Compute("sum(total)", "") Me.lblcantidad.Text = tbl.Compute("sum(cantidad)", "") End Sub End Class
RICHARD NAJARRO TALAVERANO
Página 9