SqlParameter with ParameterName is not contained...

  • Thread starter Thread starter Phenom
  • Start date Start date
P

Phenom

I'm receiving the following error when I try to use a stored proc in my
code:
SqlParameter with ParameterName '@unionRep' is not contained by this
SqlParameterCollection

I have searched several newsgroups and found similar situations, but so
far none of the solutions have worked for me.
*Note, I have tried setting the connection and opening it both before
and after setting the command text.
Please help!
Thanks in advance.

Here is the code I have:
SqlSelectCommand1.CommandType = CommandType.StoredProcedure
SqlSelectCommand1.CommandText = "sp_UpdateContractOut1"

Dim param As SqlParameter = New SqlParameter("@unionRep",
SqlDbType.NVarChar, 50)
Dim param1 As SqlParameter = New SqlParameter("@unionSign",
SqlDbType.SmallDateTime)
Dim param2 As SqlParameter = New
SqlParameter("@areaUnionRep", SqlDbType.NVarChar, 50)
Dim param3 As SqlParameter = New
SqlParameter("@areaUnionRepSign", SqlDbType.SmallDateTime)
Dim param4 As SqlParameter = New SqlParameter("@unionComm",
SqlDbType.NVarChar, 255)
Dim param5 As SqlParameter = New
SqlParameter("@companyCoord", SqlDbType.NVarChar, 50)
Dim param6 As SqlParameter = New
SqlParameter("@companySign", SqlDbType.SmallDateTime)
Dim param7 As SqlParameter = New SqlParameter("@maintSupt",
SqlDbType.NVarChar, 50)
Dim param8 As SqlParameter = New
SqlParameter("@maintSuptSign", SqlDbType.SmallDateTime)
Dim param9 As SqlParameter = New SqlParameter("@coordComm",
SqlDbType.NVarChar, 255)
Dim param10 As SqlParameter = New SqlParameter("@ID",
SqlDbType.Int)

SqlSelectCommand1.Parameters("@unionRep").Value =
unionRep.Text
SqlSelectCommand1.Parameters("@unionSign").Value =
unionSign.Text
SqlSelectCommand1.Parameters("@areaUnionRep").Value =
areaUnionRep.Text
SqlSelectCommand1.Parameters("@areaUnionRepSign").Value =
areaUnionRepSign.Text
SqlSelectCommand1.Parameters("@unionComm").Value =
unionComm.Text
SqlSelectCommand1.Parameters("@companyCoord").Value =
companyCoord.Text
SqlSelectCommand1.Parameters("@companySign").Value =
companySign.Text
SqlSelectCommand1.Parameters("@maintSupt").Value =
MaintSupt.Text
SqlSelectCommand1.Parameters("@maintSuptSign").Value =
maintSuptSign.Text
SqlSelectCommand1.Parameters("@coordComm").Value =
coordComm.Text
SqlSelectCommand1.Parameters("@ID").Value = txtWO.Text


SqlSelectCommand1.Connection = SqlConnection1
SqlConnection1.Open()
Try
SqlSelectCommand1.ExecuteNonQuery()
lblUpdateSuccess.Visible = True
btnSuccess.Visible = True
Catch ex As Exception

lblFail.Visible = True
btnFail.Visible = True

Finally
SqlConnection1.Close()
End Try
 
I solved my own problem. But for future reference, in case someone has
a similar instance, I replaced the following code:
'Dim param As SqlParameter = New SqlParameter("@unionRep",
SqlDbType.NVarChar, 50)
'Dim param1 As SqlParameter = New
SqlParameter("@unionSign", SqlDbType.SmallDateTime)
'Dim param2 As SqlParameter = New
SqlParameter("@areaUnionRep", SqlDbType.NVarChar, 50)
'Dim param3 As SqlParameter = New
SqlParameter("@areaUnionRepSign", SqlDbType.SmallDateTime)
'Dim param4 As SqlParameter = New
SqlParameter("@unionComm", SqlDbType.NVarChar, 255)
'Dim param5 As SqlParameter = New
SqlParameter("@companyCoord", SqlDbType.NVarChar, 50)
'Dim param6 As SqlParameter = New
SqlParameter("@companySign", SqlDbType.SmallDateTime)
'Dim param7 As SqlParameter = New
SqlParameter("@maintSupt", SqlDbType.NVarChar, 50)
'Dim param8 As SqlParameter = New
SqlParameter("@maintSuptSign", SqlDbType.SmallDateTime)
'Dim param9 As SqlParameter = New
SqlParameter("@coordComm", SqlDbType.NVarChar, 255)
'Dim param10 As SqlParameter = New SqlParameter("@ID",
SqlDbType.Int)

With this:

SqlSelectCommand1.Parameters.Add("@unionRep",
SqlDbType.NVarChar, 50)
SqlSelectCommand1.Parameters.Add("@unionSign",
SqlDbType.SmallDateTime)
SqlSelectCommand1.Parameters.Add("@areaUnionRep",
SqlDbType.NVarChar, 50)
SqlSelectCommand1.Parameters.Add("@areaUnionRepSign",
SqlDbType.SmallDateTime)
SqlSelectCommand1.Parameters.Add("@unionComm",
SqlDbType.NVarChar, 255)
SqlSelectCommand1.Parameters.Add("@companyCoord",
SqlDbType.NVarChar, 50)
SqlSelectCommand1.Parameters.Add("@companySign",
SqlDbType.SmallDateTime)
SqlSelectCommand1.Parameters.Add("@maintSupt",
SqlDbType.NVarChar, 50)
SqlSelectCommand1.Parameters.Add("@maintSuptSign",
SqlDbType.SmallDateTime)
SqlSelectCommand1.Parameters.Add("@coordComm",
SqlDbType.NVarChar, 255)
SqlSelectCommand1.Parameters.Add("@ID", SqlDbType.Int)
 
1) Never use "sp_" as the prefix to a stored procedure. It make the system
think that it's stored in the master database.
2) Ah, you made one little mistake. You forgot to add the SqlParameter
instances to the command.Parameters collection.

I usually create my Parameters collection like this:

cmd = New SqlCommand(....
With cmd
.Parameters.Add("@parmName", ....).Value = "fred"


End With

hth


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Yes, I've never heard what you said about naming stored procs 'sp'.
It's the naming standard where I work and has been done since before I
started to work here.
It's not a hard and fast rule, however, so I can always name them
something else in the future.
 
Back
Top