Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Is this a bug in ADOX or in SQL Server or in ADODB ??

Posted on 2003-11-26
7
469 Views
Last Modified: 2013-11-27
Win2k, ADO 2.80, SQL Server 2000

This is the ASP code:
<%
Option Explicit
Response.Buffer = False


Dim oConn, sDSN, oCatalog, oKeys, oColumns, element, subelement

' crate ADODB Connection
sDSN = "PROVIDER=SQLOLEDB;DATA SOURCE=127.0.0.1;USER ID=sa;PASSWORD=;DATABASE=tivolirooster;"
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open sDSN

' Create ADOX catalog
Set oCatalog = Server.CreateObject("ADOX.Catalog")
oCatalog.ActiveConnection = oConn

' get the keys of a table which has a primary key (on an identity field) - also some foreign keys are present.
Set oKeys = oCatalog.Tables.Item("performance").Keys

For each element In oKeys
    Response.write "<hr>" & element.Name & " (" & element.Type & ")<br>" & CHR(10)
    Set oColumns = element.Columns
    For each subelement in oColumns   '----- error on this line
        Response.write subelement.Name & "<br>" & CHR(10)
    Next
Next
%>

============================
Error:
Microsoft VBScript runtime error '800a0cb3'
Unknown runtime error
demobug.asp, line 23
============================
But, this is only an eror if oKeys.element.Type = 1 (primary key). For foreign keys it runs fine.

The same code runs completely without problems on Access.

questions:
- is this a bug in ADO?
- is there some way to avoid the bug (different connectionstring, upgrade - but i have the latest ADO installed)
0
Comment
Question by:sybe
  • 5
  • 2
7 Comments
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9825772
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9825793
I assume that you have done your round of snooping around.. eh? :o)

Cheers!!
0
 
LVL 21

Accepted Solution

by:
ap_sajith earned 500 total points
ID: 9825873
Go through this as well.. doesnt make much sense to me.. though..

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B294157

Cheers!!
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 21

Expert Comment

by:ap_sajith
ID: 9825956
hmmm.. didnt go through your entire post above... reasd through the kb article posted above..

>>CAUSE
To retrieve columns used by the primary key, ADOX uses the IDBSchemaRowset::GetRowset method with DBSCHEMA_KEY_COLUMN_USAGE, which is not supported by the SQL Server OLE DB provider (SQLOLEDB) provider and is supported only by the latest version of the Jet OLE DB Provider which is installed with the latest version of the Jet Service Pack. <<

>>STATUS
This behavior is by design. <<

why do microsoft alway piss us off with the above line... behaviour is by design...

Sorry Sybe, I guess SQL doesnt support the method....

Cheers!!
0
 
LVL 28

Author Comment

by:sybe
ID: 9830294
Your link to the ms site hits the nail exactly on the head. Thanks.
0
 
LVL 28

Author Comment

by:sybe
ID: 9830383
for anyone's information:

There is another way to get the columns in the Primary Key of a table.

All primary keys in a database are listed in a recordset using:
Set oRS = oConn.OpenSchema(28)

In the resulting recordset also fields for the name of the table and the name of the primary key are listed. Which is exactly what i needed.
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9830408
Thanks for the points and thanks for the info..

Cheers!!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
corrupt Databases 9 62
On Key Down Access 2010 6 41
Pdf to save under the folder that obtain the same name from Pdf 2 14
Run Stored Procedure uisng ADO 5 18
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

790 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