|
-
Feb 2nd, 2014, 02:38 PM
#1
Thread Starter
Fanatic Member
[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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|