ASP problems please HELP...

brian777 · April 21st, 2005, 11:10 AM

I'm new to ASP and I desperately need help. I have 6 asp pages total. The last one I just added yesterday. I'm trying to pull data and display it on page 6, but it's coming from a different table (SQL 7 databases)than the previous page. Do I have to change the Recordset. Is it still using the Recordset from the previous page. I'm getting this error when I try to go from page 5 to 6. Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal. For line 81.

Here's 80 and 81.

While Not objRS2.EOF //80
strOutputString = strOutputString & "<tr align=center>" & _ //81

Here's the code from Page 5:

<body>

<form METHOD="POST" ACTION="pricing4.asp" id=form1 name=form1>

<form id=form1 name=form1>
  <table BORDER="0">
    <tr>
      <td width= 215><b>  Part Description</b></td>
      <td>
    <%Response.Write(Request("prodgrp"))%>
    <input type="hidden" name="prodgrp" size=40 Value="<%=Request("prodgrp")%>"></td>
    </tr>
    <tr>
      <td><b>  OEM Part Number</b></td>
      <td>
    <%Response.Write(Request("OEMPARTNUM"))%>
    <input TYPE="hidden" NAME="OEMPARTNUM" VALUE="<%=Request("OEMPARTNUM")%>"></td>
    </tr>
    <tr>
      <td><b>  Customer</b></td>
      <td>
    <%Response.Write(Request("customer"))%>
    <input TYPE="hidden" NAME="customer" size=50 VALUE="<%=Request("customer")%>"></td>
    </tr>
  </table>
  <BR><BR>
  <table width = 100%>
    <tr>
      <td> </td>
      <td>
<%
dim objConn, objRS, strConnect
dim strSQL
dim strpart
dim strcust

Set objConn = Server.CreateObject ("ADODB.Connection")
Set objRS = Server.CreateObject ("ADODB.Recordset")

strpart=Request.Form("OEMPARTNUM")
strcust=Request.Form("customer")

strSQL = "SELECT * FROM vw_RepairPrice WHERE PartNumber = '" & strpart & " ' AND Name = '" & strcust & " '" & _
        "ORDER BY PartDescription"

objConn.Open strConnect
objRS.Open strSQL, objConn

strOutputString = "<table border=1 width =""100%"">" & _
    "<tr align=center>" & _
    "<td><b>Repair Description</b></td>" & _
    "<td><b>Comments</b></td>" & _
    "<td><b>Price</b></td>" & _
    "<td><b>TAT</b></td>" & _
    "<td><b>Sales Group</b></td>" & _
    "<td><b>QID/Contract #</b></td>" & _
    "</tr>"

While Not objRS.EOF
strOutputString = strOutputString & "<tr align=center>" & _
    "<td>" & objRS("RepairDescription")& "</td>" & _
    "<td>" & objRS("Comments")& "</td>" & _
    "<td>" & FormatCurrency(objRS("price"))& "</td>" & _
    "<td> " & objRS("tat")& "</td>" & _
    "<td> " & objRS("SalesGroup")& "</td>" & _
    "<td>" & objRS("QID")& "</td>" & _
    "</tr>"
objRS.MoveNext
Wend
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
Response.Write strOutputString

%></td>
    </tr>
  </table>
  <br>
  <INPUT TYPE="button" NAME="Go Back!" VALUE="Go Back" LANGUAGE="VBScript" OnClick="call window.history.back(1)">
  <input TYPE="Submit" value="Next" id=Submit1 name=Submit1>
</FORM>
<br><br><br><br><br><br>

Here's my code so far for unreachable Page 6:

<body>

<form METHOD="POST" ACTION="pricing4.asp" id=form1 name=form1>

<form id=form1 name=form1>
  <table BORDER="0">
    <tr>
      <td width= 215><b>  Part Description</b></td>
      <td>
    <%Response.Write(Request("prodgrp"))%>
    <input type="hidden" name="prodgrp" size=40 Value="<%=Request("prodgrp")%>"></td>
    </tr>
    <tr>
      <td><b>  OEM Part Number</b></td>
      <td>
    <%Response.Write(Request("OEMPARTNUM"))%>
    <input TYPE="hidden" NAME="OEMPARTNUM" size=40 VALUE="<%=Request("OEMPARTNUM")%>"></td>
    </tr>
    <tr>
      <td><b>  Customer</b></td>
      <td>
    <%Response.Write(Request("customer"))%>
    <input TYPE="hidden" NAME="customer" size=50 VALUE="<%=Request("customer")%>"></td>
    </tr>
  </table>
  <BR><BR>
  <table width = 100%>
    <tr>
      <td> </td>
      <td>
