Populate DataGridView from result of Stored Procedure

[Steven Kitzes]

Hello, I'm new to Visual Basic and have no experience in the MS ecosystem. My employer is requiring me to do a project in VB.NET so I have to figure this out on my own. I am building a client in VB.NET that connects to a SQL Server instance that is already developed and maintained by another team on our intranet. I need to call stored procedures created by this other team and fill a DataGridView with the results, in such a way that the results are selectable and will trigger subsequent Stored Procedures. So far, I have the initial query working, so the initial Stored Procedure fires, and I am getting the results back that I expect. However, I have tried many things and none of them seems to be causing the DataGridView to fill with the data I am getting back. Currently my code looks something like the following, but how can I change it to get the view both (a) populated, and (b) in such a state that I can use it to make selections that will trigger subsequent context-specific Stored Procedures?

Dim SearchQuery As String

Dim Connection As New ADODB.Connection
Dim RecordSet As New ADODB.Recordset

Connection.ConnectionTimeout = 20
Connection.CursorLocation = ADODB.CursorLocationEnum.adUseClient
Connection.Open(Globals.MyConnectionString)
For Each DBError As ADODB.Error In Connection.Errors
Log.Err("Database error: " + DBError.Description, True, True)
Next DBError
If Connection.Errors.Count > 0 Then
Return
End If

' Set up search query
SearchQuery = "myStoredProcedure @searchType = " + SearchType.ToString() + ",@searchString = '" + SearchText + "'"

RecordSet.CursorLocation = ADODB.CursorLocationEnum.adUseClient
RecordSet.Open(SearchQuery, Connection, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText)

If RecordSet.RecordCount < 1 Then
Return
Else
Log.Out("Got this many records: " + RecordSet.RecordCount.ToString(), True)
End If

' THE IMPORTANT PART
myDataGridView.DataSource = RecordSet
myDataGridView.Refresh()

RecordSet.Close()
Connection.Close()

Continue reading...