Procedure expects @location parameter which was not supplied. It is driving me nuts

Question

I know this thread will probably get flagged as duplicate but I have reviewed all similar posts but none seems to help resolve this issue:

I have this code snip:

Dim loc As String = DirectCast(GridView1 _
            .FooterRow.FindControl("txtLocation"), TextBox).Text

cmd.CommandText = "insert into Locations(Location " & _
") values(@Location);" & _
"select LocationID, Location from locations"

cmd.Parameters.AddWithValue("@Location", SqlDbType.VarChar).Value = loc

Just to keeping it simple.

This works great.

Then I turned this inline code into stored procedure:

ALTER PROCEDURE [dbo].[spx_AddNewLoc]   
            @Location varchar(150)
AS
set nocount on
    BEGIN   --add new request type
     INSERT INTO Locations(Location)VALUES(@Location)
     SELECT LocationID, Location from locations
     END
set nocount off

Then called it in my codefile:

Dim loc As String = DirectCast(GridView1 _
            .FooterRow.FindControl("txtLocation"), TextBox).Text
cmd.CommandText = "spx_AddNewLoc "
cmd.Parameters.AddWithValue("@Location", SqlDbType.VarChar).Value = loc

I just keep getting "Procedure expects @location which was not supplied."

Any ideas what I am missing?

<FooterTemplate>
    <asp:TextBox ID="txtlocation" runat="server" placeholder="Please enter location here..." style="width:400px;"></asp:TextBox><br />
</FooterTemplate>     
<FooterTemplate>
    <asp:Button ID="btnAdd" runat="server" Text="Add" OnClick = "AddNewLocation" />
</FooterTemplate>

'//** latest code below

Protected Sub AddNewLocation(ByVal sender As Object, ByVal e As EventArgs)
    Dim IsAdded As Boolean = True
    Dim loc As String = DirectCast(GridView1 _
            .FooterRow.FindControl("txtLocation"), TextBox).Text
    Dim con As New SqlConnection(strConnString)
    Dim cmd As New SqlCommand()
    cmd.CommandType = CommandType.Text
    cmd.CommandText = "insert into Locations(Location " & _
    ") values(@Location);" & _
    "select LocationID, Location from locations"
    cmd.Parameters.Add("@Location", SqlDbType.VarChar).Value = loc
    If IsAdded = True Then
        lblMsg.Text = (Convert.ToString("'") & loc) + "' location added successfully!"
        lblMsg.ForeColor = System.Drawing.Color.Green
    Else
        lblMsg.Text = (Convert.ToString("Error while adding '") & loc) + "' locaton!"
        lblMsg.ForeColor = System.Drawing.Color.Red
    End If

    GridView1.DataSource = GetData(cmd)
    GridView1.DataBind()
End Sub

Answer 1

The reason for the error is because you have not set the CommandType to use CommandType.StoredProcedure. I have mocked up both examples to highlight this error.

This is the code prior to me setting the CommandType:

This is the code once I had set the CommandType:

This is the working code:

Using con As New SqlConnection(strConnString),
      cmd As New SqlCommand With {.CommandText = "spx_AddNewLoc", .CommandType = CommandType.StoredProcedure,
                                  .Connection = con}

    cmd.Parameters.Add("@Location", SqlDbType.VarChar).Value = location

    con.Open()

    cmd.ExecuteNonQuery()

End Using

Answer 2

Quickly translated from C#. I've tested it locally and it works. You should eventually wrap the code in a Using block to dispose and close the objects/connection properly.

Dim loc As String = "test"

Dim Command As New SqlCommand("spx_AddNewLoc")
Command.Parameters.Add("@Location", SqlDbType.VarChar).Value = loc

Dim Connection As New SqlConnection(connStr)
Command.Connection = Connection
Command.CommandType = CommandType.StoredProcedure
Connection.Open()

Command.ExecuteNonQuery()

Connection.Close()

C#

string storedProcedure = "spx_AddNewLoc";
string location = "Amsterdam";

using (SqlConnection connection = new SqlConnection(connStr))
using (SqlCommand command = new SqlCommand(storedProcedure, connection))
{
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("@Location", SqlDbType.VarChar).Value = location;

    connection.Open();
    command.ExecuteNonQuery();
}

Answer 3

As far as you have changed it to a Stored Procedure, you should change .CommandType of your SqlCommand.

cmd.CommandType = CommandType.StoredProcedure;

SqlCommand.CommandType Property

Answer 4

Rename AddWithValue to Add. In your code AddWithValue accepts value as second param and I suspect it makes your parameter @Location of other type than varchar, that's why it cannot be found on a procedure execute. https://msdn.microsoft.com/en-us/library/wbys3e9s(v=vs.110).aspx