Server-side Ajax Data Table With ASP

Does not look like you are referencing another page.  

$(document).ready(function() {
    $('#standings').dataTable();
} );

Select allOpen in new window

Looking at the example for ajax, notice how they are referencing the data page   http://www.datatables.net/examples/data_sources/ajax.html

$(document).ready(function() {
    $('#example').dataTable( {
        "ajax": '../ajax/data/arrays.txt'
    } );
} );

Select allOpen in new window

You need to set up a plain asp page that accepts a get and produces the similar output.  Look in your previous question about this where I have this detailed.

This is very helpful and I appreciate you suffering my ineptitude.  I am also afraid that I have what I need in previous assistance but am just not "connecting the dots."

@padas, yes I know that the data reference is missing.  I left it at that to indicate that the ajax datatable structure was clean so that we can understand that the problem is on the server side as I try to populate the table.

When I put the reference to the asp page that is compiling the data it craps out.  Note that the asap page itself works because it renders well here: http://www.gopherstateevents.com/series/series_results.asp?series_id=19&year=2014 

But when I put that in here: http://www.gopherstateevents.com/series/series_results2.asp?series_id=19&year=2014 my data table falls apart.

I recognized from a previous post the I was directed to that there might be some escapes that I need to implement.  At this point, I don't think that is the problem.  I believe that the problem is how I "connect" the two pages.  For instance I need an lSeriesID variable on both pages.  Do I declare it on both pages or just on the "parent" page?  i assume I declare it on both (change the name?).  I am passing it via a query string.

Is it fair to say that it works more like an inline frame than an include?

I think I am getting it and I am going to try again later today.  Please let me know if you see any issues in my reasoning or notice any gaps I have to fill in.  Otherwise I will repost once I have done this and update you.

I can't come back to this until later today.  However, the drop downs should now have a jquery listener for a change and submit the ajax call based on the change or clicking the select series to review button.

Thank you.  I am excited to be moving in this direction with all the help provided at EE.  Can't wait to get back at it later today.

where do dependent dropdowns come into play here? The dropdowns on the page seem to all have the same values, changing one of them doesn't seem to change the values, so your last comment doesn't relate to what's being done here. furthermore, I believe the OP is trying to understand the flow of how everything works, so i don't think the first thing that should be done is combine the 2 forms, but rather make sure the OP understands the theory behind how everything is working.

The current drop downs require the user to select one group then another.  That tells me there is a connection.  

I totally disagree about not combining the drop downs.  I am going to guess it was done that way to make it easier to call the database.  Why force the user to click 2 buttons when really only one or none is required.  

You can force the Series to be selected first.  Until that is done, the other 2 dropdowns are greyed out (disabled).  Then once the 1st is selected, then the 2nd drop down for category.  Because each series could possibly have different categories, chaining these two selects could be a good option.

Ultimately, you are trying to get at a race id.  You drill down to that race ID by selecting the Series, Category, Gender.  It is not any more complicated than that.

I just want to say that the type of passionate and knowledgeable help I am getting on this post is incredibly helpful...from both experts!  I am working on it on my end...making good progress...hope to have either a further questions or an end result soon.

This will be my springboard into something I should have done long ago...move more effectively into new (for me) technology.  In this case, AJAX and JQuery.

Forgetting about what can or can't be done, a good start is to create a static html form that you envision in a perfect world.

As example, I am thinking the first drop down is the current races that are available for viewing.  The other 2 are disabled until the first one is selected.  Based on the first select, the 2nd will "light up" then the 3rd.

