DataGrid displays DataTable, I want to format date field.

[Wokawidget]

Have returned a datatable from the server.
Have displayed all the data in a datagrid.
I want to format the column "EntryDate" as "mm/dd/yyyy"

Woka

[crptcblade]

Change the data formatting expression for the datagrid column to {0:mm/dd/yyyy}

[Wokawidget]

Errr...So that would require me to setup columns 1st?

At the moment I am doing:

Dim Woof As DataTable = GetMeMyData
DataGrid1.DataSource = Woof
Datagrid1.DataBind

So, I am assuming, from the way you suggested I should do:

Dim Woof As DataTable = GetMeMyData
'setup columns here
DataGrid1.DataSource = Woof
Datagrid1.DataBind

How would I do this?
Lets say for example I wanted the following columns:

DataTable FieldName;DataType;Column Heading

So the columns I would like are:

AssetName; String; "Asset"
BookedFrom; Date; "Booked From"
BookedTo; Date; "Booked To"

The DataTable holds other fields, but I want these for other things, I will come to this laterz.

Woka

[crptcblade]

Dim bcAsset AsNew BoundColumn
Dim bcBookedFrom AsNew BoundColumn

Dim bcBookedTo AsNew BoundColumn

With bcAsset

.DataField = "AssetName"

.HeaderText = "Asset Name"

EndWith

DataGrid1.Columns.Add(bcAsset)

With bcBookedFrom

.DataField = "BookedFrom"

.DataFormatString = "{0:MM/dd/yyyy}"

.HeaderText = "Booked From"

EndWith

DataGrid1.Columns.Add(bcBookedFrom)

With bcBookedTo

.DataField = "BookedTo"

.DataFormatString = "{0:MM/dd/yyyy}"

.HeaderText = "Booked To"

EndWith

DataGrid1.Columns.Add(bcBookedTo)

DataGrid1.DataSource = dt

DataGrid1.DataBind()

Something along those lines.

[Wokawidget]

Cheers. Much appreciated.
One last q

I have 2 columns:

BookedFrom and BookedTo

These are displayed as:

dd/mm/yyyy hh:nn

Now, can I convert this to one column and show the data as:

dd/mm/yyyy hh:nn to hh:nn

Even though my datatabel is bringing 2 fields back?

ie, is there an event I can trap for when it's going to put something in a column, so I can change what it puts in?

Does that make sense?

Woka

[Wokawidget]

Damn...when I add columns in the data gets placed in these columns, but it also add the columns again.

So I have 2 BookedFrom column and 2 BookedTo columns and 2 AssetName column, plus the columns I haven't defined manaually...

Woka

[crptcblade]

Well, for your first question, I always use a template column, and use the Custom binding expression field to work out the display.

For your second question, check the property builder for the grid. Make sure "Generate columns dynamically" (or whatever it is) on the Columns tab is unchecked.

[Wokawidget]

Well, for your first question, I always use a template column, and use the Custom binding expression field to work out the display.

Don't suppose you have any code for this?

Cheers again, you've been more help tha the contractors here

Woka

[crptcblade]

Well, you create a template column in the property builder, and hit OK.

Then right-click and go to Edit Template->Whatever column it is

Add a label to the ItemTemplate portion, select the label, go to the Properties window and choose the Databindings property.

Select Text on the left, and Custom binding expression on the right.

Add this to the text area...

MakeDateRange(DataBinder.Eval(Container.DataItem, "BookedFrom"),DataBinder.Eval(Container.DataItem, "BookedTo"))


Then, go to your code and add this...

PublicFunction MakeDateRange(ByVal FromDate As DateTime, ByVal ToDate As DateTime) AsString

Return Format(FromDate.Date, "MM/dd/yyyy") & " to " & Format(ToDate.Date, "MM/dd/yyyy")

EndFunction

That should do it.

[indydavid32]

