• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

ASP SQL Client version of code

Hi. How do I change the following code to work with SQL Client, instead of Oledb?

   Dim schemaTable As DataTable
        Dim i As Integer


        Dim cs As String = ConfigurationManager.ConnectionStrings("survey_conn").ConnectionString
        Dim cn As New OleDbConnection(cs)
        Try
            cn.Open()

            'Retrieve schema information about tables.
            'Because tables include tables, views, and other objects,
            'restrict to just TABLE in the Object array of restrictions.
            schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                          New Object() {Nothing, Nothing, Nothing, "TABLE"})
0
Murray Brown
Asked:
Murray Brown
2 Solutions
 
Alan WarrenCommented:
Hi Murbro,
not sure if this is the sort of thing you are looking for:
  Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
    Dim schemaTable As System.Data.DataTable
    Dim i As Integer = 0
    Dim strSqlError As String = ""
    Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("survey_conn").ConnectionString)
    Try
      cn.Open()

      'Retrieve schema information about tables.
      'Because tables include tables, views, and other objects,
      'restrict to just TABLE in the Object array of restrictions.
      schemaTable = cn.GetSchema("Restrictions")

      ' Display the contents of the table.
      DisplayData(schemaTable)

    Catch SQLexc As System.Data.SqlClient.SqlException
      strSqlError = "Operation Failed. Error Details are: " & SQLexc.ToString()
    Finally
      cn.Close()
    End Try

  End Sub

  Private Sub DisplayData(ByVal table As System.Data.DataTable)
    For Each row As System.Data.DataRow In table.Rows
      For Each col As System.Data.DataColumn In table.Columns
        System.Diagnostics.Debug.WriteLine("{0} = {1}", col.ColumnName, row(col))
      Next
      System.Diagnostics.Debug.WriteLine("============================")
    Next
  End Sub

Open in new window

Working with the GetSchema Methods

Alan
0
 
Robert SchuttSoftware EngineerCommented:
I think there's an error on that MSDN page and as a consequence in the code Alan posted: GetSchema("Restrictions") is used in the VB example (returning foreign keys), whereas the correct GetSchema("Tables") is used in the C# example.

I suspect they were thinking ahead to using a second argument to get only a certain tables.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thank you both
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now