Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 11th, 2008, 06:38 AM
Authorized User
 
Join Date: Aug 2007
Location: Colne, Lancashire, United Kingdom.
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default Simple ASP page problems - FIXED

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?
 
Old January 11th, 2008, 08:33 AM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

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
 
Old January 11th, 2008, 10:19 AM
Authorized User
 
Join Date: Aug 2007
Location: Colne, Lancashire, United Kingdom.
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
%>




Similar Threads
Thread Thread Starter Forum Replies Last Post
simple code, solve many problems KeviJay Visual Basic 2005 Basics 2 September 8th, 2008 06:17 AM
Error on simple page kkk_19245 ASP.NET 2.0 Basics 0 February 22nd, 2007 10:28 AM
validate.asp problems and logon.asp problems p2ptolu Classic ASP Databases 0 February 16th, 2005 02:34 PM
refreshing a page...simple im sure but i cant do i ps124 ASP.NET 1.0 and 1.1 Basics 1 March 14th, 2004 10:57 AM
Simple Java Login Page skchbs Java GUI 0 October 9th, 2003 06:14 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.