How to insert the records into DB(MYSQL)

Hey Samarthan check the attached file.

Home.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Home.aspx.cs" Inherits="Home" %>
>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Demotitle>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" />
<script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js">script>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js">script>
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/themes/smoothness/jquery-ui.css" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/jquery-ui.min.js">script>
<style type="text/css">
table th, table td {
width: 100px;
padding: 5px;
border: 1px solid #ccc;
}
.selected {
background-color: #666;
color: #fff;
}
style>
head>
<body>
<form id="form1" runat="server">
<div class="container">
<h2>Basic Tableh2>
<table>
<tr>
<td>
<asp:TextBox ID="textBox1" runat="server">asp:TextBox>
td>
tr>
<tr>
<td>
<asp:TextBox ID="textBox2" runat="server">asp:TextBox>
td>
tr>
<tr>
<td>
<asp:TextBox ID="textBox3" runat="server">asp:TextBox>
td>
tr>
<tr>
<td>
<button type="button" id="btnAdd" class="btn btn-xs btn-primary classAdd">Add Morebutton>
td>
tr>
table>
<div id="gridView" style="display: none">
<table class="table" id="maintable">
<tr>
<th>EmpNameth>
<th>PhoneNoth>
<th>Emailth>
<th>Actionth>
tr>
table>
<button type="button" id="btnSubmit" class="btn btn-primary btn-md pull-right btn-sm">Submitbutton>
div>
div>
form>
body>
<script type="text/javascript">
$(document).ready(function () {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "Home.aspx/BindDatatable",
data: "{}",
dataType: "json",
success: function (data) {
$(gridView).show();
for (var i = 0; i < data.d.length; i++) {
$("#maintable").append("<tr><td>" + data.d[i].EmpName + "td><td>" + data.d[i].PhoneNumber + "td><td>" + data.d[i].EmailAddress + "td><td>td>tr>");
}
},
error: function (result) {
alert("Error");
}
});
$(document).on("click", ".classAdd", function () {
$(gridView).show();
//on is used for getting click event for dynamically created buttons
var rowCount = $('.data-contact-person').length + 1;
var contactdiv = '' +
'<td><label id="f-name' + rowCount + '" class="f-name01">' + $("#textBox1").val() + 'label>td>' +
'<td><label id="l-name' + rowCount + '" class="l-name01">' + $("#textBox2").val() + 'label>td>' +
'<td><label id="email' + rowCount + '" class="email01">' + $("#textBox3").val() + 'label>td>' +
'<td><button type="button" id="btnRemove" class="deleteContact btn btn btn-danger btn-xs">Removebutton>td>' +
'tr>';
$('#maintable').append(contactdiv); // Adding these controls to Main table class
$("#textBox1").val('');
$("#textBox2").val('');
$("#textBox3").val('');
});
$(document).on("click", ".deleteContact", function () {
$(this).closest("tr").remove(); // closest used to remove the respective 'tr' in which I have my controls
});
function getAllEmpData() {
var data = [];
$('tr.data-contact-person').each(function () {
var empname = $(this).find('.f-name01').text();
var phoneno = $(this).find('.l-name01').text();
var emailId = $(this).find('.email01').text();
var alldata = {
'EmpName': empname,
'PhoneNumber': phoneno,
'EmailAddress': emailId
}
data.push(alldata);
});
console.log(data);
return data;
}
$("#btnSubmit").click(function () {
var data = JSON.stringify(getAllEmpData());
$.ajax({
url: 'Home.aspx/SaveData',
type: 'POST',
dataType: 'json',
contentType: 'application/json; charset=utf-8',
data: JSON.stringify({ 'empdata': data }),
success: function () {
alert("Data Added Successfully");
location.reload(true);
},
error: function () {
alert("Error while inserting data");
}
});
});
});
script>
<script type="text/javascript">
$(function () {
$("#maintable").sortable({
items: 'tr:not(tr:first-child)',
cursor: 'pointer',
axis: 'y',
dropOnEmpty: false,
start: function (e, ui) {
ui.item.addClass("selected");
},
stop: function (e, ui) {
ui.item.removeClass("selected");
}
});
});
script>
html>

