[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Update a table after delete

Posted on 2008-01-27
19
Medium Priority
?
254 Views
Last Modified: 2013-11-23
I am trying to update a certain table after I delete a record in another table.
Both tables have a field "MED_ID" in common.
So when I delete a selected record in table2 (the table I do the deleting from)
I would like to update Table1 so that a field in this table ,called "Available" changes
its value from No to Yes.
I tried afterdelete of the table2:

Var
CurrentMed_ID:string;
begin
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Text:= 'update Table1 set Available =''Yes'' where Med_ID ='+QuotedStr(CurrentMed_ID);
ADOQuery1.ExecSQL;
end;

database is access 2003 and MED_ID is the text field.
but it dont seem to work.
Any way to do it?
0
Comment
Question by:Bosanac
  • 6
  • 4
  • 4
  • +2
19 Comments
 

Author Comment

by:Bosanac
ID: 20755924
CurrentMed_ID := wwDBGrid1.Datasource.Dataset.FieldbyName('Med_ID').value;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Text:= 'update TABLE1 set AVAILABLE =''YES'' where Med_ID ='+QuotedStrCurrentMed_ID);
ADOQuery1.ExecSQL;

dont seem to work either..I get the error 'cant convert variant of type null into string'
but both fields are text !???
0
 
LVL 26

Expert Comment

by:ee_rlee
ID: 20756440
what is the value of QuotedStrCurrentMed_ID?
0
 

Author Comment

by:Bosanac
ID: 20757036
QuotedStrCurrentMed_ID ...text field...containing numbers and letters...
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
LVL 10

Expert Comment

by:dinilud
ID: 20757039
>>can't convert variant of type null into string

  then wwDBGrid1.Datasource.Dataset.FieldbyName('Med_ID').value is null.

  -----------------------------------------

CurrentMed_ID := wwDBGrid1.Datasource.Dataset.FieldbyName('Med_ID').asstring;
showmessage(CurrentMed_ID);
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Text:= 'update TABLE1 set AVAILABLE =''YES'' where Med_ID ='+QuotedStrCurrentMed_ID);
ADOQuery1.ExecSQL;


0
 
LVL 26

Expert Comment

by:ee_rlee
ID: 20757066
try this
ADOQuery1.SQL.Text:= 'update Table1 set Available = ' + QuotedStr('Yes') + ' where Med_ID ='+QuotedStr(CurrentMed_ID);

Open in new window

0
 

Author Comment

by:Bosanac
ID: 20757249
do not work either...
0
 
LVL 26

Expert Comment

by:ee_rlee
ID: 20757315
can you elaborate on 'do not work'? does it produce an error?
0
 
LVL 10

Expert Comment

by:dinilud
ID: 20757316
What was your CurrentMed_ID value?
0
 
LVL 6

Accepted Solution

by:
bokist earned 400 total points
ID: 20757337
try with parameters

ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Text:= 'update TABLE1 set AVAILABLE = :p1 where Med_ID = :p2');
ADOQuery1.Parameters.Parambyname('p1').value := 'Yes';
ADOQuery1.Parameters.Parambyname('p2').value := CurrentMed_ID;
ADOQuery1.ExecSQL;

0
 
LVL 21

Expert Comment

by:ziolko
ID: 20757400
>>QuotedStrCurrentMed_ID ...text field...containing numbers and letters...

what type is >>Med_ID<< field in your database?

if Med_ID is string type then try this:

var currentmedid: string;

currentmedid := ... assign your value here


if Length(currentmedid) = 0 then
  ADOQuery1.SQL.Text := Format('update TABLE1 set AVAILABLE = %s where Med_ID is null', [QuotedStr('YES')])
else
  ADOQuery1.SQL.Text := Format('update TABLE1 set AVAILABLE = %s where Med_ID = %s', [QuotedStr('YES'), QuotedStr(currentmedid)]);

ziolko.
0
 
LVL 21

Expert Comment

by:ziolko
ID: 20757412
also you can try this

uses Clipbrd;

and afetr you set SQL.Text
use:

Clipboard.AsText := ADOQuery1.SQL.Text;

after execution simply Ctrl+V to your db editor and run it from there to get access generic error.

but by the name >>Med_ID<< i would guess that's numeric field not string, in this case you need StrToInt() instead of QuotedStr()

ziolko.
0
 

Author Comment

by:Bosanac
ID: 20757547
Why this does not work I do not know:

procedure TForm3.ADOTable3AfterDelete(DataSet: TDataSet);
var currentmed_id: string;
begin
CurrentMed_ID := wwDBGrid1.Datasource.Dataset.FieldbyName('Med_ID').asstring;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Text:= 'update TABLE1 set AVAILABLE= :p1 where Med_ID = :p2';
ADOQuery1.Parameters.Parambyname('p1').value := 'YES';
ADOQuery1.Parameters.Parambyname('p2').value := CurrentMed_ID;
ADOQuery1.ExecSQL;
 end;
any ideas?

Med_ID is text field in the DB (in both tables)
0
 
LVL 6

Expert Comment

by:bokist
ID: 20757572
check length and value of the fields.
also you can check sql command :
CurrentMed_ID := wwDBGrid1.Datasource.Dataset.FieldbyName('Med_ID').asstring;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Text:= 'update TABLE1 set AVAILABLE= :p1 where Med_ID = :p2';
ADOQuery1.Parameters.Parambyname('p1').value := 'YES';
ADOQuery1.Parameters.Parambyname('p2').value := CurrentMed_ID;
          showmessage(ADOQuery1.SQL.Text);
ADOQuery1.ExecSQL;

0
 
LVL 6

Expert Comment

by:bokist
ID: 20757646
Another idea: does table contains records meet your criteria??
CurrentMed_ID := wwDBGrid1.Datasource.Dataset.FieldbyName('Med_ID').asstring;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Text:= 'select * from TABLE1 where Med_ID = :p2';
ADOQuery1.Parameters.Parambyname('p2').value := CurrentMed_ID;
ADOQuery1.SQL.Open;
if ADOQuery1.EOF   --> no records
0
 
LVL 21

Expert Comment

by:ziolko
ID: 20757682
copy you SQL statement to clipboard and try to run it from access, if you have errors post here error messages

ziolko.
0
 

Author Comment

by:Bosanac
ID: 20757786
MED_ID contains numbers and letters (example: A101)
They are both text fields in their respective tables with same lenght.

I do a perfect update with (after insert) :
ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Text:=' update Table1 set Available =''No'' where MED_ID = :d1 ';
  ADOQuery1.Parameters.ParamByName('d1').Value :=cxLookUpComboBox1.Text;
  ADOQuery1.ExecSQL;
but when I select a record in the grid (in whisch the insert is visible) and delete it the update like I mentioned does not work.
Any ideas?
0
 
LVL 6

Expert Comment

by:bokist
ID: 20757805
perhaps save   CurrentMed_ID   before deleting record
CurrentMed_ID := wwDBGrid1.Datasource.Dataset.FieldbyName('Med_ID').asstring

0
 
LVL 21

Assisted Solution

by:ziolko
ziolko earned 100 total points
ID: 20757839
bokist has a point, if you delete record and try to read value from dbaware component you'll get null instead of expected value

ziolko.
0
 

Author Comment

by:Bosanac
ID: 20757947
you are perfectly right zoiko ...stupid me...
I was putting the code on afterdelete event while it should have gone  on beforedelete..
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…

611 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