[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SELECT MID 100 using Classic ASP, ADO, SQL

Posted on 2012-08-31
9
Medium Priority
?
846 Views
Last Modified: 2012-09-02
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
0
Comment
Question by:KeithMcElroy
  • 4
  • 4
9 Comments
 
LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 38356320
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
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38356748
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
 

Author Comment

by:KeithMcElroy
ID: 38357066
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:KeithMcElroy
ID: 38357069
It even works if I set the second value to 0
rs.GetRows(somevalue,0)
0
 
LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 38357325
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
 

Author Comment

by:KeithMcElroy
ID: 38357360
Yes, they all show up when I leave the args out
0
 
LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 38357847
post your code and lets see if it is a coding problem.
0
 

Author Comment

by:KeithMcElroy
ID: 38358821
<%



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
 
LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 38359765
 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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question