Using the datagrid in the data table

I am using the datagrid to display the data to the user.I would like to display the date in mm/dd/yyyy and no time.Also, In REC_GIVEN, if it 'FALSE' it should display 'GIVEN' and if it is 'TRUE', it should display 'RECEIVED'.Please let me know if you need additional information.Thanks for your help.

here is what I am using..

        Dim gft As Gifts = New Gifts
        Dim dtGifts As DataTable = Gifts.GetGiftsSelectionCriteria(gft, filter)
        Me.DataGrid1.DataSource = dtGifts
        Me.DataGrid1.DataBind()


this is what is being displayed at this moment....

GIFT_NO       EMP_ID      DATE_RECEIVED             REC_GIVEN     GIFT_DESC         BUS_REL               E    ST_VAL

97               t46905           7/5/2005 12:00:00 AM      False            sweets      sgfsgsfgsfgzvsrgbz        42542
102             t46905           7/5/2005 12:00:00 AM      False            tickets       afdfafafafafafaf             4243
103             t46905           7/5/2005 12:00:00 AM      False            tickets       fvdsadgag                     421342
104             t46905           7/5/2005 12:00:00 AM      False            tickets       fasdugui                        421342
105             t46905           7/5/2005 12:00:00 AM      False            tickets       ghahga                         421342
106             t46905           7/5/2005 12:00:00 AM      False            tickets       fadgaa                          421342
107             t46905           7/5/2005 12:00:00 AM      False            shirts         fadgaa                          252
108             t46905           7/5/2005 12:00:00 AM      False            Shirts         fadgaa                           252  
109             t46905           7/5/2005 12:00:00 AM      False            Shirts         fadgaa                           252  
110             t46905           7/5/2005 12:00:00 AM      False            Lunch Cpn    fadgaa                          4234





g46905Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff CertainCommented:
Your best bet is to modify your code at the SQL level using a CASE statement

As to the datetime field... you can also cast that in SQL server... CONVERT(DATETIME,DATE_RECEIVED,101) AS DATE_RECIEVED

SELECT
CASE WHEN REC_GIVEN='False' THEN 'Given' ELSE 'Received' END AS REC_GIVEN,
CONVERT(DATETIME,DATE_RECEIVED,101) AS DATE_RECEIVED.... add the remainder of your SQL statement here.
NowaYCommented:
I usually use helper functions in a template column when my datagrid renders.

Chaosian's suggestion should work, but I like to keep my business logic out of the data layer.


<asp:TemplateColumn HeaderText="FieldName" Visible="true">
      <ItemTemplate>
      <%# HelperFunction(DataBinder.Eval(Container.DataItem, "datafield")) %>
     </ItemTemplate>
</asp:TemplateColumn>

code behind:


    Public Function HelperFunction(ByVal value As boolean) As String

        If value = True Then
            HelperFunction= "RECEIVED"
        Else
            HelperFunction= "GIVEN"
        End If

    End Function

g46905Author Commented:
I am not very familiar with SQL statements.This is the SQL statement that I am using currently.

"SELECT *    FROM  [GIFTS] WHERE  DATE_GIFTED >= '07/04/2005' AND  DATE_GIFTED <= '07/06/2005' AND  EMP_NO = 't46905'"

how and where do I add this to ..


SELECT
CASE WHEN REC_GIVEN='False' THEN 'Given' ELSE 'Received' END AS REC_GIVEN,
CONVERT(DATETIME,DATE_RECEIVED,101) AS DATE_RECEIVED.... add the remainder of your SQL statement here.


Thanks for your help
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Jeff CertainCommented:
Noway...

Yeah... but you should always be using stored procedures to return the data from your database, so some creep between the data layer and business logic is inevitable.

g,
As a side note, you should always be explicitly identifying the columns you want returned. While this might take some additional typing on your part, avoiding the use of * will improve the efficiency of your queries. In addition, a frequesnt side benefit is that you return only the columns you need, since you don't want to type extra column names... :)

SELECT GIFT_NO, EMP_ID, CONVERT(DATETIME,DATE_RECEIVED,101) AS DATE_RECEIVED,
CASE WHEN REC_GIVEN='False' THEN 'Given' ELSE 'Received' END AS REC_GIVEN,
GIFT_DESC, BUS_REL, E, ST_VAL
FROM  [GIFTS] WHERE  DATE_GIFTED >= '07/04/2005' AND  DATE_GIFTED <= '07/06/2005' AND  EMP_NO = 't46905'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
g46905Author Commented:
Chaosian,
              First , I would like to thank you for all the help.Above mentioned statement works, but the date is still the same..

Can you tell me where I am supposed to have this one..


        As to the datetime field... you can also cast that in SQL server... CONVERT(DATETIME,DATE_RECEIVED,101) AS DATE_RECIEVED
Jeff CertainCommented:
It's in the first line of the SQL statement above... :)
g46905Author Commented:
For some reason, this is not working....Can you tell me why? Thanks for your patience.
Jeff CertainCommented:
If they're all showing up in that format, then you'll need to play with the DataGrid column definitions... :(

Are you autogenerating your columns?
NowaYCommented:
Chaosian,

I would typically have a DAL actions class that would manipulate the data and bus the data around using an arraylist or dataset if need be.

It just depends on your applications architecture I guess.

Yours is a good solution, but as always there are a million alternatives.
Jeff CertainCommented:
Noway,

I've got to admit that n-tier applications aren't my forte. However, I still maintain that you should be using stored procedures to allow the database engine to pre-compile the execution plan and execute your queries as efficiently as possible.

g46905Author Commented:
Chaosian,
              I am generating the columns from the data table.Please let me know if you can think of anything else.

Thanks for your help.
NowaYCommented:
I agree with you. Stored procedures offer a great performance benefit. I use them extensively, but I still try to avoid putting too much business logic in them (tempting as it may be).
Jeff CertainCommented:
g,

I know where you're generating them from... I saw the "DataSource =" statement...

Does autogeneratecolumns = true?
Jeff CertainCommented:
Noway,

Is it considered business logic to translate bit values (0/1) into readable text on the server? Personally, I'd be using a bit data type instead of the varachar(5+) field being used in this instance, but....

Jeff
g46905Author Commented:
Can you tell me what is wrong in this statement.

SELECT GIFT_NO, EMP_NO, CONVERT(DATETIME,DATE_GIFTED,101) AS DATE_RECEIVED,CASE WHEN RECEIVED_GIVEN= 0 THEN 'Given' ELSE 'Received' END AS REC_GIVEN,GIFT_DESC, BUS_RELATION, EST_VALUE FROM([Gifts]WHERE DATE_GIFTED >= '07/04/2005' AND  DATE_GIFTED <= '07/06/2005' AND  EMP_NO = 't46905'

This is the error I am getting..

Incorrect syntax near the keyword 'WHERE'.
Jeff CertainCommented:
change
 FROM([Gifts]WHERE
to
 FROM [Gifts] WHERE
NowaYCommented:
Chaosian ,

I would say... it depends. It's preference, really. I like to do as little on the database server as possible because it's typically more of a bottleneck for me.

Sorry to hijack the thread with this discussion, g!
Jeff CertainCommented:
Noway,

I'm kind of fortunate in that regard... the project I'm currently working on has a pretty hefty dedicated database server. And a gig-E connection about 6 inches long between the web server and the db server :):):)
g46905Author Commented:
I am able to display the data in the datagrid.I would like to give the user a sorting option.How would I be able to do this.Could you please tell me how I can achieve this? Thanks for your help.Ifound this code while surfing the net and I was wondering if this can be used with my code? If yes can you tell me the changes we need to make to get this working?


Private Sub dgReport_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles dgReport.SortCommand
        Dim dsWPReport As DataSet = Session("dsWPReport")
        Dim dv As DataView = New DataView(dsWPReport.Tables("REPORT_VW"))
        Dim lastSortExpression As String = Session("lastSortExpression")
        Dim sortExpression As String = e.SortExpression
        If lastSortExpression <> "" Then
            If lastSortExpression.IndexOf(sortExpression) = 0 Then
                sortExpression = sortExpression + IIf(lastSortExpression.IndexOf("DESC") > -1, "", " DESC")
            End If
        End If
        dv.Sort = sortExpression
        Session("lastSortExpression") = sortExpression
        dgReport.DataSource = dv
        dgReport.DataBind()
    End Sub










Thanks,
Jeff CertainCommented:
g,

A new question means a new post. Them's the rules around here...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.