Error: A SQLParamenter wtih ParameterName @myparm is not contained by this SQLParameter Collection

Question

Good Morning,

I'm working on an ASP.NET 3.5 webforms application and have written the following code:

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
    Dim connectionString As String = WebConfigurationManager.ConnectionStrings("Diel_inventoryConnectionString").ConnectionString
    Dim con As New SqlConnection(connectionString)
    Dim adapter1 As New SqlDataAdapter

    adapter1.SelectCommand = New SqlCommand
    adapter1.SelectCommand.CommandType = CommandType.StoredProcedure
    adapter1.SelectCommand.CommandText = "PartSproc"

    Dim parmNSN As New SqlParameter("@NSN", SqlDbType.NVarChar)
    Dim parmName As New SqlParameter("@PartName", SqlDbType.NVarChar)
    txtNSN.Text = adapter1.SelectCommand.Parameters("@NSN").Value
    txtSearch.Text = adapter1.SelectCommand.Parameters("@PartName").Value

    Dim dt As New DataTable()
    adapter1.Fill(dt)
    MySearch.DataSource = dt
    MySearch.DataBind()

    End Sub

When I run the page, I receive the error A SQLParameter with @NSN is not contained by this SQLParameter Collection. I tried using apostrophes around the @NSN and @PartName but that does not work either and presents expression expected error.

How might I rectify the above code so that it references the @NSN and @PartName parameters correctly?

EDIT: New Code below

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
    Dim connectionString As String = WebConfigurationManager.ConnectionStrings("Diel_inventoryConnectionString").ConnectionString
    Dim con As New SqlConnection(connectionString)
    Dim cmd As New SqlCommand("PartSproc", con)
    cmd.CommandType = CommandType.StoredProcedure
    Dim adapter1 As New SqlDataAdapter

    Dim parmNSN As New SqlParameter("@NSN", SqlDbType.NVarChar)
    Dim parmName As New SqlParameter("@PartName", SqlDbType.NVarChar)
    adapter1.SelectCommand.Parameters.Add(parmNSN)
    adapter1.SelectCommand.Parameters.Add(parmName)
    adapter1.SelectCommand.Parameters("@NSN").Value = txtNSN.Text
    adapter1.SelectCommand.Parameters("@PartName").Value = txtSearch.Text
    Using con
        Dim dt As New DataTable()
        adapter1.SelectCommand = cmd
        adapter1.Fill(dt)
        MySearch.DataSource = dt
        MySearch.DataBind()
    End Using
End Sub

I now receive an error Object reference not set to an instance of an object referencing the add parameter parmNSN. Do I really need those to add paraameter statements since I've already set them equal to the text boxes below?

Thanks, Sid

Answer 1

Did you read the error message? The Sqlparameter you create is never added to the Sqlparameter collection of the command ;) This is what the error message also ells you.

You create the parameters, but they "hang in the air" so to say.

Answer 2

I think you accidentally transposed the assignment of the parameter values - try this instead:

adapter1.SelectCommand.Parameters("@NSN").Value = txtNSN.Text
adapter1.SelectCommand.Parameters("@PartName").Value = txtSearch.Text

Answer 3

To add to what Andrew & TomTom said, you need to add the parameters to the parameters collection of SelectCommand.

Something like

adapter1.SelectCommand.Parameters.Add(parmNSN);
adapter1.SelectCommand.Parameters.Add(parmName);

Do this before you make a call to ExecuteReader() or ExecuteScalar