Link to home
Start Free TrialLog in
Avatar of Howard Bash
Howard BashFlag for United States of America

asked on

Dynamic setting up jQuery Flexigrid in Dot Net

With a good deal of wisdom from one of the Experts here I have built a little application in Dot Net that makes a valid JSON string for flexigrid and then displays it and it looks fine (although the date field is not as expected but appears like "Date (1349440000)" or simlar).

Is it possible to access the URL property of the flexigrid at run time to point it to another file/URL and also can I build all the colModel elements which represent the columns that the JSON string data contains?
Avatar of leakim971
leakim971
Flag of Guadeloupe image

Hello hbash,

>Is it possible to access the URL property of the flexigrid at run time to point it to another file/URL ?
Yes, you can change the url before reload the grid with : $("#flex1").flexigrid()[0].p.url = "path/to/newFile.txt";

>can I build all the colModel elements which represent the columns that the JSON string data contains?
Yes, you need to add the function onSuccess as parameter in the json parameter of the flexigrid

Check the following :

(look the onSuccess json member at the end)





$("#flex1").flexigrid({
            url: 'json.txt',
            method: 'GET',
            dataType: 'json',
            colModel: [
                { display: 'FirstName', name: 'fn', width: 40, sortable: true, align: 'center' },
                { display: 'LastName', name: 'ln', width: 180, sortable: true, align: 'left' },
                { display: 'Creation Date', name: 'cd', width: 120, sortable: true, align: 'left' },
                { display: 'Last Access Date', name: 'lad', width: 130, sortable: true, align: 'left' },
                { display: 'Description', name: 'desc', width: 80, sortable: true, align: 'right' }
            ],
            buttons: [
                { name: 'Add', bclass: 'add', onpress: test },
                { name: 'Delete', bclass: 'delete', onpress: test },
                { separator: true }
            ],
            searchitems: [
                { display: 'FirstName', name: 'iso' },
                { display: 'LastName', name: 'name', isdefault: true }
            ],
            sortname: "FirstName",
            sortorder: "asc",
            usepager: true,
            singleSelect: true,
            title: 'Persons',
            useRp: true, rp: 15,
            showTableToggleBtn: true,
            width: 700,
            height: 200,
            onSuccess: function (data) {
                $("tbody tr", "#flex1").each(function () {
                    cd = $("td div", $(this)).eq(2).text().replace(/\/Date\(|\)\//g, ""); // remove /Date( and )/ in the third column
                    la = $("td div", $(this)).eq(3).text().replace(/\/Date\(|\)\//g, ""); // remove /Date( and )/ in the fourth column
                    $("td div", $(this)).eq(2).text(cd); // replace the value of the third column
                    $("td div", $(this)).eq(3).text(la); // replace the value of the fourth column
                });
            }
        });

Open in new window

Avatar of Howard Bash

ASKER

What I am looking to do is to build the grid based on the dataset / datatable/ resultset.  So,  along with building the JSON string,  unless the grid will only be used for a particular query,  I would need to change the colModel column descriptions as wellas the sortname.

I am not clear about what  your onSuccess function does.  Can you explain it a bit?

Thanks.
>I am not clear about what  your onSuccess function does.  Can you explain it a bit?

Yes of course. the onSuccess function is called just after the table (the flexigrid is a table of course) is populated with the json content.
So you can modify the table content if needed.
In the example (previous post) we remove the << /Date( >> and << )/ >> in the date columns (Creation Date and Last Access Date)
 
More info about onSuccess here : http://api.jquery.com/jQuery.ajax/

>What I am looking to do is to build the grid based on the dataset / datatable/ resultset

I understand, you may reconsider a solution with a web service
Just for information you have better documented jquery plugin to manage table for example :

http://www.webdesignbooth.com/15-great-jquery-plugins-for-better-table-manipulation/

But perhaps you already saw this and did a choice...
I hope I won't need the web service.  I wrote an HTTPHandler ProcessRequest which build and returns the JSON string.  It accepts a dataset and is based on one of the links you sent.  So,  if I can tweak a page with the flexigrid (change the colModel column descriptions,  repoint the URL property,  changed the sortname) and then have the grid refresh,  I "think"  I can get there without the web service.

Is the onSuccess event too late to modify the grid column attributes as the JSON string will have already been loaded?

I would have thought the grid start with the colModel section as follows:
 colModel: [
                ]

and you would insert lines like the following between the square brackets.  If that is the case you can build these "display:" lines dynamically for the grid.
                { display: 'FirstName', name: 'fn', width: 40, sortable: true, align: 'center' }
of course I haven't a clue how to do that as well.


I haven't seen the list you referred to in the 15-great-jquery-plugins...  Any recommendations on alternative grids?  Flexigrid is a good looking widget.
SOLUTION
Avatar of leakim971
leakim971
Flag of Guadeloupe image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Can I add columns to the colModel "collection" ?
And I would love an example of the web service coding technique you have in mind. Thanks.
Yes, of course :

(you should have data for it in your json string)
nColumn = $("#flex1").flexigrid()[0].p.colModel.length;
$("#flex1").flexigrid()[0].p.colModel[nColumn] = { display: 'MiddleName', name: 'mn', width: 40, sortable: true, align: 'center' };

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
In the previous DataContract we use the following in code snippet :

mean a row : { "id":<< id of the row >>, "cell": << list of cells >> }

<DataContract()>
    Public Class Row
        Private _id As String
        Private _cell As List(Of String)

        <DataMember(Name:="id")> _
        Public Property id() As String
            Get
                Return _id
            End Get
            Set(ByVal value As String)
                _id = value
            End Set
        End Property

        <DataMember(Name:="cell")> _
        Public Property cell() As List(Of String)
            Get
                Return _cell
            End Get
            Set(ByVal value As List(Of String))
                _cell = value
            End Set
        End Property

        Public Sub New()
        End Sub

        Public Sub New(ByVal id As String, ByVal cell As List(Of String))
            Me._id = id
            Me._cell = cell
        End Sub

    End Class

Open in new window

The web method getPersons() using the DataContract in the code snippet :

(I don't use a DataTable but manually create the data as you can see)


<WebMethod()> _
    <Script.Services.ScriptMethod(ResponseFormat:=Script.Services.ResponseFormat.Json)> _
    Public Function getPersons() As String

        Dim l1 As List(Of String) = New List(Of String)() From {"Howard", "Ba", "01/01/2010", "01/01/2010", "This is a test record and also the first record."}
        Dim l2 As List(Of String) = New List(Of String)() From {"Ron", "Ba", "01/01/2010", "01/01/2010", "This is the second record added"}
        Dim l3 As List(Of String) = New List(Of String)() From {"Net", "Ba", "01/01/2010", "01/01/2010", "Duh Mud"}
        Dim row1 As New Row("1", l1)
        Dim row2 As New Row("2", l2)
        Dim row3 As New Row("3", l3)

        Dim rows As New List(Of Row)
        rows.Add(row1)
        rows.Add(row2)
        rows.Add(row3)

        Dim persons As New Persons(1, 1, rows)

        Dim ms As MemoryStream = New MemoryStream()
        Dim ser As DataContractJsonSerializer = New DataContractJsonSerializer(GetType(Persons))
        ser.WriteObject(ms, persons)
        Dim json() As Byte = ms.ToArray()
        ms.Close()
        Return Encoding.UTF8.GetString(json, 0, json.Length)

    End Function

Open in new window

Getting the data from the database :


<WebMethod()> _
    <Script.Services.ScriptMethod(ResponseFormat:=Script.Services.ResponseFormat.Json)> _
    Public Function getPersons() As String

        Dim myConn As SqlConnection = New SqlConnection("Initial Catalog=ExpertsExchange;Data Source=localhost;Integrated Security=SSPI;")
        Dim myCmd As SqlCommand = myConn.CreateCommand()
        myCmd.CommandText = "SELECT id,FirstName,LastName,CreationDate,LastAccessDate,Description FROM dbo.hbash"
        myConn.Open()
        Dim myReader As SqlDataReader = myCmd.ExecuteReader()
        Dim rows As New List(Of Row)
        Do While myReader.Read()
            rows.Add(New Row(myReader.GetInt32(0), New List(Of String)() From {myReader.GetString(1), myReader.GetString(2), myReader.GetDateTime(3).ToString(), myReader.GetDateTime(4).ToString(), myReader.GetString(5)}))
        Loop

        Dim persons As New Persons(1, rows.Count, rows)

        Dim ms As MemoryStream = New MemoryStream()
        Dim ser As DataContractJsonSerializer = New DataContractJsonSerializer(GetType(Persons))
        ser.WriteObject(ms, persons)
        Dim json() As Byte = ms.ToArray()
        ms.Close()
        Return Encoding.UTF8.GetString(json, 0, json.Length)

    End Function

Open in new window

In the previous post you have ... From {myReader.GetString(1), ...

What is "From"?
a keyword to initialize collection, replace by :

New List(Of String)(New String() {myReader.GetString(1
This line:
        Do While myReader.Read()
-->>            rows.Add(New Row(myReader.GetInt32(0), New List(Of String)() {myReader.GetString(1), myReader.GetString(2), myReader.GetDateTime(3).ToString(), myReader.GetDateTime(4).ToString(), myReader.GetString(5)}))
        Loop

does not compile.

"Value of type 'String' cannot be converted to 'System.Collections.Generic.List(Of String) "


SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the points!