Using ajax, no need for a submit button. It just works.  

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>padas</title>
</head>
<body>

                <span style="font-weight: bold;">Select Series:</span>
                <select name="series" id="series">
                    <option value="">&nbsp;</option>
                    
                            <option value="6">Central Minnesota Series (2014)</option>
                        
                            <option value="20">Hwy 12 Series (2014)</option>
                        
                            <option value="19" selected="">Hwy 94 Series (2014)</option>
                        
                            <option value="4">Lake Series (2014)</option>
                        
                            <option value="18">Litchfield Series (2014)</option>
                        
                            <option value="7">Northland Series (2014)</option>
                        
                </select>
			

  <form name="view_category" method="post" action="series_results.asp?series_id=19&amp;year=2014">
                <span style="font-weight: bold;">Category:</span>
                <select name="categories" id="categories"  disabled>
                    
                            <option value="0" selected="">Open</option>
                        
                            <option value="14">14 &amp; Under</option>
                        
                            <option value="19">15 - 19</option>
                        
                            <option value="24">20 -24</option>
                        
                            <option value="29">25 - 29</option>
                        
                            <option value="34">30 - 34</option>
                        
                            <option value="39">35 - 39</option>
                        
                            <option value="44">40 - 44</option>
                        
                            <option value="49">45 - 49</option>
                        
                            <option value="54">50 - 54</option>
                        
                            <option value="59">55 - 59</option>
                        
                            <option value="64">60 -64</option>
                        
                            <option value="69">65 - 69</option>
                        
                            <option value="99">70 &amp; Over</option>
                        
                </select>

                <select name="gender" id="gender" disabled>
                    
                        <option value="M" selected="">Male</option>
                        <option value="F">Female</option>
                    
                </select>
			
			 
              
  
</body>
</html>

Select allOpen in new window

The next step is for you to create what your ideal form is using static data without worrying if it can or can't be done and let's take it from there.

Ok I have the JSON files downloaded and referenced.  Making great progress.  Here are a  couple of questions:

1) Since I am actually taking the data from an array I will ultimately not get it right from the database but for now I am fine doing that.

2) Ultimately I will have to pass variable values via either a query string or a session variable of some other approach.

I am getting a 500 error from this code (note it seems I have to take out Option Explicit?):

<%@Language=VBScript%>

<!--#include virtual = "/includes/JSON_2.0.4.asp"-->
<!--#include virtual = "/includes/JSON_UTIL_0.1.1.asp"-->

<%
Dim conn, sql

Response.Buffer = True		'Turn buffering on
Response.Expires = -1		'Page expires immediately
				
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLNCLI10;Server=localhost\SQLExpress;Database=xxx;Uid=xxx;Pwd=xxx;"

sql = "SELECT ParticipantID, PartName, Age, Gender, EventPl1, EventPl2, EventPl3, EventPl4 FROM SeriesParts"
QueryToJSON( conn, sql ).Flush

conn.Close
Set conn = Nothing
%>

Select allOpen in new window

Here is the error:

Invalid App Id: Must be a number or numeric string representing the application id. all.js:54
FB.getLoginStatus() called before calling FB.init(). all.js:54
Consider using 'dppx' units, as in CSS 'dpi' means dots-per-CSS-inch, not dots-per-physical-inch, so does not correspond to the actual 'dpi' of a screen. In media query expression: (-webkit-min-device-pixel-ratio: 1.5), (min-resolution: 144dpi) follow_button.1404859412.html:1
Consider using 'dppx' units, as in CSS 'dpi' means dots-per-CSS-inch, not dots-per-physical-inch, so does not correspond to the actual 'dpi' of a screen. In media query expression: (-webkit-min-device-pixel-ratio: 1.5), (min-resolution: 144dpi) follow_button.1404859412.html:1
Uncaught TypeError: Cannot read property 'length' of undefined jquery.dataTables.js:3208
(anonymous function) jquery.dataTables.js:3208
_fnBuildAjax.baseAjax.success jquery.dataTables.js:2380
j jquery-1.11.0.min.js:2
k.fireWith jquery-1.11.0.min.js:2
x jquery-1.11.0.min.js:4
b jquery-1.11.0.min.js:4

Select allOpen in new window

Anyone see a problem with it?

one quick question - how are you populating the array with data? that could be an important piece, so if you can verify that, it would help.

also, which link can we use to see your progress?

for item #2, that's fine, we'll get to that part after we get the initial load of the datatable working

Right now I am not using an array.  I am just trying to populate the datatable with data.  Ultimately I will need to use an array because this is not the data I want to display.  This is just to use something that I know is "good" to iron out any issues.  The page can be found at http://www.gopherstateevents.com/series/series_results2.asp?series_id=19

so you plan on hard-coding the data into an array when you use this in a real-world solution?

