Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Professional For advanced coder questions in ASP 3. 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 Professional section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 6th, 2003, 02:00 PM
Registered User
 
Join Date: Jun 2003
Location: Tucson, AZ, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Passing Parameters to Stored Proc

Help

I can't get ASP to pass the parameters to a stored proc. I've tried everything I could think of and would appreciate any suggestions. Code is as follows:

<%
'Check to see if user is logged in. If not redirect to login page
If session("ID") = "" THEN
    Response.Redirect("reportlogin.asp")
ELSE

'Set up local page variables utilizing session variables and query string
'data passed via querystring are Request Type and what information is being drilled down
Dim startdate, enddate, familyname, RequestType, DrillFrom 'Arrowimage and carrier are established on the include page

    If Request.QueryString("PAGE") = "" Then
        intpage = 1
        RequestType = Request.QueryString("TYPE")
        DrillFrom = Request.QueryString("Drill")
        Response.Cookies("RequestType") = RequestType
        Response.Cookies("DrillFrom") = DrillFrom
    ELSE
        intPage = CINT(Request.QueryString("PAGE"))
            If intPage < 1 Then
                intpage = 1
            End if
        RequestType = Request.Cookies("RequestType")
        DrillFrom = Request.Cookies("DrillFrom")
    End If

ID = Session("ID")
carrier = Session("Carrier") 'Carrier name
startdate = Session("Startdate") 'Start Date of information
enddate = Session("enddate") 'End date of Information
familyname = Session("familyname")'Family name of information SYS unless AA or CO
arrowimage = Session("Arrowimage") ' Session arrowimage for flow diagram

'Establish counters and pass number for data being drilled. Also set up flow diagram with flow variable
IF DrillFrom = "InitTicket" Then
   counter = "IN (1, 2)"
   pass = "= 0"
   flow = "Ticket Numbers Provided Intially"
ELSEIF DrillFrom = "Other" Then
    counter = "IN (3, 4)"
    pass = "= 0"
    flow = "Other (past date, errors)"
ELSEIF DrillFrom = "TTL" Then
    counter = "= 5"
    pass = "= 0"
    flow = "Items Marked With TTLs"
ELSEIF DrillFrom = "2Warn" Then
    counter = "= 25"
    pass = "IN (1, 2)"
    flow = "Second Warnings"
ELSEIF DrillFrom = "RCanx" Then
    counter = "IN (38, 39, 58, 59)"
    pass = "IN (1, 2)"
    flow = "Robot Cancellations"
ELSEIF DrillFrom = "TNumb1" Then
    counter = "IN (21, 22)"
    pass = "IN (1, 2)"
    flow = "Ticket Numbers Provided (1st Pass)"
ELSEIF DrillFrom = "Ignored1" Then
    counter = "= 34"
    pass = "IN (1, 2)"
    flow = "Ignored Items (1st Pass)"
ELSEIF DrillFrom = "TNumb2" Then
    counter = "IN (41, 42)"
    pass = "IN (1, 2)"
    flow = "Ticket Numbers Provided (2nd Pass)"
ELSEIF DrillFrom = "Ignored2" Then
    counter = "= 54"
    pass = "IN (1, 2)"
    flow = "Ignored Items (2nd Pass)"
ELSE
    counter = Session("counter")
    pass = Session("pass")
    flow = Session("flow")
END IF

'Establish session variabes from above to allow passing to all pages and drill types ie person clicks on IATA from rule page

        Session("DrillFrom") = DrillFrom
        Session("counter") = counter
        Session("pass") = pass
        Session("flow") = flow

