Simple ASP page problems

Andrew.Berry · January 11th, 2008, 06:38 AM

Hi all.

I am an ASP.net man, but have been asked to do some classic asp work for a colleague. Have never done it before and am having a couple of problems.

I am basically calling a stored proc and using data retrieved from that proc and displaying it in a table.

Here is the stored proc code:

Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[dev_CastleColour_ProjectedBalances]
    @ProductID int

AS
BEGIN


    SELECT 
            P.ID,
            P.Code,
            p.Description,
            P.Balance,
            P.Available,
            P.InvoiceBalance,
            (SELECT 
                SUM (CASE 
                        WHEN TF.[Type] = 12 THEN TF.Balance 
                        ELSE 0 
                     END)
             FROM TransactionFile TF WHERE TF.ProductID = P.ID)
             AS 'WIP',
            (SELECT
                SUM(OL.Quantity - (OL.QuantityDelivered + OL.QuantityAllocated))
             FROM OrderLine OL WHERE OL.ProductID= P.ID) AS 'Unall Sales Orders'

    FROM Product P
    WHERE  P.ID = @ProductID

END

Here is my asp page:

Code:

<%@ Language=VBScript %>
<%Option Explicit%>



<%
Dim sSQL
Dim rs
Dim sProductID
Dim sCode
Dim sBalance
Dim sAvailable
Dim sInvoiceBal
Dim sDescription
Dim sWIP
Dim sProjBalance
Dim sUnallSalesOrders
Dim cmd As New ADODB.Command

sProductID=Request("stockctrlid")

set rs=Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection=cnnTharstern
rs.CursorLocation=adUseClient


cmd.CommandText = "[dbo].[dev_CastleColour_ProjectedBalances]"
cmd.commandType = adCmdStoredProc
rs = cmd.execute()
rs.open

Do While (NOT rs.Eof)
    sCode = rs("code")
    sDescription = rs("Description")
    sBalance = rs("balance")
    sAvailable = rs("available")
    sWIP = rs("WIP")
    sUnallSalesOrders = rs("Unall Sales Orders")
    sProjBalance = (sBalance + sWIP) - (sUnallSalesOrders + sAvailable)

rs.close
%>
<HTML>
<HEAD>
<TITLE>FSP Stock ID</TITLE>
<LINK href="css/tharstern.css" rel=StyleSheet>
</HEAD>
<BODY rightmargin=2 leftmargin=2 topmargin=0>
<TABLE border=0 cellpadding=2 cellspacing=0>
<TR>
    <TD class='heading'>Stock ID</TD>
    <TD class='detail'><strong><%=sProductID%></strong></TD>
</TR>
<TR>
    <TD class='heading'>Code</TD>
    <TD class='detail'><%=sCode%></TD>
</TR>
<TR>
    <TD class='heading'>Balance</TD>
    <TD class='detail'><%=sBalance%></TD>
</TR>
<TR>
    <TD class='heading'>Available</TD>
    <TD class='detail'><%=sAvailable%></TD>
</TR>
<TR>
    <TD class='heading'>WIP</TD>
    <TD class='detail'><%=sWIP%></TD>
</TR>
<TR>
    <TD class='heading'>Unallocated SO</TD>
    <TD class='detail'><%=sUnallSalesOrders%></TD>
</TR>
<TR>
    <TD class='heading'>Projected Balance</TD>
    <TD class='detail'><%=sProjBalance%></TD>
</TR>
</BODY>
</HTML>
<%
set rs=nothing
%>

The Projected Balances is a simple calculation involving the other numbers retrieved.

Any hints as to where i'm going wrong?

urtrivedi · January 11th, 2008, 08:33 AM

You must pass parameter to stored procedure @ProductID before executing.

using cmd.addparmeter("@parameterid",?????)
I dont know the syntax please refer asp manual.
cmd.exeucte()

urt

Andrew.Berry · January 11th, 2008, 10:19 AM

Have fixed it now, couple of little tweaks to my connection method and its all sorted. Cheers

Here's my code for future reference.

Code:

<%@ Language=VBScript %>
<%Option Explicit%>



<%

Dim sSQL
Dim rs
Dim sProductID
Dim sCode
Dim sBalance
Dim sAvailable
Dim sInvoiceBal
Dim sDescription
Dim sWIP
Dim sProjBalance
Dim sUnallSalesOrders
Dim cmd 

sProductID=Request("stockctrlid")

set rs=Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection=cnnTharstern
rs.CursorLocation=adUseClient

set cmd = Server.CreateObject("ADODB.command")
cmd.CommandText = "[dbo].[dev_CastleColour_ProjectedBalances]"
cmd.commandType = adCmdStoredProc

rs.Open "[dbo].[dev_CastleColour_ProjectedBalances] " & sProductID

if rs.State<>adStateClosed then
    if not rs.eof then

    sCode = rs("code")
    sDescription = rs("Description")
    sBalance = rs("balance")
    sAvailable = rs("available")
    sWIP = rs("WIP")
    sUnallSalesOrders = rs("Unall Sales Orders")
    sProjBalance = (sBalance + sAvailable) - (sWIP + sUnallSalesOrders) 

    end if
rs.close
end if
%>
<HTML>
<HEAD>
<TITLE>FSP Stock ID</TITLE>
<LINK href="css/tharstern.css" rel=StyleSheet>
</HEAD>
<BODY rightmargin=2 leftmargin=2 topmargin=0>
<TABLE border=0 cellpadding=2 cellspacing=0>
<TR>
    <TD class='heading'>Stock ID</TD>
    <TD class='detail'><strong><%=sProductID%></strong></TD>
</TR>
<TR>
    <TD class='heading'>Code</TD>
    <TD class='detail'><%=sCode%></TD>
</TR>
<TR>
    <TD class='heading'>Balance</TD>
    <TD class='detail'><%=sBalance%></TD>
</TR>
<TR>
    <TD class='heading'>Available</TD>
    <TD class='detail'><%=sAvailable%></TD>
</TR>
<TR>
    <TD class='heading'>WIP</TD>
    <TD class='detail'><%=sWIP%></TD>
</TR>
<TR>
    <TD class='heading'>Unallocated SO</TD>
    <TD class='detail'><%=sUnallSalesOrders%></TD>
</TR>
<TR>
    <TD class='heading'>Projected Balance</TD>
    <TD class='detail'><%=sProjBalance%></TD>
</TR>
</BODY>
</HTML>
<%
set rs=nothing
%>