<%
dim objConn, objRS2, strConnect
dim strSQL, strOutputString
dim strpart
dim strcust

Set objConn = Server.CreateObject ("ADODB.Connection")
Set objRS2 = Server.CreateObject ("ADODB.Recordset")

strpart=Request.Form("OEMPARTNUM")
strcust=Request.Form("customer")

strSQL = "SELECT DISTINCT IEN FROM vw_IENNoDups WHERE Name = '" & strcust & " '" & _
        "ORDER BY IEN"

objConn.Open strConnect
objRS2.Open strSQL, objConn

strOutputString = "<table border=1 width =""500"">" & _
    "<tr align=center>" & _
    "<td><b>Customer</b></td>" & _
    "<td><b>All IENS</b></td>" & _

    "</tr>"

While Not objRS2.EOF
strOutputString = strOutputString & "<tr align=center>" & _
       "<td>" & objRS2("QID")& "</td>" &_
       "<td>" & objRS2("IEN")& "</td>" & _
    "</tr>"
objRS2.MoveNext
Wend

objRS2.Close
objConn.Close
Set objRS2 = Nothing
Set objConn = Nothing
Response.Write strOutputString

%>

</td>
    </tr>
  </table>
  <br>
  <INPUT TYPE="button" NAME="Go Back!" VALUE="Go Back" LANGUAGE="VBScript" OnClick="call window.history.back(1)">
  <input TYPE="Submit" value="Next" id=Submit1 name=Submit1>
</FORM>
<br><br><br><br><br><br>

<a href="mailto:[email protected]">Mail Technical issues to MIS</a><br>
<a href="mailto:[email protected]">Mail Price issues to Customer Service</a>
</body>
</html>

<P> </P>

</BODY>
</HTML>

ChrisScott · April 21st, 2005, 12:27 PM

Hi Brian,

I think the error may be on the line

Code:

objRS2("QID")

as your query does not select a field of this name. try changing it to

Code:

"SELECT DISTINCT IEN, QID FROM vw_IENNoDups WHERE Name = '" & strcust & " '" & _
        "ORDER BY IEN"

HTH,

Chris

brian777 · April 21st, 2005, 12:53 PM

Sorry, I changed all the objRS2s back to objRS. The QID field was coming from a table from the previous page. QID isn't a field in vw_IENNoDups. I was just testing to see if I could get something from my new table to display and something from the old table to display as well. I've since changed everything back to the way it was for my new page. My goal is really only to get IENs to display from vw_IENNoDups.

It looks like this now:

      <td>
<%
dim objConn, objRS, strConnect
dim strSQL, strOutputString
dim strpart
dim strcust

Set objConn = Server.CreateObject ("ADODB.Connection")
Set objRS = Server.CreateObject ("ADODB.Recordset")

strpart=Request.Form("OEMPARTNUM")
strcust=Request.Form("customer")

strSQL = "SELECT DISTINCT IEN FROM vw_IENNoDups WHERE Name = '" & strcust & " '" & _
        "ORDER BY IEN"

objConn.Open strConnect
objRS.Open strSQL, objConn

strOutputString = "<table border=1 width =""500"">" & _
    "<tr align=center>" & _
    "<td><b>All IENS</b></td>" & _
    "<td><b>Customer</b></td>" & _

    "</tr>"

While Not objRS.EOF
strOutputString = strOutputString & "<tr align=center>" & _ //error
       "<td>" & objRS("IEN")& "</td>" &_
    "</tr>"
objRS.MoveNext
Wend

objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
Response.Write strOutputString
%>

</td>

Sorry about the confusion. I'm trying anything to get this to work.

TyVeryMuch,
B

ChrisScott · April 22nd, 2005, 03:07 AM

Hi Brian,

Are you still getting the same error?

What value are you giving to strConnect? Looking at the above code, it seems to have no value, so I am surprised you are not getting an error when you try to open a connection using it.

Cheers,

Chris