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
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
2 Answers
Reset to default 3You 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.
As suggested, don't use the older non ADO library. It REALLY does not play all that nice with 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, 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 , 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 data objects are going to work MUCH better then trying to use the older ADO library in - 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 ADO library - not the older outdated legacy ADO library.
sp_prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoidsp_and use something else as a prefix - or no prefix at all! – marc_s Commented Feb 17 at 15:50