ASP/Database problem with RecordSet Open and Move


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: ASP/Database problem with RecordSet Open and Move

  1. #1
    Join Date
    Mar 2005
    Posts
    3

    ASP/Database problem with RecordSet Open and Move

    Hi, i have two serious problems in the ASP/VBScript im trying to get correct.

    Im using a MySQL database and IIS on Windows XP pro SP2.

    Im trying to create a webinterface for accessing our log database. The ASP page gets an SQL statement using GET:

    Set query = Request.QueryString("query")

    If len(query) = 0 or strComp(query,"")= 0 Then
    Response.End
    End If

    ...

    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER= ...

    conn.Open
    Response.Write("This is the query:</br>" & query & "</br>")

    Set Rs = conn.Execute(query)

    ...

    Now all this works fine. Our logs can be very large and so queries could return LOTS of data. Therefore i tried to get only a limited number of results on each page, by also giving and interval as GET parms, and then use RecordSet.Move to move to the first row of interest. This seemed to work fine enough, but my query would seemingly randomly result in a error in Execute. Sometimes just hitting refresh would fix it, sometimes not(mind you, there are no concurrency issues here, im using a test database with only a few hundred rows which doesnt change) . The only fix for this is apparently to RESTART the IIS service and remove the call to Move. If i just remove the Move call, then it wont work until after i restart the IIS service.
    Im wondering if i am corrupting some shared server connection object or something like that. Is it possible that im somehow forgetting to close some connection objects or something like that?
    I would really appreciate any help anyone can provide on this as i find it pretty mysterious.

    I thought maybe my problems where related to the properties of my record set so i tried using variations on the following:

    set Rs = Server.CreateObject("ADODB.recordset")
    Rs.Open queryconn
    Rs.Open query, conn

    However, no matter the parameters to Open i ALWAYS get:
    ADODB.Recordset (0x800A0BB9)
    Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
    /default.asp, line 63

    I get that even by just putting the query alone. However, replacing the query var with "SELECT * from logs;" or similar does work so i guess its a string delimiter problem, but when i try to enclose the query with " or ' i get trouble:

    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"SELECT * from logs;"' at line 1

    However, my queries works fine with conn.Execute, unless i screw them up using Move.

    I know this is a long post, but i would really appreciate any help on this...

  2. #2
    Join Date
    Mar 2005
    Posts
    3
    I tried replacing the move call with a for loop making the required number of MoveNexts, this works fine to my surprise. However the resultsets im navigating could be extremely large and im worried that using these MoveNexts will cause all the results to be sent to the client, or at least cause a large number of cursor movement network traffic since im using a server side cursor(i cant use a clientside cursor, as i understand it, that would imply sending all the results to the webserver).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center