Yes i agree, but the way i was designing the datbase each grid column will also have a datatype field associated to it e.g.

col1 varchar(40)
col1datatype char(1)
col2 varchar(24)
col2datatype char(1)
.
.
. and so on

where the datatype is I,T,F,D (integer,text,float,Date). This allows the main data table of 200,000 records to all be VARCHAR(40) but sorted by a particlaur type. (presuming data is valid!!!!) by casting.

So i will interpret the I,T,F,D on the server side and SQL CAST() it, it will then be in the correct form for the grid so no need to use sorttype.

For a one off project i would just define the datatypes at the datbase level, but this is supposed to be a generic design of upto 20 columns each column having different types of data...and as i mentioned earlier there is a CATEGORY field, and col1 for categoryA could be a date, but col1 for categoryB could be an integer....hence the 'warpping of teh data in a VARCHAR(40)

Thanks for the guidance once again....when i eventually get paid for this i'll have to donate some beer money!!!

Regards

MarkC

So I think you'll have to send the sort-type to the host, and sort there...

Mark

But, i am using 200,000 records (LIMIT 20 at a time SQL) with scroll:true, so no paging.

Is it possible to switch between 'local' and 'xml' when we start scrolling, similar to when we start paging (onPaging). So as we scroll and retrieve the xml data, when it stops, i set it to local, refresh grid and data takes on the from of an integer again.

Also, i am filterng the data by searching on keypress, so would have to do something similar , switch to XML when keypressed get data, swicth back to local,refresh grid.

Regards

MarkC

I will have a look.

I am using scrolling not paging as we are talkng about 200,000 records and i am allowing the user to auto incremtn search on keypressing. (like your bigset.php demo)

But i'll have a look into the local setting.

regards

MarkC

I think that you maybe should look here:

http://www.trirand.com/jqgridw.....ide_paging

Regards

Tony

I Think that Mark catch the problem in the right way.

In order to perform sorting client side you should set the datatype to local. Just test it

Regards

Tony

Will have a look at your suggestions.

Regards

Mark C.

strSQL = "SELECT DOCID,CAST(INDEX1 AS SIGNED) as index1,INDEX2 FROM DOCUMENTS " &strWhere
strSQL = strSQL & " ORDER BY "&strIdx&" "&strOrd&" LIMIT "&strStart&" , "&strLimit&" "

this takes VARCHAR(20) INDEX1 and casts it into an signed integer, so when ordering it will be corectly sorted like an integer.

Works fine. BUt i thought the grid would do it using sorttype!!!

I think your problem is on the server side. sorttype only applies to local sorting, and I dont think you're using that (you /could/, by fetching all the data, and then switching to datatype: "local", but you didnt mention that).

So you need to look at your sort code on the server. I think what you need is to add the datatype to postdata in a beforeRequest handler.

Basically, in your beforeRequest handler, look up the sorttype for the sort column, and add that to postdata (any name you like), and then modify you SQL to sort according to that...

Mark

Simply...what is sorttype? maybe it's not what i thought it was, as in take any data and sort it like it was an integer?

The reason i get a sorted column like

1
10
2
2000
300

is that the SQL has already sorted the data based on it's datatype (VARCHAR) This is then parsed into the XML cells. EWhixh is then displayed in the grid. I thought the sorttype might do something with teh xml ordering, by saying now define it as an integer even though it is a varchar. Basically, sort a VARCHAR as if it was an integer to get 1,2,10,300,2000, hence my first question why use sorttype if it has already been sorted as a varcgar,data,integer,float by the SQL:

strSQL = "SELECT DOCID,INDEX1,INDEX2 FROM DOCUMENTS " &strWhere
strSQL = strSQL & " ORDER BY "&strIdx&" "&strOrd&" LIMIT "&strStart&" , "&strLimit&" "

and parsed into XML in that order.

Regards

MarkC

Could you please post the entry jqGrid options(including colModel and etc) - without modifications.

Best Regards

Tony

Yes i am using sorttype: 'int'

Maybe my post was too long winded...simply put....

If you have a column that in MySQL is defined as varchar(10) and it holds some data say 5 records  1,2,10,2000,300. Then sorting this as text (its natural datatype varchar) you get 1,10,2,2000,300 which is correct.

But without changing the datatype in the MySQL table, i want to sort it has if it was an integer, resulting in: 1,2,10,300,2000 which i thought i could do by setting sorttype: 'int' on the column. I tried that but I still get 1,10,2,2000,300 (as if it was still text).

As i mentioned it's like casting text data to integer data (presuming the text data is valid and contains 0-9 characters)

Regards

MarkC

Not sure, but you define SORTTYPE, you should sorttype :'int'

Regards

Tony

(Using MySQL->ASP->XML)

I am doing a project to define the grid from a database (like a grid contruction kit, with a backend). The data table to fill the grid will initially have 3 fields called col1,col2,col3 they will all be VARCHAR(30). We are not concerned with the datatype in the table it could be dates, integers, text, floats all wrapped around a varchar (you'll see why later)

All is working fine and indexing/sorting correctly based on text sorttype. Now i'm trying to define datatypes at grid level. (presume data is correct for now e.g. integer will only have strings of 0to9)

My next step is to allow col1,col2,col3 to be associated as integer/text/date in the backend database, so even though they are defined as VARCHAR(30) in the database, i thought i could use SORTTYPE to define how they are sorted at grid level.

For example: if col1 has 3 records with data:

1
10
2

When SORTTYPE set to INT, i was expecting

1
2
10

even though the data at database level is varchar

But i still get 1,10,2.

Is this possible, do i need to look at formatter module,to cast Varchars into a different type for sorting. Any advice or steering in the right direction would be helpful.

--

The reason i am not defining the datatypes at database level, is eventually this needs to be a generic system with upto 7 columns all based around a CATEGORY, each category will have a different set of data e.g. col1 could be an integer for categoryA, but col1 for CategoryB could be TEXT...and so on. SO i thought define them as Varchars and handle the types at grid level if possible. All the data is held in one table and when a user selects a category (dropdown combo) it reloads the grid framework and data defined by the currently selected category.

Regards

Mark C