locked
How to convert table data into JSON for javascript grid? RRS feed

  • Question

  • User2142845853 posted

    Have a VB webforms project that has 1 standard Gridview.  There is business logic that initializes and pulls in the SQL data

    In the page load, its standard and just fills the Gridview, which works just fine.

     Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
            Dim myTA As New ASTRTableAdapters.CFFsTableAdapter
            Dim myTable As ASTRData.CFFsDataTable
    
            myTable = myTA.GetData
            GridView1.DataSource = myTable
            GridView1.DataBind()
            ViewState("tables") = myTable
        End Sub

    The vbform1 has a gridview from using the Designer mode, its named GridView1.

    when the page loads it reads the database and shows all the data.  

    But I want to add a javascript grid and it uses JSON formatted data. 

    dhtmlxGrid expects loaded data in the JSON format. Here is an example of an appropriate data set:
    
    var dataset = [
        {
            "id": 0,
            "a": 1,
            "b": "Linwood Long long long",
            "c": "Petersen",
            "d": "Dahlgreen Place"
        },
        {
            "id": 1,
            "a": 2,
            "b": "Edenburg",
            "c": "Agnes",
            "d": "Gem Street"
        },
        // more columns
    ];
    Loading from local source
    To load data from a local data source, use the parse method of Data Collection. Pass a predefined data set as a parameter of this method:
    
    var grid = new dhx.Grid("grid_container");
    grid.data.parse(dataset);

    How to convert?  

    var dataset = Convert(myTable).toJson

    Saturday, December 14, 2019 11:53 PM

