Error: "more restrictions were provided" using GetSchema with ODBC connection to DSN

I want to get the schema information from a Microsoft SQL database, using an ODBC connection to the DSN for that database (this is a work constraint so it can't be changed).  The connection is made without problem, the table information comes back fine, but when I try to add a restriction it gives me an error.

Here's the code:

public void FillMySchema(OdbcConnection pConn)
{
    string[] restrictions = new string[4];
    restrictions[3] = "TABLE";

    DataTable dtTables = pConn.GetSchema("Tables", restrictions);
}

The error is: "More restrictions were provided than the requested sceme ("Tables") supports.

What I am trying to do is filter out the tables of type "SYSTEM TABLE" and leave only type "TABLE".  Am I using the syntax correctly?
LVL 1
FrancineTaylorAsked:
Who is Participating?
 
gregoryyoungCommented:
They actually cover this exact example in MSDN help.

http://msdn2.microsoft.com/en-us/library/ms135853.aspx

you actually just want to pass "Tables" which ssays you want the tables collection .. the other restrictions are database, owner, and table name ... You are passing in 4 strings not 3.

Cheers,

Greg Young
MVP - C#
http://geekswithblogs.net/gyoung
0
 
FrancineTaylorAuthor Commented:
Okay, I get it.  I was confused by the difference between the way OLE gets their schema and the way ODBC does it.

So if I want to get *only* tables of type "TABLE" (as opposed to "SYSTEM TABLE") in OLE I would do it this way:

   DataTable dtTables = pConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                                                                  new object[] { null, null, null, "TABLE" });


...but if I wanted to do the same thing with ODBC, I have to do it this way:

   DataTable dtTables = pConn.GetSchema("Tables");

   DataRow row = (DataRow)dtTables.Rows[iTable];
   string tabletype = row["TABLE_TYPE"].ToString();

   // this should filter out system files
   if (rowtype.Equals("TABLE"))
   {
       // your code goes here
   }

Thanks, Greg!
0
 
FrancineTaylorAuthor Commented:
..oops, slight mis-typing...code *should* say:

    if (tabletype.Equals("TABLE"))
   {
       // your code goes here
   }
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.