Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Declare cursor type in DataConnection SQL Server

    I am working on implementing a paging recordset to display search results from a SQL Server db. How do I declare the cursor type adOpenkeyset, asLockReadOnly, adCmdText? Referencing adovbs.inc doesn't seem to do the trick, and I am a little confused about how to fetch the records -- use myRS.Open or DataConnection.Execute(mySQL) etc.?

    TIA
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Declare cursor type in DataConnection SQL Serv

    There are some limitations on your ability to control your cursor type, but you certainly can request what you want. I typically use this tag to make the constants available:

    < ! - - METADATA TYPE="typelib" FILE="crogram filescommon filessystemadomsado15.dll" -->

    (Opening comment tag messed up for Lounge display.)

    Then I create a command object and a recordset object and...

    objRS.Open objCommand, , adOpenStatic, adLockReadOnly

    Actually, the above is Classic ASP/ADO, so you might be doing something different with your DataConnection.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declare cursor type in DataConnection SQL Serv

    actually I am using the adovbs.inc file to handle the "human readable" cursor types. the problem I was having previously was I wasn't 'backing up' far enough. I needed to add the line

    Set myRS = Server.CreateObject("ADODB.Recordset") <-- declared myRS to be a recordset

    then I can set my cursor types, etc. using the named constants:
    myRS.Open mySQL, DataConnection, adOpenStatic, adLockReadOnly, adCmdText

    What's confusing is I have used this to instantiate a Recordset using Set myRS = DataConnection.Execute(mySQL) and that works. I suppose the Execute() method expects cursor declarations from a SQL Server Stored proc, whereas I was trying to set cursors on the client side....

    Ah well live and learn!

    thanks for the response, tho

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Declare cursor type in DataConnection SQL Serv

    > actually I am using the adovbs.inc file to handle the "human readable" cursor types

    The METADATA call to the type library does the same thing. According to my ADO reference, the METADATA method has the advantage that "you don't have to worry that the location (or even the contents) of the include file might change." That wouldn't have been enough reason to change the way anyone works; I think I did it because of Tip 18 on this MSDN page that it improves performance.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declare cursor type in DataConnection SQL Serv

    some people think you shouldn't even bother with human readable cursor constants....

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Declare cursor type in DataConnection SQL Serv

    Those people value job security over code maintainability. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declare cursor type in DataConnection SQL Serv

    OK, yet another weirdness that *may* have to do with my client-side cursor declarations....

    I can open the recordset and display the record count and page count using myRS.RecordCount and myRS.PageCount respectively. When I try to assigne the AbsolutePage property I keep getting EOF/BOF errors. In an attempt to ferret this out I hard-coded the property myRS.AbsolutePage = 2 and then added the line Response.Write "Page requested: " & myRS.AbsolutePage.

    You know what happens? I keep getting a -1 for the AbsolutePage property!!!

    Strange. Weird. Eerie.

    here's my recordset declaration:
    myRS.Open mySQL, DataConnection, adOpenKeyset, adLockOptimistic, adCmdText

    The recordset is pulled from SQL Server 2000 using SQL in ASP on a table (no stored procs).

    ???
    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Declare cursor type in DataConnection SQL Serv

    See if it is supported with something like:

    If myRS.Supports(adApproxPosition) Then
    Response.Write "

    AbsolutePage should work.</p>" & vbCrLf
    Else
    Response.Write "

    AbsolutePage is not supported by this Recordset and/or Provider.</p>" & vbCrLf
    End if

    If it is supported, your search continues. If not, well, time to try something different.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declare cursor type in DataConnection SQL Serv

    i just noticed that AbsolutePage *always* returns -1 so I am noticing something that's supposed to be there. anyway, when I change the hard-coded value of AbsolutePage I do, in fact, get a different set of data. So it seems to be working. Now I am trying to figure out how to set up the old Next Record and Previous Record switches. All my attempts end up kicking out a EOF/BOF is true error; if I even LOOK at the cursor settings wrong the SQL String fails....

    Even some canned routines from other tutorials don't give me the expected results... I am starting to wonder if there's something I need to do on the SQL Server to get this thing to work as expected.

    A real pain! here's a definate instance where ASP.NET would be great...

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declare cursor type in DataConnection SQL Serv

    I haven't used classic ASP for quite some time, but I remember some constant that set the recordset to UseClient (aka the web server) rather than UseServer (aka the SQL box). When using client, the paging worked but not when using server. See if that helps.

    Also, I believe it only works properly if using a text command or opening a table directly. It does not work when using a stored procedure.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declare cursor type in DataConnection SQL Serv

    Well, according to this tutorial and that, adOpenKeyset or adOpenDynamic should work. since my last whine (with cheese!) I am having much success... i use this to connect for paging:

    myRS.Open mySQL, DataConnection, adOpenStatic, adLockReadOnly

    and my grotty code is spitting out stufff as I basically want it. I even got the Next Record link to work! turns out I was being retarded.

    Next in line: create a 'previous record' link AND, of course, an on-the-fly drop-down so users can select any page desired. Then, wrap all this in my existing code to produce a lovely, banded and sortable table... THEN, give users the option to change the number of results per page....

    Once all that's done, I need to re-visit strategies for allowing people to output the search results to file. Ah the life of the developer... hair lossage and no one to tell the war stories too. I should get a cat...

    <img src=/S/beep.gif border=0 alt=beep width=15 height=15>

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declare cursor type in DataConnection SQL Serv

    Just remember that when using adUseClient as the cursor location, ALL of the data in the recordset is transferred from the data source to the web server - even if you're only displaying a small number of records on a single page at a time. So if you have a table with 1.5 million records and only display them 5 per page, the entire table will be transferred from SQL Server to the web server to be sorted and paged before they can be displayed. This can be somewhat inefficient at times. Of course, ASP.NET works similarly in that regard (at least when using out of the box tools).

    The best way to get a good performance boost will be to use indexed fields for sorting and forward-only recordsets whenever possible. Also, you can use Stored Procedures to pull ONLY the desired number of records at a single time. The downside is that this approach takes a bit more planning and coding to pull off...

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declare cursor type in DataConnection SQL Serv

    adUseClient sounds TERRIBLE for this project. I am using adOpenStatic, adLockReadOnly, which is fine. adOpenDynamic and adOpenForwardOnly throw errors, as you might expect.

    as for embedding the RS logic in SQL Server, I am not yet able to even pass a variable to a Stored Proc from ASP (gasp!) and, besides, the search results page has a LOT of code for selecting various SQL Strings depending on the user input. The search results page contains code to support display of a recordset depending on a variety of possible selections in the find page, supports sorting on each column and (now) supports paging. Sorting on the columns also involves a fetch from the db. Also, I will add a drop-down to support different page sizes at some point. It's not just your plain-vanilla datagrid.

    But the other thing that I wonder might drastically improve performance (or not work at all <img src=/S/smile.gif border=0 alt=smile width=15 height=15> ) is to use GetRows() instead of a recordset...

    I *want* to learn how to leverage SQL Server stored procs from ASP code and have a few books now (finally!) but I think, given the complexity of the search results page, this is not the project to learn that on... (I originally intended to cut my teeth on that sort of thing with this project, but the dataset is getting too big (45K+ records) and I had to implement paging to make it at all useful for the users...)

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declare cursor type in DataConnection SQL Serv

    I realize that you may not employ this yet, but in order to call stored procedures (with input parameters) you should use ADO's command object. You have to create the parameters from the Parameters object, then add them to the Command object's parameters collection. Then use the Execute method of the Command object to return a recordset. It's not really as hard as it may seem.

    If your dataset has 45K+ records, then that means that SQL is transferring all rows to the webserver where sorting and paging is taking place - for EACH request!! That's a lot of throughput. Be sure to do load testing to see how things will work with multiple users. I would expect to see pretty serious performance degredation with more than a handful of concurrent users (depending on your hardware and connection between SQL and web box).

    If you take the time to search a bit you'll find a wealth of solutions that implement server-side paging, where SQL only returns the records for a given page. One of my favorite sites for this kind of thing is SQL Team. You'll also find more of the web-side of the process on 4 guys from rolla.

    Good luck!

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declare cursor type in DataConnection SQL Serv

    I know the very basics of using ADO to invoke a SP, and am still learning how to tune SQL Server to get good performance on even moderately large tables. Even if I index the 45K+ table, it takes SQL Server about 45 secs to output the results of a select * the first time around! I have a hard time thinking that 45K+ rows is already a challenge for SQL Server! Obviously user error plays a part... at least with the table indexed, I avoid the dreaded table scan...

    As for the paging causing a reload of all the data, I don't know. I do know that outputting a 45000 HTML table takes a LONG TIME and calling all the data on a 20-100 row table is a lot faster. This may be an artifact of ASP's slow sting-handling rather than a lighter load on the db. obviously, if the client-side cursor doesn't actual retrieve a batch but grabs all the data everytime then things are still not optimal and, yes, I would then need to go to server-side paging instead.

    Well, well.... the saving grace on this project is, no one's using it <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20> , so I can work on solutions to these concerns without howls from customers...

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •