[gymratz]

I am putting together a kind of "build your own report" page that will have up to 20 options for people to select.
Each option, selected by checkbox or dropdown (probably) will add on to the SQL data source to pull only the information needed and display it in a gridview.

For instance, the initial query is:

Select
	C.Name,
From Clients as C
INNER JOIN Rep_Client as RC on RC.Client_ID = C.ID
INNER JOIN Employees as Emp on Emp.ID = RC.Emp_ID AND Emp.ID = '10' AND RC.Eng_ID is NULL
Where
	IsNull(C.Terminated, 'False') = 'False'

If the user wants to see what their role with that client is, they would click a checkbox that would update the data source and add the following:

(
		Select
			RC.Position
		From Employees as Emp
		INNER JOIN Rep_Client as RC on RC.Emp_ID = Emp.ID
		Where
			Emp.ID = @Emp AND
			RC.Client_ID = C.ID AND
			RC.Eng_ID is NULL
	) as [My Position],

Problem:
A lot of these columns are datetime data type in SQL. I need to format it in the gridview as

DataFormatString="{0:MM/dd/yy}" HtmlEncode="False" 

How can I do this, preferably referring to the column using the column name of "My Position" and not a numeric...


If this can NOT be done....
The other option is to query everything and format it - and then hide columns in the gridview based on items that are not checked. Would this be easier? (Doesn't sound as efficient to me).

If anyone has a much smarter way to do this let me know!
Thanks much!

[gymratz]

This may not be the best option, but this is what I am using for now:

RowDataBound event:

Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
		If e.Row.RowType = DataControlRowType.DataRow Then
			Dim d As DateTime
			For Each cell As TableCell In e.Row.Cells
				If Date.TryParse(cell.Text, d) AndAlso d.TimeOfDay.Ticks = 0 Then
					cell.Text = d.ToShortDateString()
				End If
			Next cell
		End If
	End Sub

I found this code at: http://stackoverflow...ed-on-data-type

If anyone has a better solution, I am open to ideas!

Thanks,

[Jayman]

I would say that you are currently using the best solution, considering that you won't know ahead of time what the column data types will be.