ejercico asp con conexión a sql

[_-Javier-_]

_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 interfaz
http://img13.imageshack.us/i/webfya.jpg/

aki el codigo de sql

Código (vbnet) [Seleccionar] Expandir



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

Código (vbnet) [Seleccionar] Expandir



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