none
How to get a view/query sql code from vb .net?

    Question

  • Hi,

     

    Can anyone tell me how to get the view(SQL)/Query(Access) code from vb .net?

    In Vb6 i used to use the AdoX wich returned the commandtext of the view but now in VB .Net i can't seem to find a similar function. I can get table fields, etc with OleDbSchemaGuid but when it comes to views there doesn't seem to be an equivalente to commandtext wich returns the SQL instruction behind the view/query.

    Does anyone know how to do this?

    Tuesday, April 15, 2008 10:17 AM

Answers

  • Hi,

     

    This little code snippet will show you a queries definition, just point and rename things to suit.

     

    Imports System.Data.OleDb

    Module Module1

    Sub Main()

     

    Dim connBuildr As New OleDbConnectionStringBuilder("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True")

    connBuildr.DataSource = AppDomain.CurrentDomain.SetupInformation.ApplicationBase & "Sample.mdb"   'the database

     

    Dim conn As New OleDbConnection(connBuildr.ConnectionString)

    conn.Open()

     

    Dim restrictions(2) As String

    restrictions(2) = "qryOne"  'name of the query to get

     

    Dim views As DataTable = conn.GetSchema("Views", restrictions)

    Console.WriteLine(views.Rows(0)("VIEW_DEFINITION"))

     

    conn.Close()

     

    End Sub

    End Module

    Tuesday, April 15, 2008 3:01 PM

All replies

  • Hi,

     

    This little code snippet will show you a queries definition, just point and rename things to suit.

     

    Imports System.Data.OleDb

    Module Module1

    Sub Main()

     

    Dim connBuildr As New OleDbConnectionStringBuilder("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True")

    connBuildr.DataSource = AppDomain.CurrentDomain.SetupInformation.ApplicationBase & "Sample.mdb"   'the database

     

    Dim conn As New OleDbConnection(connBuildr.ConnectionString)

    conn.Open()

     

    Dim restrictions(2) As String

    restrictions(2) = "qryOne"  'name of the query to get

     

    Dim views As DataTable = conn.GetSchema("Views", restrictions)

    Console.WriteLine(views.Rows(0)("VIEW_DEFINITION"))

     

    conn.Close()

     

    End Sub

    End Module

    Tuesday, April 15, 2008 3:01 PM
  • Thaks Derek.

    Works like a charm.

     

    Tuesday, April 15, 2008 5:18 PM