crptcblade, thanks for you post I can now format my date field in my datagrid. I do have a question though. Since I add that bound column, the Date field is in there twice. I can easily hide the one I don't want, but that seems a little sloppy to me. Is there a better way? Here's my code.

Dim cn1 As New OleDb.OleDbConnection("Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
                                             & "Persist Security Info=False;" _
                                             & "Initial Catalog=McClure_Web;" _
                                             & "UID=sa;PWD=;" _
                                             & "Data Source=Zeus06")
            cn1.Open()
 
            Dim datAdapt As New OleDb.OleDbDataAdapter("Select [Date], Odometer as [Mileage], " & _
            "Remarks, MaintTotal as [Total Cost], Order_No From Garage_Maint Where Vehicle_ID = " & _
            cmbVehicle.SelectedValue & " Order By [Date] Desc", cn1)
 
            Dim datSet As New Data.DataSet
 
            datAdapt.Fill(datSet, "Garage_Maint")
 
            Dim bcDate As New BoundColumn
 
            With bcDate
                .DataField = "Date"
                .DataFormatString = "{0:MM/dd/yyyy}"
                .HeaderText = "Date"
            End With
            DataGrid1.Columns.Add(bcDate)
 
            DataGrid1.DataSource = datSet
            DataGrid1.DataKeyField = "Order_No"
            DataGrid1.Columns(0).HeaderText = "Select to view Instructions"
            DataGrid1.DataBind()
            cn1.Close()
            cn1.Dispose()
            datadapt.Dispose()
            datSet.Dispose()
            DataGrid1.Visible = True
 
Private Sub DataGrid1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemDataBound
        e.Item.Cells(2).Visible = False
    End Sub

Thanks

[indydavid32]

bump

[Wokawidget]

turn off auto column generation, and manually create the columns you want.
Right click on the grid and cliuck property builder. Then click columns. YUou can now add bound columns.

Woka

[indydavid32]

Thanks woka. I will give that a shot. Can you put combo boxes inside an asp datagrid? If so, do you have an example?

Thanks again.

[Wokawidget]

<Columns>
	<asp:TemplateColumn HeaderText="This Is A Template Column">
		<ItemTemplate>
			<table>
				<tr>
					<td>
						This is a combo:
					</td>
					<td>
						<asp:DropDownList ID="cboItems" Runat="server"></asp:DropDownList>
					</td>
				</tr>
				<tr>
					<td>
						This is a textbox:
					</td>
					<td>
						<asp:TextBox ID="txtUsername" Runat="server"></asp:TextBox>
					</td>
				</tr>
			</table>
		</ItemTemplate>
	</asp:TemplateColumn>
	<asp:TemplateColumn HeaderText="A Smaller column">
		<ItemTemplate>
			<table>
				<tr>
					<td>
						This is a smaller column
					</td>
				</tr>
			</table>
		</ItemTemplate>
	</asp:TemplateColumn>
</Columns>

[indydavid32]

Woka, thank you for all your help with this. I unchecked "Create columns automatically at run time" and I can now make the boundcollumns I want. I am now having a problem reading from the datagrid.

In the past I would read a cell from the grid with the following code:

strDate = Trim(CType(e.Item.Cells(2).Controls(0), TextBox).Text)

With the new boundcollumns that I create myself, I get the following error with the above code:
Specified argument was out of the range of valid values. Parameter name: index

This is the code I now use to load the datagrid

