SELECT MID 100 using Classic ASP, ADO, SQL

using asp, ado, is there a way to SELECT
      sql_query = "SELECT TOP(50) pe_id, pe_name, unique_key FROM pe_name_mstr WHERE pe_name LIKE '" & filteredReq & "%'"

such as

      sql_query = "SELECT MID(50,99) pe_id, pe_name, unique_key FROM pe_name_mstr WHERE pe_name LIKE '" & filteredReq & "%'"

I created a query tool in ASP that retrieves a couple thousand records usually and I need to
bring them in in small batches, so if I can get this to work, I can dynamically manage the 50,99 to be 100,149, 150, 199 , etc etc
KeithMcElroyAsked:
Who is Participating?
 
lwadwellConnect With a Mentor Commented:
The only way I can think of doing it with SQL in SQL Server 2005 would be to use row_number().  A consistent order would be required, like a primary key, to ensure repeatable results.  Examples would be:
sql_query = "SELECT pe_id, pe_name, unique_key FROM (SELECT pe_id, pe_name, unique_key, row_number()over(order by unique_key) rn FROM pe_name_mstr WHERE pe_name LIKE '" & filteredReq & "%')v WHERE rn between 1 and 50"
--or
sql_query = "SELECT pe_id, pe_name, unique_key FROM (SELECT pe_id, pe_name, unique_key, row_number()over(order by unique_key) rn FROM pe_name_mstr WHERE pe_name LIKE '" & filteredReq & "%')v WHERE rn between 51 and 100"

Open in new window

0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
If you are going to end up paging through the subets, I think simply using getrows() is the way to go.  
http://www.w3schools.com/ado/met_rs_getrows.asp
http://forums.aspfree.com/code-bank-54/getting-away-from-recordsets-use-getrows-230953.html



One option is to name a starting record number and then how many records to return.
in the portion of code objRS.GetRows(49,50) we are asking to start at the 50th result (zero based) and return 50 records).

strSQL = "SELECT T pe_id, pe_name, unique_key FROM pe_name_mstr WHERE pe_name LIKE '" & filteredReq & "%'"
Set objRS = Conn.Execute(strSQL) 
If Not objRS.EOF Then arrRS = objRS.GetRows(49,50) ' zero based 
Set objRS = Nothing
If IsArray(arrRS) Then
    For i = LBound(arrRS, 2) To UBound(arrRS, 2)
        pe_id = arrRS(0, i)
        pe_name = arrRS(1, i)
       unique_key=arrRS(2, i)
        Response.Write("<p>" & Name & " (" & pe_name & ")</p>" & vbCrLf)
    Next
    Erase arrRS
End If 

Open in new window


Another option is to just page through the records  http://www.4guysfromrolla.com/webtech/070500-1.shtml
strSQL = "SELECT T pe_id, pe_name, unique_key FROM pe_name_mstr WHERE pe_name LIKE '" & filteredReq & "%'"
Set objRS = Conn.Execute(strSQL) 
If Not objRS.EOF Then arrRS = objRS.GetRows() ' zero based 
Set objRS = Nothing

Dim iRows, iCols, iRowLoop, iColLoop, iStop
  iRows = UBound(arrRS, 2)
  iCols = UBound(arrRS, 1)

  If iRows > (iOffset + iStart) Then
    iStop = iOffset + iStart - 1
  Else
    iStop = iRows
  End If

  For iRowLoop = iStart to iStop
    For iColLoop = 0 to iCols


      Response.Write arrRS(iColLoop, iRowLoop) & " "
    Next
    Response.Write "<BR>"
  Next  

  Response.Write "<P>"
  if iStart > 0 then
    'Show Prev link
    Response.Write "<A HREF=""mypage.asp?Start=" & iStart-iOffset & _
                   "&Offset=" & iOffset & """>Previous " & iOffset & "</A>"
  end if

  if iStop < iRows then
    'Show Next link
    Response.Write " <A HREF=""mypage.asp?Start=" & iStart+iOffset & _
                   "&Offset=" & iOffset & """>Next " & iOffset & "</A>"
  end if

Open in new window

0
 
KeithMcElroyAuthor Commented:
The GetRows looks the best.  Thank you, I will make that work.

Working with the following example, with only the first arg
GetRows(somevalue) all works

However, GetRows(somevalue,somvalue)
kicks an err

I have a hunch it has to do with cursor type or some parameter in how I set up the rs.
However, not sure.  rs.recordcount returns -1 and the reason I found thru research was the cursor, so it occurs to me that maybe the getRows can not cursor forward?

Any help apprecited.
The GetRows is definitely the solution.
EEsample-09012012.odt
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
KeithMcElroyAuthor Commented:
It even works if I set the second value to 0
rs.GetRows(somevalue,0)
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
No need for the record count in ado.  Just use the default cursor.   Once you have your array, you can use ubound to get the totals.    

Are you sure you have more then 1 record?
0
 
KeithMcElroyAuthor Commented:
Yes, they all show up when I leave the args out
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
post your code and lets see if it is a coding problem.
0
 
KeithMcElroyAuthor Commented:
<%



dim arrRS, rs
      data_source = "Provider=SQLOLEDB;Server=erpdb\ONESolution;Database=production_finance;UID=SPS_SQLAdmin;PWD=1S0luti0n;"
      
      sql_query = "SELECT pe_id, pe_name, unique_key FROM pe_name_mstr WHERE pe_name LIKE 'TEST%'"
      
      ' Create Connection Object & Recordset Object and open the database
      set con = Server.CreateObject("ADODB.Connection")
      set rs = Server.CreateObject("ADODB.Recordset")
      const adOpenStatic = 3
      con.Open data_source
      rs.CursorType = adOpenStatic
      
      rs.Open sql_query, data_source

      h = "<table id=SearchResults border=1>"
      h = h & "<thead>"
      h = h & "<tr>"
      h = h & "<td>PeId</td>"
      h = h & "<td>Name</td>"
      h = h & "</tr>"  
      h = h & "</thead>"  

      h = h & "<tbody>"

      if not rs.eof then  
      rs.movefirst
            arrRS = rs.GetRows(10)   'adding the second arg here creates the issue
       
      
            if IsArray(arrRS) then
                  for i = LBound(arrRS, 2) to UBound(arrRS,2)
                  
                  h = h & "<tr >" 
                  h = h & "<td><a href='javascript:retrieveADO(""" & arrRS(2, i) & """)'><font color=black>" & arrRS(0, i)  &  "</font></a></td>"
                  h = h & "<td>" & arrRS(1, i)  &  "</td>"
                  h = h & "<td class=""uniquekeyx"">" & arrRS(2, i) & "</td>"
                  h = h & "</tr>"
            
                  next
                  erase arrRS
            end if
      
      
      else
      
            'response.write "No Data with Pe Id " & Request.Form("SearchString")

      end if  

      h = h & "</tbody>"

      h = h & "</table>"

      response.write h  

      rs.Close
      Set rs = Nothing
      con.Close
      Set con = Nothing


%>
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
 rs.movefirst
            arrRS = rs.GetRows(10)   'adding the second arg here creates the issue

If you are using getrows to loop through your data that you are not using rs.movefirst or anything similar.

GetRows will create an array of the data in your recordset that is available regardless of the db being open or closed.  So the other thing you should do is close your database right after you call getrows.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.