All replies

  • User475983607 posted

    How to convert?

    Populate a strong type not a DataTable or DataSet.  The common solution is using an ADO.NET DataReader to populate the strong type.  Then use standard JSON serialization.  

    Sunday, December 15, 2019 3:03 PM
  • User2142845853 posted

    I couldnt make that work, and tried.  They have a 'connector' that will take the sql connection string, deal with the data and hopefully format it.  this also allows updates from the new javascript grid.   Havent installed the connector yet

    Thursday, December 19, 2019 4:12 AM
  • User-857013053 posted
    var tbl = $('table#whatever tr').map(function() {
      return $(this).find('td').map(function() {
        return $(this).html();
      }).get();
    }).get();

    Then just use $.json (or whatever library you want) to turn that into a JSON string.
    Thursday, December 19, 2019 4:44 AM
  • User2142845853 posted

    Thanks!  I have a table, or dataset, dataview and gridview.  I convert to JSON but the grid rejects it, no error in the javascript side, all runs fine

     myTable = myTA.GetData
                'GridView1.DataSource = myTable
                'GridView1.DataBind()
                Dim ds1 As New DataSet()
                ds1.Tables.Add(myTable)
                Dim json As String = JsonConvert.SerializeObject(ds1, Formatting.Indented)

    Note how the GridView1 is commented out.  When its used, it fills from the sql just fine.  The data is all there.  The grid needs something more, a funnel maybe? a dipping bird?

    Thursday, December 19, 2019 10:37 PM
  • User283571144 posted

    Hi rogersbr,

    According to your description and codes, I couldn't find out the reason why your gridview still have the data even you have commented out the DataBind method.

    Do you set the datasource in the aspx page? 

    I suggest you could try to post your aspx pages codes and code-behind codes, it will help us reproduce your issue and find out the solution.

    Best Regards,

    Brando

    Thursday, December 26, 2019 9:22 AM
  • User2142845853 posted

    There is no set grid logic.  Just saying the built in Gridview fills, works but need to have the ability to add rows, save back to db, edit or delete information and save back.  The DHTMLx grid seems to have everything where the Gridview cant be modified with the new features except for 3rd party that have license $ cost.

    <script type="text/javascript">
    $(document).ready(function () {
    mygrid = new dhtmlXGridObject('gridbox');
    mygrid.setInitWidths("185,185,130,185,130,130,130,120,130,130,120,120,130,150")
    mygrid.setColAlign("center,center,center,center,center,center,center,center,center,center,center,center,center,center")
    mygrid.setSkin("light");
    mygrid.init();
    mygrid.load("ValuesController1.vb", "GetValues()", "json")
    $.ajax({
    url: 'ValuesController1.vb/GetValues',
    method: 'get',
    dataType: 'json',
    success: function (data) {
    alert("ajax call result: " + data)
    },
    error: function (err) {
    alert("Error- " + err );
    }
    })
    });
    var jqxhr = $.get("../ValuesController1.vb/GetValues", function () {
    alert("success");
    })
    .done(function () {
    alert("second success");
    })
    .fail(function () {
    alert("error");
    })
    .always(function () {
    alert("finished");
    });
    </script>

    ValuesController1 same code as Page_Load, open database per connection string on web.config, convert to JSON, return

         <div class="dhtmlXGridObject" id="gridbox">
                <%--  --%>
         </div>

    Alerts just report Error

    Also the Ajax never calls the ValuesController1 method GetValues.  Debug breakpoint never hits.   Is a WebService needed so the Ajax can find it?

    Thursday, December 26, 2019 4:38 PM
  • User475983607 posted

    Alerts just report Error

    The community needs to errors to help.  What are the errors?

    Also the Ajax never calls the ValuesController1 method GetValues.  Debug breakpoint never hits.   Is a WebService needed so the Ajax can find it?

    The URL is surly incorrect.  If this is a Web Method in an aspx page then the URL needs an aspx extension.  If you are calling a web method or API then can you provide details?

    ../ValuesController1.aspx/GetValues

    The community also needs at least to see the GetValues method to make sure the constructs are correct.

    Lastly the script does not look correct.  Can you explain the intent?  

    Thursday, December 26, 2019 5:01 PM
  • User2142845853 posted

    mgebhard

    rogersbr

    Alerts just report Error

    The community needs to errors to help.  What are the errors?

      error: function (err) {
    alert("Error- " + err );
    }

    So the error is an alert box saying:  Error-

    with no content

    rogersbr

    Also the Ajax never calls the ValuesController1 method GetValues.  Debug breakpoint never hits.   Is a WebService needed so the Ajax can find it?

    The URL is surly incorrect.  If this is a Web Method in an aspx page then the URL needs an aspx extension.  If you are calling a web method or API then can you provide details?

    ../ValuesController1.aspx/GetValues

    The properties tab lists it as ValuesController1.vb 

    The community also needs at least to see the GetValues method to make sure the constructs are correct.

    Imports System.Net
    Imports System.Web.Http
    Imports Newtonsoft.Json
    
    Public Class ValuesController1
        Inherits ApiController
    
        ' GET api/<controller>
        <HttpGet>
        Public Function GetValues() As String
            '********************************************************************************
            ' ATTEMPT TO PROVIDE SQL DATA FOR USE BY THE DHTMLX GRID
            '
            '
            '********************************************************************************
            Dim myTA As New TableAdapters.astRTableAdapter
            Dim myTable As ASDSData.astRDataTable
            myTable = myTA.GetData
            Dim ds1 As New DataSet()
            ds1.Tables.Add(myTable)
            Dim json1 As String = JsonConvert.SerializeObject(ds1, Formatting.Indented)
            Return json1                        'New String() {"value1", "value2"}
        End Function
    
        ' GET api/<controller>/5
        Public Function GetValue(ByVal id As Integer) As String
    
    
            Return "value"
        End Function
    
        ' POST api/<controller>
        Public Sub PostValue(<FromBody()> ByVal value As String)
    
        End Sub
    
        ' PUT api/<controller>/5
        Public Sub PutValue(ByVal id As Integer, <FromBody()> ByVal value As String)
    
        End Sub
    
        ' DELETE api/<controller>/5
        Public Sub DeleteValue(ByVal id As Integer)
    
        End Sub
    End Class
    

    Lastly the script does not look correct.  Can you explain the intent?  

    The Grid code was tossed into document.ready to see if it made a difference, its back in a script file within the HTML.

    The Ajax call tries to reach anything that will give it data for the grid.  Breakpoint set on ValuesController1 method being called, never hit

    Friday, December 27, 2019 1:57 AM
  • User475983607 posted

    The Ajax call tries to reach anything that will give it data for the grid.  Breakpoint set on ValuesController1 method being called, never hit

    If you take a moment and look at the template Web API code you'll see a comment that shows what the URL should look like.

        ' GET api/<controller>
        <HttpGet>
        Public Function GetValues() As String

    The word "Controller" is assumed by convention.  The actual URL is.

    /api/values

    It looks like you created a second Values controller which appended a "1" at the end of the controller class name.  You'll want to remove the "1".  This might conflict with the original values controller.  Remove or rename the original values controller.

    It's very clear from your post that you do not understand Web API fundamentals.  You'll need to learn the basics before moving forward otherwise you'll struggle.

    Friday, December 27, 2019 2:32 PM
  • User2142845853 posted

    When the user adds the API controller class, guess what the system auto generates for the name?  ValuesController1.vb    you were saying about understanding the fundamentals of the Web API or using Visual Studio perhaps?

    Even to humor you, knowing it wont work but trying it anyway, I renamed the controller and updated the calls.  it didnt work that way. 

     RouteTable.Routes.MapHttpRoute(name:="ValuesController1", routeTemplate:="api/{controller}/{id}", defaults:=New With {Key .id = System.Web.Http.RouteParameter.[Optional]

    So calling it is api/GetValues  or even ASTr/api/GetValues the ajax call fails, but the $.get reports Success, except it never causes the debugger to trap execution in the GetValues method

    Friday, December 27, 2019 6:08 PM
  • User475983607 posted

    rogersbr

    When the user adds the API controller class, guess what the system auto generates for the name?  ValuesController1.vb    you were saying about understanding the fundamentals of the Web API or using Visual Studio perhaps?

    Exactly.  The scaffolding wizard prompts for a meaningful controller name.  I find it confusing that you would purposely name all your controllers "Values".   Seems counter intuitive and difficult to maintain.

    rogersbr

    So calling it is api/GetValues  or even ASTr/api/GetValues the ajax call fails, but the $.get reports Success, except it never causes the debugger to trap execution in the GetValues method

    Right.  And this is where taking the time to understand the technology is very helpful.  Web API is a REST service.  The controller methods are invoked by the HTTP methods; GET, POST, PUT, DELETE.  One URL, api/values, accepts different HTTP methods.   This information is not hidden.  It is covered in every beginning level Web API tutorial.  

    I recommend going though the following tutorials.  

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/getting-started-with-aspnet-web-api/

    One tutorial shows how to use Web API with Web Forms which might be particularly helpful to you.

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/getting-started-with-aspnet-web-api/using-web-api-with-aspnet-web-forms

    Friday, December 27, 2019 8:19 PM
  • User2142845853 posted

    Seriously, if anyone has any observations? further questions?  or ideas about why the AJAX call isnt invoking the method?  When it returns success? but doesnt send the data back, please let me know, post and discuss another way.

    Havent seen any activity or posts from anyone for a while, thanks

    Saturday, December 28, 2019 3:46 AM
  • User475983607 posted

    So far you have not provided a correct URL which explains why the AJAX call is not invoking the method.

    Below is an example of calling a GET endpoint in a Values controller.  I used the wizard to create the controller.

    Web API

    Imports System.Net
    Imports System.Web.Http
    
    Public Class ValuesController1
        Inherits ApiController
    
        ' GET api/<controller>
        Public Function GetValues() As IEnumerable(Of String)
            Return New String() {"value1", "value2"}
        End Function

    Route configuration in global.asax

    Public Class Global_asax
        Inherits HttpApplication
    
        Sub Application_Start(sender As Object, e As EventArgs)
            ' Fires when the application is started
    
            RouteTable.Routes.MapHttpRoute(
                name:="DefaultApi",
                routeTemplate:="api/{controller}/{id}",
                defaults:=New With {.id = RouteParameter.Optional}

    The ASPX page calls the api/values GET endpoint on load then writes the resulting JSON to the page.

    <%@ Page Title="Home Page" Language="VB" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Default.aspx.vb" Inherits="VbWebApplication._Default" %>
    
    <asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">
        <h2>AJAX Demo</h2>
        <div id="results"></div>
    
        <script type="text/javascript">
            function getValues() {
                $.getJSON("api/values",
                    function (data) {
                        $('#results').empty();
                        $('#results').text(JSON.stringify(data));
                    });
            }
    
            $(document).ready(getValues);
        </script>
    
    </asp:Content>

    I suspect there are several issues with your code that hiding what's actually happening.  The community can help find these issues if you share all the relevant code.  Otherwise, learn how to use the browser's dev tools to see what is happening in the browser.  The Network view can show HTTP status errors (404, 500, etc) as well as error messages returned form the application.  Error message are tricky because they do not return a status errors they return a 200 ok and trigger the success handler.  You have to read the content to figure out what happened.

    Saturday, December 28, 2019 3:14 PM
  • User2142845853 posted

    Thank you if anyone has ideas about this, should be so simple but its a matter of business logic pulling in the SQL data and an Ajax call getting the data.  What are recommended and reliable ways to use Ajax to pull data into a vb web forms html page now and in the future?   How has this evolved, whats been deprecated?

    Saturday, December 28, 2019 4:53 PM
  • User475983607 posted

    What are recommended and reliable ways to use Ajax to pull data into a vb web forms html page now and in the future?   

    The code snippet above is a working example that illustrates how to make an HTTP GET request to an Web API end point.  If you need help with business logic or AJAX syntax then share your code. 

    Saturday, December 28, 2019 6:19 PM
  • User2142845853 posted

    If there are ANY developers out there who have used ajax calls in vb Web Forms w/JSON formatting?  By all means chime in!  Just hoping for some helpful input from software users.  Its clear how to use Ajax calls, just that where the code should work, it doesnt.  The goal is to fill the DHTMLx grid.  It just needs a source which the Ajax call starts to process, single stepping the SQL data is all there, just never finishes, data falls onto the floor.  As if the "dont allow data to pass bit" is set to TRUE.  Seems to have stumped everyone

    In lieu of solving it, any details of ajax internals is appreciated. How in memory is it passed? etc

    Saturday, December 28, 2019 10:24 PM
  • User2142845853 posted

    I guarantee that code was put into the project, exactly as example code from the microsoft docs also was.  it will not trigger the controller, it may return success but does not touch the results div in any way with data.  The jquery ajax call does not seem to be permitted here.  

    There must be a setting that blocks data transfer?  Here is an example of using ajax in VS works, (for me):

    int x = 1;
    int y = 1;
    int z = x + y;

    Error - x and y are undefined.   Not that its new or difficult, it just doesnt work when it must work.  Or maybe Ajax calls dont work in VB?

    Tuesday, December 31, 2019 2:06 PM
  • User475983607 posted

    I guarantee that code was put into the project, exactly as example code from the microsoft docs also was.  it will not trigger the controller, it may return success but does not touch the results div in any way with data.  The jquery ajax call does not seem to be permitted here. 

    In your original code, the URLs are incorrect.  There is no indication that you fixed the URLs. 

    There must be a setting that blocks data transfer?  Here is an example of using ajax in VS works, (for me):

    int x = 1;
    int y = 1;
    int z = x + y;

    Error - x and y are undefined.   Not that its new or difficult, it just doesnt work when it must work.  Or maybe Ajax calls dont work in VB?

    The code shown above is not AJAX or JavaScript.  It's C#.   C# runs on the web server.  AJAX/JavaScript run in the browser.  The error makes total sense if you are trying to access x or y in JavaScript.

    The code you have shown has many issue and community has provided solutions but there is no indication that you are following the recommendations.  The community simply cannot provide assistance without code that reproduces the issues.  

    Tuesday, December 31, 2019 2:57 PM
  • User2142845853 posted

    The ajax calls return success on the right url.  just that its not accessing the method

    Tuesday, December 31, 2019 5:22 PM
  • User475983607 posted

    rogersbr

    I got better things to do, if I say it doesnt work? it doesnt work. 

    I'm not arguing.  It is very clear the code you're provided does not work and the community has explained why.  At this point, it is not clear if you fixed the URLs which is the first step.

    rogersbr

    The code you provided does not work. 

    No, the example code functions as expected.

    Tuesday, December 31, 2019 5:47 PM
  • User2142845853 posted

    When its Microsoft documentation sample code, there is no doubt in its own context it works, or someone would have pointed it out sooner. If the example code you posted doesnt work in the existing project then it doesnt work. When your code doesnt work, find out why

    Tuesday, December 31, 2019 7:37 PM