asked on

Dataset that selects first 100 records

Hi,

My dataset always retrieves up to 1000+ records which is overwhelming
How do i display only first 100 records and not show the rest.

I use Microsoft Indexing Server and ASP.NET code to do:

Dim strQuery As String
strQuery = "select top 100 doctitle, size, write, path, rank, characterization from scope() where FREETEXT(Contents, '" & strSearch & "') order by rank desc"

Dim connString As String = "Provider=MSIDXS.1;Integrated Security .='';Data Source='" & strCatalog & "'"

Error:
Incorrect syntax near '100'. Expected ',', '.', FROM. SQLSTATE=42000
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Incorrect syntax near '100'. Expected ',', '.', FROM. SQLSTATE=42000

Anyone knows?

Thanks!

cdemir

hi,

i think "top 100" is not supported. here is the reference : http://msdn2.microsoft.com/en-us/library/ms691670.aspx

jedistar

ASKER

What should i do?

cdemir

Dim strQuery As String
strQuery = "select doctitle, size, write, path, rank, characterization from scope() where FREETEXT(Contents, '" & strSearch & "') order by rank desc"

Dim connString As String = "Provider=MSIDXS.1;Integrated Security .='';Data Source='" & strCatalog & "'"

then, to get first 100 rows, make a loop in ASP.NET code.

imrancs

hi
you cant to it from query get all result in to datatable and use following function for get your desired count of rows from result

Function GetTop(dt, RowCount, _Start) as DataTable
Dim _table as DataTable
_table = dt.Clone()
For i = _Start To _Start + RowCount
If i >= dt.Rows.Count Then
Exit For
Else
_table.ImportRow(dt.Rows(i))
End If
Next

Return _table
end function

thanks,
ahmad

jedistar

ASKER

I'm using a Repeater, how do i do that

imrancs

hi

get the result into datatable then use GetTop for getting your desire rows and then bind it to your control.

thanks,
ahmad

jedistar

ASKER

Can i use the SELECT statement for this

ASKER CERTIFIED SOLUTION

imrancs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

imrancs

sorry, some correction..

strQuery = "SET ROWCOUNT 100; select doctitle, size, write, path, rank, characterization from scope() where FREETEXT(Contents, '" & strSearch & "') order by rank desc; SET ROWCOUNT 0"

jedistar

ASKER

got an error:

Incorrect syntax near '100'. Expected ID. SQLSTATE=42000
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Incorrect syntax near '100'. Expected ID. SQLSTATE=42000

-
i used:

strQuery = "SET ROWCOUNT 100; select doctitle, size, write, path, rank, characterization from scope() where FREETEXT(Contents, '" & strSearch & "') order by rank desc; SET ROWCOUNT 0"

jefftwilley

Dim strQuery As String
strQuery = "select top(100)doctitle, size, write, path, rank, characterization from scope() where FREETEXT(Contents, '" & strSearch & "') order by rank desc;"