Dim datAdapt As New OleDb.OleDbDataAdapter
        Dim commBuild As New OleDb.OleDbCommandBuilder(datAdapt)
        Dim datSet As New Data.DataSet
        Dim datRow As Data.DataRow
 
        datAdapt = New OleDb.OleDbDataAdapter("Select * From Vacation_Temp Where " & _
        "Vac_EmpNo = '" & cmbEmployee.SelectedValue & "' Order by Vac_Date Desc", cn1)
        commBuild = New OleDb.OleDbCommandBuilder(datAdapt)
        datSet.Clear()
        datAdapt.Fill(datSet, "Vacation_Temp")
 
        Dim bcDate As New BoundColumn
        Dim bcType As New BoundColumn
        Dim bcHours As New BoundColumn
        Dim bcDetails As New BoundColumn
        Dim bcID As New BoundColumn
 
        With bcDate
            .DataField = "Vac_Date"
            .DataFormatString = "{0:MM/dd/yyyy}"
            .HeaderText = "Date"
            .ItemStyle.Width.Equals(60)
        End With
        With bcType
            .DataField = "Vac_Type"
            .HeaderText = "Time Off Type"
            .ItemStyle.Width.Equals(75)
        End With
        With bcHours
            .DataField = "Vac_Hours"
            .HeaderText = "Hours Off"
            .ItemStyle.Width.Equals(50)
        End With
        With bcDetails
            .DataField = "Vac_Type_Details"
            .HeaderText = "Other Vacation Type Details"
            .ItemStyle.Width.Equals(200)
        End With
        With bcID
            .DataField = "ID"
            .Visible = False
        End With
 
        DataGrid1.Columns.Add(bcDate)
        DataGrid1.Columns.Add(bcType)
        DataGrid1.Columns.Add(bcHours)
        DataGrid1.Columns.Add(bcDetails)
        DataGrid1.Columns.Add(bcID)
 
        DataGrid1.DataSource = datSet
        DataGrid1.Columns(0).HeaderText = "Employee # " & cmbEmployee.SelectedValue
        DataGrid1.EditItemIndex = e.Item.ItemIndex
        DataGrid1.DataBind()

Thank you

[Wokawidget]

I think it's:

Dim DataItem As DataGridItem = CType(e.Item, DataGridItem)
Dim EnteredDate As String = CType(DataItem.FindControl("txtDate"), TextBox).Text

Just curious, not saying your wrong or anything, but why have you created the columns in VB code (code behind) and not set them up in the ASPX page like my example?

Woka

[indydavid32]

I just feel more comfortable with vb and not very comfortable with html.

So with my code above, what would I use for txtDate?

If I were to use the template how would I populate the datagrid with my data adapter?

[indydavid32]

Woka, one more thing I need to know. How do you load data into the dropdownlist control made with the itemtemplate?

Thanks again

[indydavid32]

bump

[Wokawidget]

On the ItemCreated event of the grid you can add some code to populate it.

Dim DataItem As DataGridItem = CType(e.Item, DataGridItem)
Dim cbo As DropDownList = CType(DataItem.FindControl("txtDate"), DropDownList)
 
'set datasource of cbo

Woof

[indydavid32]

Woka, I am not getting this data from a table. I tried the following, but I recevied an error that said "Object reference not set to an instance of an object.".

Here is the html code

<asp:TemplateColumn HeaderText="Time Off Type">
     <ItemTemplate>
          <table>
	    <tr>
	         <td>
		  <asp:DropDownList ID="cboType" Runat="server" width="100"></asp:DropDownList>
	         </td>
	    </tr>
          </table>
     </ItemTemplate>
</asp:TemplateColumn>

Here is the vb code

Private Sub DataGrid1_ItemCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemCreated
Dim DataItem As DataGridItem = CType(e.Item, DataGridItem)
        Dim cbo As DropDownList = CType(DataItem.FindControl("cboType"), DropDownList)
        cbo.Items.Clear()
        cbo.Items.Add("Floating Holiday")
        cbo.Items.Add("Illness")
        cbo.Items.Add("Other Type")
        cbo.Items.Add("Training")
        cbo.Items.Add("Vacation")
End Sub

Thanks again.

[Wokawidget]

Use DataBound event instead and do:

Private Sub grdDockets_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles grdDockets.ItemDataBound
    If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
            Dim DataItem As DataGridItem = CType(e.Item, DataGridItem)
            Dim cbo As DropDownList = CType(DataItem.FindControl("cboType"), DropDownList)
 
        'add items here
    End If
End Sub

Hope that helps.

Woka