Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recordset not allowing the display of all rows

Status
Not open for further replies.
dpk136

dpk136

MIS
Jan 15, 2004
335
US
I am accessing a SQL Server database via SQL select statement. the query works fine, no problems there. What happens is, i open the recordset and then open other recordsets while i'm looping through this one. I have a close after i am done with every recordset.

I get this error:
Code: 
Microsoft OLE DB Provider for ODBC Drivers error '80004005' 
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied. 

/vntgsf/GPTSQL/FreightReport.asp, line 147

The line that it happens on is not always this line #. It shows a different line number everytime where the second recordset gets opened. I have no idea what is wrong and am not really sure how to explain it, but it loops through 661 records and then pops out this error. i looked at record 662 and there is nothing wrong with it. Can anyone help.

David Kuhn
------------------
 
Sort by date Sort by votes
I think we need to see some code where you make the connection, and where you are outputting the info.
 
Upvote 0 Downvote
Here is the whole asp page. Please help
Code: 
<% 
  intPageSecurity = 1 'User
  Timeout = Server.ScriptTimeout
  Server.ScriptTimeout = 270
%>
<!-- #include file="includes/connection_string.inc" -->
<!-- #include file="includes/company.inc" -->
<!-- #include file="includes/Functions.inc" -->
<%
  set conn = Server.CreateObject("ADODB.Connection")
  set rs = Server.CreateObject("ADODB.Recordset")
  set rst = Server.CreateObject("ADODB.Recordset")
  conn.open(conString)
%>
<!-- #include file="includes/Security.inc" -->
<!-- #include file="includes/checkSSISexec.inc" -->

<html>
<head>
  <title>Freight Report - Global Point</title>
  <script language="JavaScript" src="../javascript/date-picker.js"></script>
  <script language="javascript" TYPE="text/javascript">
    function CancelPage() {
      window.location="main.asp";
    }

    function ChangeDelDate() {
      document.myForm.CalcDelDate.value = FormatDate(Date(document.myForm.ConsolidationDate.value) + 42,"M/d/YYYY")
    }
  </script>
</head>
<body>

<%
  If bSecValid = False Then
    'Display that they do not have authorization.
    Response.Write("You are not authorized to view this page!<br>")
    Response.Write("Please go <a href='javascript:history.back()'>BACK</a>")    

    'Quit page
    Response.End
  End If

'-----------Include all previous code in every page----------


'---------------------------Show Freight---------------------------

