Solved

Update MS Access table through SQL

Posted on 2004-08-11
8
397 Views
Last Modified: 2010-04-05
Hi,

Database    :     MS Access
Tablename :     ChangeDate

Fieldname  Status       Date
                 New         23-12-2004
                 New         04-03-2004
                 New         13-11-2004
                 New         11-06-2004

I need to update one field (Status) in the dateset whenever the date in that record is in the past.

Result:
Fieldname  Status       Date
                 New         23-12-2004
                 Old           04-03-2004
                 New         13-11-2004
                 Old          11-06-2004

Can anybody give me the correct statement?

Thanks, Stef
0
Comment
Question by:Delphiwizard
  • 6
  • 2
8 Comments
 
LVL 12

Expert Comment

by:esoftbg
ID: 11779281
procedure TForm1.btnUpdateClick(Sender: TObject);
var
  S:      string;
begin
  S := FormatDateTime('yyyymmdd', Date);
  AdoQuery1.Connection := AdoConnection;
  AdoQuery1.Sql.Text := ''
                      + ' update CHANGEDATE '
                      + ' set STATUS='
                      + '''' + 'Old' + ''''
                      + ' where DATES<=:TODAY'
                      + '';
  AdoQuery1.Parameters.ParamByName('TODAY').Value := Date;
  AdoQuery1.ExecSQL;
end;

procedure TForm1.ADOQueryCHANGESNewRecord(DataSet: TDataSet);
begin
  ADOQueryCHANGES.FieldByName('STATUS').AsString := 'New';
end;

emil
0
 
LVL 12

Accepted Solution

by:
esoftbg earned 500 total points
ID: 11779334
unit Unit1_Q_21090692;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics,
  Controls, Forms, Dialogs, DB, DBTables, Grids, DBGrids,
  Buttons, StdCtrls, ExtCtrls, DBCtrls, ComObj, ADODB, ToolWin, ComCtrls;

type
  TForm1 = class(TForm)
    dsaLIST: TDataSource;
    ADOQueryCHANGES: TADOQuery;
    ADOCommand: TADOCommand;
    ToolBar: TToolBar;
    DBNavigator: TDBNavigator;
    ADOConnection: TADOConnection;
    btnUpdateNew: TButton;
    DBGrid1: TDBGrid;
    ADOQueryUpdate: TADOQuery;
    btnUpdateOld: TButton;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure btnUpdateNewClick(Sender: TObject);
    procedure ADOQueryCHANGESNewRecord(DataSet: TDataSet);
    procedure btnUpdateOldClick(Sender: TObject);
  private{ Private declarations }
  public { Public declarations }
    ConnStr:   string;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
var
  FN:     string;
  R:      string;
begin
  R := '';
  FN := ExtractFilePath(Application.ExeName) + 'CHANGES.MDB';
  ConnStr := 'Data Source=' + FN + ';Provider=Microsoft.Jet.OLEDB.4.0';
  if FileExists(FN) then
  try
    ADOConnection.ConnectionString := ConnStr;
    ADOConnection.Open;
  finally
    ADOQueryCHANGES.Connection := AdoConnection;
    ADOQueryCHANGES.Active := True;
    ADOQueryCHANGES.FieldByName('ID').Visible := False;
  end;
end;

procedure TForm1.FormDestroy(Sender: TObject);
var
  I:      Integer;
begin
  for I := 0 to ADOConnection.DataSetCount-1 do
    ADOConnection.DataSets[I].Active := False;
end;

procedure TForm1.btnUpdateNewClick(Sender: TObject);
begin
  ADOQueryUpdate.Connection := AdoConnection;
  ADOQueryUpdate.Sql.Text := ''
                           + ' update CHANGEDATE '
                           + ' set STATUS='
                           + '''' + 'New' + ''''
                           + ' where DATES>=:TODAY'
                           + '';
  ADOQueryUpdate.Parameters.ParamByName('TODAY').Value := Date;
  ADOQueryUpdate.ExecSQL;
  ADOQueryCHANGES.Requery;
end;