'Variables for the different type of reports (Iata, Class, Rule Number, Boardpoint)
        IF RequestType = "IATA" THEN
            DType = "IATA" 'Iata is requested drill type
            Stype1 = "b.arciata AS Iata" 'First field for stored procedure changing name from arciata to iata
            Stype2 = "b.vprule)) AS ''Rule''" 'Second field for stored procedure changing name from vprule to rule
            Stype3 = "a.boardpoint)) AS Boardpoint" 'Third field for stored procedure
            Stype4 = "a.class)) AS Class" 'Fourth Field for stored procedure
            Gtype = "b.arciata" 'Group by clause in stored procedure
            col1 = "Iata" 'Column name for insert into second temp table in stored proc
            col2 = "[rule]" '2nd column name for insert into second temp table
            col3 = "boardpoint" 'Third column name for insert into second temp table
            col4 = "class" 'Fourth Column name for insert into second temp table
            Ftype1 = "[Rule]) as [Rule]"
            Ftype2 = "Boardpoint) As Boardpoint"
            Ftype3 = "Class) AS Class"
            Display1 = "IATA" 'Variable to display first column
            Display2 = "RULE" 'Display second column
            Display3 = "BOARDPOINT" 'Display third column
            Display4 = "CLASS" 'Display fourth column
            counter = Session("counter") 'Retrieve counter data from session variable
            pass = Session("pass")'Retrieve pass data
            flow = Session("flow") 'Retrieve flow data
        ELSEIF RequestType = "CLASS" THEN
            DTYPE = "CLASS"
            Stype1 = "a.class as CLASS"
            Stype2 = "b.vprule)) AS ''Rule''"
            Stype3 = "a.boardpoint)) as Boardpoint"
            Stype4 = "b.arciata)) AS Iata"
            GType = "a.Class"
            col1 = "class"
            col2 = "[rule]"
            col3 = "boardpoint"
            col4 = "iata"
            Ftype1 = "[Rule]) as [Rule]"
            Ftype2 = "Boardpoint) As Boardpoint"
            Ftype3 = "Iata) AS Iata"
            Display1 = "CLASS"
            Display2 = "RULE"
            Display3 = "BOARDPOINT"
            Display4 = "IATA"
            counter = Session("counter")
            pass = Session("pass")
            flow = Session("flow")
        ELSEIF RequestType = "BOARD" THEN
            DTYPE = "BOARD POINT"
            Stype1 = "a.Boardpoint As Boardpoint"
            Stype2 = "b.vprule)) AS ''Rule''"
            Stype3 = "a.class)) as Class"
            Stype4 = "b.arciata)) AS Iata"
            col1 = "boardpoint"
            col2 = "[rule]"
            col3 = "class"
            col4 = "iata"
            Ftype1 = "[Rule]) as [Rule]"
            Ftype2 = "Class) As Class"
            Ftype3 = "Iata) AS Iata"
            Gtype = "a.Boardpoint"
            Display1 = "BOARDPOINT"
            Display2 = "RULE"
            Display3 = "IATA"
            Display4 = "CLASS"
            counter = Session("counter")
            pass = Session("pass")
            flow = Session("flow")
        ELSE
            DTYPE = "RULE"
            Stype1 = "b.vprule As ''Rule''"
            Stype2 = "a.class)) AS Class"
            Stype3 = "b.arciata)) as Iata"
            Stype4 = "a.boardpoint)) AS Boardpoint"
            col1 = "[rule]"
            col2 = "class"
            col3 = "iata"
            col4 = "boardpoint"
            Ftype1 = "Class) as Class"
            Ftype2 = "Iata) As Iata"
            Ftype3 = "Boardpoint) AS Boardpoint"
            Gtype = "b.vprule"
            Display1 = "RULE"
            Display2 = "CLASS"
            Display3 = "IATA"
            Display4 = "BOARDPOINT"
            counter = Session("counter")
            pass = Session("pass")
            flow = Session("flow")
        END IF
%>
        <HTML>
        <HEAD>
        <TITLE><%= Session("airlinename")%> Operation Statistics Page</TITLE>
        </HEAD>
        <BODY>
        <CENTER>
        <B>
        <P><B><%= flow & " " & arrowImage & " "& dtype%>
        <%=arrowImage%> Record Locator</B><P>

        <P>
        </B>
        </CENTER>
        </P>


<%
Response.Write "</CENTER>" & Flow & "<P>"

    IF DATEDIFF("d", startdate, enddate) > 1 THEN
        Response.Write "<P>Beginning on: <B>" & startdate & " " & "</b> and ending before: <B>" & enddate & " </b> <P>"
    ELSE
        Response.Write "<P>For the date: <B>" & startdate & " </b> <P>"
    END IF

