Update Database record on ASP.Net TextBox change inside GridView using jQuery AJAX, C# and VB.Net

Last Reply 7 months ago By dharmendr

Posted 7 months ago

I have a vb.net gridview that has onlick and selectedindexchanged events within certain columns. The behavior is to write the updated values to the database using a stored procedure that is called on these events.

The procedure is very light and the gridview is also narrow and using data types very sparingly.

The user experience is not great as everytime an update to one of these columns is made, there is a slight pause (and sometimes a 1-2 second pause) for the post back to occur and then the "maintainscrollpositiononpostback" to finish.

Is there a better way to do this that will allow the user to make updates while tabbing to the next field without having to use a submit or update button?

You are viewing reply posted by: dharmendr 7 months ago.
Posted 7 months ago

Hi Warren,

It is not possible to check with your code. I have created small example, so that you can understand it.

Check this example. Now please take its reference and correct your code.

Database

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

HTML

<asp:GridView ID="gvCustomer" runat="server" AutoGenerateColumns="False" DataKeyNames="CustomerId"
    CssClass="table table-striped table-bordered table-hover">
    <Columns>
        <asp:BoundField DataField="CustomerId" HeaderText="ID" />
        <asp:TemplateField HeaderText="Name">
            <ItemTemplate>
                <asp:TextBox runat="server" ID="txtName" Text='<%# Eval("Name") %>' CssClass="form-control Update" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Country">
            <ItemTemplate>
                <asp:TextBox runat="server" ID="txtCountry" Text='<%# Eval("Country") %>' CssClass="form-control Update" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
    $(function () {
        $('.Update').on('change', function () {
            var id = $(this).closest('tr').find('td').eq(0).html();
            var name = $(this).closest('tr').find('[id*=txtName]').val();
            var country = $(this).closest('tr').find('[id*=txtCountry]').val();
            $.ajax({
                type: "POST",
                url: "Default.aspx/Update",
                data: "{id:" + id + ",name:'" + name + "',country:'" + country + "'}",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (r) {
                    alert("Record updated successfully.");
                },
                error: function (r) {
                    alert(r.responsetext);
                }
            });
        });
    });
</script>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        gvCustomer.DataSource = GetData();
        gvCustomer.DataBind();
    }
}

private DataTable GetData()
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "SELECT CustomerId,Name,Country FROM Customers";
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);
                return dt;
            }
        }
    }
}

[WebMethod]
public static void Update(int id, string name, string country)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "UPDATE Customers SET Name = @Name,Country = @Country WHERE CustomerId = @Id";
    using (SqlConnection con = new SqlConnection(conString))
    {
        SqlCommand cmd = new SqlCommand(query, con);
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@Name", name);
        cmd.Parameters.AddWithValue("@Country", country);
        cmd.Parameters.AddWithValue("@Id", id);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        gvCustomer.DataSource = GetData()
        gvCustomer.DataBind()
    End If
End Sub

Private Function GetData() As DataTable
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "SELECT CustomerId,Name,Country FROM Customers"
    Dim cmd As SqlCommand = New SqlCommand(query)
    Using con As SqlConnection = New SqlConnection(conString)
        Using sda As SqlDataAdapter = New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Using dt As DataTable = New DataTable()
                sda.Fill(dt)
                Return dt
            End Using
        End Using
    End Using
End Function

<WebMethod()>
Public Shared Sub Update(ByVal id As Integer, ByVal name As String, ByVal country As String)
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "UPDATE Customers SET Name = @Name,Country = @Country WHERE CustomerId = @Id"
    Using con As SqlConnection = New SqlConnection(conString)
        Dim cmd As SqlCommand = New SqlCommand(query, con)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@Name", name)
        cmd.Parameters.AddWithValue("@Country", country)
        cmd.Parameters.AddWithValue("@Id", id)
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Using
End Sub

Screenshots

The Form

Record After Update

Note: I have used inline query in the code for update. You need to change it with Stored Procedure.