Incorrect syntax near store procedure error

Question

I'm updating a long list of records. In my code, everything run as predicted until it execute the query. It gave me a incorrect syntax near 'TempUpdatePhysicalCityStateZip'. (My store proc name). I've test it on the sql studio and it run fine. So, I'm not quite sure where i got it wrong. Below is my store proc. and code.

ALTER PROCEDURE [dbo].[TempUpdateCityStateZip] 
    -- Add the parameters for the stored procedure here
    @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.

Answer 3

This is one time code to split a CityZipState into City, Zip and State. The data look like this (WAYNE PA 77035). What i'm trying to do is split them up and insert them into its own separate record.