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,827 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Xingyu Zhao-MSFT 5,371 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,371 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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.