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

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

Imar

Thanks, it was not reading the adovbs.inc file. Thanks

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.

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

regards
David Cameron

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