Response.Write("<form name='myForm' method=POST action='FreightReport.asp'>")
Response.Write("<table border=1 cellspacing=0 cellpadding=0>")
  strSQL = "SELECT POH.Company,POH.PONum,POD.POLine,POR.PORelNum,Par.PartNum,PT.RevisionNum,Max(POR.RelQty) as RelQty,Max(POH.OrderDate) as OrderDate,Max(POD.UnitCost) as UnitCost,POR.OpenRelease " & _
           "FROM vPOHeader as POH INNER JOIN vPODetail as POD On POH.PONum = POD.PONum " & _
               "INNER JOIN vPORel as POR On (POD.PONum = POR.PONum And POD.POLine = POR.POLine) " & _
               "INNER JOIN vPart as Par On (POD.PartNum = Par.PartNum) " & _
               "LEFT JOIN vPartTran as PT On Par.PartNum = PT.PartNum " & _
           "GROUP BY POH.Company,POH.PONum,POD.POLine,POR.PORelNum,Par.PartNum,PT.RevisionNum,POR.OpenRelease " & _
           "HAVING POR.OpenRelease = 1 And POH.Company = '" & lcCompany & "'"
  'response.Write(strSQL & "<br>")
  rs.Open strSQL,conn

  Response.Write("<tr>")
    Response.Write("<th>Freight ID</th>")
    Response.Write("<th>PO #</th>")
    Response.Write("<th>PO<br>Line #</th>")
    Response.Write("<th>PO<br>Rel #</th>")
    Response.Write("<th>Part #</th>")
    Response.Write("<th>Rev. #</th>")
    Response.Write("<th>Rel Qty</th>")
    Response.Write("<th>Order Date</th>")
    Response.Write("<th>Unit Value</th>")
    Response.Write("<th>Ext Value</th>")
    Response.Write("<th>Delivery<br>Date<br>Conf?</th>")
    Response.Write("<th>Qty<br>Conf?</th>")
    Response.Write("<th>Amt. Conf.</th>")
    Response.Write("<th>Ship<br>Method</th>")
    Response.Write("<th>Conf.<br>Ship<br>Date</th>")
    Response.Write("<th>Revised<br>Ship<br>Date</th>")
    Response.Write("<th>PO Rel<br>Status</th>")
    Response.Write("<th>PO Rel<br>Type</th>")
    Response.Write("<th># Of<br>Cartons</th>")
    Response.Write("<th>Total m3</th>")
    Response.Write("<th>Total kg</th>")
    Response.Write("<th>Consolidation<br>Date</th>")
    Response.Write("<th>Est<br>Delivery<br>Date</th>")
    Response.Write("<th>Departure<br>Port</th>")
  Response.Write("</tr>")
  Do While rs.EOF = False
    'Check to see if the PO release is on BOL
    strSQL = "SELECT Qty FROM BOLDetail WHERE PONum=" & Trim(rs.Fields("PONum")) & " And POLineNum=" & Trim(rs.Fields("POLine")) & " And PORelNum=" & Trim(rs.Fields("PORelNum"))
    rst.Open strSQL,conn
    'If it is check to see if the RelQty is the same as the BOLQty
    if rst.EOF = True Then
      'Response.write("No Detail Info")
      'bLineExists = "False"
      intReportQty = CLng(Trim(rs.Fields("RelQty")))
    Else
      If CLng(Trim(rst.Fields("Qty"))) < CLng(Trim(rs.Fields("RelQty"))) Then
        intReportQty = CLng(Trim(rs.Fields("RelQty"))) - CLng(Trim(rst.Fields("Qty")))
      Else
        'This line should not be in the report, move to the next record
        bLineExists = "False"
      End If
    End If
    'Close the temporary recordset
    rst.Close
    strPONum = Trim(rs.Fields("PONum"))
    strPOLine = Trim(rs.Fields("POLine"))
    strPORelNum = Trim(rs.Fields("PORelNum"))
    strPartNum = Trim(rs.Fields("PartNum"))
    strRevisionNum = Trim(rs.Fields("RevisionNum"))
    strOrderDate = Trim(rs.Fields("OrderDate"))
    strUnitValue = Trim(rs.Fields("UnitCost"))

    If bLineExists <> "False" Then
      'Get the rest of the data
      'Group by the PONum for report
      'Find out if this record has a matching record in FreightReport table
      If strRevisionNum <> "" Then
        strSQL = "SELECT FreightReportID,StdPackQty,StdPackWeight,StdPackM3,DelivDateConf,QtyConfirmed,QtyConfirmation,ShipMethod,ConfirmedShipDate,RevisedShipDate,PORelStatus,PORelType,ConsolidationDate,DepartPort,CalcDelDate FROM FreightReport WHERE PONum=" & Trim(rs.Fields("PONum")) & " And POLineNum=" & Trim(rs.Fields("POLine")) & " And PORelNum=" & Trim(rs.Fields("PORelNum")) & " And RevisionNum='" & strRevisionNum & "'"
      Else
        strSQL = "SELECT FreightReportID,StdPackQty,StdPackWeight,StdPackM3,DelivDateConf,QtyConfirmed,QtyConfirmation,ShipMethod,ConfirmedShipDate,RevisedShipDate,PORelStatus,PORelType,ConsolidationDate,DepartPort,CalcDelDate FROM FreightReport WHERE PONum=" & Trim(rs.Fields("PONum")) & " And POLineNum=" & Trim(rs.Fields("POLine")) & " And PORelNum=" & Trim(rs.Fields("PORelNum"))
      End If
      rst.Open strSQL,conn
      If rst.EOF = True Then
        'If it doesn't, get the data to fill this in with the standards
        strSQL = "SELECT StdPackQty,StdShipMethod,StdPackWeight,DepPortID,StdPackM3 " & _
                 "FROM PartDetail " & _
                 "WHERE PartNum='" & strPartNum & "'"
        rst.Close
        'response.write(strSQL)
        rst.Open strSQL,conn

        If rst.EOF = False Then
          'Put std data into variables
          strShipMethod = Trim(rst.Fields("StdShipMethod"))
          strDepartPort = Trim(rst.Fields("DepPortID"))
          strStdPackQty = Trim(rst.Fields("StdPackQty"))
          strStdPackWeight = Trim(rst.Fields("StdPackWeight"))
          strStdPackM3 = Trim(rst.Fields("StdPackM3"))
        End If
        bIsNewRec = "True"
        strFreightReportID = ""
        strDelivDateConf = "N"
        strQtyConfirmed = "N"
        strQtyConfirmation = "0"
        strConfirmedShipDate = ""
        strRevisedShipDate = ""
        strPORelStatus = ""
        strPORelType = ""
        strConsolidationDate = ""
        strCalcDelDate = ""
      Else
        'Put data into variables
        strFreightReportID = Trim(rst.Fields("FreightReportID"))
        strDelivDateConf = Trim(rst.Fields("DelivDateConf"))
        strQtyConfirmed = Trim(rst.Fields("QtyConfirmed"))
        strQtyConfirmation = Trim(rst.Fields("QtyConfirmation"))
        strShipMethod = Trim(rst.Fields("ShipMethod"))
        strConfirmedShipDate = IIf(Trim(rst.Fields("ConfirmedShipDate"))="1/1/1900","",Trim(rst.Fields("ConfirmedShipDate")))
        strRevisedShipDate = IIf(Trim(rst.Fields("RevisedShipDate"))="1/1/1900","",Trim(rst.Fields("RevisedShipDate")))
        If strRevisedShipDate = "" Then
          strRevisedShipDate = strConfirmedShipDate
        End If
        strPORelStatus = Trim(rst.Fields("PORelStatus"))
        strPORelType = Trim(rst.Fields("PORelType"))
        strConsolidationDate = IIf(Trim(rst.Fields("ConsolidationDate"))="1/1/1900","",Trim(rst.Fields("ConsolidationDate")))
        strDepartPort = Trim(rst.Fields("DepartPort"))
        If strConsolidationDate <> "" Then
          strCalcDelDate = DateDiff("d", 42, CDate(strConsolidationDate))
        Else
          strCalcDelDate = ""
        End If
        strStdPackQty = Trim(rst.Fields("StdPackQty"))
        strStdPackWeight = Trim(rst.Fields("StdPackWeight"))
        strStdPackM3 = Trim(rst.Fields("StdPackM3"))
        bIsNewRec ="False"
      End If

      rst.Close

      If strPORelStatus <> "" Then
        strSQL = "SELECT PORelStatus FROM PORelStatusList WHERE PORelStatusID=" & strPORelStatus
        rst.Open strSQL,conn
        If rst.EOF = False Then
          intPORelStatus = strPORelStatus
          strPORelStatus = Trim(rst.Fields("PORelStatus"))
        Else
          strPORelStatus = ""
        End If
        rst.Close
      End If

      If strPORelType <> "" Then
        strSQL = "SELECT PORelType FROM PORelTypeList WHERE PORelTypeID=" & strPORelType
        rst.Open strSQL,conn
        If rst.EOF = False Then
          intPORelType = strPORelType
          strPORelType = Trim(rst.Fields("PORelType"))
        Else
          strPORelType = ""
        End If
        rst.Close
      End If
      
      If strShipMethod <> "" Then
        strSQL = "SELECT ShipMethod FROM ShipMethodList WHERE ShipMethodID=" & strShipMethod
        rst.Open strSQL,conn
        If rst.EOF = False Then
          intShipMethod = strShipMethod
          strShipMethod = Trim(rst.Fields("ShipMethod"))
        Else
          strShipMethod = ""
        End If
        rst.Close
      End If 

      If strDepartPort <> "" Then
        strSQL = "SELECT DepPort FROM DeparturePortList WHERE DepPortID=" & strDepartPort
        rst.Open strSQL,conn
        If rst.EOF = False Then
          intDepartPort = strDepartPort
          strDepartPort = Trim(rst.Fields("DepPort"))
        Else
          strDepartPort = ""
        End If
        rst.Close
      End If    

      If bIsNewRec = "True" Then
        strSQL = "INSERT INTO FreightReport (" & _
                 "PONum," & _
                 "POLineNum," & _
                 "PORelNum," & _
                 "RevisionNum," & _
                 "DelivDateConf," & _
                 "QtyConfirmed," & _
                 "QtyConfirmation," & _
                 "ShipMethod," & _
                 "ConfirmedShipDate," & _
                 "RevisedShipDate," & _
                 "PORelStatus," & _
                 "PORelType," & _
                 "StdPackQty," & _
                 "StdPackWeight," & _
                 "StdPackM3," & _
                 "ConsolidationDate," & _
                 "CalcDelDate," & _
                 "DepartPort" & _
                 ") VALUES (" & _
                 strPONum & "," & _
                 strPOLine & "," & _
                 strPORelNum & ",'" & _
                 strRevisionNum & "','" & _
                 strDelivDateConf & "','" & _
                 strQtyConfirmed & "'," & _
                 strQtyConfirmation & "," & _
                 IIf(intShipMethod="",0,intShipMethod) & ",'" & _
                 strConfirmedShipDate & "','" & _
                 strRevisedShipDate & "'," & _
                 IIf(intPORelStatus="",0,intPORelStatus) & "," & _
                 IIf(intPORelType="",0,intPORelType) & "," & _
                 IIf(strStdPackQty="",0,strStdPackQty) & "," & _
                 IIf(strStdPackWeight="",0,strStdPackWeight) & "," & _
                 IIf(strStdPackM3="",0,strStdPackM3) & ",'" & _
                 strConsolidationDate & "','" & _
                 strCalcDelDate & "'," & _
                 IIf(intDepartPort="",0,intDepartPort) & _
                 ")"
        conn.Execute(strSQL)
        strSQL = "SELECT Max(FreightReportID) as FreightID FROM FreightReport"
        rst.Open strSQL,conn
        If rst.EOF = False Then
          strFreightReportID = Trim(rst.Fields("FreightID"))
        End If
        rst.Close
      End If

      'Put in the records from SQL Server
      'If it's a new record, change the background so they know to change it.
      If strConfirmedShipDate = "" Then
        Response.Write("<tr bgcolor='FFFFCC'>")
      Else
        Response.Write("<tr bgcolor='FFFFFF'>")
      End If

        Response.Write("<td>" & strFreightReportID & "</td>")
        Response.Write("<td>" & strPONum & "</td>")
        Response.Write("<td>" & strPOLine & "</td>")
        Response.Write("<td>" & strPORelNum & "</td>")
        Response.Write("<td>" & strPartNum & "</td>")
        Response.Write("<td>" & strRevisionNum & "</td>")
        Response.Write("<td>" & intReportQty & "</td>")
        Response.Write("<td>" & strOrderDate & "</td>")
        Response.Write("<td>" & strUnitValue & "</td>")
        Response.Write("<td>" & CDbl(strUnitValue) * intReportQty & "</td>")
           
        Response.Write("<td>" & strDelivDateConf & "</td>")
        Response.Write("<td>" & strQtyConfirmed & "</td>")
        Response.Write("<td>" & IIf(strQtyConfirmation="0","",strQtyConfirmation) & "</td>")
        Response.Write("<td>" & strShipMethod & "</td>")
        Response.Write("<td>" & strConfirmedShipDate & "</td>")
        Response.Write("<td>" & strRevisedShipDate & "</td>")
        Response.Write("<td>" & strPORelStatus & "</td>")
        Response.Write("<td>" & strPORelType & "</td>")
        Response.Write("<td>" & IIf(strStdPackQty="0","",strStdPackQty) & "</td>")
        Response.Write("<td>" & IIf(strStdPackm3="0","",strStdPackm3) & "</td>")
        Response.Write("<td>" & IIf(strStdPackWeight="0","",strStdPackWeight) & "</td>")
        Response.Write("<td>" & strConsolidationDate & "</td>")
        Response.Write("<td>" & strCalcDelDate & "</td>")
        Response.Write("<td>" & strDepartPort & "</td>")
      Response.Write("</tr>")
    End If
    
    rs.MoveNext
  Loop
  rs.Close
Response.Write("</table>")
Response.Write("</form>")

%>
<br><br>
<!-- #include file="includes/bottomtoolbar.inc" -->
</body>
</html>

David Kuhn
------------------
 
Upvote 0 Downvote
once bLineExists is set to false it never gets unset.
 
Upvote 0 Downvote
I don't really know how that would have anything to do with the error i'm getting. It does not affect the loop. I changed it so that this gets reset, and it still didn't work.

David Kuhn
------------------
 
Upvote 0 Downvote
i would suggest you to break down the whole code into pieces and start inserting response.write statements to see if you are getting the expected values...

-DNG
 
Upvote 0 Downvote
What i finally did is make them use pages. I get the first 100 records, then the next 100.This stopped the error from coming

David Kuhn
------------------
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
156
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
185
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
iis woos (not displaying pages correctly)
Replies
1
Views
186
penguinspeaks
penguinspeaks
Swi
  • Locked
  • Question
Best/Quickest Grid to display data
Replies
1
Views
183
Swi
Swi
leifoet
  • Locked
  • Question
How to update the content of radio buttons in a database?
Replies
2
Views
128
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top