?
Solved

Locating record in query then edit and post.

Posted on 2011-04-26
11
Medium Priority
?
319 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

0
Comment
Question by:DigitalNam
  • 5
  • 4
  • 2
11 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 35466405
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.
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 35466705
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.
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35467037
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35467333

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

0
 
LVL 1

Author Comment

by:DigitalNam
ID: 35467409
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"
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35467462

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?
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 35467513
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.
0
 
LVL 24

Accepted Solution

by:
jimyX earned 2000 total points
ID: 35467550
Here is my idea:
You select a record in the DB grid and then click on the Edit Button:

 Edit Button
Once the Edit Button is clicked then the fields of the selected record will be filled in the Edit Form:

 Edit Form
Then by using a separate query you can write SQL statement in the save button to update the DB based on the new data in the EditBoxes.
SQL.Text := 'update input_vat set Col1=:Col1, Col2=:Col2, Col3=:Col3, Col4=:Col4, Col5=:Col5 where ID=:CLID';
ParamByName('Col1').Value := Edit1.Text;
ParamByName('Col2').Value := Edit2.Text;
ParamByName('Col3').Value := Edit3.Text;
ParamByName('Col4').Value := Edit4.Text;
ParamByName('Col5').Value := Edit5.Text;
ParamByName('CLID').Value := DM.iv_query.FieldByName('ID').AsString;
ExecSQL;

Open in new window

0
 
LVL 1

Author Comment

by:DigitalNam
ID: 35467699
JimyX: I see where you are going with this one but how do I get the selected record information in the new form?
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35467738
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

0
 
LVL 1

Author Closing Comment

by:DigitalNam
ID: 35467836
:-) thanks......
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question