need help using SqlCeResultSet.Seek method...

  • Thread starter Thread starter Milsnips
  • Start date Start date
M

Milsnips

Hi there,

i'm trying to replace my dataset code to get results and return them into a
sqlCeResultSet but filtered by user entry, eg:


Sub GetCustomerList(ByVal g As DataGrid, Optional ByVal search As String = "")
Dim ceRs As SqlCeResultSet
g.DataSource = Nothing
g.RowHeadersVisible = False
g.ColumnHeadersVisible = False
g.TableStyles(0).MappingName = ""
cmd.CommandType = CommandType.TableDirect
cmd.CommandText = "Partneri"
cmd.IndexName = "pnameindex"
Try
ceRs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)
g.DataSource = ceRs
ceRs.Seek(DbSeekOptions.FirstEqual, New String() {search})
g.TableStyles(0).GridColumnStyles(0).Width = 0
Catch ex As SqlCeException
MsgBox(ex.Message)
Finally
ceRs = Nothing
End Try
End Sub



but.. it returns me all the results and doesnt filter by my "search" parameter. Any ideas why?

thanks.

Paul
 
Paul,

You need to use the cmd.SetRange method to specify the top and bottom ranges based on your index.

--
Ginny


Hi there,

i'm trying to replace my dataset code to get results and return them into a
sqlCeResultSet but filtered by user entry, eg:


Sub GetCustomerList(ByVal g As DataGrid, Optional ByVal search As String = "")
Dim ceRs As SqlCeResultSet
g.DataSource = Nothing
g.RowHeadersVisible = False
g.ColumnHeadersVisible = False
g.TableStyles(0).MappingName = ""
cmd.CommandType = CommandType.TableDirect
cmd.CommandText = "Partneri"
cmd.IndexName = "pnameindex"
Try
ceRs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)
g.DataSource = ceRs
ceRs.Seek(DbSeekOptions.FirstEqual, New String() {search})
g.TableStyles(0).GridColumnStyles(0).Width = 0
Catch ex As SqlCeException
MsgBox(ex.Message)
Finally
ceRs = Nothing
End Try
End Sub



but.. it returns me all the results and doesnt filter by my "search" parameter. Any ideas why?

thanks.

Paul
 
Once you got a RS it can't be changed. Scrolling RS it won't do you any good
either as DataGrid does not care about RS position.

To filter out stuff from RS you'd need to:

1. Get rid of existing RS.
2. Construct SQL statement which would filter out data the way you need,
e.g. "Select * from Table Where whatever=search"
3. Execute new RS.
4. Bind it to the grid.

TableDirect is no good in this case. No worries though, you won't notice any
difference in performance unless your query is complex e.g. contains
multiple joins (which you can't do with TableDirect anyway).

--
Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

Hi there,

i'm trying to replace my dataset code to get results and return them into a
sqlCeResultSet but filtered by user entry, eg:


Sub GetCustomerList(ByVal g As DataGrid, Optional ByVal search As String =
"")
Dim ceRs As SqlCeResultSet
g.DataSource = Nothing
g.RowHeadersVisible = False
g.ColumnHeadersVisible = False
g.TableStyles(0).MappingName = ""
cmd.CommandType = CommandType.TableDirect
cmd.CommandText = "Partneri"
cmd.IndexName = "pnameindex"
Try
ceRs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)
g.DataSource = ceRs
ceRs.Seek(DbSeekOptions.FirstEqual, New String() {search})
g.TableStyles(0).GridColumnStyles(0).Width = 0
Catch ex As SqlCeException
MsgBox(ex.Message)
Finally
ceRs = Nothing
End Try
End Sub

but.. it returns me all the results and doesnt filter by my "search"
parameter. Any ideas why?
thanks.
Paul
 
Thanks for both input Ilya and Ginny,

No complex query, just a select from a single table with a WHERE name LIKE
'abc%'. Is this type of filtering not possible using SetRange or Seek?

I am currently binding to a dataset then attaching that to the datagrid, and
there is a slight performance drop when more records are returned, thats why
i was trying out this method.

regards.
Paul
 
Paul,

You should be able to achieve the filtering you want using SetRange, but
also bear in mind Ilya's point that once you do get the resultset you can't
change the range on that resultset and you have to get another one. You
could also use SQL syntax to do it, and unless you have a large amount of
data and a constrained amount of memory, you might not be able to tell much
difference between using SQL and specifying the range with SetRange. Of
course in both cases the value you're filtering on needs to be indexed.
 
Back
Top