I. P. Fray L. Beltrán 2014
7º año "A"
Bases de Datos Relacionales
Ejemplos de uso de sentencias SQL
INTERFAZ GRAFICA
CODIGO ASOCIADO A LA INTERFAZ (BUSCAR UN REGISTRO PARA ELIMINARLO)
EN GENERAL
Dim mi_codigodeprestamo As Integer
Dim mi_id_Deprestamo As Integer
Private Sub Command1_Click()
Dim busca As ADODB.Recordset
Set busca = New ADODB.Recordset
With busca
.Open "SELECT id_prestamo FROM prestamo where id_prestamo=" & mi_codigodeprestamo, ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
.Delete
.Update
.Close
End With
End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
Form7.Hide
Form2.Show
End Sub
Private Sub migrilla1_Click()
mi_codigodeherramienta = migrilla1.TextMatrix(migrilla1.RowSel, 1)
Dim busca As ADODB.Recordset
Set busca = New ADODB.Recordset
With busca
.Open "SELECT id_prestamo,herramientas.nombre,nombre_alum,fecha FROM herramientas,prestamo where prestamo.cod_herramienta = herramientas.cod_herramienta and prestamo.cod_herramienta =" & mi_codigodeherramienta, ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
With migrilla2
.ColWidth(0) = 15
.ColWidth(1) = 15
.ColWidth(2) = 1900
.ColWidth(3) = 1900
.ColWidth(4) = 1900
End With
Set migrilla2.DataSource = busca
End With
End Sub
Private Sub migrilla2_DblClick()
mi_codigodeprestamo = migrilla2.TextMatrix(migrilla2.RowSel, 1)
Dim busca As ADODB.Recordset
Set busca = New ADODB.Recordset
With busca
.Open "SELECT id_prestamo FROM prestamo where id_prestamo=" & mi_codigodeprestamo, ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
Label1 = migrilla2.TextMatrix(migrilla2.RowSel, 2) & " " & migrilla2.TextMatrix(migrilla2.RowSel, 3) & " " & migrilla2.TextMatrix(migrilla2.RowSel, 4)
End With
End Sub
Private Sub Text1_Change()
Dim busca As ADODB.Recordset
Set busca = New ADODB.Recordset
With busca
.Open "SELECT cod_herramienta,nombre FROM herramientas where nombre like'" & Trim(Text1.Text) & "%'", ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
With migrilla1
.ColWidth(0) = 15
.ColWidth(1) = 15
.ColWidth(2) = 1900
End With
Set migrilla1.DataSource = busca
End With
End Sub
Ejemplo de búsqueda SQL donde intervienen varias tablas. Observe que mientras las comparaciones son entre los campos de las tablas, todo se desarrolla dentro de las comillas principales, pero cuando hay que comparar un campo con un valor externo, dicho valor se coloca fuera de las comillas principales, separado por un & (ampersand).
.Open "select * from factura,ventas,articulos,clientes where factura.cod_cliente = clientes.cod_cli and factura.num_factura = ventas.num_fact and ventas.num_articulo = articulos.cod_artic and factura.num_factura = " & Val(Text6), ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
Ejemplo de búsqueda SQL entre dos fechas (text9 y text10). Observese que si bien text9 y text10 son datos externos, es decir, no son campos de tablas, no se colocan fuera de las comillas principales, pero utilizan el símbolo # para separar los datos.
.Open "SELECT num_factura as Numero_Factura,fecha,clientes.apel as Apellido FROM factura,clientes where factura.fecha between #" & CDate(Text9.Text) & "# AND #" & CDate(Text10.Text) & "# and clientes.cod_cli = factura.cod_cliente", ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
Tener en cuenta que el orden de búsqueda será Mes/Día/Año
Igual a la búsqueda anterior, pero con format de fecha, donde podemos buscar Dia/Mes/Año
.Open "SELECT * FROM cuotas where cuotas.mes_anio_cobro between #" & Format(CDate(Text14.Text), "dd/mm/yyyy") & "# AND #" & Format(CDate(Text15.Text), "dd/mm/yyyy") & "# And cod_curso =" & Val(Text16), AdoCN, adOpenDynamic, adLockOptimistic, adCmdText
Ejemplo de búsqueda SQL . Aqui la búsqueda es de tipo alfanumérica. Observese que si bien el campo descrip es comparado con un dato externo (text7), todo queda dentro de las comillas principales, separado por el carácter ‘ (apóstrofe). Cuando se comparan valores alfanuméricos, utilizamos la cláusula LIKE.
busca.Open "SELECT * FROM articulos where descrip like'" & Trim(Text7) & "%'", ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
Similar al ejemplo anterior, pero con un agregado AND. Observe que el campo se compara con un valor externo, por lo tanto, sa coloca fuera de las comillas principales.
busca.Open "SELECT * FROM articulos where descrip like'" & Trim(Text7) & "%' and cod_artic =" & Val(Text2), ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
Ejemplo de como comparar VARIOS CAMPOS con variables numéricas.
busca.Open "SELECT * FROM profesor where cod_materia =" & mate_guardada & " And cod_profe = " & profesor1, ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
EJEMPLO DE "ALTAS" EN EL PROYECTO PAÑOL
EN GENERAL
Dim Mi_codigo_categoria As Integer
Dim Mi_codigo_profesor As Integer
Dim Mi_codigo_materia As Integer
Private Sub Command1_Click()
Dim altas As ADODB.Recordset
Set altas = New ADODB.Recordset
If Text1.Text = "" Then
MsgBox "Complete los campos"
Exit Sub
End If
With altas
.Open "select * from categorias", ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
.AddNew
!Desc = Text1.Text
.Update
.Close
End With
Text1.Text = ""
Text1.SetFocus
End Sub
Private Sub Command2_Click()
Dim altas As ADODB.Recordset
Set altas = New ADODB.Recordset
If Text2.Text = "" Then
MsgBox "Complete los campos"
Exit Sub
End If
With altas
.Open "select * from herramientas", ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
.AddNew
!nombre = Text2.Text
!cod_categoria = Mi_codigo_categoria
.Update
.Close
End With
Text2.Text = ""
Label4 = ""
Text2.SetFocus
migrilla1.Clear
End Sub
Private Sub Command3_Click()
Dim busca As ADODB.Recordset
Set busca = New ADODB.Recordset
With busca
.Open "SELECT cod_catego as Codigo_Categoría, desc as Descripción FROM categorias ", ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
With migrilla1
.ColWidth(0) = 15
.ColWidth(1) = 1500
.ColWidth(2) = 1500
End With
Set migrilla1.DataSource = busca
End With
End Sub
Private Sub Command4_Click()
Dim altas As ADODB.Recordset
Set altas = New ADODB.Recordset
If Text3.Text = "" Then
MsgBox "Complete los campos"
Exit Sub
End If
With altas
.Open "select * from curso", ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
.AddNew
!nombre = Text3.Text
.Update
.Close
End With
Text3.Text = ""
Text3.SetFocus
End Sub
Private Sub Command5_Click()
Dim altas As ADODB.Recordset
Set altas = New ADODB.Recordset
If Text4.Text = "" Then
MsgBox "Complete los campos"
Exit Sub
End If
With altas
.Open "select * from apel_prof", ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
.AddNew
!apel = Text4.Text
.Update
.Close
End With
Text4.Text = ""
Text4.SetFocus
End Sub
Private Sub Command6_Click()
Dim altas As ADODB.Recordset
Set altas = New ADODB.Recordset
If Text5.Text = "" Then
MsgBox "Complete los campos"
Exit Sub
End If
With altas
.Open "select * from estado", ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
.AddNew
!Desc = Text5.Text
.Update
.Close
End With
Text5.Text = ""
Text5.SetFocus
End Sub
Private Sub Command7_Click()
Dim altas As ADODB.Recordset
Set altas = New ADODB.Recordset
If Text6.Text = "" Then
MsgBox "Complete los campos"
Exit Sub
End If
With altas
.Open "select * from materia", ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
.AddNew
!nom_materia = Text6.Text
.Update
.Close
End With
Text6.Text = ""
Text6.SetFocus
End Sub
Private Sub Command8_Click()
Dim altas As ADODB.Recordset
Set altas = New ADODB.Recordset
If Label13 = "" Or Label15 = "" Then
MsgBox "Complete los campos"
Exit Sub
End If
With altas
.Open "select * from profesor", ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
.AddNew
!cod_profe = Mi_codigo_profesor
!cod_materia = Mi_codigo_materia
.Update
.Close
End With
Text7.Text = ""
Text7.SetFocus
Text8 = ""
migrilla2.Clear
migrilla3.Clear
Label13 = ""
Label15 = ""
End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
Form3.Hide
Form2.Show
End Sub
Private Sub migrilla1_DblClick()
Label4 = migrilla1.TextMatrix(migrilla1.RowSel, 1)
Mi_codigo_categoria = migrilla1.TextMatrix(migrilla1.RowSel, 1)
End Sub
Private Sub migrilla2_DblClick()
Label15 = migrilla2.TextMatrix(migrilla2.RowSel, 2)
Mi_codigo_materia = migrilla2.TextMatrix(migrilla2.RowSel, 1)
End Sub
Private Sub migrilla3_DblClick()
Label13 = migrilla3.TextMatrix(migrilla3.RowSel, 2)
Mi_codigo_profesor = migrilla3.TextMatrix(migrilla3.RowSel, 1)
End Sub
Private Sub Text7_Change()
Dim busca As ADODB.Recordset
Set busca = New ADODB.Recordset
With busca
.Open "SELECT cod_materia as Código_Materia,nom_materia as Nombre_Materia FROM materia where nom_materia like'" & Trim(Text7.Text) & "%'", ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
With migrilla2
.ColWidth(0) = 15
.ColWidth(1) = 1500
.ColWidth(2) = 1500
End With
Set migrilla2.DataSource = busca
End With
End Sub
Private Sub Text8_Change()
Dim busca As ADODB.Recordset
Set busca = New ADODB.Recordset
With busca
.Open "SELECT id_prof as Código_Profesor,apel as Apellido FROM apel_prof where apel like'" & Trim(Text8.Text) & "%'", ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
With migrilla3
.ColWidth(0) = 15
.ColWidth(1) = 1500
.ColWidth(2) = 1500
End With
Set migrilla3.DataSource = busca
End With
End Sub
Ejemplo : INGRESAR O QUITAR PAÑOLERO
Codigo asociado a la pantalla "Agregar / Quitar Pañolero"
Private Sub Command2_Click()
Dim busca As ADODB.Recordset
Set busca = New ADODB.Recordset
With busca
.Open "SELECT hora_salida,apel_panol FROM turno where fecha = date() and hora_salida =" & 24, ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
If .RecordCount > 0 Then
sale = !apel_panol
!hora_salida = Time
.Update
MsgBox "El Pañolero " & sale & " Ya no se encuentra afectado a la tarea"
End If
End With
End Sub
Private Sub Form_Activate()
Text2.SetFocus
End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
Form4.Hide
Form2.Show
End Sub
Private Sub migrilla4_DblClick()
Label4 = migrilla4.TextMatrix(migrilla4.RowSel, 1)
Mi_codigo_del_curso = migrilla4.TextMatrix(migrilla4.RowSel, 1)
End Sub
Private Sub Text1_Change()
Dim busca As ADODB.Recordset
Set busca = New ADODB.Recordset
With busca
.Open "SELECT cod_curso as código, nombre as Curso FROM curso where nombre like'" & Trim(Text1.Text) & "%'", ADOCN, adOpenDynamic, adLockOptimistic, adCmdText
With migrilla4
.ColWidth(0) = 15
.ColWidth(1) = 1500
End With
Set migrilla4.DataSource = busca
End With
End Sub
