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
