Board index » delphi » ADO SQL build Index and test for unique [First name], [Middle name], [Last name]

ADO SQL build Index and test for unique [First name], [Middle name], [Last name]

Hello All,

I have trouble in making an index who check that the combined fields
[First name], [Middle name], [Last name] are unique

When the program execute the line
  QryExecSqlIndex(qryTable, 'idxContLFName',     'tblContacts', '[LastName],
[MiddleName], [FirstName]', sioUnique); // ERROR
I get the error:
EOleException with message CREATE INDEX statement
When the program is at the line
  Qry.ExecSQL; // <- location of program when error ocurs

Any idea how to solve it, I want that the user must fill in one of the 3
fields and that they are unique.

Kind regards,

Willy

Here is my source code:

procedure TDM.QryExecSqlIndex(Qry: TADOQuery; IdxName, TableName,
TableField: string;
                            SqlIdxOpt: TSqlIdxOpt);
begin
  Qry.SQL.Clear;
  Qry.SQL.Add('CREATE INDEX ' + IdxName);
  Qry.SQL.Add('ON ' + TableName + ' (' + TableField + ')');
  case SqlIdxOpt of
    sioNone         : ;
    sioPrimary      : Qry.SQL.Add('WITH PRIMARY');
    sioDisallowNull : Qry.SQL.Add('WITH DISALLOW NULL');
    sioIgnoreNull   : Qry.SQL.Add('WITH IGNORE NULL');
    sioUnique       : Qry.SQL.Add('WITH UNIQUE');
  end;
  Qry.ExecSQL; // <- location of program when error ocurs
end;

procedure TDM.BuildTableContacts;
begin
  with qryTable do
  begin
    SQL.Clear;
    SQL.Add('CREATE TABLE tblContacts (');
    SQL.Add('[ContactID]  TEXT (6) NOT NULL UNIQUE');
    SQL.Add('             CONSTRAINT pkContacts PRIMARY KEY,');
    SQL.Add('[CustomerID] TEXT (6) NOT NULL');
    SQL.Add('             CONSTRAINT fkCustomers');
    SQL.Add('             REFERENCES tblCustomers (CustomerID),');
    SQL.Add('[Sequence]   INTEGER,');
    SQL.Add('[Title]      TEXT (6),');
    SQL.Add('[FirstName]  TEXT (10),');
    SQL.Add('[MiddleName] TEXT (10),');
    SQL.Add('[LastName]   TEXT (15),');
    SQL.Add('[Name]       TEXT (30),');   { Concatenation of names }
    SQL.Add('[Address1]   TEXT (35),');
    SQL.Add('[Address2]   TEXT (35),');
    SQL.Add('[ZIP]        TEXT (10),');
    SQL.Add('[City]       TEXT (35),');
    SQL.Add('[StateID]    TEXT (2),');
    SQL.Add('[CountryID]  TEXT (2),');
    SQL.Add('[Function]   TEXT (20),');
    SQL.Add('[Phone]      TEXT (15),');
    SQL.Add('[Mobile]     TEXT (15),');
    SQL.Add('[Fax]        TEXT (15),');
    SQL.Add('[EMail]      TEXT (50),');
    SQL.Add('[Language]   TEXT (2),');
    SQL.Add('[Update]     DATETIME,');
    SQL.Add('[WS_Name]    TEXT (25),');
    SQL.Add('[WS_User]    TEXT (25)');
    { NO COMMA CHAR AT END OF PREVIOUS LINE !! }
    SQL.Add(')');
    ExecSQL;
  end; { with qryAddTable }
  QryExecSqlIndex(qryTable, 'idxContCustomerID', 'tblContacts',
'[CustomerID]', sioNone);
  QryExecSqlIndex(qryTable, 'idxContLFName',     'tblContacts', '[LastName],
[MiddleName], [FirstName]', sioUnique); // ERROR
  QryExecSqlIndex(qryTable, 'idxContName',       'tblContacts', '[Name]',
sioNone);
  QryExecSqlIndex(qryTable, 'idxContZIP',        'tblContacts', '[ZIP]',
sioNone);
  QryExecSqlIndex(qryTable, 'idxContCity',       'tblContacts', '[City]',
sioNone);
  QryExecSqlIndex(qryTable, 'idxContState',      'tblContacts', '[StateID]',
sioNone);
  QryExecSqlIndex(qryTable, 'idxContCountry',    'tblContacts',
'[CountryID]',  sioNone);
  QryExecSqlIndex(qryTable, 'idxContUpdate',     'tblContacts', '[Update]',
sioNone);
  QryExecSqlIndex(qryTable, 'idxContWS_Name',    'tblContacts', '[WS_Name]',
sioNone);
  QryExecSqlIndex(qryTable, 'idxContWS_User',    'tblContacts', '[WS_User]',
sioNone);
end;

 

Re:ADO SQL build Index and test for unique [First name], [Middle name], [Last name]


Quote
>  QryExecSqlIndex(qryTable, 'idxContLFName',     'tblContacts', '[LastName],
>[MiddleName], [FirstName]', sioUnique); // ERROR

What is the code you use in this procedure?
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:ADO SQL build Index and test for unique [First name], [Middle name], [Last name]


Hello Brian,

I use the following code to simplify the creation of indexes:

procedure TDM.QryExecSqlIndex(Qry: TADOQuery; IdxName, TableName,
TableField: string;
                            SqlIdxOpt: TSqlIdxOpt);
begin
  Qry.SQL.Clear;
  Qry.SQL.Add('CREATE INDEX ' + IdxName);
  Qry.SQL.Add('ON ' + TableName + ' (' + TableField + ')');
  case SqlIdxOpt of
    sioNone         : ;
    sioPrimary      : Qry.SQL.Add('WITH PRIMARY');
    sioDisallowNull : Qry.SQL.Add('WITH DISALLOW NULL');
    sioIgnoreNull   : Qry.SQL.Add('WITH IGNORE NULL');
    sioUnique       : Qry.SQL.Add('WITH UNIQUE');
  end;
  Qry.ExecSQL; // <- location of program when error ocurs
end;

I only want to create an index on the fields [LastName], [MiddleName],
[FirstName] to make sure they are unique and not left empty when post the
record.
I create the whole database from out Delphi and don't want to make changes
with the MS-Access program.

Kind regards,
Willy Verbiest

Brian Bushay TeamB <BBus...@Nmpls.com> wrote in message <news:q5ht5vorvrl0ojjjplmd4e965rnqdcg512@4ax.com>...

Quote
> >  QryExecSqlIndex(qryTable, 'idxContLFName',     'tblContacts', '[LastName],
> >[MiddleName], [FirstName]', sioUnique); // ERROR
> What is the code you use in this procedure?

Re:ADO SQL build Index and test for unique [First name], [Middle name], [Last name]


Hello Brian,

I use the following code to simplify the creation of indexes:

procedure TDM.QryExecSqlIndex(Qry: TADOQuery; IdxName, TableName,TableField:
string; SqlIdxOpt: TSqlIdxOpt);
begin
  Qry.SQL.Clear;
  Qry.SQL.Add('CREATE INDEX ' + IdxName);
  Qry.SQL.Add('ON ' + TableName + ' (' + TableField + ')');
  case SqlIdxOpt of
    sioNone         : ;
    sioPrimary      : Qry.SQL.Add('WITH PRIMARY');
    sioDisallowNull : Qry.SQL.Add('WITH DISALLOW NULL');
    sioIgnoreNull   : Qry.SQL.Add('WITH IGNORE NULL');
    sioUnique       : Qry.SQL.Add('WITH UNIQUE');
  end;
  Qry.ExecSQL;
end;

I only want to create an index on the fields [LastName], [MiddleName],
[FirstName] to make sure they are unique and not left empty when post the
record.
I create the whole database from out Delphi and don't want to make changes
with the MS-Access program.

Kind regards,
Willy Verbiest

"Brian Bushay TeamB" <BBus...@Nmpls.com> schreef in bericht
news:q5ht5vorvrl0ojjjplmd4e965rnqdcg512@4ax.com...

Quote

> >  QryExecSqlIndex(qryTable, 'idxContLFName',     'tblContacts',
'[LastName],
> >[MiddleName], [FirstName]', sioUnique); // ERROR
> What is the code you use in this procedure?
> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:ADO SQL build Index and test for unique [First name], [Middle name], [Last name]


Quote
>I only want to create an index on the fields [LastName], [MiddleName],
>[FirstName] to make sure they are unique and not left empty when post the
>record.

I believe that the syntax for creating an unique index is

Create Unique Index
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Other Threads