[RESOLVED] How to add a property to a StoredProcedure?

[Mark@SF]

I have a VB.Net application that uses a Microsoft Access database as its datasource. The database has a query that I use as a StoredProcedure to populate a DataGridView control. When my application's startup form loads, it checks the database to see if the query exists, and if it cannot be found then it adds it.

I would like to add a property to the query (e.g., "Description") and set its value. I know how to do this in Access, but not in VB.Net.

Here's my code (note the "TBD" comment is where I need help):

    Sub QueryPropertySet(strQueryName As String, strPropertyName As String, strPropertyValue As String)

        'Example:
        '   QueryPropertySet("myQuery","Description","This is my query.")

        Dim strMethodName = New System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name    '...this procedure's name

        Dim _DEBUG As Boolean = False

        Dim cs As New clsAppConnectionStrings
        Dim con As New ADODB.Connection
        Dim cmd As ADODB.Command
        Dim cat As New ADOX.Catalog
        Dim vi As ADOX.View

        Try
            con.ConnectionString = cs.GetConnectionString("MyConnection")
            con.Open()

            cat.ActiveConnection = con

            For Each vi In cat.Views
                If _DEBUG Then
                    Debug.WriteLine(vi.Name)
                    For i As Integer = 0 To CType(vi.Command, ADODB.Command).Properties.Count - 1
                        Debug.Print(CStr(CType(vi.Command, ADODB.Command).Properties.Item(i).Name))
                    Next
                    Debug.WriteLine(CType(vi.Command, ADODB.Command).CommandText.ToString)
                End If
            Next

            vi = cat.Views(strQueryName)
            Dim prp As Object = CType(vi.Command, ADODB.Command).Properties(strPropertyName)
            If Not prp Is Nothing Then
                CType(vi.Command, ADODB.Command).Properties(strPropertyName).Value = strPropertyValue
            Else
                '...TBD
                '...property.CreateProperty   <--??
                CType(vi.Command, ADODB.Command).Properties(strPropertyName).Value = strPropertyValue
            End If


        Catch ex As Exception
            MessageBox.Show(ex.Message & " (err=" & Err.Number & ")", strMethodName, MessageBoxButtons.OK, MessageBoxIcon.Error)
            LogErrors(ex, Err)

        Finally
            cs = Nothing
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
            cmd = Nothing
            cat = Nothing
            vi = Nothing

        End Try

    End Sub

Appreciate any advice on this question. Thanks

[Mark@SF]

Here's the solution...

    Sub QueryPropertyAdd(strQueryName As String, strPropertyName As String, daoPropertyType As DAO.DataTypeEnum, strPropertyValue As String)

        '...Requires a reference to the following COM library:
        '   Microsoft DAO 3.6 Object Library

        Dim strMethodName = New System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name    '...this procedure's name

        Dim _DEBUG As Boolean = False

        Dim cs As New clsAppConnectionStrings
        Dim JetEngine As New DAO.DBEngine
        Dim AccessDB As DAO.Database = Nothing
        Dim qdef As DAO.QueryDef
        Dim pdef As DAO.Property
        Dim strDataSource As String

        Try
            strDataSource = cs.GetDataSource(cs.GetConnectionString("MyConnection"))
            AccessDB = JetEngine.OpenDatabase(strDataSource)
            qdef = AccessDB.QueryDefs(strQueryName)
            pdef = AccessDB.CreateProperty(strPropertyName, daoPropertyType, strPropertyValue)

            Try
                qdef.Properties.Delete(strPropertyName)
            Catch
                'do nothing, property doesn't exist
            Finally
                qdef.Properties.Append(pdef)
            End Try

        Catch ex As Exception
            MessageBox.Show(ex.Message & " (err=" & Err.Number & ")", strMethodName, MessageBoxButtons.OK, MessageBoxIcon.Error)
            LogErrors(ex, Err)

        Finally
            cs = Nothing
            JetEngine = Nothing
            AccessDB.Close()
            AccessDB = Nothing
            qdef = Nothing
            pdef = Nothing

        End Try

    End Sub

http://social.msdn.microsoft.com/For...orum=vbgeneral