Pulling from Access Query, Cannot Change Date/Time Formats

J

Jim in Arizona

I have a gridview that's being populated from an access db query. The
problem I'm having is that the date/time fields in access that are
populating the gridview are showing both date and time, when the field
should only be showing one or the other (date or time).

Even on the back end of the database where the column properties are, I
have chosen the smallest date/time formats. When the aspx page runs, it
shows the date and time (ie:in a date field: 8/16/2006 12:00:00 AM or in
a time field: 12/30/1899 3:14:00 PM).

The datagrid has OnRowDataBound="doColor" set. The doColor sub procedure
on the aspx.vb page is pretty strait forward:

===============================================

Sub doColor(ByVal sender As Object, ByVal e As GridViewRowEventArgs)

If e.Row.RowType = DataControlRowType.DataRow Then
Dim i9, i10 As Integer

If Int32.TryParse(e.Row.Cells(9).Text, i9) AndAlso i9 >= 45
Then
e.Row.BackColor = Drawing.Color.LightYellow
e.Row.Cells(9).ForeColor = Drawing.Color.Red
e.Row.Cells(9).Font.Bold = True
End If

If Int32.TryParse(e.Row.Cells(9).Text, i9) AndAlso i9 < 0 Then
e.Row.BackColor = Drawing.Color.Yellow
e.Row.Cells(9).ForeColor = Drawing.Color.Red
e.Row.Cells(9).Font.Bold = True
End If

If Int32.TryParse(e.Row.Cells(10).Text, i10) AndAlso i10 >=
60 Then
e.Row.BackColor = Drawing.Color.LightYellow
e.Row.Cells(10).ForeColor = Drawing.Color.Red
e.Row.Cells(10).Font.Bold = True
End If

End If

End Sub
===============================================

I tried doing some formatting on the code side, by adding a little bit
within the doColor sub, like so:

e.Row.Cells(0).Text.Remove(10)

The first cell returned is a cell that shows the date and time but
should only show the date. The time is always 12:00:00 AM. I tried to
use the remove method to remove all characters in the string after the
date, but nothing happened.

Realizing that the number of characters will change depending on if the
month or date are single or double characters, I also tried doing an
if/else statement, like so:

If e.Row.Cells(0).Text.Length = 21 Then
e.Row.Cells(0).Text.Remove(10)
End If

This also didn't work.

I can't seem to do formatting on the access end or the vb page end to
remove the date or time that is not needed in the returned result within
the gridview.

Incase it matters, here's all my code (aspx and aspx.vb page). I submit
a single date to the query for processing, which returns vales only for
a given date.

txdf.aspx
===================================================
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="txdf.aspx.vb"
Inherits="txdb_txdf" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="hiddenbox" runat="server"
style="visibility:hidden;display:none;" />
<asp:TextBox ID="txtDate" runat="server" BackColor="WhiteSmoke"
Font-Names="Bookman Old Style"></asp:TextBox>
<asp:Button ID="btnSubmit" runat="server" Text="Get Data"
BackColor="Maroon" BorderColor="Black" BorderStyle="Solid"
BorderWidth="2px" Font-Bold="True" Font-Names="Bookman Old Style"
ForeColor="White" /><br />
<br />
<asp:GridView ID="gvData" runat="server"
OnRowDataBound="doColor" Font-Names="Bookman Old Style"
ForeColor="White" GridLines="None" BackColor="Black"
BorderColor="Maroon" BorderStyle="Solid" BorderWidth="0px"
CellSpacing="1" Font-Size="Small" Width="100%" CellPadding="2">
<FooterStyle BackColor="#990000" Font-Bold="True"
ForeColor="White" />
<RowStyle ForeColor="#000066" BackColor="LightGray" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True"
ForeColor="White" />
<PagerStyle BackColor="#804040" ForeColor="#333333"
HorizontalAlign="Center" />
<HeaderStyle BackColor="#990000" Font-Bold="True"
ForeColor="White" />
<AlternatingRowStyle BackColor="WhiteSmoke"
BorderColor="White" Font-Names="Bookman Old Style"
Font-Size="Small" ForeColor="Black" />
</asp:GridView>
</div>
</form>
</body>
</html>
===================================================

