Unable to insert data into MySQL table via ASP.NET Web Application

Question

I have installed MySQL on my local computer and am trying to enable insertion of data to a created MySQL table from my ASP.NET web application on Visual Studio 2017. I've managed to set up a MySQL connection successfully and created table called 'events'.

Initially, I do not have any syntax or system errors, using the code below, but whenever I try to insert data from my web form upon clicking, no data is inserted into the MySQL table. Any advice if there is anything wrong in my code or any other file configurations?

Later, after I tried installing Connector for .NET, I still cannot insert data into the MySql and I get the below errors in namespace MySqlConnection stating 'the-type-mysqlconnection-exists-in-both-mysql-data-issue' as seen below

Any advice what can be corrected to be able to insert data into my table? Is it in my Insert code or does problem lie elsewhere? Below are my code:

AddEvent.aspx.cs:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;

namespace Prototype3
{
   public partial class AddEvent : System.Web.UI.Page
   {
     //convert event date input into DateTime format on back-end of page 
     string dateStr = dateinput1.Value;
     DateTime date;

     //Saving data on added event into MySQL database
     string constring = "server=localhost;user id=***********; 
     password=***********!; 
     persistsecurityinfo=True;database=homesecretary";

      //Connection to database below-check!!
      string Query = "INSERT into events (eventTitle) VALUES 
      ('"+tb_eventTitle.Text+"')"; //Parse date input from jQuery 
      datepicker 
      MySqlConnection connDataBase = new MySqlConnection(constring); 

      MySqlCommand cmdDataBase = new MySqlCommand(Query, connDataBase);
      MySqlDataReader myReader;

      try
         {
             connDataBase.Open();
             myReader = cmdDataBase.ExecuteReader();
             connDataBase.Close();
         }

Web.config:

  <configuration>
   <connectionStrings>
   <add name="homesecretaryConnectionString" 
   connectionString="server=localhost;user 
   id=********; password=********; persistsecurityinfo=True; 
   database=homesecretary" providerName="MySql.Data.MySqlClient" />
   </connectionStrings>
   .....
  </configuration>

Note that the visual-studio tag description says "DO NOT use this tag on questions regarding code which merely happened to be written in Visual Studio." Please edit your question to remove it. — Richardissimo
– Richardissimo, Commented Jul 31, 2018 at 12:33
Unrelated tips: SqlConnection and SqlCommand are both IDisposable so each should be in a using block. Then you won't need to explicitly Close it, since exiting the block calls Dispose which calls Close. Avoid string concatenation to construct queries or the code becomes vulnerable to SQL injection attacks: use SQL parameters. — Richardissimo
– Richardissimo, Commented Jul 31, 2018 at 12:42
And although you have put your connection string in config, the code doesn't use it, it just duplicates it - add a reference to System.Configuration and use ConfigurationManager.ConnectionStrings. — Richardissimo
– Richardissimo, Commented Jul 31, 2018 at 12:44

Ehsan Sajjad · Accepted Answer · 2018-07-30 14:25:43Z

1

You are doing a very basic/fundamental mistake here.

The MySqlDataReader purpose is to use when we are selecting data. You need to insert data not query so it should be calling ExecuteNonQuery() method:

// for inserts/updates queries
cmdDataBase.ExecuteNonQuery()

Please refer to the following docs for seeing an example on how to insert data in ADO.NET:

MSDN - Insert New Records in to Database

Note: Please don't do string concatenation for creating queries as it is not a good practice instead of that you should be using Parameterized queries. See an example here

edited Jul 30, 2018 at 14:25

answered Jul 30, 2018 at 14:20

Ehsan Sajjad

62.6k16 gold badges112 silver badges171 bronze badges

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

Comments

Afnan Ahmad · Accepted Answer · 2018-07-30 14:41:01Z

To update/insert the data you may need to use ExecuteNonQuery and to fill the DataSet you may use MySqlDataAdapter as follow:

private static string _DBConnectionString = string.Empty;    
public static int SetData(string sqlQuery)
{
    int iReturn = -1;
    try
    {
        MySqlCommand cmd = new MySqlCommand(sqlQuery);
        using (MySqlConnection conn = new MySqlConnection(DBConnectionString))
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            iReturn = cmd.ExecuteNonQuery();

            if (conn.State == ConnectionState.Open)
                conn.Close();
        }
    }
    catch (Exception E)
    {
        iReturn = -1;

    }
    return iReturn;
}

public static object GetData(string sqlQuery)
{
    DataSet dtSet = new DataSet();
    try
    {
        MySqlCommand cmd = new MySqlCommand(sqlQuery);
        MySqlDataAdapter adpt = new MySqlDataAdapter();

        using (MySqlConnection conn = new MySqlConnection(DBConnectionString))
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.CommandTimeout = 0;
            adpt.SelectCommand = cmd;

            cmd.Connection = conn;
            adpt.Fill(dtSet);


            if (conn.State == ConnectionState.Open)
                conn.Close();
        }
    }
    catch (Exception E)
    {   

    }
    return dtSet;
}

Collectives™ on Stack Overflow

Unable to insert data into MySQL table via ASP.NET Web Application

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related