Results 1 to 2 of 2

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

Threaded View

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    528

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

    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):

    Code:
        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
    Last edited by Mark@SF; Feb 2nd, 2014 at 02:52 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width