Recordset.MovePrevious - "Operation not allowed in this Context" error 1

[CritterLover]

On a separate screen the user inputs search criteria
and the records are displayed in a listview. Then the
person chooses a record to view on this screen. The
SQL and the itemindex are passed to this screen.
This screen creates a NEW recordset with the same SQL
as the prior search criteria and moves to the desired
record in that recordset and displays the data. I want
to be able to move (forward and backward) through that
recordset using the buttons. We are using Delphi 7 and
MSSQL Server 2005. I have confirmed that it is retrieving
the desired records and I can move forward, but not backwards.
I get
"Operation is not allowed in this context."

There were references to a driver, but this link is no
longer there.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprocursortype.asp

Here's the code:

var
  frmTapeDetail: TfrmTapeDetail;
  ret_rs: _Recordset;  // This is public declarations
  vsql: string;        // sql statement from prior screen 
  vrecnum: integer;    // ItemIndex to go to desired record

procedure TfrmTapeDetail.RetRecs;
var i: integer;
begin
  i := 0;
  ret_rs := CoRecordset.Create;

ret_rs.CursorLocation := adUseClient;  // I tried these based on internet searches
ret_rs.CursorType := adOpenDynamic;

  ssConn.ExecuteSQLStmt(vsql, ret_rs);
  If (ret_rs.BOF) and (ret_rs.EOF) Then
  begin
    ShowMessage('Records not found');
    exit;
  end;
  ret_rs.MoveFirst;
  while i < vrecnum do
  begin
     ret_rs.moveNext;  // This loops to the desired record
     inc(i);                
  end;
  setvalues;
  //ret_rs.CursorType := adOpenDynamic;   // I tried it here, too, to no avail
end;

procedure TfrmTapeDetail.setValues;
begin
   vSysID := VarToStr(ret_rs.Fields['SysID'].Value);
   lblStyleData.caption := VarToStr(ret_rs.Fields['tap_cstyle'].Value);
   txtCourtType.Text := VarToStr(ret_rs.Fields['tap_crttype'].Value);
   txtCourtNumber.Text := VarToStr(ret_rs.Fields['tap_crtloc'].Value);
   txtCaseCategory.Text := VarToStr(ret_rs.Fields['tap_casecat'].Value);
   txtCaseNumber.Text := VarToStr(ret_rs.Fields['tap_casenbr'].Value);
// etc, etc etc - Sets values on screen for each record - one at a time - This is all fine going FORWARD
end;

procedure TfrmTapeDetail.btnFirstRecClick(Sender: TObject);
begin
  if not ret_rs.BOF then
  begin
     ret_rs.MoveFirst;
     if not ret_rs.BOF then
        setValues;
  end;
end;

procedure TfrmTapeDetail.btnPrevRecClick(Sender: TObject);
begin
  if not ret_rs.BOF then
  begin
     ret_rs.MovePrevious;
     if not ret_rs.BOF then
        setValues;
  end;
end;

procedure TfrmTapeDetail.btnNextRecClick(Sender: TObject);
begin
   if not ret_rs.EOF then
   begin
     ret_rs.MoveNext;
     if not ret_rs.EOF then
        setValues;
   end;
end;

procedure TfrmTapeDetail.btnLastRecClick(Sender: TObject);
begin
  if not ret_rs.EOF then
  begin
     ret_rs.MoveLast;
     if not ret_rs.EOF then
        setValues;
  end;
end;

I know I'm missing something and just haven't been able to figure it out. Thanks in advance.

 

[DjangMan]

Perhaps open the query through the recordset instead of through the connection. The connection might be generating a recordset that you can't navigate backwards through.

 

[CritterLover]

Hmmm... I'm not even sure how to do that. I used to use BDE and this is how I learned to do the ADO. This is all I know.... To me it looks like it IS using the recordset since it is creating a recordset and using the recordset functionality to navigate.

Here's how the connection is created and executed, if that helps any.

type
   TDataConnObj = class(TObject)
   private
   public
      SS_Conn: _Connection;
      procedure ExecuteSQLStmt(sql_string: string; var rslt_rs: _Recordset);
      Constructor Create;
      Destructor Destroy;
end;

var ss_Conn: TDataConnObj;
   ssConn: TDataConnObj;
  etc, etc

Constructor TDataConnObj.Create;
begin
   If CheckKeys(ss_con, srver, dbuser, pwd) = True Then
   begin
      ss_con := ss_con + srver;
      try
         ss_Conn := CoConnection.Create;
         ss_Conn.Open(ss_con, dbuser, pwd, adConnectUnspecified);
      except on e:Exception do
         begin
            ShowMessage('Error connecting to SSData database.' + Chr(13) + e.Message);
            Exit;
         end;
      end;
  end;
end;

procedure TDataConnObj.ExecuteSQLStmt(sql_string: string; var rslt_rs: _Recordset);
begin
   If not Assigned(rslt_rs) Then
      rslt_rs := CoRecordset.Create;
   try
      rslt_rs := ss_Conn.Execute(sql_string, EmptyParam, 0);
   except
      ShowMessage('Error executing statement.');
   end;
end;

Can you tell me how I'd do it through the recordset or where I could find something on that (or an example)? And thank you for the quick response!

 

[DjangMan]

What is the declaration of _Connection?

 

[CritterLover]

It's using the ADODB_TLB unit which was created from Project > Import Type Library of type Microsoft ActiveX 2.7. Here's an excerpt (hopefully, what you were asking about).

// Forward declaration of types defined in TypeLibrary 
 _Connection = interface;
 _Recordset = interface;

// Declaration of CoClasses defined in Type Library                       
// (NOTE: Here we map each CoClass to its Default Interface)              
// ******************************************************//
  Connection = _Connection;
  Recordset = _Recordset;

I hope this helps.

Were you talking about using one of the ADOQuery objects from the ADO tab? I'd be breaking new ground, but if that's how most people use ADO maybe it's time I learned it. I guess prior programmers had problems with it and so that's why we don't use those. Or is there something else you had in mind?

 

[DjangMan]

Okay - check to see if you have the ADODB unit. That will give you much more direct Access to the different objects.

However - you should be able to do something like this:

rslt_rs.commandtext := 'SELECT ... ';
rslt_rs.Connection := ss_Conn;
rslt_rs.Open;

Working with the interfaces feels awkward. If you can use the ADODB unit you'll have a much easier time working with the database queries.

For example - I have a TADOConnection object that I can connect to a database using the following code:

    ADOJobConn.Close;
    ADOJobConn.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+JobWorkspace+aJobNo+'.mdb'+';Persist Security Info=False';
    ADOJobConn.Open;

Then I can create a TADODataSet or a TADOQuery to run queries against the database connection.

You'll also be able to find far more examples on the web - including on Tek-tips using the dbGo/ADO components.

 

[CritterLover]

Thanks! That gives me a new angle on it! I'll look into that! Much appreciated!