CODEWITHSUNDEEP
c#asp.net-mvc
Andreas Panteli
Andreas Panteli

Reputation: 47

Update an Entry into mysql database

I am new in coding and i was recently chosen to start training in C# and Asp.Net. I am trying to update an entry into the database but to no avail. The delete, create and read functions work properly. (i know that my code lacks try and catch)

I 've tried different solutions that i found while googling but since i am still training i never fully understand if what i am following is completely correct. Currently i am able to diplay the fields with their info when navigating to the "Article" i am trying to edit but when i click the Save button nothing happens.

This is in my Article Context file:

public Article EditSingleArticle(int id, int userID, string Title, string Body)
    {
        Article article = new Article();
        using (MySqlConnection conn = GetConnection())
        {
            MySqlCommand cmdslct = new MySqlCommand("SELECT * FROM Article WHERE ID=@id", conn);
            MySqlCommand cmdul = new MySqlCommand("UPDATE Article SET userID=@userID,Title=@Title,Body=@Body WHERE ID=@id", conn);
            conn.Open();
            cmdslct.Parameters.AddWithValue("@ID", id);
            cmdslct.ExecuteNonQuery();
            cmdul.Parameters.AddWithValue("@userID", article.userID);
            cmdul.Parameters.AddWithValue("@Title", article.Title);
            cmdul.Parameters.AddWithValue("@Body", article.Body);
            cmdul.ExecuteNonQuery();
            using (MySqlDataReader reader = cmdslct.ExecuteReader())
            {
                while (reader.Read())
                {
                    article = new Article()
                    {
                        userID = reader.GetInt32("userID"),
                        ID = reader.GetInt32("ID"),
                        Title = reader.GetString("Title"),
                        Body = reader.GetString("Body")
                    };
                }
            }
            conn.Close();
        }

The code below is in my ArticleController file:

public IActionResult Edit(int id, int userID, string Title, string Body)
    {
        ArticleContext context = HttpContext.RequestServices.GetService(typeof(ArticleContext)) as ArticleContext;
        if (ModelState.IsValid)
        {
            return View(context.EditSingleArticle(id, userID, Title, Body));
        }
        return View();
    }

I don't get any error messages but the article i am trying to edit never updates

Upvotes: 0

Views: 692

Answers (2)

Aakanksha
Aakanksha

Reputation: 349

In your update query you are using a where condition which uses parameter @id. But you are not passing the ID argument to your query. Add a argument '@id' for the update query. Add this line to the code

cmul.Parameters.AddWithValue("@id", id);

Upvotes: 0

leandro.andrioli
leandro.andrioli

Reputation: 1047

The issue is that article object only have the default empty fields because you are calling cmdslct.ExecuteNonQuery() that does not return data at all, only the number of rows affected by an insert, update, or delete.

So you should call ExecuteReader() that returns an object that can iterate over the entire result set while only keeping one record in memory at a time.

Your code should looks like the following code in order to work as expected:

        Article article = new Article();
        using (MySqlConnection conn = GetConnection())
        {
            MySqlCommand cmdslct = new MySqlCommand("SELECT * FROM Article WHERE ID=@id", conn);
            MySqlCommand cmdul = new MySqlCommand("UPDATE Article SET userID=@userID,Title=@Title,Body=@Body WHERE ID=@id", conn);
            conn.Open();
            cmdslct.Parameters.AddWithValue("@ID", id);
            using (MySqlDataReader reader = cmdslct.ExecuteReader())
            {
                while (reader.Read())
                {
                    article = new Article()
                    {
                        userID = reader.GetInt32("userID"),
                        ID = reader.GetInt32("ID"),
                        Title = reader.GetString("Title"),
                        Body = reader.GetString("Body")
                    };
                }
            }
            cmdul.Parameters.AddWithValue("@userID", article.userID);
            cmdul.Parameters.AddWithValue("@Title", article.Title);
            cmdul.Parameters.AddWithValue("@Body", article.Body);
            cmdul.ExecuteNonQuery();

            conn.Close();
        }

Regards,

Upvotes: 1

Related Questions