We help IT Professionals succeed at work.

Update a table after delete

Bosanac
Bosanac asked
on
Medium Priority
268 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?
Comment
Watch Question

Author

Commented:
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 !???
Top Expert 2008

Commented:
what is the value of QuotedStrCurrentMed_ID?

Author

Commented:
QuotedStrCurrentMed_ID ...text field...containing numbers and letters...
Top Expert 2007

Commented:
>>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;


Top Expert 2008

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

Open in new window

Author

Commented:
do not work either...
Top Expert 2008

Commented:
can you elaborate on 'do not work'? does it produce an error?
Top Expert 2007

Commented:
What was your CurrentMed_ID value?
Commented:
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;

Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
>>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.
Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
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.

Author

Commented:
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)

Commented:
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;

Commented:
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
Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

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

ziolko.

Author

Commented:
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?

Commented:
perhaps save   CurrentMed_ID   before deleting record
CurrentMed_ID := wwDBGrid1.Datasource.Dataset.FieldbyName('Med_ID').asstring

Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008
Commented:
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.

Author

Commented:
you are perfectly right zoiko ...stupid me...
I was putting the code on afterdelete event while it should have gone  on beforedelete..

Explore More ContentExplore courses, solutions, and other research materials related to this topic.