Home.aspx.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
using Newtonsoft.Json;
public partial class Home : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
[WebMethod]
public static string SaveData(string empdata)
{
try
{
var serializeData = JsonConvert.DeserializeObject>(empdata);
using (var con = new SqlConnection("Data Source=.;Initial Catalog=DemoTest;Integrated Security=true"))
{
foreach (var data in serializeData)
{
using (var cmd = new SqlCommand("INSERT INTO Employee(EmpName,PhoneNumber,EmailAddress) VALUES(@EmpName, @PhoneNumber,@EmailAddress)"))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@EmpName", data.EmpName);
cmd.Parameters.AddWithValue("@PhoneNumber", data.PhoneNumber);
cmd.Parameters.AddWithValue("@EmailAddress", data.EmailAddress);
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}
catch(Exception ex)
{
}
return null;
}
[WebMethod]
public static Employee[] BindDatatable()
{
DataTable dt = new DataTable();
List empdetails = new List();
using (SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=DemoTest;Integrated Security=true"))
{
using (SqlCommand cmd = new SqlCommand("select EmpName,PhoneNumber,EmailAddress from Employee", con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow dtrow in dt.Rows)
{
Employee employee = new Employee();
employee.EmpName = dtrow["EmpName"].ToString();
employee.PhoneNumber = dtrow["PhoneNumber"].ToString();
employee.EmailAddress = dtrow["EmailAddress"].ToString();
empdetails.Add(employee);
}
}
}
return empdetails.ToArray();
}
}
public class Employee
{
public string EmpName { get;set;}
public string PhoneNumber { get; set; }
public string EmailAddress { get; set; }
}

Hello Amit,

Still facing an issue, Not able to insert the records into DB.

var serializeData = JsonConvert.DeserializeObject>(empdata);

using (var con = new MySqlConnection("Data Source=localhost;Initial Catalog=world;Integrated Security=true"))

{

foreach (var data in serializeData)

{

using (var cmd = new MySqlCommand("INSERT INTO Employee(EmpName,PhoneNumber,EmailAddress) VALUES(@EmpName, @PhoneNumber,@EmailAddress)"))

{

cmd.CommandType = CommandType.Text;

cmd.Parameters.AddWithValue("@EmpName", data.EmpName);

cmd.Parameters.AddWithValue("@PhoneNumber", data.PhoneNumber);

cmd.Parameters.AddWithValue("@EmailAddress", data.EmailAddress);

cmd.Connection = con;

if (con.State == ConnectionState.Closed)

{

con.Open();

}

cmd.ExecuteNonQuery();

con.Close();

I was changes the connection string path, Can you please guide me here

Thank you in advance!!

15 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty6y ago
Accepted answer
Hey Samarthan check the attached file.
Home.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Home.aspx.cs" Inherits="Home" %>

>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Demotitle>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" />
    <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js">script>

    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js">script>
    <link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/themes/smoothness/jquery-ui.css" />
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/jquery-ui.min.js">script>

    <style type="text/css">
        table th, table td {
            width: 100px;
            padding: 5px;
            border: 1px solid #ccc;
        }

        .selected {
            background-color: #666;
            color: #fff;
        }
    style>
head>
<body>
    <form id="form1" runat="server">
        <div class="container">
            <h2>Basic Tableh2>
            <table>
                <tr>
                    <td>
                        <asp:TextBox ID="textBox1" runat="server">asp:TextBox>
                    td>
                tr>
                <tr>
                    <td>
                        <asp:TextBox ID="textBox2" runat="server">asp:TextBox>
                    td>
                tr>
                <tr>
                    <td>
                        <asp:TextBox ID="textBox3" runat="server">asp:TextBox>
                    td>
                tr>
                <tr>
                    <td>
                        <button type="button" id="btnAdd" class="btn btn-xs btn-primary classAdd">Add Morebutton>
                    td>
                tr>
            table>
            <div id="gridView" style="display: none">
                <table class="table" id="maintable">
                    <tr>
                        <th>EmpNameth>
                        <th>PhoneNoth>
                        <th>Emailth>
                        <th>Actionth>
                    tr>
                table>
                <button type="button" id="btnSubmit" class="btn btn-primary btn-md pull-right btn-sm">Submitbutton>
            div>

        div>
    form>
body>

<script type="text/javascript">
    $(document).ready(function () {
        $.ajax({
            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "Home.aspx/BindDatatable",
            data: "{}",
            dataType: "json",
            success: function (data) {
                $(gridView).show();
                for (var i = 0; i < data.d.length; i++) {
                    $("#maintable").append("<tr><td>" + data.d[i].EmpName + "td><td>" + data.d[i].PhoneNumber + "td><td>" + data.d[i].EmailAddress + "td><td>td>tr>");
                }
            },
            error: function (result) {
                alert("Error");
            }
        });

        $(document).on("click", ".classAdd", function () {
            $(gridView).show();
            //on is used for getting click event for dynamically created buttons
            var rowCount = $('.data-contact-person').length + 1;
            var contactdiv = '' +
                '<td><label id="f-name' + rowCount + '" class="f-name01">' + $("#textBox1").val() + 'label>td>' +
                '<td><label id="l-name' + rowCount + '" class="l-name01">' + $("#textBox2").val() + 'label>td>' +
                '<td><label id="email' + rowCount + '" class="email01">' + $("#textBox3").val() + 'label>td>' +
                '<td><button type="button" id="btnRemove" class="deleteContact btn btn btn-danger btn-xs">Removebutton>td>' +
                'tr>';
            $('#maintable').append(contactdiv); // Adding these controls to Main table class
            $("#textBox1").val('');
            $("#textBox2").val('');
            $("#textBox3").val('');
        });

        $(document).on("click", ".deleteContact", function () {
            $(this).closest("tr").remove(); // closest used to remove the respective 'tr' in which I have my controls
        });

        function getAllEmpData() {
            var data = [];
            $('tr.data-contact-person').each(function () {
                var empname = $(this).find('.f-name01').text();
                var phoneno = $(this).find('.l-name01').text();
                var emailId = $(this).find('.email01').text();
                var alldata = {
                    'EmpName': empname,
                    'PhoneNumber': phoneno,
                    'EmailAddress': emailId
                }
                data.push(alldata);
            });
            console.log(data);
            return data;
        }

        $("#btnSubmit").click(function () {
            var data = JSON.stringify(getAllEmpData());
            $.ajax({
                url: 'Home.aspx/SaveData',
                type: 'POST',
                dataType: 'json',
                contentType: 'application/json; charset=utf-8',
                data: JSON.stringify({ 'empdata': data }),
                success: function () {
                    alert("Data Added Successfully");
                    location.reload(true);
                },
                error: function () {
                    alert("Error while inserting data");
                }
            });
        });
    });
script>
<script type="text/javascript">
    $(function () {
        $("#maintable").sortable({
            items: 'tr:not(tr:first-child)',
            cursor: 'pointer',
            axis: 'y',
            dropOnEmpty: false,
            start: function (e, ui) {
                ui.item.addClass("selected");
            },
            stop: function (e, ui) {
                ui.item.removeClass("selected");
            }
        });
    });
script>
html>
Home.aspx.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
using Newtonsoft.Json;

public partial class Home : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }

    [WebMethod]
    public static string SaveData(string empdata)
    {
        try
        {
            var serializeData = JsonConvert.DeserializeObject>(empdata);
            using (var con = new SqlConnection("Data Source=.;Initial Catalog=DemoTest;Integrated Security=true"))
            {
                foreach (var data in serializeData)
                {
                    using (var cmd = new SqlCommand("INSERT INTO Employee(EmpName,PhoneNumber,EmailAddress) VALUES(@EmpName, @PhoneNumber,@EmailAddress)"))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.AddWithValue("@EmpName", data.EmpName);
                        cmd.Parameters.AddWithValue("@PhoneNumber", data.PhoneNumber);
                        cmd.Parameters.AddWithValue("@EmailAddress", data.EmailAddress);
                        cmd.Connection = con;
                        if (con.State == ConnectionState.Closed)
                        {
                            con.Open();
                        }
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                }
            }
        }
        catch(Exception ex)
        {
        }
        return null;
    }

    [WebMethod]
    public static Employee[] BindDatatable()
    {
        DataTable dt = new DataTable();
        List empdetails = new List();
        using (SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=DemoTest;Integrated Security=true"))
        {
            using (SqlCommand cmd = new SqlCommand("select EmpName,PhoneNumber,EmailAddress from Employee", con))
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                foreach (DataRow dtrow in dt.Rows)
                {
                    Employee employee = new Employee();
                    employee.EmpName = dtrow["EmpName"].ToString();
                    employee.PhoneNumber = dtrow["PhoneNumber"].ToString();
                    employee.EmailAddress = dtrow["EmailAddress"].ToString();
                    empdetails.Add(employee);
                }
            }
        }
        return empdetails.ToArray();
    }
}

public class Employee
{
    public string EmpName { get;set;}
    public string PhoneNumber { get; set; }
    public string EmailAddress { get; set; }
}
Amit Mohanty6y ago
Hey check the below link how to connect MySql database.

https://www.c-sharpcorner.com/UploadFile/8af3e0/connecting-to-mysql-using-C-Sharp-net/

http://net-informations.com/q/faq/mysql.html
Mas6y ago
Hello Amit,

Still facing an issue, Not able to insert the records into DB.

var serializeData = JsonConvert.DeserializeObject>(empdata);
using (var con = new MySqlConnection("Data Source=localhost;Initial Catalog=world;Integrated Security=true"))
{
foreach (var data in serializeData)
{
using (var cmd = new MySqlCommand("INSERT INTO Employee(EmpName,PhoneNumber,EmailAddress) VALUES(@EmpName, @PhoneNumber,@EmailAddress)"))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@EmpName", data.EmpName);
cmd.Parameters.AddWithValue("@PhoneNumber", data.PhoneNumber);
cmd.Parameters.AddWithValue("@EmailAddress", data.EmailAddress);
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.ExecuteNonQuery();
con.Close();

I was changes the connection string path, Can you please guide me here

Thank you in advance!!
Jignesh Kumar6y ago
Hey,

Please find below link,
https://www.c-sharpcorner.com/article/crud-using-stored-procedure-in-asp-net-gridview-real-time/
Mas6y ago
Hello Rajanikanth,

This is not working, Here am attaching the screenshot..!!
Initially grid shouls not display...

Please suggest
Mas6y ago
Hello Rajanikant,

This is also not working...

Here i am attaching the file, Can you please go through it and let me know!!
Rajanikant Hawaldar6y ago
instead of
try example
<div class="gridTable" style="width:100%">
  <asp:GridView runat="server" ID="gridView1">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <div id="insideDiv" runat="server">div>
            ItemTemplate>
        asp:TemplateField>
    Columns>
  asp:GridView>
div>

and $(gridView1).show() should be $(#gridView1).show()
Mas6y ago
Hey Amit,

Thak you so much!!

Can you guide me here...

By clicking on submit button I need to insert the data into table using MYsql and C#..
Here I am adding the ASPX code.
we are using the Jquery.

Thank you in advance!!
Amit Mohanty6y ago
Hey Samarthan Gridview1 is your div id. it is not an asp.net control. thats why you are getting error.

Mas6y ago

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Home.aspx.cs" Inherits="Applicationjquery.Home" %>

Demo

Basic Table

Enter interview question

Enter expected answer

Question	Answers

Rajanikant Hawaldar6y ago
please post your html code, may grid name is same but something is wrong in html code
Mas6y ago
I am given as gridView1 only..
But still hitting the error...
Rajanikant Hawaldar6y ago
check in html code what name you have gave to gridview, copy and paste instead of gridView1
Mas6y ago
Hello Rajanikant,

Thank you for your response, Here I am facing issue...
Rajanikant Hawaldar6y ago
this may help you
https://www.aspsnippets.com/Articles/ASPNet-MySQL-CRUD-Select-Insert-Edit-Update-and-Delete-in-GridView-using-MySQL-database-in-ASPNet.aspx