Unable to Edit ADO query

[lespaul]

I'm trying ADO components for the first time and I'm having problems editing any of the data!

The user enters search criteria:

procedure TfrmJurorSearchInfo.bbtnJSISearchClick(Sender: TObject);
var
i : integer;
strJurorNumber :string;
begin
  for i := 0 to frmJurorSearchInfo.ComponentCount - 1
  do
  begin
    If Components[i] is TEdit Then
      with Components[i] as TEdit do
      begin
        If Text <> '' then
          strJurorNumber := Text;
      end;
  end;
  If not GetJurorInfo(strJurorNumber) then
    MessageDlg('There is no matching juror!', mtInformation, [mbOK], 0);
  ClearJSIForm();
end;


function GetJurorInfo(AJurorNumber : string): boolean;
begin
//check if juror exists
  With dmJMS.qryJurorExists do
  begin
    SQL.Clear;
    SQL.Add('SELECT JURNUM FROM JMPMAIN WHERE ');
    If pos('P', AJurorNumber) > 0 then
      SQL.Add('PANLELID = ' + QuotedStr(AJurorNumber))
    else if length(AJurorNumber) = 9 then
      SQL.Add('SSN = ' + QuotedStr(AJurorNumber))
    else
      SQL.Add('JURNUM = ' + AJurorNumber);
    Active := True;
    //if no record returned, return false and exit
    If isempty then
    begin
      Result := False;
      Active := False;
    end
    //if record returned, create JurorInformation form
    else begin
      With TfrmJurorInformation.Create(frmMain) do
      begin
        qryJurorInformation.SQL.Clear;
        qryJurorInformation.SQL.Add('SELECT JMPMAIN.*, ' +
        '(TRIM(FIRSTNAME) || '' '' || TRIM(LASTNAME)) AS FULLNAME, CASE PANELID ' +
        'WHEN '' '' THEN ''NO PANEL ASSIGNED'' ELSE PANELID END AS PANELSTAT ' +
        'FROM JMPMAIN WHERE JURNUM = ' +
        dmJMS.qryJurorExists.FieldByName('JURNUM').AsString);
        qryJurorInformation.Active := True;
        Caption := 'Juror Information ' + qryJurorInformation.FieldByName('FULLNAME').AsString;
        
        Show;
      end;
      Result := True;
    end;
  end;
end;

In the onShow event of frmJurorInformation, I run subsequent queries to get the rest of the information I need to display. None of the queries are ordered or have any joins:

procedure TfrmJurorInformation.FormShow(Sender: TObject);
var
strJurorNumber, strTermDate : string;
begin
  strJurorNumber := qryJurorInformation.FieldByName('JURNUM').AsString;
  strTermDate := qryJurorInformation.FieldByName('TERMDATE').AsString;
  if strTermDate = qryJurorInformation.FieldByName('ORGPOST').AsString then
  begin
    lblOrgPost.Visible := false;
    dbtOrgPost.Visible := False;
  end;
  //if Public Employee, gather public employee information
  if qryJurorInformation.FieldByName('PUBEMP').AsString = 'T' then
  begin
    With Self.qryPubEmployees do
    begin
      SQL.Clear;
      SQL.Add('SELECT * FROM JMPPEMPLE WHERE JURNUM = ' + strJurorNumber);
      Active := True;
      Self.dtpPEStartTime.Time := IntToTime(FieldByName('REGTIMEIN').AsInteger);
      Self.dtpPEEndTime.Time := IntToTime(FieldByName('REGTIMEOUT').AsInteger);
    end;
  end
  else begin
    Self.Height := 550;
    panPubEmp.Visible := False;
  end;
  With Self.qryJurorHours do
  begin
    SQL.Clear;
    SQL.Add('SELECT * FROM JMPHOURS WHERE JURNUM = ' + strJurorNumber);
    Active := True;
    if not isempty then
      //Fill hours grid
    else
      sgHours.Visible := False;
  end;
  with Self.qryJurorPayment do
  begin
    //get payment information
  end;
   //testing edit & posting
  qryJurorInformation.Edit;
end;

When I try to post the changes in the OnClose event:
qryJurorInformation.Post

I get the following error:
Insufficient base table information for updating or refreshing.

Anybody know what I'm doing wrong or can point me in the right direction?

Thanks,
leslie

 

[VintageWine]

Leslie,

Before posting would you mind stripping your problem down to the bare minimum needed to replicate the fault.

After all, in doing so you may well solve the problem

 

[Punchinello]

Your problem could be that your qryJurorInformation in the GetJurorInfo routine is resulting in columns that may not exist as fields in the underlying data table (fullname and panelstat). To test this theory, change the SQL to simply query for JPMAIN.* and run the program. If this revision works, you can reproduce these values from the resulting record set rather than within the SQL.

 

[lespaul]

I'll try that this morning! Thanks!

Leslie

 

[lespaul]

That doesn't seem to be helping any.

So, I created a brand new project that has a form with an ADO connection, an ADO query and a DBEdit box.

The Query says:

SELECT * FROM JMPMAIN WHERE JURNUM = 8909

When I run the application, the form appears with the person's last name in the DBEdit box. In the OnClose event I have

QUERY.POST

When I change the person's last name and try to close the form I get the same error!

Any other suggestions?

Leslie

 

[Punchinello]

Is it possible that the Post command triggers an error if the data is not actually modified? I'm working with ADO against a SQLServer database for a client in Albuquerque but that is all read-only - no posting.

 

[lespaul]

I get the error whether I've modified the data or not. I'm not sure what the problem is. I've been out sick a few days this week so, I haven't been able to look into this.

I'll post back with any additional information.

Leslie

ps - are you IN albuquerque or do you just have a client here?