oTable.fnAddData(responseText) and column rows

met00

Have a form that takes the # of days in the past and makes an ajax call to get the data from the database.

Then I attempt to insert the data into the empty existing table.

I have attempted returning the data in the following formats:

[code]
[ "aaData": ["29496","01/31/2010 18:08:28", " User Name
865252 Ervindale street
Spears, Louisiana 71227
870-000-0000
useraaa@yahoo.com "," 1 x Civil War All-in-one Bulletin Board Set SKU: GAL0635063808
--------------------------------------
USAePay

Sub-Total: $9.19
United Parcel Service (XML) (UPS 3 Day Select): $14.55
Low Order Fee: $1.95
Total: $25.69",""] ["29495","01/31/2010 17:39:28", " full name1
020B Nitany Hall
University, Pennsylvania 16829
484-000-0000
user@yahoo.com "," 1 x Candy Land SKU: MBG4700
--------------------------------------
USAePay

Sub-Total: $11.03
United Parcel Service (XML) (UPS Ground, 2010-02-04): $9.29
Low Order Fee: $1.95
Total: $22.27",""]]
[/code]

and

[code]
[["29496","01/31/2010 18:08:28", " User Name
865252 Ervindale street
Spears, Louisiana 71227
870-000-0000
useraaa@yahoo.com "," 1 x Civil War All-in-one Bulletin Board Set SKU: GAL0635063808
--------------------------------------
USAePay

Sub-Total: $9.19
United Parcel Service (XML) (UPS 3 Day Select): $14.55
Low Order Fee: $1.95
Total: $25.69",""] ["29495","01/31/2010 17:39:28", " full name1
020B Nitany Hall
University, Pennsylvania 16829
484-000-0000
user@yahoo.com "," 1 x Candy Land SKU: MBG4700
--------------------------------------
USAePay

Sub-Total: $11.03
United Parcel Service (XML) (UPS Ground, 2010-02-04): $9.29
Low Order Fee: $1.95
Total: $22.27",""]]
[/code]

and finally

[code]
["29496","01/31/2010 18:08:28", " User Name
865252 Ervindale street
Spears, Louisiana 71227
870-000-0000
useraaa@yahoo.com "," 1 x Civil War All-in-one Bulletin Board Set SKU: GAL0635063808
--------------------------------------
USAePay

Sub-Total: $9.19
United Parcel Service (XML) (UPS 3 Day Select): $14.55
Low Order Fee: $1.95
Total: $25.69",""] ["29495","01/31/2010 17:39:28", " full name1
020B Nitany Hall
University, Pennsylvania 16829
484-000-0000
user@yahoo.com "," 1 x Candy Land SKU: MBG4700
--------------------------------------
USAePay

Sub-Total: $11.03
United Parcel Service (XML) (UPS Ground, 2010-02-04): $9.29
Low Order Fee: $1.95
Total: $22.27",""]
[/code]

The table has 5 columns. When the ajax returns the return callback attempts to issue the fnAddData with the returned data. I get "Warning - Added Data does not match number of known columns" In every form (shown above) the data in the inner array (and the individual arrays when I don't enclose in brackets) does have 5 columns of formatted HTML (and yes, in the empty column I have used " " instead of "" ).

As for why this way and not some other... we are providing 5 different methods to allow the user to pull data from the database as it is intensive for the system to pull lots of records (it can take a minute to make all the calls to create the report for just a week depending on how busy the store is and how many products are in each order). In order to "limit" the pain from creating the report we allow it to be done (1) Between 2 dates, (2) Start date + N days, (3) End date - N days, (4) Current day - N days and (5) Are you really sure you want to dump the entire database?

Any suggestions?

allan

First thought is that you have new lines inside your strings, which makes this invalid json. http://jsonlint.com is great for checking if JSON is valid or not - which is always the first place I go to check that.

If you want 'new lines' to appear in your HTML, then replace \n with
. If the new lines aren't important, then just remove them.

Allan

met00

[code]
[
iTotalRecords: 6,
iTotalDisplayRecords: 6,
aaData: [
"29496",
"01/31/2010 18:08:28",
"user name
1234 Ein Rd
sville, Louisiana 77777
870-000-0000
user@yahoo.com",
" 1 x  Civil War All-in-one Bulletin Board Set    SKU: GAL0635063808
--------------------------------------
USAePay

Sub-Total: $9.19
United Parcel Service (XML) (UPS 3 Day Select): $14.55
Low Order Fee: $1.95
Total: $25.69",
""
]
]
[/code]

is giving me a lint error:
syntax error, unexpected ':', expecting ']' at line 2
Parsing failed

on the other hand...

[code]
[
"29496",
"01/31/2010 18:08:28",
"user name
222 iplirin Rd
Spoolopoini, Louisiana 77977
870-000-000
user@yahoo.com",
" 1 x  Civil War All-in-one Bulletin Board Set    SKU: GAL0635063808
--------------------------------------
USAePay

Sub-Total: $9.19
United Parcel Service (XML) (UPS 3 Day Select): $14.55
Low Order Fee: $1.95
Total: $25.69",
" "
]
[/code]

generates as totally valid (Valid JSON), yet still generates
"Warning - added data does not match known number of columns"

and the table is:

[code]




Order #DateCustomerOrderSelect






[/code]

allan

In the first one, it's an object that you need to return, not an array (although that are mostly interchangeable in Javascript...). Also it should be a 2D array - not a 1D:

[code]
{
sEcho: 1,
iTotalRecords: 6,
iTotalDisplayRecords: 6,
aaData: [ [
"29496",
"01/31/2010 18:08:28",
"user name
1234 Ein Rd
sville, Louisiana 77777
870-000-0000
user@yahoo.com",
" 1 x  Civil War All-in-one Bulletin Board Set    SKU: GAL0635063808
--------------------------------------
USAePay

Sub-Total: $9.19
United Parcel Service (XML) (UPS 3 Day Select): $14.55
Low Order Fee: $1.95
Total: $25.69",
""
],
[ ... ]
]
}
[/code]
Note that I've added sEcho in - it's a really good idea to use this. It ensures the drawing order is correct.

Allan

met00

[code]
{ "sEcho": 1, "iTotalRecords":2, "iTotalDisplayRecords":2, "aaData": [["29498","02/01/2010 11:19:10","user name
1341 B St.
Ball, Massachusetts 02001
508-000-0000
user@gmail.com"," 2 x  Yacker Tracker    SKU: CTU3030
--------------------------------------
USAePay

Sub-Total: $117.70
United Parcel Service (XML) (UPS Ground, 2010-02-08): $13.63
Total: $131.33"," "],["29497","02/01/2010 10:19:10","user2 name
13 C St.
Biship, Massachusetts 02201
508-000-0000
me@you.com"," 2 x  Yacker Tracker    SKU: CTU3030
--------------------------------------
USAePay

Sub-Total: $117.70
United Parcel Service (XML) (UPS Ground, 2010-02-08): $13.63
Total: $131.33"," "]]}
[/code]

everything in the aaData is valid JSON. There are 5 columns of valid JSON data per packet. 2 packets. When I pass the HTML into the oTable.fnAddData(responseText) it still generates the same error message "Warning - added data does not match known number of columns"

(note: I did it with and without the " 's around the field names for sEcho, iTotalRecords, iTotalDisplayRecords and aaData)

allan

But fnAddData is expecting a 1D or 2D array not an object like that, and certainly not a string like responseText usually is. Sorry - I'm confused about what you are trying to do. sEcho, iTotalRecords etc only have any relevance when you are using server-side processing - and if you are, you don't need to call fnAddData - it's done automatically.

Are you loading data yourself (or obtaining it from somewhere) and then using fnAddData to add it to the table? Here is an example of adding a new row: http://datatables.net/examples/api/add_row.html - you'll see it's adding an array. If responseText is a string, then it will need to be parsed to JSON (or just use the JSON return handle from jQuery). Also pass in obj.aaData - if you want to use the above formatting.

Allan

met00

Okay, here is the application...

In a store there is a need at times to contact customers (say all customers who purchased a coffee mug between date 1 and date 2 as it was determined that the coffee mug had lead paint). But, when you have 20,000 orders in the system, you really don't want to load every order. So, before you load the orders into the table, you cann select to do so by

1) start and end date
2) start date + N days
3) end date - N days
4) today - N days
5) The whole enchilada for the sado-masochist

What I am doing is displaying the page with 5 tabs (each one having a form, data entry and a submit button) and an empty table. Then based on the tab and submit button used calling via ajax a program that returns the selected data. Then I will load the returned data into the table with oTable.fnAddData()

Once the data has been loaded into the table then the user can use the features of datatables to reduce the data set, and then use a text entry area (ckeditor) and select boxes on each row to send a mass e-mail message to those selected customers.

Currently I have the page loading up the tabs, the datatable and the editor. I have the tabs working on calling the ajax routine, and the called file does generate the appropriate data. What I haven't been able to do is get that data into the tables.

I attempted to use http://www.packtpub.com/article/data-tables-datatables-plugin-jquery-1.3-with-php as a starting point, but since I have disassociated the ajax query from the datatables I am having challenges getting the data returned (in the formats above) to get into the datatable.

allan

Sounds like a very cool application - nice one :-).

So the format your data needs to be in for passing to fnAddData is

[code]
[
[ 1, 2, 3 ],
[ 4, 5, 6 ]
]
[/code]
i.e. a 2D array. What is the formatting that you currently have?

Allan

met00

The application is very neat (I had it working on another site where the issue of the amount of table data wasn't an issue and it loads very nicely. That version has the ability to allow the user to click a button to opt-out users who never want to get contacted again... that was the challenge I was having with associating click events to the stuff in the tables - and I still have a problem there, but that's another issue for another thread).

here is the ajax return string that is in responseText.

[code]
[
[
"29504",
"02/03/2010 10:37:15",
"user name1
5024 street
Las Vegas, Nevada 89131
702-000-0000
name@yahoo.com",
" 1 x  Yacker Tracker    SKU: CTU3030
--------------------------------------
USAePay

Sub-Total: $58.85
United Parcel Service (XML) (UPS Ground, 2010-02-10): $10.80
Nevada Tax: $4.56
Total: $74.21",
" "
],
[
"29503",
"02/02/2010 11:29:01",
"user name2
10 Road
city, New Hampshire 03051
603-000-0000
mail@mail.org",
" 1 x  2 Self-adhesive Letters Yellow    SKU: PAC51652
1 x  Glitter 1 Lb. Gold    SKU: CK-8917
1 x  Glitter 1 Lb. Blue    SKU: CK-8915
6 x  Assorted Frames    SKU: R-22041
1 x  4 Self-adhesive Letters Blue    SKU: PAC51623
2 x  4 Self-adhesive Letters Yellow    SKU: PAC51622
1 x  Colorful Cut-outs Gold/silver Stars 36/pack Single Design    SKU: CD-5544
3 x  Design Paper Star Students    SKU: TF-3527
2 x  Border Gold And Silver Stars Scalloped    SKU: CD-1229
1 x  2 Self-adhesive Letters Blue    SKU: PAC51653
--------------------------------------
USAePay

Sub-Total: $113.90
United Parcel Service (XML) (UPS Ground, 2010-02-09): $12.95
Total: $126.85",
" "
],
[
"29502",
"02/02/2010 10:01:46",
"user name3
51 drive
cherry hill, New Jersey 08003
123-456-7890
user3@gmail.com",
" 1 x  Loud & Clear 4-position Headphone Jack Box    SKU: EI-3917
--------------------------------------
USAePay

Sub-Total: $11.03
United Parcel Service (XML) (UPS Ground, 2010-02-08): $9.29
Low Order Fee: $1.95
Total: $22.27",
" "
],
[
"29501",
"02/02/2010 04:49:50",
"user name4
10 Drive
Annapolis, Maryland 21401
123-456-7890
mail@msn.com",
" 1 x  Flash Cards Multiplication 0-12 91/box    SKU: T-53105
1 x  Three-corner Flash Cards Multiplication And Division 48/pack    SKU: T-1671
--------------------------------------
USAePay

Sub-Total: $13.78
United Parcel Service (XML) (UPS Ground, 2010-02-08): $9.29
Low Order Fee: $1.95
Total: $25.02",
" "
],
[
"1234",
"trec",
"trec",
"trec",
"trec"
]
]
[/code]

before I load it I first make sure it's clean

[code]
var rtxt = jQuery.trim(responseText);
oTable.fnAddData(rtxt);
[/code]

The last row is on there for testing purposes only (to make sure all the data was loaded)

here is where I set the datatable up..

[code]
$(document).ready(function(){
oTable = $('#the_table').dataTable({
"bJQueryUI": true,
"sPaginationType": "full_numbers",
'aoColumns': [
null,
null,
null,
{ 'bSortable': false, 'sClass': 'flag' },
{ 'bSortable': false, 'sClass': 'flag' }
],

} );
} );
[/code]

allan

Hi met00,

Your JSON there looks perfectly valid, correct number of rows etc. The one thing I can see that looks a bit odd is that you are passing a 'string' to fnAddData, rather than a JSON object (it just so happens that your string looks like JSON data). To confirm if this is the case you could do alert( typeof rtxt );

So what you need to do is evaluate your JSON. json2.js is ( http://www.JSON.org/js.html ) is a good way of doing this. jQuery has an option built into 1.4 now as well: http://api.jquery.com/jQuery.parseJSON/ , or eval() if you need it...

Regards,
Allan