存储过程错误附近的语法不正确

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'

'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:

(我的存储过程名称)。我已经使用SQL Server Management Studio对其进行了测试,运行正常。所以,我不太确定我错在哪里。下面是我的存储过程和代码:

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();
    }
}

2 个解决方案

#1

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

如果您不指定命令类型,我相信您可以得到令人费解的错误消息:

cmdUpdate.CommandType = CommandType.StoredProcedure;

#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.

FWIW,那是一段很脏的代码,你可能会有很多问题试图维护它。出于性能原因,您可能希望在打开连接之前解析CityStateZipList,这样您就不会将其保持打开的时间超过您的需要。

#1