'Links to view data by Rule, Class or Boardpoint From Iata display
IF RequestType = "IATA" THEN
%>
<B><A HREF="SeatLevel1.asp?">View By Rule</A></B>
<B><A HREF="SeatLevel1.asp?Type=CLASS">View By Class</A></B>
<B><A HREF="SeatLevel1.asp?Type=BOARD">View By Board Points</A></B>
<P>
<%
'Links to view data by Rule, Iata or Boardpoint From class display
ELSEIF RequestType = "CLASS" THEN
%>
<B><A HREF="SeatLevel1.asp?">View By Rule</A></B>
<B><A HREF="SeatLevel1.asp?Type=IATA">View By Iata</A></B>
<B><A HREF="SeatLevel1.asp?Type=BOARD">View By Board Point</A></B>
<P>
<%
'Links to view data by Rule, Class or Iata From Boardpoint display
ELSEIF RequestType = "BOARD" THEN
%>
<B><A HREF="SeatLevel1.asp">View By Rule</A></B>
<B><A HREF="SeatLevel1.asp?Type=CLASS">View By Class</A></B>
<B><A HREF="SeatLevel1.asp?Type=IATA">View By IATA</A></B>
<P>
<%
'Links to view data by Iata, Class or Boardpoint From Rule display. This is the default display
ELSE
%>
<B><A HREF="SeatLevel1.asp?Type=BOARD">View By Board</A></B>
<B><A HREF="SeatLevel1.asp?Type=CLASS">View By Class</A></B>
<B><A HREF="SeatLevel1.asp?Type=IATA">View By IATA</A></B>
<P>
<%
END IF

    Dim StrSql, objComm, rsData, strQuote

    strquote = Chr(34)

    Set objComm = Server.CreateObject ("ADODB.command")

    Set rsData = Server.CreateObject("ADODB.Recordset")

    strsql = "Seat_level_1"

            objComm.ActiveConnection = objconne
            objComm.CommandText = "Seat_level_1test"
            objComm.CommandType = adcmdStoredProc

            objComm.Parameters.Append objComm.CreateParameter("Return_value", adInteger, adParamReturnValue, 8)
            objComm.Parameters.Append objComm.CreateParameter("@SType1", adVarChar, 1, 50, SType1)
            objComm.Parameters.Append objComm.CreateParameter("@Stype2", adVarChar, 1, 50, Stype2)
            objComm.Parameters.Append objComm.CreateParameter("@SType3", adVarChar,1, 50, SType3)
            objComm.Parameters.Append objComm.CreateParameter("@SType4", adVarChar, 1, 50, SType4)
            objComm.Parameters.Append objComm.CreateParameter("@startdate", adVarChar, 1, 25, startdate)
            objComm.Parameters.Append objComm.CreateParameter("@enddate", adVarChar, 1, 25, enddate)
            objComm.Parameters.Append objComm.CreateParameter("@pass", adVarChar, 1, 10, pass)
            objComm.Parameters.Append objComm.CreateParameter("@counter", adVarChar, 1, 20, counter)
            objComm.Parameters.Append objComm.CreateParameter("@familyname", adVarChar, 1, 5, familyname)
            objComm.Parameters.Append objComm.CreateParameter("@gtype", adVarChar, 1, 50, gtype)
            objComm.Parameters.Append objComm.CreateParameter("@col1", adVarChar, 1, 50, col1)
            objComm.Parameters.Append objComm.CreateParameter("@col2", adVarChar, 1, 50, col2)
            objComm.Parameters.Append objComm.CreateParameter("@col3", adVarChar, 1, 50, col3)
            objComm.Parameters.Append objComm.CreateParameter("@col4", adVarChar, 1, 50, col4)
            objComm.Parameters.Append objComm.CreateParameter("@Ftype1", adVarChar, 1, 50, Ftype1)
            objComm.Parameters.Append objComm.CreateParameter("@Ftype2", adVarChar, 1, 50, Ftype2)
            objComm.Parameters.Append objComm.CreateParameter("@Ftype3", adVarChar, 1, 50, Ftype3)
            objComm.Parameters.Append objComm.CreateParameter("@ID", adVarChar, 1, 4, ID)
            objComm.Parameters.Append objComm.CreateParameter("@iPage", adInteger, 1, 8, iPage)
            objComm.Parameters.Append objComm.CreateParameter("@iPageSize", adInteger, 8, 100)

            SET rsData = objComm.Execute



        IF NOT rsData.EOF THEN

                     tblOutput = " <TABLE WIDTH=""50%"" BORDER=1 CELLSPACING=1 CELLPADDING=1 name=""rsTable"" id=rsTable cols=5><TR bgcolor=mediumblue> " & _
                    " <TH><A href=""javascript:sortTable(0, rsTable);""><B>" & display1 & "</B></A></TH> " & _
                    " <TH><A href=""javascript:sortTable(1, rsTable);""><B>SEATS</B></A></TH> " & _
                    " <TH><A href=""javascript:sortTable(2, rsTable);""><B>" & display2 & "</B></A></TH> " & _
                    " <TH><A href=""javascript:sortTable(3, rsTable);""><B>" & display3 & "</B></A></TH> " & _
                    " <TH><A href=""javascript:sortTable(4, rsTable);""><B>" & display4 & "</B></A></TH> "

                    While not rsData.EOF
                            tblOutput = tbloutput & "<TR><TD>" & rsData("" & display1 & "") & " </TD>" & _
                                                        "<TD><A HREF=""SeatLevelFinal.ASP?Type=" & display1 & "&DATA=" & rsData("" & display1 & "") & """)>" & rsData("seats") & " </TD>" & _
                                                        "<TD><A HREF=""SeatLevel2.asp?Type=" & display1 & "&QTYPE=" & display2 & "&DATA=" & rsData("" & display1 & "") & """)>" & rsData("" & display2 & "") & "</A></TD>" &_
                                                        "<TD><A HREF=""SeatLevel2.asp?Type=" & display1 & "&QTYPE=" & display3 & "&DATA=" & rsData("" & display1 & "") & """)>" & rsData("" & display3 & "") & "</A></TD>" & _
                                                        "<TD><A HREF=""SeatLevel2.asp?Type=" & display1 & "&QTYPE=" & display4 & "&DATA=" & rsData("" & display1 & "") & """)>" & rsData("" & display4 & "") & "</A></TD></TR>"
                            rsData.MoveNext
                    Wend
                    tbloutput = tbloutput & "</table>"
                    Response.Write tbloutput


            ELSE Response.Write "SQL Server did not return any data. Please check your dates or return later today for the most recent data."

            END IF

            strScriptName = Request.ServerVariables("Script_name")

            Response.Write " <A HREF=" & strQuote & strScriptName & _
                           "?Page=1" & strQuote & ">First Page</A>"
                IF intPage < 1 Then
                    Response.Write " <SPAN>PreviousPage</SPAN>"
                ELSE
                    Response.Write " <A HREF=" & strquote & strScriptName & _
                    "?Page=" & intPage - 1 & strquote & ">Previous Page</A>"
                    rsData.Close
                    intLastPage = objComm.Parameters("Return_value")
                End if

            If intLastPage = intPage Then
                Response.Write " <SPAN>Next Page</SPAN>"
            ELSE
                Response.Write " <A HREF=" & strQuote & strScriptName & _
                               "?PAGE=" & intpage + 1 & strQuote & ">Next Page</A>"
            END IF

            Response.Write " <A HREF=" & strQuote & strScriptName & _
                           "?PAGE=" & intLastpage & strQuote & ">Last Page </A>"

           Set rsData = Nothing
           Set objComm = Nothing

