Skip to content

jQuery $.ajax / Web Method Postback with a Datatable Return

January 2, 2012
function updateMaint() {
    var record = {};
    record.Number = number;
    record.Type = type;
    record.Comment = comment;
    record.Login = login;

    var DTO = { "record": record };

    $.ajax({
        type: "POST",
        url: location.pathname + "/InsertRecord",
        data: JSON.stringify(DTO),
        contentType: "application/json; charset=utf-8",
        datatype: "json",
        success: function (response) {
            var records = eval(response.d);
            if (records != "") {
                records = JSON.parse(records);
                var cbo = document.getElementById('cboRecords');
                cbo.clear();
                for (var i = 0; i < records.length; i++) {
                    cb.options[cb.options.length] = new Option(records[i].type, records[i].number);
                }
            }
        },
        error: function (response) {
            alert(response.responseText);
        }
    });
}
     
    '###########################################
    '      Jquery SQL Update And DataTable Return
    '###########################################
    Public Class Record
        Public Property Number() As String = ""
        Public Property Type() As String = ""
        Public Property Comment() As String = ""
        Public Property Login() As String = ""
    End Class

    <WebMethod()> _
    Public Shared Function InsertRecord(ByVal record As Record) As String
        Try
            Using cmd As New SqlCommand("INSERT INTO tblRecords ( Number, Type, Comment, Login ) VALUES ( @Number, @Type, @Comment, @Login )", New SqlConnection(Conn))
                cmd.Parameters.AddWithValue("@Number", Number)
                cmd.Parameters.AddWithValue("@Type", Type)
                cmd.Parameters.AddWithValue("@Comment", Comment)
                cmd.Parameters.AddWithValue("@Login", Login)
                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
                cmd.Connection.Dispose()
            End Using
        Catch ex As Exception
            Return "Error Inserting Record."
        End Try

        'Serialize datatable to return
        Dim sb As New StringBuilder
        Dim rows As New List(Of Dictionary(Of String, Object))
        Using da As New SqlDataAdapter("SELECT Number, Type FROM tblRecords WHERE Type = @Type", Materials.Conn)
            Using dt As New DataTable
                da.Fill(dt)
                For Each dr As DataRow In dt.Rows
                    Dim Dictionary As New Dictionary(Of String, Object)
                    For Each dc As DataColumn In dt.Columns
                        Dictionary.Add(dc.ColumnName, dr(dc.ColumnName).ToString())
                    Next
                    rows.Add(Dictionary)
                Next
                Dim js As New JavaScriptSerializer
                js.Serialize(rows, sb)
                Return sb.ToString()
            End Using
        End Using
    End Function
    
Advertisements

From → .Net, Javascript, SQL

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: