CODEWITHSUNDEEP
sql-serverstored-proceduresado.net.net-3.5
Jack
Jack

Reputation: 10037

Incorrect syntax near stored procedure error

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

Upvotes: 5

Views: 5601

Answers (2)

StingyJack
StingyJack

Reputation: 19459

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

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

FWIW, that is 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.

Upvotes: 1

DOK
DOK

Reputation: 32831

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

cmdUpdate.CommandType = CommandType.StoredProcedure;

Upvotes: 8

Related Questions