_Este ejercicio consiste en registrar buscar y modificar datos que se encuentran en una base de datos
_utilizaremos Visual studio 2010
_Sql server 2008
_crearemos un nuevo sitio web
aki la interfazhttp://img13.imageshack.us/i/webfya.jpg/
aki el codigo de sql
go
use master
go
if(DB_ID('parcialvidarteDelgad')is not null)
drop database parcialvidarteDelgad
go
create database parcialvidarteDelgad
go
use parcialvidarteDelgad
go
create table tipopelicula(
tipo varchar(14)primary key
)
go
insert tipopelicula values ('DRAMA')
insert tipopelicula values ('Suspenso')
insert tipopelicula values ('Terror')
insert tipopelicula values ('CienciaFiccion')
insert tipopelicula values ('Otros')
go
create table pelicula(
id int identity primary key,
nombre varchar(30)unique,
tipo varchar(14)foreign key references tipopelicula,
añof date,
stock int
)
go
create proc listartipo(@tipo varchar(30))
as begin
select * from pelicula where tipo =@tipo
end
go
create proc registrar(@n varchar(30),@tipo varchar(14),@añof date,@stock int,@msj varchar(60)output)
as begin
if (exists(select * from pelicula where nombre =@n ))
set @msj ='Ya existe pelicula'
else
begin
insert into pelicula values(@n ,@tipo ,@añof ,@stock )
set @msj ='OK'
end
end
GO
create proc MODIFICAR(@id int,@n varchar(30),@tipo varchar(14),@añof date,@stock int,@msj varchar(60)output)
as begin
if (NOT exists(select * from pelicula where id =@id ))
set @msj ='no existe pelicula'
else
begin
update pelicula set nombre =@n ,tipo =@tipo ,añof=@añof ,stock =@stock where id =@id
set @msj ='OK'
end
end
go
create proc buscar(@id int)
as begin
select * from pelicula where id =@id
end
go
insert into pelicula values('Odisea','DRAMA','10/10/2010',18)
go
Ahora el codigo en Vb
nota: activamos en la venta de propiedad ispostback=true en la barra de propiedades del combobox o cbxtipo
Imports System.Data
Imports System.Data.SqlClient
Partial Class pagina1
Inherits System.Web.UI.Page
Private con As New SqlConnection("Server=.;DataBase=parcialvidarteDelgad;Integrated Security=true")
Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim cmd As New SqlCommand
Dim msj As String = ""
Try
abrir()
cmd = New SqlCommand("registrar", con)
cmd.CommandType = 4
With cmd.Parameters
.AddWithValue("@n", txtn.Text)
.AddWithValue("@tipo", cbxtipo.SelectedValue)
.AddWithValue("@añof", CDate(txtaño.Text))
.AddWithValue("@stock", CInt(txtstock.Text))
.Add("@msj", SqlDbType.VarChar, 60).Direction = 2
End With
cmd.ExecuteNonQuery()
msj = cmd.Parameters("@msj").Value
MsgBox(msj)
Catch ex As Exception
MsgBox(ex.Message)
End Try
cerrar()
limpiar()
End Sub
Sub limpiar()
txtaño.Text = ""
txtid.Text = ""
txtn.Text = ""
txtstock.Text = ""
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
listartipos()
End If
End Sub
Sub listartipos()
Dim dt As New DataTable
Dim da As SqlDataAdapter
Try
abrir()
da = New SqlDataAdapter("select * from tipopelicula", con)
da.Fill(dt)
cbxtipo.DataValueField = "tipo"
cbxtipo.DataTextField = "tipo"
cbxtipo.DataSource = dt
cbxtipo.DataBind()
Catch ex As Exception
MsgBox(ex.Message)
End Try
cerrar()
End Sub
Sub abrir()
If con.State = 0 Then con.Open()
End Sub
Sub cerrar()
If con.State = 1 Then con.Close()
End Sub
Protected Sub cbxtipo_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbxtipo.SelectedIndexChanged
Dim dt As New DataTable
Dim da As SqlDataAdapter
Try
abrir()
da = New SqlDataAdapter("listartipo", con)
da.SelectCommand.CommandType = 4
da.SelectCommand.Parameters.AddWithValue("@tipo", cbxtipo.SelectedValue)
da.Fill(dt)
GridView1.DataSource = dt
GridView1.DataBind()
Catch ex As Exception
MsgBox(ex.Message)
End Try
cerrar()
End Sub
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim dt As New DataTable
Dim da As SqlDataAdapter
Try
abrir()
da = New SqlDataAdapter("buscar", con)
da.SelectCommand.CommandType = 4
da.SelectCommand.Parameters.AddWithValue("@id", txtid.Text)
da.Fill(dt)
txtid.Text = dt.Rows(0).Item(0).ToString
txtn.Text = dt.Rows(0).Item(1).ToString
cbxtipo.Text = dt.Rows(0).Item(2).ToString
txtaño.Text = dt.Rows(0).Item(3).ToString
txtstock.Text = dt.Rows(0).Item(4).ToString
Catch ex As Exception
MsgBox(ex.Message)
End Try
cerrar()
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd As New SqlCommand
Dim msj As String = ""
Try
abrir()
cmd = New SqlCommand("MODIFICAR", con)
cmd.CommandType = 4
With cmd.Parameters
.AddWithValue("@id", txtid.Text)
.AddWithValue("@n", txtn.Text)
.AddWithValue("@tipo", cbxtipo.SelectedValue)
.AddWithValue("@añof", CDate(txtaño.Text))
.AddWithValue("@stock", CInt(txtstock.Text))
.Add("@msj", SqlDbType.VarChar, 60).Direction = 2
End With
cmd.ExecuteNonQuery()
msj = cmd.Parameters("@msj").Value
MsgBox(msj)
Catch ex As Exception
MsgBox(ex.Message)
End Try
cerrar()
limpiar()
End Sub
Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LinkButton1.Click
limpiar()
End Sub
End Class
eso es todo espero que les haya servido