query then insert into db fields

Hi guys

table 1 has the following colums of interest (uniqueref,style,plu,rootplu)
table 2 has the following colums of interest (uniqueref, description, comments)

each table can have its data referenced by its uniqueref colum due to sharing mapped information. i am after somehow having a text box of which i can type a numeric(rootplu) and it then runs the query and the comments cell i can enter data and then save. the rest is just for reference to the end user.

if possible i dont want it to keep the connection during the person reading whats being shown and then waiting for the user input. (its only because the mdb can be edited from other sources and want to try my best to avoid deadlocks
TG-SteveAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
keeping the connection open and avoiding deadlocks are 2 completely different things
you can have a connection open without ever having a deadlock

a deadlock occurs only with 2 updates and 2 waits

person A
* starts transaction X and updates row 1 and does not commit

person B
* starts transaction Y and updates row 2 and does not commit
* then asks for a lock on row 1 and waits for it to become free

person A
continous transaction X and asks for a lock on row 2 and waits for it to become free

this is a deadlock

tran X: update row 1, update row 2, commit both
tran Y: update row 2, update row 1, commit both

how to solve: ?
tran X, lock row 1 and row 2 with nowait, if locked, update row 1 and row2, commit
tran Y, same principal

if the locking mechanism fails on both rows, the whole transaction fails

0
Geert GOracle dbaCommented:
if you are talking about the time between the user displaying the info, the time it takes to update the data, and do the commit
then do not make a lock when displaying the info

do a check before saving to see if the data has changed


you have probably seen excel (or similar) throw a message
userX has opened the file for editing, your options:
* open a read-only version
* send a warning you want to edit the file to user X
* kill userX's session and open file in edit mode

these are the same choices in database land :)
0
TG-SteveAuthor Commented:
hi geert

note taken. the deadlocking shouldnt happen anyway because the retail system only kicks in on the database in the evening. but wanted to express the idea incase i was rushing into something without asking the question. and yes i have seen the timeouts etc whilst using datasource in excel and access whilst querying a sql server with many people using the same information. i am willing to sit this to one side and run with the rest of the question if possible please.
0
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Geert GOracle dbaCommented:
you mentioned deadlocks ... so i picked in on that to point out

do you have sample data ?
it's difficult to follow like this ...
0
TG-SteveAuthor Commented:
table one

UniqueRef      Style      PLU      RootPlu
217387      test      23880504      238805
217387      test      23880504      238805
217388      test      23880504      238806
217388      test      23880504      238806

table two

uniqueref      Description      Comments
217387      Naomi  Jacket 93      
217388      Kate Knit Cardi 93

so table one has a relation to table two. the comments field has never been used but i want to somehow run a query against the tables by a single rootplu and populate i.e a memo and click save. so it will save the memo data in the comments cell for the relevant product that has been queried.

so if i put 238805 in an edit box i want it to query the whole database and bring up the relevant data for that product and have a memo box of which i can type a description and click save and it updates the cell (comments) within table two

i hope this makes more sense thankyou
0
Ephraim WangoyaCommented:

First run your query and if it has comments, put in memobox, I'm not sure which components you are using but I'll assume  ADO query objects. Note I'm creating queries at runtime, opening and closing the immediately

Here is sample code
unit Unit3;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, StdCtrls;

type
  TForm3 = class(TForm)
    Memo1: TMemo;
    UpdateButton: TButton;
    SearchButton: TButton;
    Edit1: TEdit;
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    procedure SearchButtonClick(Sender: TObject);
    procedure UpdateButtonClick(Sender: TObject);
  private
    FCurrentRef: Integer;
    function GetUniqueRefFromPLU(const ARootPlu: Integer): Integer;
  public
    { Public declarations }
  end;

var
  Form3: TForm3;

implementation

{$R *.dfm}

function TForm3.GetUniqueRefFromPLU(const ARootPlu: Integer): Integer;
var
  Q: TADOQuery;
begin
  Q := TADOQuery.Create(nil);
  try
    Q.Connection := ADOConnection1;
    Q.SQL.Text := Format('SELECT UniqueRef FROM TableOne where RootPlu = %d', [ARootPlu]);
    Q.Active := True;
    if not Q.IsEmpty then
      Result := Q.FieldByName('UniqueRef').AsInteger
    else
      Result := -1;
    Q.Close; //we are not holding to any data
  finally
    FreeAndNil(Q);
  end;
end;

procedure TForm3.SearchButtonClick(Sender: TObject);
var
  Q: TADOQuery;
begin
  Memo1.Lines.Clear;
  FCurrentRef := GetUniqueRefFromPLU(StrToInt(Edit1.Text));
  if FCurrentRef > 0 then
  begin
    Memo1.Enabled := True;

    Q := TADOQuery.Create(nil);
    try
      Q.Connection := ADOConnection1;//enable the memo so you can insert comments
      Q.SQL.Text := Format('Select Comments from TableTwo where UniqueRef = %d', [FCurrentRef]);
      Memo1.Lines.Text := Q.FieldByName('Comments').AsString;
      Q.Close; //again we dont hold on
    finally
      FreeAndNil(Q);
    end;
  end;
