SQL Parameter not found

Question

The error:

Procedure or Function '' expects parameter '@Param1' which was not supplied.

An excerpt of the stored procedure:

I have a stored procedure on a SQL Server 2012. The procedure looks something like this...

SELECT *
FROM Orders
WHERE Orders.CustomerID = @param1 AND 
    Orders.CustomerJoinDate = @param2

I call it from my code {Using Visual Studio 2008} like so...

The calling method in Visual Studios:

First I create an array of parameters I'm going to pass...

Dim Param() As SqlClient.SqlParameter = 
    New SqlClient.SqlParameter() 
        {
            New SqlClient.SqlParameter("@Param1", Me.cmbFilter1.Text), 
            New SqlClient.SqlParameter("@Param2", Me.cmbFilter2.Text)
        }

Then I loop through the parameters and add them to a command to be executed by a datareader.

The mSQLCmd is set to call the stored procedure described above...

mSQLCmd.Parameters.Clear()
mSQLCmd.CommandText = SQLCmd

For Each sParam As SqlClient.SqlParameter In Param
    mSQLCmd.Parameters.AddWithValue(sParam.ParameterName, sParam.Value)
Next

Error occurs when:

I try to run mSQL.ExecuteReader() Can someone point me in the right direction on this? I've verified that each parameter is included in the Param() with the correct values.

I've also tested the stored procedure on SQL Server and verified when the two necessary parameters are provided it works correctly. Something is wrong on the vb side.

Answer 1

If you're calling a stored procedure, you need to set the CommandType of the SqlCommand accordingly!

mSQLCmd.CommandText = SQLCmd

// add this line!
mSQLCmd.CommandType = CommandType.StoredProcedure

Otherwise the name of the stored procedure you're trying to call is interpreted as a SQL command you're trying to execute.