Page scrolling in search results

[tvbruwae]

Hi

I created an Interdev ASP site with MS SQL server running behind it as a data source. From the SQL tables I want to display the entire contents when the index page is loaded, so I used the "Select * from Overview" command in the SQL recordset. When entering a string in a textbox, the database looks for any values that contain this string. Therefore, different buttons call the next procedure with different values for the s_ID parameter :

sub SetQuery(S_ID)
Dim S_String
S_String = S_box.value
if S_String <> &quot;&quot; then
SQLString = &quot;SELECT * FROM Overview WHERE &quot; + trim (S_ID) + &quot; LIKE &quot;
SQLString = SQLString + &quot;'&quot; + trim(S_String) + &quot;%'&quot;
else
SQLString = &quot;SELECT * FROM Overview&quot;
end if
Search.close()
Search.setSQLText(SQLString)
Search.open()
end sub

Until now, everything works fine : the grid that is linked to the recordset shows the entire DB contents when first loading the page and when no value is entered before a search, and it displays the search results when a valid search parameter is supplied.

However, when the scroll buttons at the bottom of the grid are pressed after a search (when for example the grid shows page 1 of 3 of the search results), the grid continues with page 2... of the entire table contents (so page 2 of 865 instead of the 3 pages with search results). Apparently, the recordset opens with the adapted SQL statement after a search, but when you start to scroll it seems to take the default SQL statement (Select * ...) back. Can these scroll buttons be redefined to use the search SQL command, or is there any other solution to solve this?

Thanks for helping.

Tim

 

[MerlinB]

Try turning on the Trace feature
<SCRIPT id=DebugDirectives runat=server language=javascript>
// Set these to true to enable debugging or tracing
@set @debug=false
@set @trace=true //****
</SCRIPT>

this may help unravel the goings on.

Ensure the recordset does not auto-populate.

The paging buttons do inherit the recordset, so this is not the problem.

You may have a page initialisation procedure that sets the default recordset string (indeed, look at the code-behind the pages recordset DTC.) Clearly, this needs to either only execute on the first page load - or to supply the correct SQL on every page load.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[tvbruwae]

On initialization of the RecordSet the SQL statement is set (cmdTmp.CommandText = 'SELECT * FROM Overview WHERE...). But I don't know where to find a statement that forces the set to take its default SQL on every reload...

I've also read your post about tracing in ASP pages (october 2001), but I'm afraid I haven't found the proposed <script>-code in my pages. Even in PM.ASP I haven't found anything pointing to a trace feature. I'm sorry for being such a newbie, but I have been working with Interdev for only 2 weeks now, and before I didn't even know ASP (I'm using PHP for my personal pages, but at work I'm required to use the Microsoft tools).

I'd appreciate it a lot if you could give some more details about how to find the commands that set the SQL statement on page changes.

Thanks for posting!

Tim

 

[tvbruwae]

Update : it works. Don't ask me what happened : until I would find a solution I disabled paging (so all records were shown on a single page). I just re-enabled it to see if the tracing would help (without changing anything to the previous code), and apparently the problem with the paging buttons was solved... I read the MSDN article about tracing too, but I still have questions. For example : when tracing is enabled, do you still have to use &quot;response.write&quot; to send messages to the output, or is this done automatically when an error occurs?

 

[MerlinB]

I assume that you are using VI version 6, load up service pack 4 as well.
The code block that includes the trace feature is added when you enable the SOM (Script Object Model) for a page, which is a pre-requisit for most DTC's anyway.
Many things are simplified when you add a PageObject DTC - one per page. This gives you a thisPage_onenter event, amongst other handy things.

Actually, I have just looked at the Recordset DTC, and as you spotted, the SQL is re-set each time during initialisation.

You need to store the options that determine the SQL clause and apply it during EVERY page load.

1. Ensure the Automatic Open is NOT ticked for the recordset (Implementation tab)
2. Add a PageObject DTC
3. Add a thisPage_onenter() script method...
go to Script Outline view - typically with the 'toolbox' view. Open the Server Objects tree. open the thisPage tree, and double-click the onenter event!
4. enter the following in the onload method:

if thisPage.firstEntered then
rsSearch.open 'use the default list
else
setQuery(strID) 'set the appropriate SQL
end if

5. The strID value would come from a list box? If it comes from some other source then you may need to 'store' it between server round-trips. The PageObject can help - add a Property to the page object. You can then store/read the value using the set/get<PropertyName> syntax...
thisPage.setID &quot;Fred&quot;
strID = thisPage.getID()
Where ID is the name of the property.
All this does is to create hidden input fields on the form (if you selected Page scope for the Property - Session scope will generate a Session variable).

6. You may want to open the page in some pre-filled state. You would typically do this using query-string values after the URL of the page. The PageObject simplifies this process via the 'navigate' functions. Write a page initialise function with parameters - reference this in the page object. Reference this page from another page via the Page Object. Now the other page 'sees' this initialise function - so you write in the first page:
secondpage.navigate.myinitialisefunction param1, param2
where secondpage is the name given to the PageObject in the second page!!!

Hope this helps/makes sense.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[MerlinB]

Sorry, I did not see your successful message.
Tracing does not change any error reporting. You still need to add your own response.writes at strategic points to see what is going on etc.
Look in PM.ASP under the _SOM_traceEvent or _SOM_traceWarning methods.
You call these using:
@if (@trace_warning)
thisPage._traceWarning('Err -135: Blaa'
, 'blaa.asp'
, 'myfunction()');
@end
or just
@if (@trace)
response.write '<br>Hello!'
@end

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)