end;

procedure TForm3.UpdateButtonClick(Sender: TObject);
var
  Q: TADOQuery;
begin
  if Memo1.Lines.Count > 0 then
  begin
    Q := TADOQuery.Create(nil);
    try
      Q.Connection := ADOConnection1;//enable the memo so you can insert comments
      Q.SQL.Add('Update TableTwo');
      Q.SQL.Add(Format('SET Comments = %s', [QuotedStr(Memo1.Lines.Text)]));
      Q.SQL.Add(Format('WHERE UniqueRef = %d', [FCurrentRef]));
      Q.ExecSQL;
      Memo1.Enabled := False;
    finally
      FreeAndNil(Q);
    end;
  end;
end;

end.

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jimyXCommented:
Here is my two cents:
// Set the DB connection and everything then
//  ADOConnection1.Open;

procedure TForm1.ButtonSaveClick(Sender: TObject);
begin
  AdoTable1.Close;
  AdoTable1.SQL.Text := 'update Table2 set Comments = '+QuotedStr(Memo1.Text)+' where UniqueRef = (select distinct(UniqueRef) from Table1 where RootPlu ='+QuotedStr(Edit1.Text)+')';
  AdoTable1.ExecSQL;
  AdoTable1.Close;
end;

procedure TForm1.ButtonSearchClick(Sender: TObject);
begin
  ABSDatabase1.Open;
  AdoTable1.Close;
  AdoTable1.SQL.Text := 'Select * from Table2 where (select count(UniqueRef) from Table1 where RootPlu = '+QuotedStr(Edit1.Text) +') >= 1 and  UniqueRef = (select distinct(UniqueRef) from Table1 where RootPlu = '+QuotedStr(Edit1.Text) +')';
  AdoTable1.Open;
  Edit2.Text := AdoTable1.fieldbyname('UniqueRef').AsString;
  Edit3.Text := AdoTable1.fieldbyname('Description').AsString;
  Memo2.Text := AdoTable1.fieldbyname('Comments').AsString;
  AdoTable1.Close;
end;

Open in new window

Search-Save.JPG
0
jimyXCommented:
PS: it's an AdoQuery component and not AdoTable.
0
Geert GOracle dbaCommented:
>ewangoya, jimyX,
use parameters ?
a lot better for performance and for tuning

function TForm3.GetUniqueRefFromPLU(const ARootPlu: Integer): Integer;
Q.SQL.Text := 'SELECT UniqueRef FROM TableOne where RootPlu = :ROOTPLU';
Q.Parameters.ParseSQL(Q.SQL.Text, True);
Q.Parameters.ParamValues['ROOTPLU'] := ARootPlu;

Open in new window


procedure TForm3.SearchButtonClick(Sender: TObject);
Q.SQL.Text := 'Select Comments from TableTwo where UniqueRef = :UNIQUEREF'
Q.Parameters.ParseSQL(Q.SQL.Text, True);
Q.Parameters.ParamValues['UNIQUEREF'] := FCurrentRef;
Q.Active := True;
Memo1.Lines.Text := Q.FieldByName('Comments').AsString;
Q.Close; //again we dont hold on

Open in new window


procedure TForm3.UpdateButtonClick(Sender: TObject);
Q.SQL.Text('Update TableTwo SET Comments = :COMMENTS WHERE UniqueRef = :UNIQUEREF';
Q.Parameters.ParseSQL(Q.SQL.Text);
Q.Parameters.ParamValues['UNIQUEREF'] := FCurrentRef;
Q.Parameters.ParamValues['COMMENTS'] := Memo1.Lines.Text;
Q.ExecSQL;

Open in new window

0
TG-SteveAuthor Commented:
Hi guys

tested between the options above and the inserting of the data into database is great. but for example in this source for the search button

      Q.Connection := ADOConnection1;//enable the memo so you can insert comments
      Q.SQL.Text := Format('Select Comments from TableTwo where UniqueRef = %d', [FCurrentRef]);
      Memo1.Lines.Text := Q.FieldByName('Comments').AsString;
      Q.Close; //again we dont hold on

i am getting an error stating "Field Comments not found" although it is there and updates fine through the other function
0
Ephraim WangoyaCommented:
Oops, my bad
You need to set actvie to true

      Q.Connection := ADOConnection1;//enable the memo so you can insert comments
      Q.SQL.Text := Format('Select Comments from TableTwo where UniqueRef = %d', [FCurrentRef]);
      Q.Active := True;
      Memo1.Lines.Text := Q.FieldByName('Comments').AsString;
      Q.Close; //a
0
TG-SteveAuthor Commented:
Thank you guys . combination of the above and utilizing geerts advice worked perfect

a+
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.