שתף באמצעות

Populate DataGridView from result of Stored Procedure

Question

Saturday, September 28, 2019 12:04 AM

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

All replies (11)

Saturday, September 28, 2019 3:02 AM

Hello,

  • First off using RecordSet method for working with data is an ancient way to work with data in Visual Studio no matter if it's VB.NET or C#. 
  • Second, the proper method to open a connection is with a using statement, same goes for what is called a command object.

I have a code sample which includes a script to create the database and tables to run the code sample done many years ago and still works today.

Visual Studio solution

Source code link

Notes on source code

  • Data is populated using stored procedures into a DataGridView
  • Add CRUD operations are done using button clicks. Same can be done with what is done with buttons via DataGridView events.
  • Simple filtering is done, we could easily spend a good deal of time just on filtering and like conditions but not in this code sample.
  • Using a BindingSource component as used here is critical as one should not rely on getting or setting data directly from cells in a DataGridView, instead use the BindingSource.Current property which can be cast to a DataRow e.g. CType(bsCustomers.Current,DataRowView).Row. Values can be read via Row.Field(Of String)("CompanyName") to get a string field named CompanyName Row.Field(Of T) where T can be any type in a the underlying DataTable.
  • BindingSource components provide methods to navigate records e.g. someBindingSource.MoveFirst, MoveNext, MoveLast etc. along with other cool methods.
  • I make use of language extension methods a good deal, they make code easier and cleaner to code.

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

Saturday, September 28, 2019 4:52 PM

We don't know how your stored procedure looks like. If we keep your SQL code almost the same (beside using the VB stringConnector "&") then this is in fact all the code you need. 

The adapter opens the connection and closes that itself if the connection is not already open.

    Private Sub LoadDG(Searchtype As Integer, SearchText As String, MyConnectionstring As String)
        Dim myAdapter As New SqlClient.SqlDataAdapter("myStoredProcedure @searchType = " & Searchtype.ToString() & ",@searchString = '" & SearchText + "'", MyConnectionstring)
        Dim dt As New DataTable
        myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
        myAdapter.Fill(dt)
        MyDataGridView.DataSource = dt
    End Sub

Success
Cor

Monday, September 30, 2019 7:12 PM

This method is attractive due to its brevity, but it yields an error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Could not find stored procedure 'myStoredProcedure @source = 123,@value = 'myValue''.

I know that this stored procedure exists, because I am looking at it on SQL Server, and because the identical stored procedure string works when I use the old RecordSet method.  Why might this error be reported if the stored procedure exists and works in other scenarios?

Tuesday, October 1, 2019 6:33 AM

Hi,

If you want to use my way to call a stored procedure.

You can try my code as follows.

Imports System.Data.SqlClient

Public Class Form1
    Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= D:\Local Database\190925.mdf"
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Using conn = New SqlConnection(constr)
            conn.Open()
            Using sda As SqlDataAdapter = New SqlDataAdapter("UpGetInfo", conn)
                Dim ds As DataSet = New DataSet()
                sda.Fill(ds)
                DataGridView1.DataSource = ds.Tables(0)
                Dim htext As String() = {"Index", "Sno", "Sname", "Sex"}
                For i As Integer = 0 To htext.Length - 1
                    DataGridView1.Columns(i).HeaderText = htext(i)
                Next
            End Using
        End Using
    End Sub
End Class

If the stored procedure takes parameters, you need to pass the parameters to the stored procedure, otherwise you do not need the following code.

        Dim para0 As SqlParameter = New SqlParameter("@sno", SqlDbType.NChar, 10) 'Parameter name, type, size
        para0.Value = TextBox1.text
        cmd.Parameters.Add(para0)

Hope it will be helpful.

Best Regards,

Julie

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Tuesday, October 1, 2019 10:08 AM

This method is attractive due to its brevity, but it yields an error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Could not find stored procedure 'myStoredProcedure @source = 123,@value = 'myValue''.

I know that this stored procedure exists, because I am looking at it on SQL Server, and because the identical stored procedure string works when I use the old RecordSet method.  Why might this error be reported if the stored procedure exists and works in other scenarios?

Is there a reason why you are not using current methods to work with data and sticking with recordsets? 

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

Tuesday, October 1, 2019 4:45 PM

This method is attractive due to its brevity, but it yields an error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Could not find stored procedure 'myStoredProcedure @source = 123,@value = 'myValue''.

I know that this stored procedure exists, because I am looking at it on SQL Server, and because the identical stored procedure string works when I use the old RecordSet method.  Why might this error be reported if the stored procedure exists and works in other scenarios?

If you don't show how the stored procedures looks like, you get only replies where persons are guessing and guessing or give you just general advices. Important is if the stored procedure itself contains parameters and what are there names. I doubt the externals for that start with an @ (the internals does).

Success
Cor

Tuesday, October 1, 2019 9:11 PM

Karen: current methods yield the "Could not find stored procedure" error described above, for reasons I do not understand.

Tuesday, October 1, 2019 9:12 PM

Cor: this string construction is lifted verbatim from a working VB6 application.  If I understand you correctly, you are saying that I am making a mistake in the call to the stored procedure by including '@' symbols on my parameter names.  However, as mentioned in the original post, the original code I posted does work with the '@' symbols; in other words, the queries to the stored procedures do work and do return the expected data.  The question was how to get that data into a DataGridView.  I do not know why the existing, working query does not work with your SqlDataAdapter method, that is what I was asking.  The content of the stored procedure does not, intuitively, seem to be relevant to whether the stored procedure itself can be found.

Tuesday, October 1, 2019 9:20 PM

The stored procedure in question is a couple of hundred lines long and reveals business information I'm not comfortable sharing publicly.  Maybe if you can describe the reason why the procedure's content would cause it not to be able to be found, I could use that to help me find the answer to the question.

Tuesday, October 1, 2019 9:31 PM

As mentioned, I have no idea what I'm doing.  I'm a JS ecosystem and AWS web/cloud developer with zero experience in MS products or development.  My team has equivalently negligible experience, but the boss demands we use VB.NET to develop this project because that's what someone chose to build the predecessor product 20 years ago.  My reasoning for choosing RecordSet (and for structuring my code in the way you see it here) is that this is based off of the 20-year-old code.  Why would I do that?  Because the various connection strings and queries we are using all point toward a SQL Server instance that is also 20 years old, and does not appear to be compatible with current methods.  The current methods I've tried that you and Cor have helpfully provided here do not work, instead generating further errors and more questions.  Rewriting literally hundreds of stored procedures is currently considered to be out of scope for this client-side rewrite.

Tuesday, October 1, 2019 11:17 PM

Okay, first off I've never used RecordSet to work with data but looking at this code sample (from Microsoft) and your code sample there seems to be a difference in how they are constructed.

In regards to not finding the stored procedure, try running this query best done in SSMS (SQL-Server Management Studio).

SELECT   name ,
         type
FROM     dbo.sysobjects
WHERE    type IN ( 'P' ,  -- stored procedures
                   'FN' , -- scalar functions 
                   'IF' , -- inline table-valued functions
                   'TF'   -- table-valued functions
    )
ORDER BY type , name;

Here is the same query suitable to run in a query in code.

SELECT name,[type] FROM dbo.sysobjects WHERE [type] IN ( 'P' , 'FN' , 'IF' , 'TF' ) ORDER BY [type] , name;

See if your stored procedure appears, if so then the issue is with how it's being called.

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow