Calling a SQL Server stored procedure with parameters in VB.NET

Question

I have a SQL server stored procedure with an input parameter @ClientID which I am trying to call from a VB.NET application. The following code always fails on the line which sets the parameter with an error:

System.Runtime.InteropServices.COMException: 'Item cannot be found in the collection corresponding to the requested name or ordinal.'

Everything I have read so far suggests I need to use a cmd.Parameters.Add or cmd.Parameters.AddWithValue command. However when I try either, I get an 'Add' is not a member of 'Parameters' error. Also the Add and AddWithValue methods are not listed in the list of properties/methods for cmd.parameters.

Here is the code. The cnn connection has already been defined and opened elsewhere.

Private Sub GetProjectList()
    Dim dt As New DataTable("tblProject")
    dt.Columns.AddRange({
        New DataColumn("ProjectID", GetType(Integer)),
        New DataColumn("ProjectName", GetType(String))
    })

    Dim row As DataRow = dt.NewRow()

    Dim strStoredProc As String
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset

    strStoredProc = "dbo.sp_ProjectList"

    cmd.ActiveConnection = cnn
    cmd.CommandText = strStoredProc
    cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc

    cmd.Parameters.Refresh()

    Dim strParam As String
    strParam = cboClient.GetItemText(cboClient.SelectedValue)
    cmd.Parameters("@ClientID").Value = strParam

Answer 1

You shouldn't be using the old ADODB.Command COM interfaces. There is the much newer Microsoft.Data.SqlClient library (and has been in use as the old System.Data.SqlClient for over 20 years already!)

Imports Microsoft.Data.SqlCLient

Private Sub GetProjectList()
    Using
      cnn As SqlConnection = New SqlConnection(YourConnStringHere),
      cmd As SqlCommand = New SqlCommand("dbo.sp_ProjectList", cnn)
        cmd.CommandType = CommandType.StoredProcedure
        ' add correct type and length below
        cmd.Parameters.Add("@ClientID", SqlDbType.NVarChar, 1000).Value = cboClient.GetItemText(cboClient.SelectedValue)
        Dim dt As New DataTable("tblProject")
        cnn.Open
        Using reader As SqlDataReader = cmd.ExecuteReader()
            dt.Load(reader)
        End Using
        ' do something with dt
    End Using
End Sub

Note correct use of Using. Do NOT create a "global" connection or command, only create when needed and dispose immediately with Using.

---

An even better idea is to use an ORM such as EF Core, or a micro-ORM such as Dapper.

Answer 2

As suggested, don't use the older non .net ADO library. It REALLY does not play all that nice with .net code anyway.

As pointed out, you ALSO don't want to try and create some global connection. In the old days, often a developer would write code and try to "manage" the conneciton. This made sense in the old days, since the "development eviroment" did not manage and automatic "take care" of this issue for you. Now, .net does all this managment for you automatic.

What this means? You ALWAYS re-open and ALWAYS create a new connection object. However, behind the scenes, there is a bunch of worker bee's managing a connection pool for you! And if there is a existing connection in that pool? Then that will be used WHEN you create your new connection object! So, in .net, you the developer don't need (nor want) to bother trying to manage some global connection object - DO NOT try and do this anymore! You just wind up having a bunch of stray connection objects that don't dispose and clean up after themselves correctly.

So, the basic "design pattern" you are to adopt for your code?

It will look like this:

    Dim strParam As String = "Banff"
    Dim dt As New DataTable

    Using conn As New SqlConnection(My.Settings.TEST4)
        conn.Open()
        Using cmdSQL As New SqlCommand("dbo.sp_ProjectList", conn)
            cmdSQL.Parameters.Add("@ClientID", SqlDbType.NVarChar).Value = strParam
            cmdSQL.CommandType = CommandType.StoredProcedure
            dt.Load(cmdSQL.ExecuteReader)
        End Using
    End Using

    ' now view results
    For Each MyOneRow As DataRow In dt.Rows

        Debug.Print("Project name = " & MyOneRow("ProjectlName"))

    Next

Now even if this was old BASIC, old VB5, or even VBA?

Well, it really does not make sense to type such code "over and over". Hence, I grasp and understand why you "already" attempted to create that connection object (its a pain to have to create the connection object over and over).

However, even in the old days, it makes little sense to write all that code over and over. Just create a standard code module, and dump in these 3 routines into a standard code module:

Public Function MyRst(strSQL As String) As DataTable

    Dim rstData As New DataTable
    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL As New SqlCommand(strSQL, conn)
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
        End Using
    End Using

    Return rstData

End Function

Public Function MyRstP(cmdSQL As SqlCommand) As DataTable

    Dim rstData As New DataTable
    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL
            cmdSQL.Connection = conn
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
        End Using
    End Using

    Return rstData

End Function


Public Sub MyRstN(cmdSQL As SqlCommand)

    ' this will runa update command, or a stored procedure
    ' but does NOT return data

    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL
            cmdSQL.Connection = conn
            conn.Open()
            cmdSQL.ExecuteNonQuery()
        End Using
    End Using

End Sub

So, we have 3 routines:

MyRst - returns a data table with given SQL

MyRstP - returns a data table based on a SQL command object

MyRstN - runs SQL - great for update statements and routines that don't return data.

So, now when you have to write code, you have 3 "helper" routines.

So, then now we can do this in our code:

    Dim strSQL As String =
        "SELECT * FROM tblHotels
        ORDER BY HoteName"

    Dim tblHotels As DataTable = MyRst(strSQL)

    ' do whatever with our table of hotels here

Or, say call a stored procedure that we pass the City as a parameter

    Dim cmdSQL As New SqlCommand("dbo.GetHotels2")
    cmdSQL.Parameters.Add("@City", SqlDbType.NVarChar).Value = "Edmonton"
    cmdSQL.CommandType = CommandType.StoredProcedure

    Dim tblHotels As DataTable = MyRstP(cmdSQL)

    ' do whatever with our table of hotels here

And the last one, MyRstN? That does NOT return a table, but lets us run SQL as we want, say like this:

    Dim intHotelPK As Integer = 75 ' test example
    Dim strSQL As String =
        "Update tblHotels SET Descripiton = @Descript
        WHERE ID = @ID"

    Dim cmdSQL As New SqlCommand(strSQL)
    cmdSQL.Parameters.Add("@ID", SqlDbType.Int).Value = intHotelPK
    cmdSQL.Parameters.Add("@Descript", SqlDbType.NVarChar).Value = "Free wi-fi included"
    MyRstN(cmdSQL)

And for the global connection string?

Go Project->project settings, and on the settings page, use the database connection wizard to setup the connection for you.

Hence this:

Click on the [...], and you thus get the standard SQL connection dialog. In my case, I used TEST4. Hence, My.Settings.TEST4 will return this connection string. So, setup a global connection string using the "settings" page, and that eliminates the need to "hard code" such values in your code.

It stands to reason that the .net data objects are going to work MUCH better then trying to use the older ADO library in .net - don't do it!

And with those 3 helper routines, you find you are writing LESS code then even what you did in the old days.

So, now with our above helper routines setup?

Then your code becomes this:

    Dim strParam As String = "123" ' this SHOULD be a integer type!!!!
    Dim cmdSQL As New SqlCommand("dbo.sp_ProjectList")
    cmdSQL.Parameters.Add("ClientID", SqlDbType.Int).Value = strParam
    cmdSQL.CommandType = CommandType.StoredProcedure

    Dim dt As DataTable = MyRstP(cmdSQL)

    ' do whatever with your table here
    For Each OneRow As DataRow In dt.Rows
        Debug.Print("Project name = " & OneRow("ProjectName"))
    Next

Note how simple, how clean the code becomes. So, yes, I do strong recommend that you use the .net ADO library - not the older outdated legacy ADO library.