<%
  iStart = Request("Start")
  iOffset = Request("Offset")
  if Not IsNumeric(iStart) or Len(iStart) = 0 then
    iStart = 0
  else
    iStart = CInt(iStart)
  end if
  if Not IsNumeric(iOffset) or Len(iOffset) = 0 then
    iOffset = 100
  else
    iOffset = Cint(iOffset)
  end if

  Response.Write "Viewing " & iOffset & " records starting at record " & iStart & "<BR>"

'------------------------------
' Get chunked records using getrows
'------------------------------
  Set objConn = Server.CreateObject("ADODB.Connection")
  objConn.Open sDSN
  Set objRS = Server.CreateObject("ADODB.Recordset")
  With objRS
  	.Open "Select * from Reports",objConn,0,1,&H0001
  	.Move(iStart)
  	aResults = .GetRows(iOffset)
  	.Close
  End With
  Set objRS = Nothing
' with count
  Set objRS2 = objConn.Execute("Select Count(ReportID) from Reports")
  recs = objRS2(0)
  objRS2.close
  Set objRS2 = Nothing
  objConn.Close
  Set objConn = Nothing
'------------------------------
' Count results
'------------------------------
  iRows = UBound(aResults, 2)
  iCols = UBound(aResults, 1)
  If iRows > (iOffset + iStart) Then
    iStop = iOffset + iStart - 1
  Else
    iStop = iRows
  End If
'------------------------------
' Formatting each row
'------------------------------
  For iRowLoop = 0 to iStop
      Response.Write aResults(0, iRowLoop) &","
  Next
'------------------------------
' Paging
'------------------------------
  Response.Write "<P>"
  if iStart > 0 then Response.Write "<A HREF=""getrows.asp?Start=" & iStart-iOffset & "&Offset=" & iOffset & """>Previous " & iOffset & "</A>"
  if iStop+1 = iOffset then Response.Write " <A HREF=""getrows.asp?Start=" & iStart+iOffset & "&Offset=" & iOffset & """>Next " & iOffset & "</A>"
  Response.write "<br>Record Count = "&recs
%>

§þk¥

• Database / DRW help Check out the Tutorials for Frontpage Databases
  
• Forum FAQ' s
  
• Developers Edition Spooky Login User Registration and Management tool
  
• Personalized Frontpage Hosting
  

§þk¥

• Database / DRW help Check out the Tutorials for Frontpage Databases
  
• Forum FAQ' s
  
• Developers Edition Spooky Login User Registration and Management tool
  
• Personalized Frontpage Hosting
  

--this proc returns the results of a search, but only the number
--of records requested
--searches the info table by title, blurb and date seperately
-- and gives a relevance score
/*declare @Count int
exec dbo.KeyDoc3SEARCH_usp 'positive', -1, 10, 9, @Count OUTPUT
select @Count*/
ALTER   PROC  dbo.KeyDoc3SEARCH_usp
	(
	@searchterm nvarchar(60)
	, @mDiff int = -1
	, @magnitude int= 10
	, @page tinyint = 1
	, @Count int OUTPUT
	)
--@mDiff is the month difference
--@magnitude is the number of results per page
--@page is the page required
--@Count is the total number of records returned by the search
AS
--turn off count to avoid errors with output variable
SET NOCOUNT ON
--use the magnitude and page number to work out the lower and higher bounds of the records
--asked for - eg magnitude 10 = 10 records per page, page = 2 means @lower = 10 and 
--@higher = 20
	declare  @date1 smalldatetime, @date2 smalldatetime, @higher int, @lower int
	set @higher = @magnitude * @page
	set @lower = @higher - @magnitude
--select @higher, @lower
	IF @mDiff >= 0 OR @mDiff IS NULL
	BEGIN
              --if empty then set for 1 month back
		SET @mDiff = -1
	END	
set @date1 = DATEADD(  m, @mDiff, GETDATE()  )
set  @date2 = GETDATE()
/* -- some ready made inputs for error checking
declare @searchterm nvarchar(60), @date1 smalldatetime, @mDiff int
set @searchterm = N'camhs'
set @date1 = null */

-- the select statement
	select  i.[id], i.Title, i.Blurb
			, dbo.DateFormat_fn(i.DateCreated, ' ') AS DateCreated
			, h.Host AS host, u.Path AS path
			, u.ALTcontent
			, i2.Score
		FROM Info AS i
			INNER JOIN 
                            --this udf is a 2d array with 3 columns
                            --1st col is IDENTITY, 2nd col is the id's from the Info table
                            --3rd col is the search relevance score eg 3 or 2
                            --sorted by count DESC, then the IDENTITY is added
				dbo.SearchResultsKEYDOC_udf(@searchterm,@date1, @date2)
			AS i2
				ON i.[id] = i2.[Infoid]
-- getting the urls
			INNER JOIN InfoRelURL AS r  
				 ON i.[Id] = r.InfoId 
			INNER JOIN URL AS u
				ON r.URLid = u.[id]
			INNER JOIN dbo.urlHost_udf()  AS h
					ON u.Hostid = h.id

-- this where clause restricts the returned records using values derived from @magnitude
-- and @page
	WHERE i2.id BETWEEN @lower AND @higher
-- output the count
	SELECT @Count = Count(*)
		FROM dbo.SearchResultsKEYDOC_udf(@searchterm,@date1, @date2)

SET NOCOUNT OFF

--last updated 1 9 05 17.20
--select * from dbo.SearchResults_udf('positive', '10 Aug 2005', '1 Sep 2005')
--optimised for KEYDOCs
-- UNION ALL is used to allow a GROUP BY which can then be counted
--giving a rudimentary relevance score
CREATE  FUNCTION  dbo.SearchResultsKEYDOC_udf
	(
	@searchterm nvarchar(60)
	, @date1 smalldatetime = null
	, @date2 smalldatetime = null
	)
	RETURNS @myTable TABLE
	(
	id int IDENTITY(1,1)
	, Infoid int
	, Score tinyint
	)
AS
	BEGIN
-- the insert
		INSERT @myTable
			select  i.[id]
				,  COUNT(*) AS Score		
				FROM Info AS i
					INNER JOIN
					(
--search the title
				select * from dbo.InfoSELECTidsfromTitleKEYDOC_udf(@searchterm)
							UNION ALL
--search the blurb
				select * from dbo.InfoSELECTidsfromBlurbKEYDOC_udf(@searchterm)
							UNION ALL
--search between dates
				select * from dbo.InfoSELECTidsfromDatesKEYDOC_udf(@date1, @date2)
					) AS i2
						ON i2.[id] = i.[id]
-- group by and sort by...
				GROUP BY i.[id]
				ORDER BY Score DESC

-- return the array
			RETURN
	END