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&nbsp</td>
<td width="500"><input type="text" size="55"
name="empresa" value="<%=objRS("CompanyName")%>"></td>
</tr>
<tr>
<td align="right" width="100">Contato&nbsp</td>
<td width="500"><input type="text" size="55"
name="contato" value="<%=objRS("ContactName")%>"></td>
</tr>
<tr>
<td align="right" width="100">Cargo&nbsp</td>
<td width="500"><input type="text" size="55"
name="cargo" value="<%=objRS("ContactTitle")%>"></td>
</tr>
<tr>
<td align="right" width="100">Endereço&nbsp</td>
<td width="500"><input type="text" size="55"
name="endereco" value="<%=objRS("Address")%>"></td>
</tr>
<tr>
<td align="right" width="100">Cidade&nbsp</td>
<td width="500"><input type="text" size="30"
name="cidade" value="<%=objRS("City")%>"></td>
</tr>
<tr>
<td align="right" width="100">Região&nbsp</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&nbsp</td>
<td width="500"><input type="text" size="20"
name="postal" value="<%=objRS("PostalCode")%>"></td>
</tr>
<tr>
<td align="right" width="100">País&nbsp</td>
<td width="500"><input type="text" size="30"
name="pais" value="<%=objRS("Country")%>"></td>
</tr>
<tr>
<td align="right" width="100">Telefone&nbsp</td>
<td width="500"><input type="text" size="20"
name="telefone" value="<%=objRS("Phone")%>"></td>
</tr>
<tr>
<td align="right" width="100">Fax&nbsp</td>
<td width="500"><input type="text" size="20"
name="fax" value="<%=objRS("Fax")%>"></td>
</tr>
<tr>
<td align="right" width="100">Home Page&nbsp</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>

 

[Volta]   [Início]

 

Página desenvolvida por João Carlos Lauriano.
Atualizada em: 04/06/2000