Programacion

Default1.aspx

[pic]

Código sql:

create proc usp_ListaContactos
as
select distinct contacttitle from customers
go

create proc usp_ListaCiudad
as
select distinct * from customers
go

create proc usp_guardar
@codigo varchar(5),@compañia varchar(50),@contacto varchar(50),
@titulo varchar(50),@direccion varchar(50),@ciudad varchar(30)
as
insert intoCustomers(CustomerID,CompanyName,ContactName,
ContactTitle,Address,City) values(@codigo,@compañia,@contacto,
@titulo,@direccion,@ciudad)
go

create proc usp_buscar b
@cliente varchar(50)
as
exec('select CustomerID,companyName from Customers
where CompanyName like '''+@cliente+'%''')
go

create proc usp_buscaredit
@codigo varchar(5)
as
select CompanyName,ContactName,
ContactTitle,Address,City from Customerswhere CustomerID=@codigo
go
usp_buscaredit 'alfki'

create proc usp_Eliminar
@codigo varchar(5)
as
delete from Customers
where CustomerID=@codigo
go

create proc usp_Actualizar
@codigo varchar(5),@compañia varchar(50),@contacto varchar(50),
@titulo varchar(50),@direccion varchar(50),@ciudad varchar(30)
as
update Customers set CompanyName=@compañia,ContactName=@contacto,ContactTitle=@titulo,Address=@direccion,City=@ciudad
where CustomerID=@codigo
go

Programacion VB:

Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Dim cn As New SqlConnection("server=(local);integrated Security=SSPI;database=northwind")
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If NotPage.IsPostBack Then
Dim da As New SqlDataAdapter("usp_ListaContactos", cn)
Dim dt As New DataTable
da.Fill(dt)
DropdownList1.DataSource = dt
DropDownList1.DataTextField = "ContactTitle"
DropDownList1.DataBind()
Dim da2 As New SqlDataAdapter("usp_ListaCiudad", cn)
Dim tbl As New DataTable
da2.Fill(tbl)
DropDownList2.DataSource = tb1
DropDownList2.DataTextField = "City"
DropDownList2.DataBind()Panel1.Visible = False
ListBox1.AutoPostBack = True
txtcompañia.Enabled = False
txtcontacto.Enabled = False
DropDownList1.Enabled = False
txtdireccion.Enabled = False
DropDownList2.Enabled = False
End If
End Sub

Protected Sub btnGuardar_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnGuardar.Click
Try
Dim le1 = txtcompañia.Text.Substring(0, 3)le1 = UCase(le1.Substring(0, 1))
Dim l0 = txtcompañia.Text.Substring(1, 2)
Dim espacio As Integer
espacio = txtcompañia.Text.IndexOf(" ")

Dim num_carac As Integer = (txtcompañia.Text.Substring(0, espacio)).Length
Dim le2 = txtcompañia.Text.Substring(esp + num_carac, 2)
Dim codigo As String = l1 + l0 + l2
Dim compañia As String = txtcompañia.Text
Dim contacto As String = txtcontacto.TextDim titulo As String = Me.DropDownList1.Text
Dim direccion As String = txtdireccion.Text
Dim ciudad As String = DropDownList2.Text
Dim cmd As New SqlCommand("usp_guardar", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@codigo", SqlDbType.VarChar, 5).Value = codigo
cmd.Parameters.Add("@compañia", SqlDbType.VarChar, 50).Value = compañia
cmd.Parameters.Add("@contacto",SqlDbType.VarChar, 50).Value = contacto
cmd.Parameters.Add("@titulo", SqlDbType.VarChar, 50).Value = titulo
cmd.Parameters.Add("@direccion", SqlDbType.VarChar, 50).Value = direccion
cmd.Parameters.Add("@ciudad", SqlDbType.VarChar, 30).Value = ciudad
cn.Open()
Dim i As Integer = cmd.ExecuteNonQuery
If i = 1 Then
Response.Write("Guardado")
End If
Catch ex As ExceptionResponse.Write(ex.Message)
Finally
cn.Close()
End Try
End Sub

Protected Sub btnbuscar_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnbuscar.Click
Panel1.Visible = True
txtbuscar.Focus()
End Sub

Protected Sub txtbuscar_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtbuscar.TextChanged
Dim cliente As String = txtbuscar.Text
Dim cmd As...