How to auto load data form mysql database and display on table while the page is scrolled down in my ASP.NET project?

Question

0.00/5 (No votes)

See more:

The below code is the cs code of my project

 [WebMethod]
public static string alertfun(string DeviceID)
{
    DataSet ds = new DataSet();
    string db = DeviceID.ToLower().Trim();
    string IPAddress = ConfigurationManager.AppSettings["IPAddress"].ToString();
    string strcon = "SERVER=" + IPAddress + "; DATABASE=" + db + "; UID=" + db +
    "DB_123;PASSWORD=" + db + "@PW_123;";
    MySqlConnection conn = new MySqlConnection(strcon);


    string query_date = "SELECT `device_id` , `pf_update_time`,
     DATE_FORMAT(date_time, ' %T %d/%m/%Y') AS date_time  FROM
    `settings_alert_time` ORDER BY id DESC LIMIT 10 ";

    try
    {
        MySqlDataAdapter adp1 = new MySqlDataAdapter(query_date, conn);

        adp1.Fill(ds, "alertValues");

    }
    catch (Exception ex)
    {

    }

    return ds.GetXml();

}



 The below code is my jquery and ajax code to append the values

 function alertfun() {

    var device = $("[id*='dp2'] :selected").val();
    $.ajax({
        type: "POST",
        url: "../PFSettings/pfreports.aspx/alertfun",
        data: '{DeviceID: "' + device + '"}',
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: OnSuccessalert,
        failure: function () {
            $('#loader').css('display', 'none');
        }

       });
      }


   function OnSuccessalert(response) {
      $('#loader').css('display', 'none');
      var xmlDoc = $.parseXML(response.d);
      var xml = $(xmlDoc);
      var mapdata = xml.find("alertValues");
      $(mapdata).each(function () {

        var time_update="";

        var time = $(this).find("pf_update_time").text();
        if (time >= 60) {

            time = time / 60;
            time_update = time + " hr";

        }
        else {
            time_update = time + " Mins";
        }

        $('#alertTable').append('<tr><td>' + $(this).find("device_id").text() +
       '</td><td>' + time_update  + '</td><<td>' + $(this).find("date_time").text()
        +'</td>/tr>');


       });

      }

What I have tried:

The above code was working perfectly alright I just want to modifey the code to autoload the remaining data from the database when the page gets scrolled.

Posted 10-Feb-20 18:48pm

Member 14741008

Updated 17-Feb-20 8:33am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

lmoelleb · Answer 1 · 2020-02-17T08:34:00

There are certainly issues with your "working" code (converting to XML and using find in the string might seem like a clever solution... but only because you do not know about the ways it is normally done). But ignoring this (as you do see stuff on the screen I guess) you would need something like the following changes:

Server side
You can add an optional "from" parameter to your web method (or have two separate methods). This "from" parameter contain the last known ID on the webside. You can then change the SQL query to only return data where the id is less than "from".
You will need to learn about SQL Query Parameters. Or some joker will simply delete your table using SQL injection (yes, you should google this and learn about it).

Catching Exception and simply ignoring it with an empty block as you do in your C# code is an excellent idea as long as you hate the users and anyone who else who have the misfortune of dealing with the code (in both cases it might of course be yourself). In case the goal of the code isn't to make life misserable, then do not use empty catch blocks. Catch blocks should be used when you can handle the error and recover from it - or throw a new exception with more information to help troubleshooting. Returning an empty response is not recovering anything, it is just hiding it.

When things break (and it is not if, it is when) you will be trying to find out what goes wrong. The details in the exception will be trying to tell you, but you have no way to see it. In the start it will be hard for you to understand the exception messages, but you will learn and they will become your biggest help when troubleshooting - so start learning to read them right away.

If you simply don't catch the error the web site will be informed something went wrong (by receiving a response with status code 500) and can choose how it want to deal with this. Next to this the error will be logged on the server (depends on the web server where it will be) giving you a chance of identifying why things are not working.

In the web site
You need to detect when the page is scrolled to the end (or close to the end). When this happens, you need to request the next set of data - making sure you do not keep requesting the same data over and over if the user scrolls up and down fast. Once the data comes back, you can add to your HTML page.