Right now I have some db experts on this site trying to optimize my query for me.  Right now it writes to an array.  If they are able to turn it into a data query that sorts by totals then I may not need to although I am not sure that is possible.  If not then I will pull the data into an array, sort by total, and write to this page.  That is what is happening here: http://www.gopherstateevents.com/series/series_results.asp?series_id=19 (this is the old version that I am trying to convert to a datatable page for several reasons...the biggest being it is cumbersomely slow when the series in question is at all bigger.  For instance: http://www.gopherstateevents.com/series/series_results.asp?series_id=6&year=2014

Assuming your db will be tuned, you still need to output something.  The array you are talking about is probably via getrows.

For now just make a test case setting the race on the job output page to a specific id.

Create the json/object manually or try the asptojson.  Note that the output is different and uses different example in datatables.

ok good, it is coming from the database, so you'll be able to use the aspJSON library and the code I posted on how to use it. Instead of putting everything into a recordset, then an array, just use the QueryToJSON() I showed you, and that's it, nothing more is needed for that.

as for your errors, the first thing I noticed is the reference to the AppID has to do with your facebook plugin you're using seems to be having an issue. Also, when I looked at this page, you still had the form submitting to itself whenever a dropdown option was selected. I would try removing that and see what happens.

Update:
1) I now have the "results.asp" page opening a sql statement with a specific series_id.
2) I temporarily removed my facebook reference...no change...so I put it back.  I did find a missing closing tag in a <td> however.
3) I have removed any form submissions on the page in anticipation of using AJAX to do that eventually.

Still no success.  Again, the page we are working on is this one.

Please look carefully at my html/ajax and let me know if you see a problem.  I am going to continue to look carefully myself.

Thanks again for the help!

Looks like the error is in the .js file???ajax-error.docx

just saw  this come through, everything looks ok to me, but let's try tweaking it a bit. change your  datatables initialization code to:

$(document).ready(function() {
	$('#standings').dataTable( {
		"processing": true,
		"serverSide": true,
		"ajax": "/series/results.asp"
	} );
} );

Select allOpen in new window

I may be able to help a bit more tomorrow morning, depends on a few things. If not, maybe Scott (or any other experts of course!) can help you progress farther if he is around, otherwise I'll try to check tomorrow or over the weekend if I get a chance.

you can use the link below for reference / help if you like:

http://www.datatables.net/examples/data_sources/server_side.html

The problem we are having is I have pointed you to use datatables previously and again here in the first post but using a slightly different method.   The BM and I have not given you the same method to use datatables and that probably has caused some confusion.

The method I was showing you first was using an array and The BM switched to an ojbect. One is not better than the next, just different.

I have been using datatables in my projects for a good handful of years and it was very confusing at first to get this right.   I have also been  using asptojson for a long time as well.  I have specifically chosen not to use the the plug in to connect directly to your database when you need to accept user input because it is not safe.  It will take a little more coding (but not much) to go what what I started, but  you can clean your data before accepting it and use a parameterized query.  For sake of the direction of this question in the current form, here is what you need for your jquery.

$(document).ready(function() {
    $('#standings').dataTable( {
        "ajax": {
            "url": "/series/results.asp",
            "dataSrc": ""
        },
        "columns": [
            { "data": "ParticipantID" },
            { "data": "PartName" },
            { "data": "Gender" },
            { "data": "Age" }
        ]
    } );
} );

Select allOpen in new window

You can see I have created a live test case using a portion of your data   http://jsbin.com/zeyaki/1/edit

Also, if I have not mentioned already, part of the trouble shooting should be to check your json output.  You can copy/paste it to a linter like http://jsonlint.com/.  I checked out your output and it is good.

This has been very helpful.  I now have a datatable that works by pulling data directly from a database query.  While waiting and hoping that the db folks get me a more efficient query (this one is not the one I ultimately want) I will try to make this page more dynamic.  Ultimately I may need to go back to finding out how to populate a datatable with an array.  I will read through the assistance given by both and try not to bother you all with information that has already been given.

I can't express how much you have both helped me get to this point!

By far the most helpful assistance I have ever received on EE!  Thanks so much!!

Thank you! for the compliments.  It is as much a thrill for us as it is for you and especially when we can see how this is impacting your site.