Cannot find stored procedure

Question

I have many stored procedure calls in my C# code, but only this one keeps failing. I'm running VS 2012 and SQL Server 2008 R2. My connection string is the same for all my stored procedures and I have the same permissions on all of them.

I get this error

Could not find stored procedure 'StP_Map_Preload @bldg, @linePos, @startD, @lineNo, @Pgrm, @apPos, @sessionID'.
System.Exception {System.Data.SqlClient.SqlException}

on this line:

SqlDataReader dr = cmd.ExecuteReader();:

I have tried creating a new stored procedure with the same code, setting permissions, but it fails too.

public ArrayList DetailPreload(string bldg, string linePos, DateTime startD, string lineNo, string Pgrm, int apPos, string sessionID)
{
    string strSQL = "StP_Map_Preload @bldg, @linePos, @startD, @lineNo, @Pgrm, @apPos, @sessionID";

    ArrayList list = new ArrayList();

    using (SqlConnection conStr = new SqlConnection(connM))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Clear();

            cmd.Parameters.AddWithValue("@bldg", bldg);
            cmd.Parameters.AddWithValue("@linePos", linePos);
            cmd.Parameters.AddWithValue("@startD", startD);
            cmd.Parameters.AddWithValue("@lineNo", lineNo);
            cmd.Parameters.AddWithValue("@Pgrm", Pgrm);
            cmd.Parameters.AddWithValue("@apPos", apPos);
            cmd.Parameters.AddWithValue("@sessionID", sessionID);

            cmd.CommandText = strSQL;
            cmd.Connection = conStr;

            conStr.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                list.Add(new
                        {
                            company = dr["Company"],
                            Pgrm = dr["Pgrm"],
                            lineNum = dr["lineNum"],
                            lineStation = dr["lineStation"],
                            jobCount = dr["Job"],
                            item = dr["Item"],
                            qty = dr["Qty"],
                            partQty = dr["partQty"],
                            invCnt = dr["invCnt"],
                            runID = dr["runID"]
                        });
            }

            conStr.Close();

            return list;
        }
    }
}

My stored procedure is in SQL Server and I can execute it in SQL Server

/*
StP_Map_Preload 'AA-12', '7', '09/19/2014', '', '247', 7, 'val2gxfh5ihoqy4tshzl4tp3'
*/
ALTER proc [dbo].[StP_Map_Preload] 
      @Bldg varchar(10), 
      @linePos varchar(5), 
      @startD date, 
      @LineNo varchar(5), 
      @Pgrm varchar(5), 
      @apPos int, 
      @sessionID varchar(50) 
AS 
BEGIN
    declare @sql varchar(Max), @PST varchar(20), 
            @SI1 varchar(5), @SI2 varchar(5), @SI3 varchar(5), 
            @hasAP bit, @CCLen varchar, 
            @Co varchar(5), @CCs varchar(25), @lsGrp varchar(max)...

Answer 1

Change your line to

string strSQL = "StP_Map_Preload";

There is no need to list the parameters in the string that names the stored procedure.