En Microsoft SQL Server
use master
go
if exists (select * from sysdatabases where name = 'BDEvento')
drop database BDEvento
go
create database BDEvento
go
use BDEvento
go
--creacion de las tablas
CREATE TABLE Ponente
(
codponente varchar (4) NOT NULL,
nombre varchar (80) NOT NULL,
fecnac date NOT NULL,
sexo char (1) NOT NULL,
grado varchar (25) NOT NULL,
PRIMARY KEY (codponente)
)
go
CREATE TABLE Evento
(
codevento char (3) NOT NULL,
nombreevento varchar (80) NULL,
lugar varchar (80) NULL,
fecha datetime NULL,
hora datetime NULL,
precio decimal (8,2) NULL,
idponente varchar (4) NULL,
PRIMARY KEY (codevento),
FOREIGN KEY (idponente) REFERENCES Ponente
)
go
CREATE TABLE Participante
(
codparticipante int identity (1,1),
nombre varchar (60) NULL,
direccion varchar (40) NULL,
telefono varchar (11) NULL,
fecnac date NULL,
sexo char (1) NULL,
profesion varchar (60) NULL,
PRIMARY KEY (codparticipante)
)
go
CREATE TABLE ReciboPago
(
nrecibo char (8) NOT NULL,
fecha date NULL,
idparticipante int NULL,
idevento char (3) NULL,
PRIMARY KEY (nrecibo),
FOREIGN KEY (idparticipante) REFERENCES Participante,
FOREIGN KEY (idevento) REFERENCES Evento
)
go
--Procedimientos
CREATE PROCEDURE sp_Inserta_ReciboPago
@col1 CHAR (8),
@col2 DATE,
@col3 VARCHAR (60),
@col4 VARCHAR (80)
AS
DECLARE @cod_part INT
SELECT @cod_part = codparticipante FROM Participante WHERE nombre=@col3
DECLARE @cod_Event CHAR(8)
SELECT @cod_Event = codevento FROM Evento WHERE nombreevento=@col4
INSERT ReciboPago VALUES (@col1,@col2,@cod_part ,@cod_Event)
Go
CREATE PROCEDURE sp_Listado_ReciboPago
AS
SELECT * FROM ReciboPago
Go
CREATE PROCEDURE sp_Listado_Evento
AS
SELECT * FROM Evento
Go
CREATE PROCEDURE sp_Listado_Participante
AS
SELECT * FROM Participante
Go
--Registros
INSERT INTO Participante VALUES ('MARIBEL FERNANDEZ','JR ANGAMOS','966965603','23/06/91','F','ENFERMERA')
INSERT INTO Participante VALUES ('SONIA FERNANDEZ','JR ANGAMOS','930245263','23/06/98','F','DOCTORA')
SELECT * FROM Participante
INSERT INTO Ponente VALUES ('PO01','Enma Lizeth Mezones Fernandez','24/06/97','F','Bachiller')
INSERT INTO Ponente VALUES ('PO02','Manuel Rojas','11/06/97','M','licenciado')
SELECT * FROM Ponente
INSERT INTO Evento VALUES ('E01','SIMPOSIO PRINCIPIOS Y VIRTUDES','RESTAURANTE CENTRAL','28/06/2017','11:00',30.00,'PO02')
INSERT INTO Evento VALUES ('E02','BIENESTAR','HOTEL MAR','28/08/2017','4:00',30.00,'PO02')
SELECT * FROM Evento
EXEC sp_Inserta_ReciboPago 'REC-0052','28/06/2017','MARIBEL FERNANDEZ','SIMPOSIO PRINCIPIOS Y VIRTUDES'
Go
EXEC sp_Listado_ReciboPago
Go
En Visual .NET
1. En la Capa de Datos crear una clase con el nombre conecta
Imports System.Data.SqlClient
Public Class Conecta
Private Con As SqlConnection
'Funcion para conectar la base de datos
Public Function Conecta() As SqlConnection
Con = New SqlConnection("Server=SONIA\SERVIDOR;Initial Catalog=BDEvento; Integrated Security=SSPI")
Return Con
End Function
End Class
2. Luego crear otra clase con el nombre procesos
Imports System.Data.SqlClient
Public Class Procesos
Private con As SqlConnection 'Conxeion a la BD
Private cmd As SqlCommand 'Objetos de la BD
Private dtb As DataTable 'Acesso a datos
Private da As SqlDataAdapter 'Consultar a la BD
Private cn As New Conecta
Public Structure ReciboPagos
Public NUMRECIBO As String
Public FECHA As Date
Public CODPAR As String
Public CODEVEN As String
End Structure
Public Function Inserta_ReciboPago(st As ReciboPagos) As Boolean
Dim opc As Boolean
Try 'Manejo de Excepciones
If con.State = ConnectionState.Closed Then con.Open()
cmd = New SqlCommand("sp_Inserta_ReciboPago ", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@col1", SqlDbType.Char, 8).Value = st.NUMRECIBO
cmd.Parameters.Add("@col2", SqlDbType.Date).Value = st.FECHA
cmd.Parameters.Add("@col3", SqlDbType.VarChar, 60).Value = st.CODPAR
cmd.Parameters.Add("@col4", SqlDbType.VarChar, 80).Value = st.CODEVEN
Dim i As Integer = cmd.ExecuteNonQuery 'Ejecutar el comando
opc = IIf(i > 0, True, False)
Catch ex As Exception
MsgBox("Error : " & ex.Message, 0, "Aviso")
opc = False
End Try
Return opc
End Function
Public Function Listado_Evento() As DataTable
con = cn.Conecta
dtb = New DataTable
da = New SqlDataAdapter("SELECT * FROM Evento", con)
da.Fill(dtb)
Return dtb
End Function
Public Function Listado_Participante() As DataTable
con = cn.Conecta
dtb = New DataTable
da = New SqlDataAdapter("SELECT * FROM Participante", con)
da.Fill(dtb)
Return dtb
End Function
End Class
3. Luego en la capa Negocios crear la clase con el nombre valida
Imports Datos
Public Class Valida
Private obj As New Procesos
Public Event mistake(ByVal c As Integer) 'Manejo de Excepcion
Public Function insert(cod As Procesos.ReciboPagos) As Boolean
Dim opc As Boolean
If cod.NUMRECIBO = "" Then
opc = False
RaiseEvent mistake(1) 'Llamar a una Excepcion
Throw New Exception("Ingrese Numero del recibo")
Else
obj.Inserta_ReciboPago(cod)
opc = True 'OK
End If
Return opc
End Function
Public Function selectAll_Evento() As DataTable
Return obj.Listado_Evento
End Function
Public Function selectAll_Participante() As DataTable
Return obj.Listado_Participante
End Function
End Class
4. Luego en la capa Presentacion crear los formularios FRM_Registrar_Pago y FRM_Participante
Imports System.Data.SqlClient
Imports Negocios
Imports Datos
Public Class FRM_Registrar_Pago
Dim Sw As Integer
Dim WithEvents app As New Valida
Dim reg As New Procesos.ReciboPagos
Sub Limpiar()
Me.Lbleven1.Text = ""
Me.Lbleven2.Text = ""
Me.LblEven3.Text = ""
Me.LblPar1.Text = ""
Me.LblPar2.Text = ""
End Sub
Sub Cajas(ByVal Estado As Boolean)
Me.Lbleven1.Enabled = Estado
Me.Lbleven2.Enabled = Estado
Me.LblEven3.Enabled = Estado
Me.LblPar1.Enabled = Estado
Me.LblPar2.Enabled = Estado
End Sub
Sub Botones(ByVal Estado As Boolean)
Me.BTNNUEVO.Enabled = Estado
Me.BTNCANCELAR.Enabled = Not Estado
Me.BtnGrabar.Enabled = Not Estado
Me.BtnCERRAR.Enabled = Not Estado
End Sub
Private Sub FRM_Registrar_Pago_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.DGVEvento1.DataSource = app.selectAll_Evento
End Sub
Private Sub BtnCancelar_Click(sender As Object, e As EventArgs) Handles BtnCancelar.Click
Cajas(False)
Botones(True)
End Sub
Private Sub BtnNuevo_Click(sender As Object, e As EventArgs) Handles BtnNuevo.Click
Sw = 1
Limpiar()
Cajas(True)
Botones(False)
End Sub
Private Sub BtnGrabar_Click(sender As Object, e As EventArgs) Handles BtnGrabar.Click
Dim R As Integer
If Sw = 1 Then
R = MsgBox("Grabar Datos", 4 + 32 + 256, "Registrar")
If R = 6 Then
Try
With reg
.NUMRECIBO = Me.TXTNUME.Text
.FECHA = Me.DTPFECHA.Text
.CODPAR = Me.LblPar2.Text
.CODEVEN = Me.Lbleven2.Text
End With
If app.insert(reg) Then
MsgBox("Datos Grabados Correctamente", 0, "Bien!!!")
Me.Limpiar()
Else
MsgBox("No se Guardado es Registro", 0, "Fracaso")
End If
Catch ex As Exception
MsgBox(ex.Message, 0, "ERROR")
End Try
End If
End If
Cajas(False)
Botones(True)
End Sub
Private Sub BtnBuscar_Click(sender As Object, e As EventArgs) Handles BtnBuscar.Click
FRM_Participante.ShowDialog()
End Sub
Sub MostrarEvento()
Dim Dt As SqlDataAdapter
Dim Tb As New DataTable
Dim Con As SqlConnection
Dim cn As New Conecta
Con = cn.Conecta
Dt = New SqlDataAdapter("SELECT * FROM Evento WHERE codevento='" & Me.DGVEvento1.Rows(Me.DGVEvento1.CurrentRow.Index).Cells(0).Value & "'", Con)
Dt.Fill(Tb)
If Tb.Rows.Count > 0 Then
Me.Lbleven1.Text = Tb.Rows(0).Item(0)
Me.Lbleven2.Text = Tb.Rows(0).Item(1)
Me.LblEven3.Text = Tb.Rows(0).Item(2) + " ** " + Tb.Rows(0).Item(3) + " ** " + Tb.Rows(0).Item(4) + " ** " + CStr(Tb.Rows(0).Item(5))
End If
End Sub
Private Sub DGVEvento1_CellContentClick(sender As Object, e As Windows.Forms.DataGridViewCellEventArgs) Handles DGVEvento1.CellContentClick
MostrarEvento()
End Sub
Private Sub BtnCERRAR_Click(sender As Object, e As EventArgs) Handles BtnCERRAR.Click
Dim R As Byte
R = MsgBox("Salir", 4 + 32 + 256, "Cerrar Formulario")
If R = 6 Then
End
End If
End Sub
Private Sub TxtEvento1_TextChanged(sender As Object, e As EventArgs) Handles TxtEvento1.TextChanged
Dim Dt As SqlDataAdapter
Dim Ds As New DataSet
Dim Con As SqlConnection
Dim cn As New Conecta
Con = cn.Conecta
Dt = New SqlDataAdapter(" SELECT * FROM Evento WHERE codevento LIKE '%" & Me.TxtEvento1.Text & "%'", Con)
Dt.Fill(Ds, "Evento")
Me.DGVEvento1.DataSource = Ds
Me.DGVEvento1.DataMember = "Evento"
End Sub
End Class
Imports System.Data.SqlClient
Imports Negocios
Imports Datos
Public Class FRM_Participante
Dim WithEvents app As New Valida
Private Sub FRM_Recibo_Pago_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.DGVEvento.DataSource = app.selectAll_Participante
End Sub
Private Sub TXTDATO_TextChanged(sender As Object, e As EventArgs) Handles TxtEvento.TextChanged
Dim Dt As SqlDataAdapter
Dim Ds As New DataSet
Dim Con As SqlConnection
Dim cn As New Conecta
Con = cn.Conecta
Dt = New SqlDataAdapter(" SELECT * FROM Participante WHERE nombre LIKE '%" & Me.TxtEvento.Text & "%'", Con)
Dt.Fill(Ds, "Part")
Me.DGVEvento.DataSource = Ds
Me.DGVEvento.DataMember = "Part"
End Sub
Private Sub DGVEvento_CellContentClick(sender As Object, e As Windows.Forms.DataGridViewCellEventArgs) Handles DGVEvento.CellContentClick
Dim Dt As SqlDataAdapter
Dim Tb As New DataTable
Dim codigo As String
Dim Con As SqlConnection
Dim cn As New Conecta
Con = cn.Conecta
codigo = Me.DGVEvento.Rows(Me.DGVEvento.CurrentRow.Index).Cells(0).Value
Dt = New SqlDataAdapter("SELECT * FROM Participante WHERE Codparticipante='" & codigo & "'", Con)
Dt.Fill(Tb)
If Tb.Rows.Count > 0 Then
FRM_Registrar_Pago.LblPar1.Text = Tb.Rows(0).Item(0)
FRM_Registrar_Pago.LblPar2.Text = Tb.Rows(0).Item(1)
End If
Me.Close()
Me.DGVEvento.DataSource = app.selectAll_Participante
End Sub
End Class
Muy bien . Saludos. Gracias
ResponderEliminar