question

bobguo-4031 avatar image
0 Votes"
bobguo-4031 asked bobguo-4031 commented

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

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


dotnet-visual-basic
无标题.png (77.6 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @bobguo-4031 ,
Unfortunately, I cannot reproduce your problem, could you share your project on onedrive or github? It will help us make a test.

0 Votes 0 ·

Hi XingyuZhao-MSFT,
The project is private and big, it's not convenient to share, the whole code related to GetSchema() had been posted in TryGetSchema() subroutine, You create a new project, then copy and paste the code to the project to try If it doesn't work yet, I will created a new project for your. Thanks.

0 Votes 0 ·

Hi @bobguo-4031 ,
We do not collect any information. Please be careful not to show any personal information on the forum.

0 Votes 0 ·
XingyuZhao-MSFT avatar image
0 Votes"
XingyuZhao-MSFT answered bobguo-4031 commented

Hi @bobguo-4031 ,
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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I have try FilterValues = {Nothing, Nothing, "TABLE"} just now, it's not a right usage, it return 0 rows.
Because OdbcConnection.GetSchema return a recordset with these fields:TABLE_CAT,TABLE_SCHEM,TABLE_NAME,TABLE_TYPE, so the third parameter in FilterValues is used for filter table name, what I want to filter is Table_type, so we should use the the fourth parametar.

0 Votes 0 ·
XingyuZhao-MSFT avatar image
0 Votes"
XingyuZhao-MSFT answered bobguo-4031 commented

Hi @bobguo-4031 ,
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.


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AsEnumerable().where is not desirable, it will fetch all table information from database, and then select on client, it's hurt on performance. I want to filter information on database side, to reduce data roundtrip between client and database, just like OleDbConn.GetSchema("Tables", FilterValues) has done.

0 Votes 0 ·

Hi @bobguo-4031 ,
I have reported this issue on Developer Community, you can follow the thread there.


0 Votes 0 ·

Ok, thank you very much.

0 Votes 0 ·