mvc asp validate query from controller to ajax

4300 views ajax
-5

Currently I have insert query to my table and I don't have an idea how can I validate it if the data is already exist in the table.

So for now I just used try-catch to handle the duplicate entry. I just wondering if I could return some text If value goes to catch then display it to my view as alert or something.

Here is my query from controller:

 public ActionResult AddUser(int id, string name, string age)
    {

        string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
        using (MySqlConnection con = new MySqlConnection(constr))
        {
            string sqlQuery = "INSERT INTO myTable (id, name, age) VALUES (@id, @name, @age)";
            MySqlCommand cmd = new MySqlCommand(sqlQuery, con);
            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@name", name);
            cmd.Parameters.AddWithValue("@age", age);
            con.Open();
            try {
                cmd.ExecuteNonQuery();
                con.Close();
                return RedirectToAction("Index");

            }
            catch (Exception )
            {
                con.Close();
                return this.Json("This Data already exist on table");
            }

        }
    }

And this is my script to read my controller query:

 function add(id, name, age) {

        var result = confirm("Are you want to add " + name + " to list?");
        if (result == true) {

            $.ajax({
                url: '/Home/AddUser',
                type: 'POST',
                data: {
                    'id': id,
                    'name': name,
                    'age': age,
                },
                success: function (data) {                                                    
                    alert('Data has been successfully added');
                },
                error: function (jqXhr, textStatus, errorThrown) {
                    alert(errorThrown);
                }
            });
        }
    }

So far I can successfully insert it to my table but if there is already exist and it's a duplicate it will do nothing so the user will not have a prompt if they already added it or already exist or not.

Any suggestions or comments. TIA.

answered question

Are you want to check duplicate inputs in server side? You can set up a stored procedure that accepts name parameter and execute it inside the controller action, then check with if condition.

is this .net core

1 Answer

5

Consider creating a constraint in your database table (unique constraint on that column(s)). That will prevent any duplicate records to be saved even if it passes your C# code which is talking to the database.

Add a check to see whether the record exist, just before executing the INSERT statement. You can write a SQL statement which checks before the insertion step. May be a stored procedure which has this SQL Scripts and you may call the stored procedure from your c# method.

You should also consider returning a JSON structure which can tell the client side code whether the record was successfully inserted or duplicate found or code crashed.

public ActionResult AddUser(int id, string name, string age)
{
    try 
    {
       // your existing code to insert record to db
       return Json(new { status="success", message = "Successfully saved"});
    }
    catch (Exception ex)
    {
       // to do : log the exeception
       return Json(new { status="error", message = "Error in saving"});
    }
}

and in your success handler, check the status property of the response json and show the user the appropriate message

success:function(data)
{
  if(data.status==="success")
  {
      alert("Saved successfully");
  }
  else if(data.status==="failed")
  {
      alert(data.message);
  }
}

You can set the status property of your JSON object to failed when you are trying to insert duplicate record.

posted this

Have an answer?

JD

Please login first before posting an answer.