We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Locating record in query then edit and post.

DigitalNam
DigitalNam asked
on
Medium Priority
329 Views
Last Modified: 2012-05-11
I have the following query.

It shows the records in a dbgrid so it shows the results fo the query. If my user want to double click on a record in the dbgrid and edit the specific record how do I locate that record so I can edit and post it again?
with DM.iv_query do
  begin
    Active := False;
    SQL.Clear;

    SQL.Text := ('SELECT SUM(VAT_Amount) AS tmpTotal FROM input_vat WHERE Client_Name = :Client');
    ParamByName('Client').Value := tmpClientName;
    Active := True;
    if not IsEmpty then
      Label4.Caption := 'VAT Total : ' + FloatToStr(FieldByName('tmpTotal').AsFloat)
     else
      Label4.Caption := 'No Vat for the client: '+ tmpClientName;
    Active := False;

    SQL.Text := ('SELECT * FROM input_vat WHERE Client_Name = :Client ORDER BY Inv_Date');
    ParamByName('Client').Value := tmpClientName;
    Active := True;
  end;

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011

Commented:
Do you have a unique ID for every client?

If Yes then you can create additional form for editing the info of any client by using a query that locates the client by the ID. Once the info of that particular client in the new form is updated you can update the DB by altering the columns of the client that has that ID.

Author

Commented:
The grid already shows all the transactions for the client I select, the problem is when I edit the record in the grid it gives me and error because it is a result set.
CERTIFIED EXPERT
Top Expert 2011

Commented:
You will be using an external Form with extra query statement to update the table in the Database.
When you use Query the DBGrid will not allow changing the records.
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

The record selected on the grid corresponds to the actual record in the dataset. You don't need to find that record.
tmpClientName := GridDataset.FieldByName('ClientName').AsString;  //dataset connected to the grid

  with DM.iv_query do
  begin
    Active := False;
    SQL.Clear;

    SQL.Text := ('SELECT SUM(VAT_Amount) AS tmpTotal FROM input_vat WHERE Client_Name = :Client');
    ParamByName('Client').Value := tmpClientName;
    Active := True;
    if not IsEmpty then
      Label4.Caption := 'VAT Total : ' + FloatToStr(FieldByName('tmpTotal').AsFloat)
     else
      Label4.Caption := 'No Vat for the client: '+ tmpClientName;
    Active := False;

    SQL.Text := ('SELECT * FROM input_vat WHERE Client_Name = :Client ORDER BY Inv_Date');
    ParamByName('Client').Value := tmpClientName;
    Active := True;
  end;

Open in new window

Author

Commented:
If I set the dbgrid options to editing = true and edit one of the fields in the row it gives me "Update failed . Found 11 records"
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

What kind of query are you using to get the data for the grid? Does it have a join or some kind of aggregate function in it?

Author

Commented:
I am using no query, I am using the inplace editor in the grid. The query I use to get the data is the source code I inlcuded in my question to populate the grid.
CERTIFIED EXPERT
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
JimyX: I see where you are going with this one but how do I get the selected record information in the new form?
CERTIFIED EXPERT
Top Expert 2011

Commented:
In the Edit Button:
  Form_Edit.Edit1.Text := DM.iv_query.FieldByName('Col1').AsString;
  Form_Edit.Edit2.Text := DM.iv_query.FieldByName('Col2').AsString;
  Form_Edit.Edit3.Text := DM.iv_query.FieldByName('Col3').AsString;
  Form_Edit.Edit4.Text := DM.iv_query.FieldByName('Col4').AsString;
  Form_Edit.Edit5.Text := DM.iv_query.FieldByName('Col5').AsString;

Open in new window

Author

Commented:
:-) thanks......
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.