I am trying to be able to let users

[GuntherKS]

I am trying to be able to let users do a search for a name in a database. If the name is partially correct (for example names are Zwork, Ztest, and Zbob. and they just typed 'Z') I would like them to be able to step through a list. I already have a recordset that can be navigated by a recordset navbar. I also have a successful query if they hit the name exact. However, when they just enter 'Z' the user only gets the first Z and if you use the recordset navbar to navigate to the next record it goes to the beginning of all records. This is the code I am using.

Sub btnNameSearch_onclick()
rsITSxxx.close
newSQL = "Select * from itsxxx where account_name like '%" & tbNameSearch.value & "%'"
rsITSxxx.setSQLText(newSQL)
rsITSxxx.open

I hope some of this made sense. Thanks for looking at it.

Kevin

 

[palbano]

Kevin,

> if you use the recordset navbar to navigate to the next record it
> goes to the beginning of all records

That causes another trip to the server and another query of the database. You need to use the same 'like' query when the navbar navigates the recordset.

Hope this helps
-pete

 

[GuntherKS]

What you said makes perfect sense, but how do I go about keeping the same query for the navbar to use.

Shouldn't this statement change the SQL statement:

>rsITSxxx.setSQLText(newSQL)

Why does it revert back to the original query statement every time it goes to the server to query the database? Shouldn't it keep this SQL until physical code changes it to something else? Any suggestions are appreciated.

Thanks,
Kevin

 

[palbano]

Kevin,

You must keep in mind that you are in an multi-tier stateless environment (HTTP). Your server code values (variables) are destroyed once the response is finished being sent to the browser. Each time the user returns to the server page, all values are initialized again.

So you want to look at the Recordset.onbeforeload event. That is where you can initialize the SQLText value.

Now how you decide to persist the data needed to set the value is up to you. Session variables would work but they are expensive in a high volume site. Using hidden form variables is another technique. Rolling your own session data using a RDBMS is another technique that is gaining in popularity.

Since you are using the Design-Time Controls in Visual Interdev any technique used must be compatible with the operations of those controls.

Hope this helps
-pete