%>
</TABLE>
<BR>

<BR>
<CENTER><B><A HREF="opstat.asp">Return to Date Entry Page</A></B> </CENTER>
</P>

</BODY>
</HTML>

<%
END IF
%>
Reply With Quote
  #2 (permalink)  
Old June 6th, 2003, 02:46 PM
Imar's Avatar
Wrox Author
Points: 71,804, Level: 100
Points: 71,804, Level: 100 Points: 71,804, Level: 100 Points: 71,804, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,046
Thanks: 80
Thanked 1,580 Times in 1,557 Posts
Default

Hi there,

What error are you getting? What is not working / what is working?
This is quite a lot of code to wade through to find an error.

Common things to check:

1. ADO version
2. Did you include a reference to adovbs.inc or somehow define the ad* constants somewhere?
3. Did you use Option Explicit on top of your page?
4. Does the code work when you try it on a sproc with just one or two parameters?
5. Does your sproc parameters match your ADO parameters? (both in number as in type)

If this doesn't help, please strip your code so it only contains the relevant code and post it again.

Cheers,

Imar
Reply With Quote
  #3 (permalink)  
Old June 6th, 2003, 04:48 PM
Registered User
 
Join Date: Jun 2003
Location: Tucson, AZ, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Imar

Thanks, it was not reading the adovbs.inc file. Thanks
Reply With Quote
  #4 (permalink)  
Old June 9th, 2003, 07:41 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Also try to tab your code in. It makes it easier to read.

regards
David Cameron
Reply With Quote
  #5 (permalink)  
Old June 10th, 2003, 04:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by David Cameron
 Also try to tab your code in. It makes it easier to read.

regards
David Cameron
David, its the forum that removes the tabbing. I've posted nicley indented code here before only to see it all appear left-aligned.
Reply With Quote
  #6 (permalink)  
Old June 10th, 2003, 07:21 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry, I'll have to get used to that.

regards
David Cameron
Reply With Quote
  #7 (permalink)  
Old June 10th, 2003, 11:35 PM
Friend of Wrox
Points: 3,489, Level: 24
Points: 3,489, Level: 24 Points: 3,489, Level: 24 Points: 3,489, Level: 24
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Central, NJ, USA.
Posts: 1,102
Thanks: 0
Thanked 2 Times in 2 Posts
Default

The tab loss thing looks to be an issue- Please post a "suggestion" about the problem in the feedback forum...

You might find it easier to read code if you use the ]code[ ]/code[ (obviously, reverse the brackets) block around the code your pasting. It will also (when it's implemented) prevent the spellcheker from checking the spelling on your code.

Hal Levy
Daddyshome, LLC
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
unicode parameters in stored proc CharityPays SQL Server 2000 1 November 12th, 2006 12:02 AM
problems with stored proc and output parameters zieg42 VB.NET 2002/2003 Basics 1 June 12th, 2004 08:11 AM
Passing variables into the SELECT of Stored Proc kerrj SQL Language 1 October 15th, 2003 07:00 AM
Passing variables into the SELECT of Stored Proc kerrj SQL Server 2000 0 October 14th, 2003 11:16 PM
Optional Stored Proc Parameters? VBAHole22 SQL Server 2000 3 August 13th, 2003 12:46 PM



All times are GMT -4. The time now is 02:20 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.