customize raw data before binding? 2

[jn03]

Hi all,

I want to modify raw data retrieved from the database before binding it to datagrid/datalist. For example, I have a date column with date and time format, something like "06/04/2003 10:01:00 PM" and I want to customize the date format, as well as get rid of the time, to display just the customized date nicely on the datagrid. Could someone show me how to do it? I mean, I know how manipulate the date format, I just don't how to do it in terms of asp.net, datagrid binding, where to do it in my code-behind. Thanks

 

[Rhys666]

What data source are you using?
Could you do it in your SELECT statement, (or equivalent)...

Rhys

 

[bigfoot]

Look this up in the DataGrid help: ItemCreated, and ItemBound. In these 2 Methods, it gives you samples of what you are trying to do, and how to do it. Been there too.

 

[jn03]

Hi again Rhys

I'm using dataset.
I don't know if it can be done in the select statement since there is more than just the date example. I have another column, Status, which returns characters like C (closed), P (processed), O (open), etc,. When displayed on the column status of the datagrid, I want these to be like Closed, Processed... instead of C, P as they are in the database.
I'm thinking of handling it this way: iterate through the rows of the table in the dataset and change the values in each row before binding the dataset to the datagrid. I want to try this out to see, unless you have better suggestion...

BTW, could you show me how to do it with the select statement using the date example? Thanks.

 

[jn03]

Bigfoot, I'm looking it up. Thanks for the tip.

 

[Rhys666]

I use MS SQL Server 2000, so we're talking T-SQL here. In your select statement you can use the SQL functions like CAST and CONVERT, DATEADD to wrap up individual columns

I.E.,
One Table, owner: dbo, name: Dates
Four Columns in Table: DateOne,DateTwo,DateThree,DateFour
DateFormat of Date fields: ddd mm yyyy hh:mm:ss
SELECT
CAST(DateOne AS VARCHAR(1)) AS 'DateOne'
FROM
dbo.Dates

Will convert The values in the DateOne column to a VARCHAR(11) datatype. The total number of characters in the column DateOne is 20, the VARCHAR length is set to 11, so the training characters 12 to 20 are removed during conversion. Basically this returns one column called DateOne containing values of the VARCHAR(11) data type.

Extend this concept...
SELECT
CAST(DateOne AS VARCHAR(1)) AS 'DateOne',
GetDate() AS 'CurrentDate',
DateAdd(dd,DateTwo,1) AS 'DateTwoPlusOneDay'
FROM
dbo.Dates

...as far as you like.

What you you really need to know is the syntax for the appropriate database language, and you can set up stored procedures or queries to return all of your data already formatted so you don't need to do it in ASP.net.

The methodology is really up to you in the end, as you can format data in a datagrid and datatable by iterating through columns etc as well.

Based on your hardware, which is going to be more efficient? Find this out and then re-examine the problem...

Rhys

 

[jn03]

I've got it using the DataGrid's ItemDataBound method. yay!

Rhys, I'm using Visual FoxPro database. I'll look into FoxPro for the syntax similar to yours as I want to deal with the date format that way.

Thanks, guys, for your help!