OdbcConnection.GetSchema("Tables", FilterValues) doesn't work, how to solve the problem

bob guo 1 Reputation point
2021-06-25T06:34:29.273+00:00

I have a vb.net desktop program, which uses ODBC to connect to database. I need to use GetSchema() to get the user's table from database.

I find that if I use OleDbConnection.GetSchema(), it works fine, OdbcConnection.GetSchema("Tables") works fine too, only OdbcConnection.GetSchema("Tables", FilterValues) doesn't work, an error is occurred, it say:
System.ArgumentException : In addition to the required architectural (" tables ") support, there are more restrictions.

My program need to support OdbcConnection, my develop environment is win7 chinese + visual studio 2019, the program's target framework is .net 2.0. I have ever try to change target framework to 4.6, 4.72 etc, they don't work.

Anyone know how to solve the problem? Thanks.

The codes are as follows:

Private Sub TryGetSchema()

Dim OleDbConn As New OleDbConnection  
Dim OdbcConn As New OdbcConnection  
Dim FilterValues As Array  
Dim mySchema As New DataTable  

FilterValues = {Nothing, Nothing, Nothing, "TABLE"}  

'OleDbConnection works fine:   
OleDbConn.ConnectionString = "Provider=SQLNCLI10;Server=(local);Database=test;Uid=sa;Pwd=123456;"  
OleDbConn.Open()  
mySchema = OleDbConn.GetSchema("Tables")  
mySchema = OleDbConn.GetSchema("Tables", FilterValues)  
OleDbConn.Close()  

'OdbcConnection  
OdbcConn.ConnectionString = "Driver={SQL Server Native Client 10.0};Server=(local);Database=test;Uid=sa;Pwd=123456;"  
OdbcConn.Open()  
'GetSchema("Tables") works fine too:   
mySchema = OdbcConn.GetSchema("Tables")  
'GetSchema("Tables", FilterValues) doesn't work, an error is occurred, it say:  
'System.ArgumentException : In addition to the required architectural (" tables ") support, there are more restrictions.  
mySchema = OdbcConn.GetSchema("Tables", FilterValues)  
OdbcConn.Close()  

End Sub

The error screen is as follows:
109207-%E6%97%A0%E6%A0%87%E9%A2%98.png

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,605 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Xingyu Zhao-MSFT 5,356 Reputation points
    2021-06-28T05:54:34.893+00:00

    Hi @bob guo ,
    After further testing the code, you can consider changing the 'FilterValues' :

    FilterValues = {Nothing, Nothing, "TABLE"}  
    

    Hope it could be helpful.

    Best Regards,
    Xingyu Zhao
    *
    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Xingyu Zhao-MSFT 5,356 Reputation points
    2021-06-29T06:09:41.86+00:00

    Hi @bob guo ,
    Try the following alternatives:

        Dim schema As DataTable = OdbcConn.GetSchema("Tables").AsEnumerable().  
                Where(Function(r) r.Field(Of String)("TABLE_TYPE") = "TABLE").CopyToDataTable  
    

    Hope it could be helpful.
    Best Regards,
    Xingyu Zhao
    *
    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.