ASP.NET - Unable to insert data into database

Question

I know that the connection string is not the problem because I can read data from the database fine but I cannot figure out why I cannot insert data into the database.

.aspx file

<div class="column one-second">
  <asp:TextBox placeholder="Your name" type="text" name="name" id="namelbl" size="40" aria-required="true" aria-invalid="false" runat="server"></asp:TextBox>
</div>

<div class="column one-second">
  <asp:TextBox placeholder="location" type="text" name="location" id="LocationLbl" size="40" aria-required="true" aria-invalid="false" runat="server"></asp:TextBox>
</div>
<div class="column one">
  <asp:TextBox placeholder="Body" type="text" name="text" id="TextLBL" size="40" aria-required="true" aria-invalid="false" runat="server"></asp:TextBox>
</div>
<div class="column one">
  <asp:FileUpload id="FileUpload1" runat="server"> </asp:FileUpload>

  <asp:Label ID="lblmessage" runat="server" />
</div>
<div class="column one">
  <asp:Button id="submit" Text="Submit" runat="server" OnClick="submit_Click"> </asp:Button>
</div> 

C# function

protected void submit_Click(object sender, EventArgs e)
{
    Console.WriteLine("BUTTON CLICKED");
    string constr = ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;
    using (MySqlConnection con = new MySqlConnection(constr))
    {
        string query = "INSERT INTO blo(Title, post, location) VALUES (@Title, @post, @location)";
        using (MySqlCommand cmd = new MySqlCommand(query))
        {
            cmd.Connection = con;

            string title = namelbl.Text;
            Console.WriteLine(title);
            cmd.Parameters.AddWithValue("Title", title);
            string post = TextLBL.Text;
            cmd.Parameters.AddWithValue("post", post);
            string location = LocationLbl.Text;
            cmd.Parameters.AddWithValue("location", location);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

Answer 1

Your query was not able to work as you mistype the parameter name in your .AddWithValue().

cmd.Parameters.AddWithValue("Title", title);
cmd.Parameters.AddWithValue("post", post);
cmd.Parameters.AddWithValue("location", location);

The correct way should be:

cmd.Parameters.AddWithValue("@Title", title);
cmd.Parameters.AddWithValue("@post", post);
cmd.Parameters.AddWithValue("@location", location);

---

RECOMMENDATIONS

1. It is recommended not to use `.AddWithValue()` as the concern mentioned in this [article](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). Please ensure that you need to pass the value with **exact datatype and length** that matches the respective table column in the `SqlParameter`.

cmd.Parameters.Add("@Param", <<MySqlDbType>>, <<Length>>).Value = value;

2. As you apply using block for MySqlConnection, MySqlCommand, you don't have to manually call con.Close() as these IDisposable objects will dispose the resources & connection automatically as mentioned in this article.

3. (Optional) Add try catch block and get value from ExecuteNonQuery() for verifying the record is inserted into the database and exception handling.

• Exception handling will be useful in debugging and handling the exception hit during the execution. At the same time, you can provide a meaningful error message to notify the users.

• ExecuteNonQuery() able to return the value indicate

The number of rows affected.

• Hence, this will also be useful to return a useful message to notify whether the record is successfully inserted/updated into the database or not.

In the end, your code should be:

try
{
    using (MySqlConnection con = new MySqlConnection(constr))
    {
        string query = "INSERT INTO blo(Title, post, location) VALUES (@Title, @Post, @Location)";
        using (MySqlCommand cmd = new MySqlCommand(query))
        {
            cmd.Connection = con;

            string title = namelbl.Text;
            Console.WriteLine(title);
            cmd.Parameters.AddWithValue("@Title", title);
        
            string post = TextLBL.Text;
            cmd.Parameters.AddWithValue("@Post", post);

            string location = LocationLbl.Text;
            cmd.Parameters.AddWithValue("@Location", location);
        
            con.Open();
            var recordAffected = (int)cmd.ExecuteNonQuery();

            if (recordAffected > 0)
            {
                // Record successfully inserted case
            }
            else
            {
                // Record fail inserted case
            }
        }
    }
}
catch (Exception ex)
{
    // Handling exception
}

Edited: Much appreciated and credited to the comment provided by @BradleyGrainger, I had 'strikethrough' recommendation 1 as the concerns mentioned in Can We Stop Using .AddWithValue() are handled by MySql.

Start Using AddWithValue

The primary reason that AddWithValue is OK to use is that MySQL’s text protocol is not typed in a way that matters for client-side type inference.