procedure TForm1.btnUpdateOldClick(Sender: TObject);
begin
  ADOQueryUpdate.Connection := AdoConnection;
  ADOQueryUpdate.Sql.Text := ''
                           + ' update CHANGEDATE '
                           + ' set STATUS='
                           + '''' + 'Old' + ''''
                           + ' where DATES<:TODAY'
                           + '';
  ADOQueryUpdate.Parameters.ParamByName('TODAY').Value := Date;
  ADOQueryUpdate.ExecSQL;
  ADOQueryCHANGES.Requery;
end;

procedure TForm1.ADOQueryCHANGESNewRecord(DataSet: TDataSet);
begin
  ADOQueryCHANGES.FieldByName('STATUS').AsString := 'New';
end;

end.

//........

object Form1: TForm1
  Left = 210
  Top = 113
  BorderIcons = [biSystemMenu, biMinimize]
  BorderStyle = bsSingle
  Caption = 'Insert / Edit Form'
  ClientHeight = 382
  ClientWidth = 376
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  OnDestroy = FormDestroy
  PixelsPerInch = 96
  TextHeight = 13
  object ToolBar: TToolBar
    Left = 0
    Top = 0
    Width = 376
    Height = 29
    ButtonHeight = 24
    Caption = 'ToolBar'
    TabOrder = 0
    object DBNavigator: TDBNavigator
      Left = 0
      Top = 2
      Width = 180
      Height = 24
      DataSource = dsaLIST
      VisibleButtons = [nbFirst, nbPrior, nbNext, nbLast, nbInsert, nbEdit, nbPost, nbCancel, nbRefresh]
      TabOrder = 0
    end
  end
  object btnUpdateNew: TButton
    Left = 4
    Top = 34
    Width = 72
    Height = 25
    Caption = 'Update New'
    TabOrder = 1
    OnClick = btnUpdateNewClick
  end
  object DBGrid1: TDBGrid
    Left = 80
    Top = 32
    Width = 256
    Height = 344
    DataSource = dsaLIST
    TabOrder = 2
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object btnUpdateOld: TButton
    Left = 4
    Top = 64
    Width = 72
    Height = 25
    Caption = 'Update Old'
    TabOrder = 3
    OnClick = btnUpdateOldClick
  end
  object dsaLIST: TDataSource
    DataSet = ADOQueryCHANGES
    Left = 20
    Top = 196
  end
  object ADOQueryCHANGES: TADOQuery
    OnNewRecord = ADOQueryCHANGESNewRecord
    Parameters = <>
    SQL.Strings = (
      'SELECT * FROM CHANGEDATE')
    Left = 18
    Top = 168
  end
  object ADOCommand: TADOCommand
    CommandText =
      'CREATE TABLE MY_DATA (FIRST_NAME VARCHAR(16), LAST_NAME VARCHAR(' +
      '16), INFO TEXT, COUNTRY TEXT)'
    Parameters = <>
    Left = 54
    Top = 170
  end
  object ADOConnection: TADOConnection
    LoginPrompt = False
    Left = 20
    Top = 128
  end
  object ADOQueryUpdate: TADOQuery
    Connection = ADOConnection
    Parameters = <>
    Left = 24
    Top = 248
  end
end
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 11779340
The above example expected the database is in the same directory with .exe
0
 

Author Comment

by:Delphiwizard
ID: 11779345
Thanks.
Why do you this?

var
  S:      string;
begin
  S := FormatDateTime('yyyymmdd', Date);

I don't see you use it futheron.

Stef
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 12

Expert Comment

by:esoftbg
ID: 11779388
this is an unsuccessful attemt to assign TODAY as string, please ignore it....

download working example from :
page:        http://www.geocities.com/esoftbg/
  link:        Q_21090692.zip
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 11779679
> this is an unsuccessful attemt to assign TODAY as string, please ignore it....

mean that:

var
  S:      string;

and

  S := FormatDateTime('yyyymmdd', Date);

to be ignored....
0
 

Author Comment

by:Delphiwizard
ID: 11780865
Thanks Emil for your help and for the example on your site it works great.

Stef
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 11781741
Hi Delphiwizard,
You are welcome !
I did improve the example on my site. If you are interested, download it again ....
Best Regards,
Emil
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now