MySql Update Query not working, in asp.net

Question

I submit the query, and it doesn't throw an exception, and it does write to the database, but not what I want. For some reason it doesn't touch anything apart from the first thing I tell the query, and sets that to a 0.

Here is my code:

    public static int UpdateTagDefinition(Dictionary<string, object> sqlQueryParams)
    {
        MySqlCommand query = new MySqlCommand();
        query.CommandText = "UPDATE `windows_dev` SET `name` = @name AND `desc_short` = @desc_short AND `desc_long` = @desc_long AND `tags` = @tags AND `image_title` = @image_title AND `images` = @images AND `release_date` = @release_date AND `update_date` = @update_date AND `is_on_sourcecontrol` = @is_on_sourcecontrol AND `sourcecontrol_type` = @sourcecontrol_type AND `sourcecontrol_uri` = @sourcecontrol_uri AND `download_uri` = @download_uri AND `project_uri` = @project_uri AND `source_uri` = @source_uri AND `is_public` = @is_public WHERE `DID` = @did;";
        query.Parameters.Clear();
        foreach(var sqlParam in sqlQueryParams)
        {
            query.Parameters.AddWithValue("@" + sqlParam.Key.ToString(), sqlParam.Value);
        }

        return Connector.RunSQLUpdateQuery(query);
    }

    public static int RunSQLUpdateQuery(MySqlCommand query)
    {
        try
        {
            MySqlConnection cnx = new MySqlConnection(ConfigurationManager.ConnectionStrings["******"].ConnectionString);

            // Connect to News Database and get recent article
            query.Connection = cnx;
            cnx.Open();
            MySqlDataReader Reader;
            Reader = query.ExecuteReader();
            cnx.Close();

            return 1;
        }
        catch
        {
            return 2;
        }
    }

And the place where I set the SQL Params;

            Dictionary<string, object> sqlQueryParams = new Dictionary<string, object>();
            sqlQueryParams.Add("name", Request.Form["name"]);
            sqlQueryParams.Add("desc_short", Request.Form["desc_short"]);
            sqlQueryParams.Add("desc_long", Request.Form["desc_long"]);
            sqlQueryParams.Add("tags", TagDefinitions.TagDefinitionsToSQL(TagDefinitions.GetTagDefinitionIdentsFromList((List<string>)JsonConvert.DeserializeObject(Request.Form["tags"], typeof(List<string>)))));
            sqlQueryParams.Add("image_title", Request.Form["image_title"]);
            sqlQueryParams.Add("images", Request.Form["images"]);
            sqlQueryParams.Add("release_date", Request.Form["release_date"]);
            sqlQueryParams.Add("update_date", Request.Form["update_date"]);
            sqlQueryParams.Add("is_on_sourcecontrol", Request.Form["is_on_sourcecontrol"]);
            sqlQueryParams.Add("sourcecontrol_type", Request.Form["sourcecontrol_type"]);
            sqlQueryParams.Add("sourcecontrol_uri", Request.Form["sourcecontrol_uri"]);
            sqlQueryParams.Add("download_uri", Request.Form["download_uri"]);
            sqlQueryParams.Add("project_uri", Request.Form["project_uri"]);
            sqlQueryParams.Add("source_uri", Request.Form["source_uri"]);
            sqlQueryParams.Add("is_public", Request.Form["is_public"]);
            sqlQueryParams.Add("did", int.Parse(Request.Form["did"]));

John Woo · Accepted Answer · 2012-12-25 12:46:36Z

8

when you want to update multiple columns, use COMMA instead of AND

query.CommandText = @"UPDATE `windows_dev` 
                      SET    `name` = @name, 
                             `desc_short` = @desc_short, 
                             ... other columns .....
                             `is_public` = @is_public 
                      WHERE `DID` = @did";

answered Dec 25, 2012 at 12:46

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alexander Forbes-Reed Over a year ago

This worked perfectly, thanks. Also, thanks for the tip on being able to use @ for multiline quotes.

Collectives™ on Stack Overflow

MySql Update Query not working, in asp.net

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related