ASP
executando Stored Procedures
©
2000 João Carlos Lauriano
Exemplos 1 - Consulta
Parte 1 : Default.asp - Carrega combo box com todas as categorias.
<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = 0
%>
<!-- #include file="adovbs.inc" -->
<%
Dim objConn, objRS
Dim strConnection, strOut
Dim objCmd
Set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "DSN=Northwind; Database=Northwind;"
strConnection = strConnection & "UID=sa;PWD=;"
objConn.Open strConnection
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.CommandText = "MostraCategorias"
objCmd.CommandType = adCmdStoredProc
Set objCmd.ActiveConnection = objConn
Set objRS = objCmd.Execute( )
%>
<HTML>
<HEAD>
<TITLE>Carrega Combo com Categorias</TITLE>
</HEAD>
<BODY>
<FORM action="Produtos.asp" method="POST"
name="frmCategoria">
<p align="center">Categoria <select
name="Categoria" size="1">
<%
While Not objRS.EOF
If objRS("CategoryID") = 1 Then %>
<OPTION selected
value="<%=objRS("CategoryID")%>"><%=objRS("CategoryName")%></OPTION>
<% Else %>
<OPTION
value="<%=objRS("CategoryID")%>"><%=objRS("CategoryName")%></OPTION>
<% End If %>
<%
objRS.MoveNext
Wend
%>
</SELECT>
<INPUT type="submit" name="cmdEnviar"
value="Enviar"></p>
</FORM>
<%
objRS.close
objConn.close
Set objRS = Nothing
Set objConn = Nothing
Set objCmd = Nothing
%>
</BODY>
</HTML>
Parte 2 : Produtos.asp - Mostra numa tabela todos os produtos da categoria escolhida
<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = 0
%>
<!-- #include file="adovbs.inc" -->
<%
Dim CodCateg
Dim objConn, objRS
Dim strConnection
Dim objCmd, objParam
CodCateg = Request.Form("Categoria")
If CodCateg = 0 Then
CodCateg = Session("Codigo")
End If
Set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "DSN=Northwind;Database=Northwind;"
strConnection = strConnection & "UID=sa;PWD=;"
objConn.Open strConnection
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.CommandText = "ListaProdutos"
objCmd.CommandType = adCmdStoredProc
Set objCmd.ActiveConnection = objConn
Set objParam =
objCmd.CreateParameter("@CategoryID",adInteger,
adParamInput, 4, CInt(CodCateg))
objCmd.Parameters.Append objParam
Set objRS = objCmd.Execute( )
%>
<HTML>
<BODY>
Lista dos Produtos da Categoria: <%= CodCateg %>
<BR><BR>
<TABLE BORDER="1" CELLSPACING="0"
BORDERCOLOR="#000000">
<TR>
<TH BGCOLOR="0000FF"><FONT
COLOR="#FFFFFF"><p
align="left">Produto</TH>
<TH BGCOLOR="0000FF"><FONT
COLOR="#FFFFFF">Fornecedor</TH>
<TH BGCOLOR="0000FF"><FONT
COLOR="#FFFFFF">Estoque</TH>
<TH BGCOLOR="0000FF"><FONT
COLOR="#FFFFFF">Detalhe</TH>
</TR>
<%
While Not objRS.EOF
%>
<TR>
<TD> <%= objRS("ProductName") %>
</TD>
<TD><P ALIGN="RIGHT"> <%=
objRS("SupplierID") %> </P></TD>
<TD><P ALIGN="RIGHT"> <%=
objRS("UnitsInStock") %> </P></TD>
<TD> <a
href="Fornecedor.asp?CodFornec=<%=objRS("SupplierID")%>&Categ=<%=CodCateg%>">Detalhar</a>
</TD>
</TR>
<%
objRS.MoveNext
Wend
objRS.close
objConn.close
Set objRS = Nothing
Set objConn = Nothing
Set objParam = Nothing
Set objCmd = Nothing
%>
</TABLE>
<p><a
href="default.asp">[Volta]</a></p>
</BODY>
</HTML>
Parte 3 : Fornecedor.asp - Mostra o detalhe do fornecedor escolhido.
<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = 0
%>
<!-- #include file="adovbs.inc" -->
<%
Dim CodFor, CodCat
Dim objConn, objRS
Dim strConnection
Dim objCmd, objParam
CodFor = Request.QueryString("CodFornec")
Session("Codigo") =
Request.QueryString("Categ")
Set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "DSN=Northwind;Database=Northwind;"
strConnection = strConnection & "UID=sa;PWD=;"
objConn.Open strConnection
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.CommandText = "DetalhaFornecedor"
objCmd.CommandType = adCmdStoredProc
Set objCmd.ActiveConnection = objConn
Set objParam =
objCmd.CreateParameter("@SupplierID",adInteger,
adParamInput, 4, CInt(CodFor))
objCmd.Parameters.Append objParam
Set objRS = objCmd.Execute( )
%>
<HTML>
<HEAD>
<TITLE>Pega o Código do Fornecedor</TITLE>
</HEAD>
<BODY>
<p>Código do Fornecedor Escolhido:
<%=CodFor%></p>
<form>
<div align="center"><center><table
border="0" cellpadding="0"
cellspacing="1" width="600">
<tr>
<td align="right"
width="100">Empresa </td>
<td width="500"><input type="text"
size="55"
name="empresa"
value="<%=objRS("CompanyName")%>"></td>
</tr>
<tr>
<td align="right"
width="100">Contato </td>
<td width="500"><input type="text"
size="55"
name="contato"
value="<%=objRS("ContactName")%>"></td>
</tr>
<tr>
<td align="right"
width="100">Cargo </td>
<td width="500"><input type="text"
size="55"
name="cargo"
value="<%=objRS("ContactTitle")%>"></td>
</tr>
<tr>
<td align="right"
width="100">Endereço </td>
<td width="500"><input type="text"
size="55"
name="endereco"
value="<%=objRS("Address")%>"></td>
</tr>
<tr>
<td align="right"
width="100">Cidade </td>
<td width="500"><input type="text"
size="30"
name="cidade"
value="<%=objRS("City")%>"></td>
</tr>
<tr>
<td align="right"
width="100">Região </td>
<td width="500"><input type="text"
size="30"
name="regiao"
value="<%=objRS("Region")%>"></td>
</tr>
<tr>
<td align="right" width="100">Código
Postal </td>
<td width="500"><input type="text"
size="20"
name="postal"
value="<%=objRS("PostalCode")%>"></td>
</tr>
<tr>
<td align="right"
width="100">País </td>
<td width="500"><input type="text"
size="30"
name="pais"
value="<%=objRS("Country")%>"></td>
</tr>
<tr>
<td align="right"
width="100">Telefone </td>
<td width="500"><input type="text"
size="20"
name="telefone"
value="<%=objRS("Phone")%>"></td>
</tr>
<tr>
<td align="right"
width="100">Fax </td>
<td width="500"><input type="text"
size="20"
name="fax"
value="<%=objRS("Fax")%>"></td>
</tr>
<tr>
<td align="right" width="100">Home
Page </td>
<td width="500"><input type="text"
size="55"
name="homepage"
value="<%=objRS("HomePage")%>"></td>
</tr>
</table>
</center></div>
<%
objRS.close
objConn.close
Set objRS = Nothing
Set objConn = Nothing
Set objParam = Nothing
Set objCmd = Nothing
%>
</form>
<p><a
href="Produtos.asp">[Volta]</a></p>
</BODY>
</HTML>
Página desenvolvida por João Carlos
Lauriano.
Atualizada em: 04/06/2000