Record set problem (desperate)

Results 1 to 2 of 2

Thread: Record set problem (desperate)

  1. #1
    Dave Ireland Guest

    Default Record set problem (desperate)

    I need to access the RECORDCOUNT property of a recordset object. Therefore I had to change the way I returned data from the DB from the implicit method of declaring a record set to the explicit method.<BR>My SQL now causes errors<BR><BR>Can anyone help me please!!!<BR><BR><BR>Details below ----<BR><BR><BR><BR>&#039--------------------------------- OLD WAY (Implicit) -----------------------------------------------<BR><BR>Set Connection = Server.CreateObject("ADODB.Connection")<BR>Connect ion.Mode = adModeRead<BR>Connection.Open "DSN=HawkDBIssues; UID=; PWD="<BR>Set RSP = Connection.Execute(SQLStmt)<BR><BR><BR><BR><BR>&#0 39--------------------------------- NEW WAY (Explicit) --------------------------------------<BR><BR><BR>openConnStr = "DSN=HawkDBIssues; UID=; PWD="<BR>Set RSP = Server.CreateObject("ADODB.recordSet")<BR>RSP.Curs orType = adOpenStatic<BR> SQLStmt, openConnStr,,,adCmdUnknown<BR>recCount = RSP.recordCount<BR><BR>&#039-------------------------------------------------------------------------------------------------------<BR><BR><BR>The SQL is below. Using the old method it works fine, but with the explicit method, it produces the following error whilst evaluating the CASE statement -<BR><BR><BR>************************************** ************************************************** ***************<BR>Error <BR>3704 ADODB.Recordset The operation requested by the application is not allowed if the object is closed. <BR>********************************************** ************************************************** *******<BR><BR>Which doesn&#039t appear to make any sense.<BR><BR><BR>&#039-------------------------------------- SQL ---------------------------------------------<BR><BR>SQLStmt = "SELECT i.ID_Number, i.Incident_Number, p.Person, i.Date, i.Lev, i.Area, i.Status, i.Type, i.Fixby, i.fixedIn, i.DB, i.Notes, t.Project, r.Release "<BR>SQLStmt = SQLStmt & "FROM Incidents i, Person p, Project t, Release r "<BR>SQLStmt = SQLStmt & "WHERE i.PersonID=p.PersonID "<BR>SQLStmt = SQLStmt & "AND i.ProjectID=t.ProjectID "<BR>SQLStmt = SQLStmt & "AND t.ReleaseID=r.ReleaseID "<BR>SQLStmt = SQLStmt & "AND i.Incident_Number Is Not Null "<BR>SQLStmt = SQLStmt & "AND i.Status NOT IN (&#039Duplicate&#039, &#039Closed&#039, &#039Resolved&#039) "<BR><BR><BR>select case intSortBy &#039------ intSortBy is requested from a form and copies across OK<BR>Case "1" <BR>SQLStmt = SQLStmt & "ORDER BY i.Incident_Number; "<BR>Case "2"<BR>SQLStmt = SQLStmt & "ORDER BY person, i.Incident_Number; "<BR>Case "3"<BR>SQLStmt = SQLStmt & "ORDER BY person, lev, i.Incident_Number ; "<BR>Case "4"<BR>SQLStmt = SQLStmt & "ORDER BY lev, person, i.Incident_Number; "<BR>Case "5"<BR>SQLStmt = SQLStmt & "ORDER BY lev, i.Incident_Number, person; "<BR>Case "6" <BR>SQLStmt = SQLStmt & "ORDER BY fixBy desc, i.Incident_Number; "<BR>Case "7"<BR>SQLStmt = SQLStmt & "ORDER BY fixBy desc, lev, i.Incident_Number; "<BR>Case "8"<BR>SQLStmt = SQLStmt & "ORDER BY fixBy desc, person, i.Incident_Number; "<BR>Case "9"<BR>SQLStmt = SQLStmt & "ORDER BY status, i.Incident_Number ; "<BR>Case "10"<BR>SQLStmt = SQLStmt & "ORDER BY status, lev, i.Incident_Number ; "<BR>End select<BR><BR>&#039---------------------------------------------------------------------------------------------------------------------------------------------

  2. #2
    Neil Popham Guest

    Default RE: Record set problem (desperate)

    without having time to look through all your code, try changing this:<BR><BR>&#062; RSP.CursorType = adOpenStatic<BR>&#062; SQLStmt, openConnStr,,,adCmdUnknown<BR><BR>to this:<BR><BR>&#062; SQLStmt, openConnStr, adOpenStatic, adLockReadOnly, adCmdText<BR><BR>i think it may be because you are trying to set the cursortype BEFORE the recordset has been opened. however, as i have pointed out before, my knowledge of ADO is pants.<BR><BR>its the only weird thing i can see though<BR>

Posting Permissions

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