473,387 Members | 1,492 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

Pulling from Access Query, Cannot Change Date/Time Formats

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\tran track2003B.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.CloseConn ection)
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
Sep 5 '06 #1
3 3267
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" <ti*******@hotmail.comwrote in message
news:u0**************@TK2MSFTNGP06.phx.gbl...
>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\tran track2003B.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.CloseConn ection)
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

Sep 5 '06 #2
Teemu Keiski wrote:
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.
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.
Sep 5 '06 #3
Teemu Keiski wrote:
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.
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.
Sep 5 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
13
by: Peter James | last post by:
Access 97 If I select New on the Query tab of the db window, and go staight to sql view and type in the following for example: INSERT INTO tblMyTable ( dtDate, txtAny) VALUES (#2003-09-03#,...
1
by: mark | last post by:
In Access 2000 and 2002, I have created an import specification to import the fixed-width recordset below into an existing table. I am having strange problems with the import of the date and time...
3
by: John Ortt | last post by:
> I have a table of dates in ascending order but with varying intervals. I > would like to create a query to pull out the date (in field 1) and then pull > the date from the subsequent record...
4
by: bhbgroup | last post by:
I have a query on one large table. I only add one condition, i.e. a date (the SQL reads like 'where date > parameterdate'. This query is rather quick if 'parameterdate' is either explicitly...
7
by: Andy Davis | last post by:
I have a table of data in Access 2002 which is used as the source table for a mail merge document using Word 2002 on my clients PC. The data is transferred OK but I've noticed that any dates which...
5
by: Henning M | last post by:
Hi all, I having some problems with Access and selecting records between dates.. When I try this in access, it works fine!! "Select * from Bilag Where Mdates Between #1/1/2006# And...
3
by: Sheldon | last post by:
I have the following query expression - Like Format((!!)) & "/*/" & (! !) which would translate to e.g. 04/*/2007 if someone is running a report for last month. The above expression is part of a...
9
by: prakashwadhwani | last post by:
Hi !! I'm about to develop a new project for a client. Should I go about it in Access 2003 or 2007 ? Purchasing it either for me or for my client is not a major consideration here ... what I'd...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.