Board index » delphi » MS SQL, FindKey, index not selected

MS SQL, FindKey, index not selected

Using TTable to connect to MS SQL 7.0,  I get a "No index currently
active" error message when doing a FindKey on the table. The table
contains a primary key which is defined as both UNIQUE and NOT NULL.
I've tried connecting with BDE+SQLINKS and BDE+ODBC, but get the same
error message both ways.  It sounds like TTable is not recognizing the
primary key as the default index.  Any suggestions?  Thanks.  John.

Here's my table definition:
    Sql.Add('CREATE TABLE ACCTS (A_ACCOUNT INTEGER UNIQUE NOT NULL,');
    Sql.Add('PRIMARY KEY (A_ACCOUNT));');
Simple, eh?

 

Re:MS SQL, FindKey, index not selected


Hello,

i had this message with MS-SQL 6.5 and the problem was casesensetive in the
index
name. please check that the index name in the SQL SERVER and in your code
is the same (for primary index you should check that the property
indexfiledname is '')

HTH
Yaron

Quote
John Erdner <m...@enid.com> wrote in message

news:37E7F439.FFA458FD@enid.com...
Quote
> Using TTable to connect to MS SQL 7.0,  I get a "No index currently
> active" error message when doing a FindKey on the table. The table
> contains a primary key which is defined as both UNIQUE and NOT NULL.
> I've tried connecting with BDE+SQLINKS and BDE+ODBC, but get the same
> error message both ways.  It sounds like TTable is not recognizing the
> primary key as the default index.  Any suggestions?  Thanks.  John.

> Here's my table definition:
>     Sql.Add('CREATE TABLE ACCTS (A_ACCOUNT INTEGER UNIQUE NOT NULL,');
>     Sql.Add('PRIMARY KEY (A_ACCOUNT));');
> Simple, eh?

Re:MS SQL, FindKey, index not selected


Do you have the fully qualified table name in your TTable component?
With MS-SQL 6.5 you could just use the table name such as MyTable.  With
MS-SQL 7 you need to make sure the owner is prepended to the table name:
dbo.MyTable (or whatever the owner is in your situation).

This looks like a bug in Delphi.  It should either work in all
situations or fail in all.  Unfortunately, if you just ave the bare
table name, most things will work fine but a few things don't (like
locate and secondary indexes). I was hoping this would be fixed in
Delphi 5, but it's the same there too.

John

In article <7s9302$7...@forums.borland.com>, miku...@isdn.net.il says...

Quote
> Hello,

> i had this message with MS-SQL 6.5 and the problem was casesensetive in the
> index
> name. please check that the index name in the SQL SERVER and in your code
> is the same (for primary index you should check that the property
> indexfiledname is '')

> HTH
> Yaron

> John Erdner <m...@enid.com> wrote in message
> news:37E7F439.FFA458FD@enid.com...
> > Using TTable to connect to MS SQL 7.0,  I get a "No index currently
> > active" error message when doing a FindKey on the table. The table
> > contains a primary key which is defined as both UNIQUE and NOT NULL.
> > I've tried connecting with BDE+SQLINKS and BDE+ODBC, but get the same
> > error message both ways.  It sounds like TTable is not recognizing the
> > primary key as the default index.  Any suggestions?  Thanks.  John.

> > Here's my table definition:
> >     Sql.Add('CREATE TABLE ACCTS (A_ACCOUNT INTEGER UNIQUE NOT NULL,');
> >     Sql.Add('PRIMARY KEY (A_ACCOUNT));');
> > Simple, eh?

Re:MS SQL, FindKey, index not selected


Does your table name in the TTable component have the owner prepended to
the actual table name?  With MS-SQL 6.5 you could just use a table name
such as MyTable, but the behavior is different with MS-SQL 7 and you
need to use the fully qualified name: dbo.MyTable (or whatever the owner
prefix is in your situation).

Seems like a bug in Delphi to me. It should either work completely or
fail completely, not just fail for some operations.  I was hoping it
would be fixed in Delphi 5, but it's the same there too.

John

In article <7s9302$7...@forums.borland.com>, miku...@isdn.net.il says...

Quote
> Hello,

> i had this message with MS-SQL 6.5 and the problem was casesensetive in the
> index
> name. please check that the index name in the SQL SERVER and in your code
> is the same (for primary index you should check that the property
> indexfiledname is '')

> HTH
> Yaron

> John Erdner <m...@enid.com> wrote in message
> news:37E7F439.FFA458FD@enid.com...
> > Using TTable to connect to MS SQL 7.0,  I get a "No index currently
> > active" error message when doing a FindKey on the table. The table
> > contains a primary key which is defined as both UNIQUE and NOT NULL.
> > I've tried connecting with BDE+SQLINKS and BDE+ODBC, but get the same
> > error message both ways.  It sounds like TTable is not recognizing the
> > primary key as the default index.  Any suggestions?  Thanks.  John.

> > Here's my table definition:
> >     Sql.Add('CREATE TABLE ACCTS (A_ACCOUNT INTEGER UNIQUE NOT NULL,');
> >     Sql.Add('PRIMARY KEY (A_ACCOUNT));');
> > Simple, eh?

Other Threads