Incorrect syntax near stored procedure error

Question

I'm updating a long list of records. In my code, everything run as predicted until it execute the query. I get an

Incorrect syntax near 'TempUpdatePhysicalCityStateZip'

(my stored procedure name). I've tested it with SQL Server Management Studio and it runs fine. So, I'm not quite sure where I got it wrong. Below is my stored procedure and code:

ALTER PROCEDURE [dbo].[TempUpdateCityStateZip] 
    @StoreNo nvarchar (11),
    @City nvarchar(50),
    @State nvarchar(2),
    @Zip nvarchar(5)    
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE StoreContact
    SET City = @City, State = @State, Zip = @Zip
    WHERE StoreNo = @StoreNo
END

Here is my code:

Dictionary<string, string> CityStateZipList = getCityStateZipList(dbPath);

using (SqlConnection conn = new SqlConnection(dbPath))
{
    conn.Open();

    SqlCommand cmdUpdate = new SqlCommand("TempUpdateCityStateZip", conn);

    foreach (KeyValuePair<string, string> frKeyValue in CityStateZipList)
    {
        cmdUpdate.Parameters.Clear();

        string[] strCityStateZip = frKeyValue.Value.Split(' ');
        cmdUpdate.Parameters.AddWithValue("StoreNo", frKeyValue.Key.ToString());

        foreach (String i in strCityStateZip)
        {
            double zipCode;

            if (i.Length == 2)
            {
                cmdUpdate.Parameters.AddWithValue("State", i);
            }
            else if (i.Length == 5 && double.TryParse(i, out zipCode))
            {
                cmdUpdate.Parameters.AddWithValue("Zip", i);
            }
            else
            {
                cmdUpdate.Parameters.AddWithValue("City", i);
            }
        }

        cmdUpdate.ExecuteNonQuery();
    }
}

Answer 1

I believe you can get that puzzling error message if you don't specify the command type:

cmdUpdate.CommandType = CommandType.StoredProcedure;

Answer 2

Don't you need the @ sign before the parameter?

 cmdUpdate.Parameters.AddWithValue("@State", i);

FWIW, Thats kind of a dirty piece of code there, you will probably have many issues trying to maintain that. For performance reasons you may want to parse out the CityStateZipList before you open the connection, that way you aren't keeping it open longer than you need.