txdf.aspx.vb
===================================================
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb


Partial Class txdb_txdf
Inherits System.Web.UI.Page

Sub doColor(ByVal sender As Object, ByVal e As GridViewRowEventArgs)

If e.Row.RowType = DataControlRowType.DataRow Then
Dim i9, i10 As Integer

If Int32.TryParse(e.Row.Cells(9).Text, i9) AndAlso i9 >= 45
Then
e.Row.BackColor = Drawing.Color.LightYellow
e.Row.Cells(9).ForeColor = Drawing.Color.Red
e.Row.Cells(9).Font.Bold = True
End If

If Int32.TryParse(e.Row.Cells(9).Text, i9) AndAlso i9 < 0 Then
e.Row.BackColor = Drawing.Color.Yellow
e.Row.Cells(9).ForeColor = Drawing.Color.Red
e.Row.Cells(9).Font.Bold = True
End If

If Int32.TryParse(e.Row.Cells(10).Text, i10) AndAlso i10 >=
60 Then
e.Row.BackColor = Drawing.Color.LightYellow
e.Row.Cells(10).ForeColor = Drawing.Color.Red
e.Row.Cells(10).Font.Bold = True
End If

End If

If e.Row.Cells(0).Text.Length = 21 Then
e.Row.Cells(0).Text.Remove(10)
End If

End Sub

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnSubmit.Click
oledbconnectioncode()
End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
txtDate.Focus()
If Not Page.IsPostBack Then
txtDate.Text = Date.Today
End If
End Sub

Sub oledbconnectioncode()
Dim strConnection, strStoredProc As String
Dim dateString As String = txtDate.Text
strConnection = "Provider=Microsoft.Jet.OleDb.4.0;" & _
"data
source=\\server\share\database\transportation\trantrack2003B.mdb;User
Id=admin;Password=;"
strStoredProc = "[T-Transportation Monitor]"
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objdatetime As New OleDbParameter("dt", OleDbType.Date)
objdatetime.Value = dateString
objConnection = New OleDbConnection(strConnection)
objCommand = New OleDbCommand(strStoredProc, objConnection)
objCommand.Parameters.Add(objdatetime)
objdatetime.Direction = ParameterDirection.Input
objCommand.CommandType = CommandType.StoredProcedure
Try
objConnection.Open()
gvData.DataSource =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)
gvData.DataBind()
If objConnection.State = ConnectionState.Open Then
objConnection.Close()
End If
Catch ex As Exception
Response.Write(ex.Message.ToString & "<br><br>")
If objConnection.State = ConnectionState.Open Then
objConnection.Close()
End If
End Try
End Sub
End Class
===================================================

Just an FYI: I temporarily copied the access database into a sql
database and had a stored proc that formatted the data with SQL code,
which worked great. Unfortunately, the port over to sql had locking up
problems when using an access front end and access SQL doesn't support
the same type of SQL statements that SQL server does.

TIA,
Jim
 
T

Teemu Keiski

Hi,

use BoundColumns. With them you can specify DataFormatString to apply string
formatting. With string formatting you get complete control over how date or
time is presented. Just remember that you also need to set
HtmlEncode="False" for a BoundColumn.

--
Teemu Keiski
ASP.NET MVP, AspInsider
Finland, EU
http://blogs.aspadvice.com/joteke



Jim in Arizona said:
I have a gridview that's being populated from an access db query. The
problem I'm having is that the date/time fields in access that are
populating the gridview are showing both date and time, when the field
should only be showing one or the other (date or time).

Even on the back end of the database where the column properties are, I
have chosen the smallest date/time formats. When the aspx page runs, it
shows the date and time (ie:in a date field: 8/16/2006 12:00:00 AM or in a
time field: 12/30/1899 3:14:00 PM).

The datagrid has OnRowDataBound="doColor" set. The doColor sub procedure
on the aspx.vb page is pretty strait forward:

===============================================

Sub doColor(ByVal sender As Object, ByVal e As GridViewRowEventArgs)

If e.Row.RowType = DataControlRowType.DataRow Then
Dim i9, i10 As Integer

If Int32.TryParse(e.Row.Cells(9).Text, i9) AndAlso i9 >= 45
Then
e.Row.BackColor = Drawing.Color.LightYellow
e.Row.Cells(9).ForeColor = Drawing.Color.Red
e.Row.Cells(9).Font.Bold = True
End If

If Int32.TryParse(e.Row.Cells(9).Text, i9) AndAlso i9 < 0 Then
e.Row.BackColor = Drawing.Color.Yellow
e.Row.Cells(9).ForeColor = Drawing.Color.Red
e.Row.Cells(9).Font.Bold = True
End If

If Int32.TryParse(e.Row.Cells(10).Text, i10) AndAlso i10 >= 60
Then
e.Row.BackColor = Drawing.Color.LightYellow
e.Row.Cells(10).ForeColor = Drawing.Color.Red
e.Row.Cells(10).Font.Bold = True
End If

End If

End Sub
===============================================

I tried doing some formatting on the code side, by adding a little bit
within the doColor sub, like so:

e.Row.Cells(0).Text.Remove(10)

The first cell returned is a cell that shows the date and time but should
only show the date. The time is always 12:00:00 AM. I tried to use the
remove method to remove all characters in the string after the date, but
nothing happened.

Realizing that the number of characters will change depending on if the
month or date are single or double characters, I also tried doing an
if/else statement, like so:

If e.Row.Cells(0).Text.Length = 21 Then
e.Row.Cells(0).Text.Remove(10)
End If

This also didn't work.

I can't seem to do formatting on the access end or the vb page end to
remove the date or time that is not needed in the returned result within
the gridview.

Incase it matters, here's all my code (aspx and aspx.vb page). I submit a
single date to the query for processing, which returns vales only for a
given date.

txdf.aspx
===================================================
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="txdf.aspx.vb"
Inherits="txdb_txdf" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="hiddenbox" runat="server"
style="visibility:hidden;display:none;" />
<asp:TextBox ID="txtDate" runat="server" BackColor="WhiteSmoke"
Font-Names="Bookman Old Style"></asp:TextBox>
<asp:Button ID="btnSubmit" runat="server" Text="Get Data"
BackColor="Maroon" BorderColor="Black" BorderStyle="Solid"
BorderWidth="2px" Font-Bold="True" Font-Names="Bookman Old Style"
ForeColor="White" /><br />
<br />
<asp:GridView ID="gvData" runat="server" OnRowDataBound="doColor"
Font-Names="Bookman Old Style" ForeColor="White" GridLines="None"
BackColor="Black" BorderColor="Maroon" BorderStyle="Solid"
BorderWidth="0px" CellSpacing="1" Font-Size="Small" Width="100%"
CellPadding="2">
<FooterStyle BackColor="#990000" Font-Bold="True"
ForeColor="White" />
<RowStyle ForeColor="#000066" BackColor="LightGray" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True"
ForeColor="White" />
<PagerStyle BackColor="#804040" ForeColor="#333333"
HorizontalAlign="Center" />
<HeaderStyle BackColor="#990000" Font-Bold="True"
ForeColor="White" />
<AlternatingRowStyle BackColor="WhiteSmoke"
BorderColor="White" Font-Names="Bookman Old Style"
Font-Size="Small" ForeColor="Black" />
</asp:GridView>
</div>
</form>
</body>
</html>
===================================================

txdf.aspx.vb
===================================================
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb


Partial Class txdb_txdf
Inherits System.Web.UI.Page

Sub doColor(ByVal sender As Object, ByVal e As GridViewRowEventArgs)

If e.Row.RowType = DataControlRowType.DataRow Then
Dim i9, i10 As Integer

If Int32.TryParse(e.Row.Cells(9).Text, i9) AndAlso i9 >= 45
Then
e.Row.BackColor = Drawing.Color.LightYellow
e.Row.Cells(9).ForeColor = Drawing.Color.Red
e.Row.Cells(9).Font.Bold = True
End If

If Int32.TryParse(e.Row.Cells(9).Text, i9) AndAlso i9 < 0 Then
e.Row.BackColor = Drawing.Color.Yellow
e.Row.Cells(9).ForeColor = Drawing.Color.Red
e.Row.Cells(9).Font.Bold = True
End If

If Int32.TryParse(e.Row.Cells(10).Text, i10) AndAlso i10 >= 60
Then
e.Row.BackColor = Drawing.Color.LightYellow
e.Row.Cells(10).ForeColor = Drawing.Color.Red
e.Row.Cells(10).Font.Bold = True
End If

End If

If e.Row.Cells(0).Text.Length = 21 Then
e.Row.Cells(0).Text.Remove(10)
End If

End Sub

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnSubmit.Click
oledbconnectioncode()
End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
txtDate.Focus()
If Not Page.IsPostBack Then
txtDate.Text = Date.Today
End If
End Sub

Sub oledbconnectioncode()
Dim strConnection, strStoredProc As String
Dim dateString As String = txtDate.Text
strConnection = "Provider=Microsoft.Jet.OleDb.4.0;" & _
"data
source=\\server\share\database\transportation\trantrack2003B.mdb;User
Id=admin;Password=;"
strStoredProc = "[T-Transportation Monitor]"
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objdatetime As New OleDbParameter("dt", OleDbType.Date)
objdatetime.Value = dateString
objConnection = New OleDbConnection(strConnection)
objCommand = New OleDbCommand(strStoredProc, objConnection)
objCommand.Parameters.Add(objdatetime)
objdatetime.Direction = ParameterDirection.Input
objCommand.CommandType = CommandType.StoredProcedure
Try
objConnection.Open()
gvData.DataSource =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)
gvData.DataBind()
If objConnection.State = ConnectionState.Open Then
objConnection.Close()
End If
Catch ex As Exception
Response.Write(ex.Message.ToString & "<br><br>")
If objConnection.State = ConnectionState.Open Then
objConnection.Close()
End If
End Try
End Sub
End Class
===================================================

Just an FYI: I temporarily copied the access database into a sql database
and had a stored proc that formatted the data with SQL code, which worked
great. Unfortunately, the port over to sql had locking up problems when
using an access front end and access SQL doesn't support the same type of
SQL statements that SQL server does.

TIA,
Jim
Click to expand...
 
J

Jim in Arizona

Teemu said:
Hi,

use BoundColumns. With them you can specify DataFormatString to apply string
formatting. With string formatting you get complete control over how date or
time is presented. Just remember that you also need to set
HtmlEncode="False" for a BoundColumn.
Click to expand...

That went a bit over my head. Could you give me a code example? I did a
search in Object Browser for BoundColumn and found it in
System.Web.UI.WebControls.BoundColumn but I don't know how to properly
implement it. Where would I set the htmlEncode?

Thanks Teemu.
 
J

Jim in Arizona

Teemu said:
Hi,

use BoundColumns. With them you can specify DataFormatString to apply string
formatting. With string formatting you get complete control over how date or
time is presented. Just remember that you also need to set
HtmlEncode="False" for a BoundColumn.
Click to expand...

I've tried this but I don't know what I'm doing and I have no idea how
to set htmlencode=false.

Imports System.Web.UI.WebControls.BoundColumn

Sub doColor(ByVal sender As Object, ByVal e As GridViewRowEventArgs)

Dim test As New BoundColumn()

e.Row.Cells(0).Text =
test.DataFormatString(System.DateTime.Today)


I get an error underline under the System.DateTime.Today part that says
'Value of type Date cannot be converted to Integer'. I don't know why or
how this has anything to do with an integer.

I tried this as well:

e.Row.Cells(0).Text = test.DataFormatString(System.String.Format("",
System.DateTime.Today.Day))

But I get this error:

Conversion from string "" to type 'Integer' is not valid.

If I put a number in there, say, a zero or one, like so:

e.Row.Cells(0).Text = test.DataFormatString(System.String.Format("1",
System.DateTime.Today.Day))

I get this error:

Index was outside the bounds of the array.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Gridview rowdatabound event firing twice 0
Very odd "If value >= x" Error 13
conditional formatting of text in a gridview 6
problem with e.Row.Cells() of gridview 3
GridView edit mode issue 2
Collect Excel Data from Website 5
GridView.Sort problem 2
Column count / Access GridView Column after DataBind 4

Members online

No members online now.
Total: 297 (members: 1, guests: 296)
Robots: 281

Forum statistics

Threads
473,967
Messages
2,570,148
Members
46,694
Latest member
LetaCadwal

Latest Threads

Top