Reading A Table Multiple Times

Results 1 to 3 of 3

Thread: Reading A Table Multiple Times

  1. #1
    Ray Street Guest

    Default Reading A Table Multiple Times

    I&#039;m reading a table multiple times in an ASP script and the table is only being read the first time through the script. The basic coding is as follows:-<BR><BR>Dim objConn<BR><BR>Set objConn = Server.CreateObject("ADODB.Connection")<BR>objConn .ConnectionString = "DSN=properties"<BR>objConn.Open<BR><BR>For intLoop = LBound(arrLocation) to Ubound(arrLocation)<BR> strLocSQL = "SELECT Region,DistrictCity,Location FROM Locations WHERE SortName = &#039;"<BR> strLocSQL = strLocSQL & arrLocation(intLoop) & "&#039;"<BR> Set objRS = Server.CreateObject("ADODB.Recordset")<BR> objRS.Open strLocSQL, objCONN<BR><BR>&#039; response number 1<BR><BR> Response.Write strLocSQL & "<BR>"<BR><BR><BR>&#039; can be only one match<BR><BR> Do While Not objRS.EOF<BR> strRegion = objRS("Region")<BR> strDistrictCity = objRS("DistrictCity")<BR> strLocation = objRS("Location")<BR><BR>&#039; response number 2<BR><BR> Response.Write strLocation & "<BR>"<BR><BR> objRS.MoveNext<BR> Loop<BR><BR> objRS.Close<BR> Set objRS = Nothing<BR><BR>Next<BR><BR>strSQL = strSQL & ") "<BR><BR>Response.Write (strSQL)<BR><BR>The response 1 value is being shown for every value of arrLocation but the response 2 value is only showing the first time through.<BR><BR>What am I missing?

  2. #2
    Join Date
    Dec 1969

    Default I don't see a bug, but...

    ...I see some pretty horrendous database usage.<BR><BR>There is no reason that I can see than you should be opening and closing a recordset for each selected value.<BR><BR>Why can&#039;t you get *all* the records in a single query and then just process them with a tiny bit of VBS logic?<BR><BR>I gather that you have the multiple locations in an array, since you access them via <BR>&nbsp; &nbsp; &nbsp; arrLocation(intLoop)<BR><BR>Right?<BR><BR>SO why not do this:<BR><BR>&#060;%<BR>&#039; put &#039;,&#039; between each pair of values from array and<BR>&#039; tack a &#039; on each end...<BR>allLocs = "&#039;" & Join( arrLocation, "&#039;,&#039;" ) & "&#039;"<BR><BR>&#039; build a query that gets all locations in one recordset:<BR>SQL = "SELECT Region,DistrictCity,Location FROM Locations WHERE SortName IN (" & allLocs & ") ORDER BY SortName"<BR>&#039; you might want another ORDER BY in that?<BR><BR>&#039; get the recordset the easy way...instead of using two steps, just one:<BR>Set RS = objConn.Execute( SQL )<BR><BR>curLocation = ""<BR><BR>&#039; loop through all records, grouping by location:<BR>Do Until RS.EOF<BR>&nbsp; &nbsp; If RS("Location") &#060;&#062; curLocation Then<BR>&nbsp; &nbsp; &nbsp; &nbsp; curLocation = RS("Location")<BR>&nbsp; &nbsp; &nbsp; &nbsp; Response.Write curLocation & ": &lt;BR&#062;"<BR>&nbsp; &nbsp; End If<BR>&nbsp; &nbsp; Response.Write "&amp;nbsp;&amp;nbsp;&amp;nbsp; " & RS("Region") & " -- " & RS("DistrictCity") & "&lt;BR&#062;"<BR>&nbsp; &nbsp; ...<BR>&nbsp; &nbsp; RS.MoveNext<BR>Loop<BR>RS.Close<BR>%&#062;<BR><BR> It should execute N times faster, where N is the number of elements in that array of location names!<BR><BR><BR>

  3. #3
    Ray Street Guest

    Default RE: I don't see a bug, but...

    I&#039;m sure you do see a lot of bad database code. This is my first ASP website and the only reason I was opening and closing the recordset was to make sure that some sort of database pointer wasn&#039;t being left hanging around when I went into the code a second time.<BR><BR>I take your point about reading all of the records in a single hit and then scanning thru an array which I build. I was hoping to avoid that because the number of locations could be HUGE. The only reason that I go thru the code multiple times is that the preceding form allows multiple location selection - maybe I could get the customer to change their mind about this requirement.<BR><BR>Thanks for your input.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts