Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.clipper.visual-objects > #686 > unrolled thread

Opening Date columns

Started by"Kevin" <kdmurphy@eircom.net>
First post2011-04-05 16:31 +0100
Last post2011-04-09 09:29 +0000
Articles 13 — 3 participants

Back to article view | Back to comp.lang.clipper.visual-objects


Contents

  Opening Date columns "Kevin" <kdmurphy@eircom.net> - 2011-04-05 16:31 +0100
    Re: Opening Date columns "Geoff Schaller" <geoffx@softwarexobjectives.com.au> - 2011-04-05 21:51 +0000
      Re: Opening Date columns "Kevin" <kdmurphy@eircom.net> - 2011-04-06 10:17 +0100
        Re: Opening Date columns "Willie Moore" <williem@wmconsulting.com> - 2011-04-06 07:55 -0500
          Re: Opening Date columns "Kevin" <kdmurphy@eircom.net> - 2011-04-06 16:50 +0100
            Re: Opening Date columns "Willie Moore" <williem@wmconsulting.com> - 2011-04-06 12:28 -0500
              Re: Opening Date columns "Kevin" <kdmurphy@eircom.net> - 2011-04-07 08:30 +0100
                Re: Opening Date columns "Willie Moore" <williem@wmconsulting.com> - 2011-04-07 09:53 -0500
                  Re: Opening Date columns "Kevin" <kdmurphy@eircom.net> - 2011-04-07 16:51 +0100
                    Re: Opening Date columns "Willie Moore" <williem@wmconsulting.com> - 2011-04-07 11:08 -0500
        Re: Opening Date columns "Geoff Schaller" <geoffx@softwarexobjectives.com.au> - 2011-04-07 22:28 +0000
          Re: Opening Date columns "Kevin" <kdmurphy@eircom.net> - 2011-04-08 17:06 +0100
            Re: Opening Date columns "Geoff Schaller" <geoffx@softwarexobjectives.com.au> - 2011-04-09 09:29 +0000

#686 — Opening Date columns

From"Kevin" <kdmurphy@eircom.net>
Date2011-04-05 16:31 +0100
SubjectOpening Date columns
Message-ID<oBGmp.928$K4.267@news.indigo.ie>
I have a table in SQL server which has several columns including one which 
is a Date column.

When I open the table in Geoffs SQL Master the date is displayed that is in 
dd/mm/yyyy format. If I open it in SQL Server Management Studio it opens in 
ANSI format - yyyy-mm-dd. Using Visual Studio to create the DataSet the SQL 
syntax is the same but the column displays as a string and looking at the 
column using the debugger it is a string type. The SQL syntax in both cases 
is the same and looks like;

--> Select ClaimID, PurchaseDate, Detail from Purchases.

At the moment there is very little data in the table. I will add in 
selection criteria later.

Is there any way of changing the SQL syntax to force it to return a date 
type instead of a string?

Alternatively is there a way of linking a DataTable to a SQL source to get 
the date column formatted as a date?

Geoff,

Is there anyway of stopping the prompt for an email address from coming up 
when SQL Master starts? I have filled it in the past but now simply clicking 
it closes it till the next time. Running as Administrator stops the prompt 
coming up but the connections are not not available. (Win 7 64);

Kevin 

[toc] | [next] | [standalone]


#698

From"Geoff Schaller" <geoffx@softwarexobjectives.com.au>
Date2011-04-05 21:51 +0000
Message-ID<4d9b8ed4$0$11191$c30e37c6@exi-reader.telstra.net>
In reply to#686
So many questions!

> When I open the table in Geoffs SQL Master the date is displayed that is in
> dd/mm/yyyy format. If I open it in SQL Server Management Studio it opens in

It is a VO app so I have chosen to display the date as a VO date using 
the current regional settings. Naturally this can be any format you 
wish. I could even extend such options to the user of SQL Master...were 
someone interested in paying for this but it is a free tool. Of course 
all those who own the source code may do as they wish!

> ANSI format - yyyy-mm-dd. Using Visual Studio to create the DataSet the SQL
> syntax is the same but the column displays as a string and looking at the
> column using the debugger it is a string type. The SQL syntax in both cases

Same deal. They have to format it some way and they chose that way. But 
you can display and use the date in any format you wish or add an 
expression column to the dataset to show it another way.

> Is there any way of changing the SQL syntax to force it to return a date
> type instead of a string?

Of course there is. Do you have a T-SQL book? Just look at date formats. 
There is a whole table of them to return the value back in a multitude 
of ways.

> Is there anyway of stopping the prompt for an email address from coming up
> when SQL Master starts? I have filled it in the past but now simply clicking
> it closes it till the next time. Running as Administrator stops the prompt
> coming up but the connections are not not available. (Win 7 64);

Now you've lost me. The email address is used to send error messages to 
me or I just get user@company.com and cannot then offer support. Of 
course you can ignore me <g>. Running as Administrator should do nothing 
but there is a COM DLL that needs to be registered. And the install 
should have done that.

Email me privately so I can look into this.

Geoff

[toc] | [prev] | [next] | [standalone]


#712

From"Kevin" <kdmurphy@eircom.net>
Date2011-04-06 10:17 +0100
Message-ID<TcWmp.929$K4.384@news.indigo.ie>
In reply to#698
Geoff,

> It is a VO app so I have chosen to display the date as a VO date using the 
> current regional settings.

I was not certain whether it was the way you chose to interpret the date 
columns or there was something you were doing in the SQL that I was missing.

> Same deal. They have to format it some way and they chose that way. But 
> you can display and use the date in any format you wish or add an 
> expression column to the dataset to show it another way.

This I understand and was looking for some way of changing the way the value 
was getting returned. I had a look around the MSDN help and could find info 
on what column types were available. What I could not find was how to get 
the date column returned as a DateTime object rather than a String.

> Of course there is. Do you have a T-SQL book? Just look at date formats. 
> There is a whole table of them to return the value back in a multitude of 
> ways.

Simple answer, no I don't have a T-SQL book. Can you suggest any?
The only way that I could find to get the date column to return a date 
rather than a string was a CAST. For example:
--> SELECT ClaimID, CAST(PurchaseDate as date) as PurDate From Purchases

The code runs and the columns display but unfortunately the PurchaseDate is 
still a String. I'm probably missing something.

I think I have found a way to get what I am after. That is to create a 
DataTable first with a DateTime field and then fill it from an 
OleDbDataAdapter. In my original post I had been thinking that the issue may 
have been with my lack of understanding of SQL and that the solution might 
be there.

Kevin 

[toc] | [prev] | [next] | [standalone]


#721

From"Willie Moore" <williem@wmconsulting.com>
Date2011-04-06 07:55 -0500
Message-ID<inhnsn$s3h$1@speranza.aioe.org>
In reply to#712
Kevin,

If you are using VO2ADO, the recordset will return a VO date or an 
ADODateTime object depending the ADODateTimeAdDate function. Set it to FALSE 
at the beginning of your app to always return datetime objects instead of VO 
date.

Regards,
Willie

"Kevin"  wrote in message news:TcWmp.929$K4.384@news.indigo.ie...

Geoff,

> It is a VO app so I have chosen to display the date as a VO date using the 
> current regional settings.

I was not certain whether it was the way you chose to interpret the date
columns or there was something you were doing in the SQL that I was missing.

> Same deal. They have to format it some way and they chose that way. But 
> you can display and use the date in any format you wish or add an 
> expression column to the dataset to show it another way.

This I understand and was looking for some way of changing the way the value
was getting returned. I had a look around the MSDN help and could find info
on what column types were available. What I could not find was how to get
the date column returned as a DateTime object rather than a String.

> Of course there is. Do you have a T-SQL book? Just look at date formats. 
> There is a whole table of them to return the value back in a multitude of 
> ways.

Simple answer, no I don't have a T-SQL book. Can you suggest any?
The only way that I could find to get the date column to return a date
rather than a string was a CAST. For example:
--> SELECT ClaimID, CAST(PurchaseDate as date) as PurDate From Purchases

The code runs and the columns display but unfortunately the PurchaseDate is
still a String. I'm probably missing something.

I think I have found a way to get what I am after. That is to create a
DataTable first with a DateTime field and then fill it from an
OleDbDataAdapter. In my original post I had been thinking that the issue may
have been with my lack of understanding of SQL and that the solution might
be there.

Kevin 

[toc] | [prev] | [next] | [standalone]


#726

From"Kevin" <kdmurphy@eircom.net>
Date2011-04-06 16:50 +0100
Message-ID<RY%mp.932$K4.423@news.indigo.ie>
In reply to#721
Willie,

I am not using VO2ADO as it is not a VO app. I have Vn2ADO for Vulcan 1 but 
don't know if if exists for Vulcan 2. There has been some discussion about 
it in the past and I have heard nothing to say there was an update. I think 
some people have got it to work but I would not be confident to try that 
myself.

Kevin


"Willie Moore"  wrote in message news:inhnsn$s3h$1@speranza.aioe.org...

Kevin,

If you are using VO2ADO, the recordset will return a VO date or an
ADODateTime object depending the ADODateTimeAdDate function. Set it to FALSE
at the beginning of your app to always return datetime objects instead of VO
date.

Regards,
Willie

"Kevin"  wrote in message news:TcWmp.929$K4.384@news.indigo.ie...

Geoff,

> It is a VO app so I have chosen to display the date as a VO date using the 
> current regional settings.

I was not certain whether it was the way you chose to interpret the date
columns or there was something you were doing in the SQL that I was missing.

> Same deal. They have to format it some way and they chose that way. But 
> you can display and use the date in any format you wish or add an 
> expression column to the dataset to show it another way.

This I understand and was looking for some way of changing the way the value
was getting returned. I had a look around the MSDN help and could find info
on what column types were available. What I could not find was how to get
the date column returned as a DateTime object rather than a String.

> Of course there is. Do you have a T-SQL book? Just look at date formats. 
> There is a whole table of them to return the value back in a multitude of 
> ways.

Simple answer, no I don't have a T-SQL book. Can you suggest any?
The only way that I could find to get the date column to return a date
rather than a string was a CAST. For example:
--> SELECT ClaimID, CAST(PurchaseDate as date) as PurDate From Purchases

The code runs and the columns display but unfortunately the PurchaseDate is
still a String. I'm probably missing something.

I think I have found a way to get what I am after. That is to create a
DataTable first with a DateTime field and then fill it from an
OleDbDataAdapter. In my original post I had been thinking that the issue may
have been with my lack of understanding of SQL and that the solution might
be there.

Kevin 

[toc] | [prev] | [next] | [standalone]


#728

From"Willie Moore" <williem@wmconsulting.com>
Date2011-04-06 12:28 -0500
Message-ID<ini7rv$57m$1@speranza.aioe.org>
In reply to#726
Kevin,

I thought we were talking about VO that is why I made the statement that I 
did. BTW I have VN2ADO and it works fine with Vulcan 2. I do not use VN2ADO 
for any new work but it is great for my legacy code. When I read in data 
from ADO.Net, I usually do something like this to make sure that it doesn’t 
cause a runtime error:
        LOCAL oDate as DateTime
        LOCAL cDate := "04/01/2011" as String // us date format
        if DateTime.TryParse(cDate,oDate)
           // do something
        else
           // handle error
        endif
You can also do this oDate := DateTime.Parse(cDate)
If you are reading out of an SQL table, you can do something like this:
    oDate := (datetime)oRow:Item["reg_date"]    // this coms in from SQL as 
an object so you have to cast
    oDate := Convert.ToDateTimeoRow:Item["reg_date"])

Regards,
Willie

"Kevin"  wrote in message news:RY%mp.932$K4.423@news.indigo.ie...

Willie,

I am not using VO2ADO as it is not a VO app. I have Vn2ADO for Vulcan 1 but
don't know if if exists for Vulcan 2. There has been some discussion about
it in the past and I have heard nothing to say there was an update. I think
some people have got it to work but I would not be confident to try that
myself.

Kevin


"Willie Moore"  wrote in message news:inhnsn$s3h$1@speranza.aioe.org...

Kevin,

If you are using VO2ADO, the recordset will return a VO date or an
ADODateTime object depending the ADODateTimeAdDate function. Set it to FALSE
at the beginning of your app to always return datetime objects instead of VO
date.

Regards,
Willie

"Kevin"  wrote in message news:TcWmp.929$K4.384@news.indigo.ie...

Geoff,

> It is a VO app so I have chosen to display the date as a VO date using the 
> current regional settings.

I was not certain whether it was the way you chose to interpret the date
columns or there was something you were doing in the SQL that I was missing.

> Same deal. They have to format it some way and they chose that way. But 
> you can display and use the date in any format you wish or add an 
> expression column to the dataset to show it another way.

This I understand and was looking for some way of changing the way the value
was getting returned. I had a look around the MSDN help and could find info
on what column types were available. What I could not find was how to get
the date column returned as a DateTime object rather than a String.

> Of course there is. Do you have a T-SQL book? Just look at date formats. 
> There is a whole table of them to return the value back in a multitude of 
> ways.

Simple answer, no I don't have a T-SQL book. Can you suggest any?
The only way that I could find to get the date column to return a date
rather than a string was a CAST. For example:
--> SELECT ClaimID, CAST(PurchaseDate as date) as PurDate From Purchases

The code runs and the columns display but unfortunately the PurchaseDate is
still a String. I'm probably missing something.

I think I have found a way to get what I am after. That is to create a
DataTable first with a DateTime field and then fill it from an
OleDbDataAdapter. In my original post I had been thinking that the issue may
have been with my lack of understanding of SQL and that the solution might
be there.

Kevin 

[toc] | [prev] | [next] | [standalone]


#733

From"Kevin" <kdmurphy@eircom.net>
Date2011-04-07 08:30 +0100
Message-ID<vKdnp.933$K4.436@news.indigo.ie>
In reply to#728
Willie,

I had thought my issue was with the SQL syntax and could be fixed with a 
change in it.  That's why I said nothing about VO or Vulcan.

Your suggestion would work fine for a single value but, as far as I can see, 
not when opening the table to browse. Again, this is something I had not 
mentioned.

My solution was to code a table manually including a DateTime column and 
then use and OleDbAdapter to fill it. This has worked and given me the 
desired results.

Can I ask two other questions?

1. Is the best way to use dates in Date or DateTime fields in the table to 
surround it with '#'?

2. One or two of the columns in the table are look up values to another 
table and showing the 'code'. If I wish to show the value instead is JOIN 
the best SQL table to use?

Thanks.

Kevin


"Willie Moore"  wrote in message news:ini7rv$57m$1@speranza.aioe.org...

Kevin,

I thought we were talking about VO that is why I made the statement that I
did. BTW I have VN2ADO and it works fine with Vulcan 2. I do not use VN2ADO
for any new work but it is great for my legacy code. When I read in data
from ADO.Net, I usually do something like this to make sure that it doesn’t
cause a runtime error:
        LOCAL oDate as DateTime
        LOCAL cDate := "04/01/2011" as String // us date format
        if DateTime.TryParse(cDate,oDate)
           // do something
        else
           // handle error
        endif
You can also do this oDate := DateTime.Parse(cDate)
If you are reading out of an SQL table, you can do something like this:
    oDate := (datetime)oRow:Item["reg_date"]    // this coms in from SQL as
an object so you have to cast
    oDate := Convert.ToDateTimeoRow:Item["reg_date"])

Regards,
Willie

"Kevin"  wrote in message news:RY%mp.932$K4.423@news.indigo.ie...

Willie,

I am not using VO2ADO as it is not a VO app. I have Vn2ADO for Vulcan 1 but
don't know if if exists for Vulcan 2. There has been some discussion about
it in the past and I have heard nothing to say there was an update. I think
some people have got it to work but I would not be confident to try that
myself.

Kevin


"Willie Moore"  wrote in message news:inhnsn$s3h$1@speranza.aioe.org...

Kevin,

If you are using VO2ADO, the recordset will return a VO date or an
ADODateTime object depending the ADODateTimeAdDate function. Set it to FALSE
at the beginning of your app to always return datetime objects instead of VO
date.

Regards,
Willie

"Kevin"  wrote in message news:TcWmp.929$K4.384@news.indigo.ie...

Geoff,

> It is a VO app so I have chosen to display the date as a VO date using the 
> current regional settings.

I was not certain whether it was the way you chose to interpret the date
columns or there was something you were doing in the SQL that I was missing.

> Same deal. They have to format it some way and they chose that way. But 
> you can display and use the date in any format you wish or add an 
> expression column to the dataset to show it another way.

This I understand and was looking for some way of changing the way the value
was getting returned. I had a look around the MSDN help and could find info
on what column types were available. What I could not find was how to get
the date column returned as a DateTime object rather than a String.

> Of course there is. Do you have a T-SQL book? Just look at date formats. 
> There is a whole table of them to return the value back in a multitude of 
> ways.

Simple answer, no I don't have a T-SQL book. Can you suggest any?
The only way that I could find to get the date column to return a date
rather than a string was a CAST. For example:
--> SELECT ClaimID, CAST(PurchaseDate as date) as PurDate From Purchases

The code runs and the columns display but unfortunately the PurchaseDate is
still a String. I'm probably missing something.

I think I have found a way to get what I am after. That is to create a
DataTable first with a DateTime field and then fill it from an
OleDbDataAdapter. In my original post I had been thinking that the issue may
have been with my lack of understanding of SQL and that the solution might
be there.

Kevin 

[toc] | [prev] | [next] | [standalone]


#741

From"Willie Moore" <williem@wmconsulting.com>
Date2011-04-07 09:53 -0500
Message-ID<inkj57$dju$1@speranza.aioe.org>
In reply to#733
Kevin,

It sounds like you are using an access table. If so yes, you surround the 
value of the date you are looking for in the SQL statement with a "#". 
Attached is an example. I am also attaching a small aspx that shows 
databinding a table with a date column. I use the join in SQL a lot when 
setting up for grids. Once you have the result set, you can bind it to a 
grid or create a table and then fill it from the result set.

Regards,
Willie

Method WriteTempRegistration() as string
    local cResult       as string
    local cSQL          as string
    local oCmd          As OleDbCommand

    try

        cResult := self:OpenConnection()
        if !empty(cResult)
            throw ApplicationException{"Error Opening Connection"}
        endif
        // get the timestamp
        SELF:SubmitDate := DateTime.Now:ToString()

        // build the SQL to write the record
        cSQL := "insert into PendingRegistration 
(eventID,name,addr1,addr2,city,state,zip,phone,email,dogname,dogage,dogbreed,amt,SystemTS)"
        cSQL += " Values ("
        cSQL += " " + ConstClass.SQLString(self:cID)
        cSQL += ", '" + ConstClass.SQLString(self:cfullname) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:caddr1) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:caddr2) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cCity) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cState) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cpostal) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cphone) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cEmail) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cdogname) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cdogage) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cdogbreed) + "'"
        cSQL += ", " + self:nAmt:ToString()
        cSQL += ", #" + SubmitDate + "#"
        cSQL += " )"


        oCmd                := OleDbCommand{}
        oCmd:Connection     := self:oConn
        oCmd:CommandText    := cSQL
        oCmd:CommandTimeout := 300
        oCmd:ExecuteNonQuery()  // executes the command without a return 
value

        cResult         := ""
    catch e as System.Exception
        cResult := e:Message
    end try
    RETURN cResult


<%@ Page Language="VB" ClassName="EventsMain" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OLEDB" %>
<%@ Import Namespace="System.collections" %>

<script runat="server">
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As 
System.EventArgs)
        If Not IsPostBack Then
            Me.EventsGrid.DataSource = FillGrid()
            Me.EventsGrid.DataBind()
        End If
    End Sub

    Sub Grid_Change(ByVal sender As Object, ByVal e As 
DataGridPageChangedEventArgs)
        Me.EventsGrid.CurrentPageIndex = e.NewPageIndex
        Me.EventsGrid.DataBind()
    End Sub 'Grid_Change

    Function FillGrid() As ICollection
        Dim connStr As String
        Dim conn As OleDbConnection

        Dim cSQL As String
        Dim adapter As New OleDbDataAdapter()
        Dim oDS As New DataSet()
        Dim title_col As New BoundColumn()
        Dim date_col As New BoundColumn()
        Dim loc_col As New BoundColumn()
        Dim description_col As New BoundColumn()
        Dim Cost_col As New BoundColumn()
        Dim Info_col As New ButtonColumn()

        ' put the user into the database
        connStr = 
System.Configuration.ConfigurationManager.AppSettings("DSN")
        conn = New OleDbConnection(connStr)
        conn.Open()

        cSQL = "select * from Events"
        cSQL = cSQL & " Order by EventDate Desc"

        adapter.SelectCommand = New OleDbCommand(cSQL, conn)
        adapter.Fill(oDS)

        ' Add out columns
        title_col.DataField = "EventTitle"
        title_col.HeaderText = "Event"
        Me.EventsGrid.Columns.Add(title_col)

        date_col.DataField = "EventDate"
        date_col.HeaderText = "Date"
        date_col.DataFormatString = "{0:MM-dd-yyyy}"
        Me.EventsGrid.Columns.Add(date_col)

        loc_col.DataField = "EventLocation"
        loc_col.HeaderText = "Loction"
        Me.EventsGrid.Columns.Add(loc_col)

        description_col.DataField = "EventDescription"
        description_col.HeaderText = "Description"
        description_col.ItemStyle.Width = 250
        description_col.ItemStyle.Wrap = "True"
        Me.EventsGrid.Columns.Add(description_col)

        Cost_col.DataField = "EventCost"
        Cost_col.HeaderText = "Cost"
        Cost_col.DataFormatString = "{0:c}"
        Me.EventsGrid.Columns.Add(Cost_col)

        Info_col.DataTextField = "EventOpen"
        Info_col.ButtonType = ButtonColumnType.LinkButton
        'Info_col.Text = "cancel"
        Info_col.CommandName = "cancel"
        Info_col.HeaderText = " "
        Me.EventsGrid.Columns.Add(Info_col)

        Return oDS.Tables(0).DefaultView

    End Function 'FillGrid

    Sub Item_Bound(ByVal sender As Object, ByVal e As DataGridItemEventArgs)
        Dim item As DataGridItem
        Dim rv As DataRowView
        Dim b As New HyperLink
        Dim cUrl As String
        Dim cEventID As String
        Dim EventDate As Date

        item = e.Item
        If item.ItemType = ListItemType.AlternatingItem Or item.ItemType = 
ListItemType.Item Then
            rv = item.DataItem
            If rv.Row.Item("EventOpen") = False Then
                item.Cells(5).Visible = False
                'item.Visible = False
            Else
                cEventID = rv.Row.Item("EventID")
                EventDate = rv.Row.Item("EventDate")
                cUrl = rv.Row.Item("EventUrl") & ".aspx?EventID=" & cEventID 
& "&EventDate=" & Server.HtmlEncode(EventDate.ToString("D"))
                cUrl = cUrl & "&EventLoc=" & 
Server.HtmlEncode(rv.Row.Item("EventLocation"))
                cUrl = cUrl & "&EventCost=" & 
Server.HtmlEncode(rv.Row.Item("EventCost"))
                cUrl = cUrl & "&Eventtime=" & 
Server.HtmlEncode(rv.Row.Item("EventStartTime"))
                cUrl = cUrl & "&Eventtitle=" & 
Server.HtmlEncode(rv.Row.Item("EventDescription"))
                b.ID = "BtnClick"
                b.Text = "Open"
                b.NavigateUrl = cUrl
                b.Enabled = True
                item.Cells(5).Controls.RemoveAt(0)
                item.Cells(5).Controls.Add(b)
            End If
        End If

    End Sub 'Item_Bound

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; 
charset=windows-1252" />
        <meta http-equiv="Content-Language" content="en-us" />
        <meta name="keywords" content="flyball, dogs, birmingham, alabama, 
team, bandits, agility, obedience, tracking, herding, daycare" />
        <meta name="description" content="Manners in Motion utilized the 
click and treat method of training " />
        <meta name="robots" content="FOLLOW,INDEX" />
        <title>Manners In Motion</title>
        <link href="/mannersinmotion/include/mim.css" rel="stylesheet" 
title="styles" type="text/css" />
    </head>
    <body>
        <table width="100%" cellspacing="0" cellpadding="0" border="0" 
runat="server">
            <tr>
                <td width="97%" align="left" valign="top">
                    <p align="center">
                        <a href="http://www.mannersinmotion.com" 
target="blank"><asp:image id="Logo" 
ImageUrl="/mannersinmotion/pictures/web%20dog%20with%20bouncing%20ball.jpg" 
class="logo" runat="server" ImageAlign="Top" /></a> 
<br /> <br />
                        <font size="5"><b>  Upcoming Events at Manners in 
Motion</b></font>
                    </p>

                   <form id="Form1" method="post" runat="server" 
style="text-align: center">
                        <asp:datagrid id="EventsGrid" 
OnItemDataBound="item_bound" runat="server" OnPageIndexChanged="Grid_Change" 
PageSize="15" BorderWidth="4px" BorderStyle="Double" ForeColor="Black" 
AutoGenerateColumns="False" CellPadding="10" AllowPaging="True">
                            <HeaderStyle font-underline="True" 
font-bold="True" backcolor="#FFFF66"></HeaderStyle>
                            <PagerStyle borderwidth="4px" 
borderstyle="Solid" mode="NumericPages"></PagerStyle>
                        </asp:datagrid>
                       &nbsp;&nbsp; &nbsp;</form>


                    <div align="center">
                        <font size="2"><a 
href="javascript:history.back()">Return to Previous Page</a></font>
                        <br /><br />
                        <hr style="width: 85%;" />
                        <font size="1">Last Updated: September 13th, 2007<br 
/></font>
                    </div>

                </td>
            </tr>
        </table>
    <!--#include virtual="/mannersinmotion/include/sys-footer.inc" -->

    </body>
</html>

"Kevin"  wrote in message news:vKdnp.933$K4.436@news.indigo.ie...

Willie,

I had thought my issue was with the SQL syntax and could be fixed with a
change in it.  That's why I said nothing about VO or Vulcan.

Your suggestion would work fine for a single value but, as far as I can see,
not when opening the table to browse. Again, this is something I had not
mentioned.

My solution was to code a table manually including a DateTime column and
then use and OleDbAdapter to fill it. This has worked and given me the
desired results.

Can I ask two other questions?

1. Is the best way to use dates in Date or DateTime fields in the table to
surround it with '#'?

2. One or two of the columns in the table are look up values to another
table and showing the 'code'. If I wish to show the value instead is JOIN
the best SQL table to use?

Thanks.

Kevin


"Willie Moore"  wrote in message news:ini7rv$57m$1@speranza.aioe.org...

Kevin,

I thought we were talking about VO that is why I made the statement that I
did. BTW I have VN2ADO and it works fine with Vulcan 2. I do not use VN2ADO
for any new work but it is great for my legacy code. When I read in data
from ADO.Net, I usually do something like this to make sure that it doesn’t
cause a runtime error:
        LOCAL oDate as DateTime
        LOCAL cDate := "04/01/2011" as String // us date format
        if DateTime.TryParse(cDate,oDate)
           // do something
        else
           // handle error
        endif
You can also do this oDate := DateTime.Parse(cDate)
If you are reading out of an SQL table, you can do something like this:
    oDate := (datetime)oRow:Item["reg_date"]    // this coms in from SQL as
an object so you have to cast
    oDate := Convert.ToDateTimeoRow:Item["reg_date"])

Regards,
Willie

"Kevin"  wrote in message news:RY%mp.932$K4.423@news.indigo.ie...

Willie,

I am not using VO2ADO as it is not a VO app. I have Vn2ADO for Vulcan 1 but
don't know if if exists for Vulcan 2. There has been some discussion about
it in the past and I have heard nothing to say there was an update. I think
some people have got it to work but I would not be confident to try that
myself.

Kevin


"Willie Moore"  wrote in message news:inhnsn$s3h$1@speranza.aioe.org...

Kevin,

If you are using VO2ADO, the recordset will return a VO date or an
ADODateTime object depending the ADODateTimeAdDate function. Set it to FALSE
at the beginning of your app to always return datetime objects instead of VO
date.

Regards,
Willie

"Kevin"  wrote in message news:TcWmp.929$K4.384@news.indigo.ie...

Geoff,

> It is a VO app so I have chosen to display the date as a VO date using the 
> current regional settings.

I was not certain whether it was the way you chose to interpret the date
columns or there was something you were doing in the SQL that I was missing.

> Same deal. They have to format it some way and they chose that way. But 
> you can display and use the date in any format you wish or add an 
> expression column to the dataset to show it another way.

This I understand and was looking for some way of changing the way the value
was getting returned. I had a look around the MSDN help and could find info
on what column types were available. What I could not find was how to get
the date column returned as a DateTime object rather than a String.

> Of course there is. Do you have a T-SQL book? Just look at date formats. 
> There is a whole table of them to return the value back in a multitude of 
> ways.

Simple answer, no I don't have a T-SQL book. Can you suggest any?
The only way that I could find to get the date column to return a date
rather than a string was a CAST. For example:
--> SELECT ClaimID, CAST(PurchaseDate as date) as PurDate From Purchases

The code runs and the columns display but unfortunately the PurchaseDate is
still a String. I'm probably missing something.

I think I have found a way to get what I am after. That is to create a
DataTable first with a DateTime field and then fill it from an
OleDbDataAdapter. In my original post I had been thinking that the issue may
have been with my lack of understanding of SQL and that the solution might
be there.

Kevin 

[toc] | [prev] | [next] | [standalone]


#742

From"Kevin" <kdmurphy@eircom.net>
Date2011-04-07 16:51 +0100
Message-ID<44lnp.934$K4.351@news.indigo.ie>
In reply to#741
Willie,

It is an SQL table. The impression of the '#' came from looking at the code 
generated by something else.

I actually got what I was looking for by using

  Select table1.field1,table1.field2,  table2.field2 from table1, table2 
where table1.field1 = table2.field1

JOIN may do the trick but I am not sure if it gives me exactly what I am 
looking for. Need to do a bit more research on it.

Kevin


"Willie Moore"  wrote in message news:inkj57$dju$1@speranza.aioe.org...

Kevin,

It sounds like you are using an access table. If so yes, you surround the
value of the date you are looking for in the SQL statement with a "#".
Attached is an example. I am also attaching a small aspx that shows
databinding a table with a date column. I use the join in SQL a lot when
setting up for grids. Once you have the result set, you can bind it to a
grid or create a table and then fill it from the result set.

Regards,
Willie

Method WriteTempRegistration() as string
    local cResult       as string
    local cSQL          as string
    local oCmd          As OleDbCommand

    try

        cResult := self:OpenConnection()
        if !empty(cResult)
            throw ApplicationException{"Error Opening Connection"}
        endif
        // get the timestamp
        SELF:SubmitDate := DateTime.Now:ToString()

        // build the SQL to write the record
        cSQL := "insert into PendingRegistration
(eventID,name,addr1,addr2,city,state,zip,phone,email,dogname,dogage,dogbreed,amt,SystemTS)"
        cSQL += " Values ("
        cSQL += " " + ConstClass.SQLString(self:cID)
        cSQL += ", '" + ConstClass.SQLString(self:cfullname) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:caddr1) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:caddr2) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cCity) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cState) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cpostal) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cphone) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cEmail) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cdogname) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cdogage) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cdogbreed) + "'"
        cSQL += ", " + self:nAmt:ToString()
        cSQL += ", #" + SubmitDate + "#"
        cSQL += " )"


        oCmd                := OleDbCommand{}
        oCmd:Connection     := self:oConn
        oCmd:CommandText    := cSQL
        oCmd:CommandTimeout := 300
        oCmd:ExecuteNonQuery()  // executes the command without a return
value

        cResult         := ""
    catch e as System.Exception
        cResult := e:Message
    end try
    RETURN cResult


<%@ Page Language="VB" ClassName="EventsMain" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OLEDB" %>
<%@ Import Namespace="System.collections" %>

<script runat="server">
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs)
        If Not IsPostBack Then
            Me.EventsGrid.DataSource = FillGrid()
            Me.EventsGrid.DataBind()
        End If
    End Sub

    Sub Grid_Change(ByVal sender As Object, ByVal e As
DataGridPageChangedEventArgs)
        Me.EventsGrid.CurrentPageIndex = e.NewPageIndex
        Me.EventsGrid.DataBind()
    End Sub 'Grid_Change

    Function FillGrid() As ICollection
        Dim connStr As String
        Dim conn As OleDbConnection

        Dim cSQL As String
        Dim adapter As New OleDbDataAdapter()
        Dim oDS As New DataSet()
        Dim title_col As New BoundColumn()
        Dim date_col As New BoundColumn()
        Dim loc_col As New BoundColumn()
        Dim description_col As New BoundColumn()
        Dim Cost_col As New BoundColumn()
        Dim Info_col As New ButtonColumn()

        ' put the user into the database
        connStr =
System.Configuration.ConfigurationManager.AppSettings("DSN")
        conn = New OleDbConnection(connStr)
        conn.Open()

        cSQL = "select * from Events"
        cSQL = cSQL & " Order by EventDate Desc"

        adapter.SelectCommand = New OleDbCommand(cSQL, conn)
        adapter.Fill(oDS)

        ' Add out columns
        title_col.DataField = "EventTitle"
        title_col.HeaderText = "Event"
        Me.EventsGrid.Columns.Add(title_col)

        date_col.DataField = "EventDate"
        date_col.HeaderText = "Date"
        date_col.DataFormatString = "{0:MM-dd-yyyy}"
        Me.EventsGrid.Columns.Add(date_col)

        loc_col.DataField = "EventLocation"
        loc_col.HeaderText = "Loction"
        Me.EventsGrid.Columns.Add(loc_col)

        description_col.DataField = "EventDescription"
        description_col.HeaderText = "Description"
        description_col.ItemStyle.Width = 250
        description_col.ItemStyle.Wrap = "True"
        Me.EventsGrid.Columns.Add(description_col)

        Cost_col.DataField = "EventCost"
        Cost_col.HeaderText = "Cost"
        Cost_col.DataFormatString = "{0:c}"
        Me.EventsGrid.Columns.Add(Cost_col)

        Info_col.DataTextField = "EventOpen"
        Info_col.ButtonType = ButtonColumnType.LinkButton
        'Info_col.Text = "cancel"
        Info_col.CommandName = "cancel"
        Info_col.HeaderText = " "
        Me.EventsGrid.Columns.Add(Info_col)

        Return oDS.Tables(0).DefaultView

    End Function 'FillGrid

    Sub Item_Bound(ByVal sender As Object, ByVal e As DataGridItemEventArgs)
        Dim item As DataGridItem
        Dim rv As DataRowView
        Dim b As New HyperLink
        Dim cUrl As String
        Dim cEventID As String
        Dim EventDate As Date

        item = e.Item
        If item.ItemType = ListItemType.AlternatingItem Or item.ItemType =
ListItemType.Item Then
            rv = item.DataItem
            If rv.Row.Item("EventOpen") = False Then
                item.Cells(5).Visible = False
                'item.Visible = False
            Else
                cEventID = rv.Row.Item("EventID")
                EventDate = rv.Row.Item("EventDate")
                cUrl = rv.Row.Item("EventUrl") & ".aspx?EventID=" & cEventID
& "&EventDate=" & Server.HtmlEncode(EventDate.ToString("D"))
                cUrl = cUrl & "&EventLoc=" &
Server.HtmlEncode(rv.Row.Item("EventLocation"))
                cUrl = cUrl & "&EventCost=" &
Server.HtmlEncode(rv.Row.Item("EventCost"))
                cUrl = cUrl & "&Eventtime=" &
Server.HtmlEncode(rv.Row.Item("EventStartTime"))
                cUrl = cUrl & "&Eventtitle=" &
Server.HtmlEncode(rv.Row.Item("EventDescription"))
                b.ID = "BtnClick"
                b.Text = "Open"
                b.NavigateUrl = cUrl
                b.Enabled = True
                item.Cells(5).Controls.RemoveAt(0)
                item.Cells(5).Controls.Add(b)
            End If
        End If

    End Sub 'Item_Bound

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html;
charset=windows-1252" />
        <meta http-equiv="Content-Language" content="en-us" />
        <meta name="keywords" content="flyball, dogs, birmingham, alabama,
team, bandits, agility, obedience, tracking, herding, daycare" />
        <meta name="description" content="Manners in Motion utilized the
click and treat method of training " />
        <meta name="robots" content="FOLLOW,INDEX" />
        <title>Manners In Motion</title>
        <link href="/mannersinmotion/include/mim.css" rel="stylesheet"
title="styles" type="text/css" />
    </head>
    <body>
        <table width="100%" cellspacing="0" cellpadding="0" border="0"
runat="server">
            <tr>
                <td width="97%" align="left" valign="top">
                    <p align="center">
                        <a href="http://www.mannersinmotion.com"
target="blank"><asp:image id="Logo"
ImageUrl="/mannersinmotion/pictures/web%20dog%20with%20bouncing%20ball.jpg"
class="logo" runat="server" ImageAlign="Top" /></a>
<br /> <br />
                        <font size="5"><b>  Upcoming Events at Manners in
Motion</b></font>
                    </p>

                   <form id="Form1" method="post" runat="server"
style="text-align: center">
                        <asp:datagrid id="EventsGrid"
OnItemDataBound="item_bound" runat="server" OnPageIndexChanged="Grid_Change"
PageSize="15" BorderWidth="4px" BorderStyle="Double" ForeColor="Black"
AutoGenerateColumns="False" CellPadding="10" AllowPaging="True">
                            <HeaderStyle font-underline="True"
font-bold="True" backcolor="#FFFF66"></HeaderStyle>
                            <PagerStyle borderwidth="4px"
borderstyle="Solid" mode="NumericPages"></PagerStyle>
                        </asp:datagrid>
                       &nbsp;&nbsp; &nbsp;</form>


                    <div align="center">
                        <font size="2"><a
href="javascript:history.back()">Return to Previous Page</a></font>
                        <br /><br />
                        <hr style="width: 85%;" />
                        <font size="1">Last Updated: September 13th, 2007<br
/></font>
                    </div>

                </td>
            </tr>
        </table>
    <!--#include virtual="/mannersinmotion/include/sys-footer.inc" -->

    </body>
</html>

"Kevin"  wrote in message news:vKdnp.933$K4.436@news.indigo.ie...

Willie,

I had thought my issue was with the SQL syntax and could be fixed with a
change in it.  That's why I said nothing about VO or Vulcan.

Your suggestion would work fine for a single value but, as far as I can see,
not when opening the table to browse. Again, this is something I had not
mentioned.

My solution was to code a table manually including a DateTime column and
then use and OleDbAdapter to fill it. This has worked and given me the
desired results.

Can I ask two other questions?

1. Is the best way to use dates in Date or DateTime fields in the table to
surround it with '#'?

2. One or two of the columns in the table are look up values to another
table and showing the 'code'. If I wish to show the value instead is JOIN
the best SQL table to use?

Thanks.

Kevin


"Willie Moore"  wrote in message news:ini7rv$57m$1@speranza.aioe.org...

Kevin,

I thought we were talking about VO that is why I made the statement that I
did. BTW I have VN2ADO and it works fine with Vulcan 2. I do not use VN2ADO
for any new work but it is great for my legacy code. When I read in data
from ADO.Net, I usually do something like this to make sure that it doesn’t
cause a runtime error:
        LOCAL oDate as DateTime
        LOCAL cDate := "04/01/2011" as String // us date format
        if DateTime.TryParse(cDate,oDate)
           // do something
        else
           // handle error
        endif
You can also do this oDate := DateTime.Parse(cDate)
If you are reading out of an SQL table, you can do something like this:
    oDate := (datetime)oRow:Item["reg_date"]    // this coms in from SQL as
an object so you have to cast
    oDate := Convert.ToDateTimeoRow:Item["reg_date"])

Regards,
Willie

"Kevin"  wrote in message news:RY%mp.932$K4.423@news.indigo.ie...

Willie,

I am not using VO2ADO as it is not a VO app. I have Vn2ADO for Vulcan 1 but
don't know if if exists for Vulcan 2. There has been some discussion about
it in the past and I have heard nothing to say there was an update. I think
some people have got it to work but I would not be confident to try that
myself.

Kevin


"Willie Moore"  wrote in message news:inhnsn$s3h$1@speranza.aioe.org...

Kevin,

If you are using VO2ADO, the recordset will return a VO date or an
ADODateTime object depending the ADODateTimeAdDate function. Set it to FALSE
at the beginning of your app to always return datetime objects instead of VO
date.

Regards,
Willie

"Kevin"  wrote in message news:TcWmp.929$K4.384@news.indigo.ie...

Geoff,

> It is a VO app so I have chosen to display the date as a VO date using the 
> current regional settings.

I was not certain whether it was the way you chose to interpret the date
columns or there was something you were doing in the SQL that I was missing.

> Same deal. They have to format it some way and they chose that way. But 
> you can display and use the date in any format you wish or add an 
> expression column to the dataset to show it another way.

This I understand and was looking for some way of changing the way the value
was getting returned. I had a look around the MSDN help and could find info
on what column types were available. What I could not find was how to get
the date column returned as a DateTime object rather than a String.

> Of course there is. Do you have a T-SQL book? Just look at date formats. 
> There is a whole table of them to return the value back in a multitude of 
> ways.

Simple answer, no I don't have a T-SQL book. Can you suggest any?
The only way that I could find to get the date column to return a date
rather than a string was a CAST. For example:
--> SELECT ClaimID, CAST(PurchaseDate as date) as PurDate From Purchases

The code runs and the columns display but unfortunately the PurchaseDate is
still a String. I'm probably missing something.

I think I have found a way to get what I am after. That is to create a
DataTable first with a DateTime field and then fill it from an
OleDbDataAdapter. In my original post I had been thinking that the issue may
have been with my lack of understanding of SQL and that the solution might
be there.

Kevin 

[toc] | [prev] | [next] | [standalone]


#743

From"Willie Moore" <williem@wmconsulting.com>
Date2011-04-07 11:08 -0500
Message-ID<inknhn$oco$1@speranza.aioe.org>
In reply to#742
Kevin,

The SQL you just gave me is a join, you are just using a different syntax. 
If you take that statement and paste it into the SQL management solution, it 
will return the join that it translates to:
  Select table1.field1,table1.field2,  table2.field2 from table1
        Join table2 on (table1.field1 = table2.field1)


Regards,
Willie


"Kevin"  wrote in message news:44lnp.934$K4.351@news.indigo.ie...

Willie,

It is an SQL table. The impression of the '#' came from looking at the code
generated by something else.

I actually got what I was looking for by using

  Select table1.field1,table1.field2,  table2.field2 from table1, table2
where table1.field1 = table2.field1

JOIN may do the trick but I am not sure if it gives me exactly what I am
looking for. Need to do a bit more research on it.

Kevin


"Willie Moore"  wrote in message news:inkj57$dju$1@speranza.aioe.org...

Kevin,

It sounds like you are using an access table. If so yes, you surround the
value of the date you are looking for in the SQL statement with a "#".
Attached is an example. I am also attaching a small aspx that shows
databinding a table with a date column. I use the join in SQL a lot when
setting up for grids. Once you have the result set, you can bind it to a
grid or create a table and then fill it from the result set.

Regards,
Willie

Method WriteTempRegistration() as string
    local cResult       as string
    local cSQL          as string
    local oCmd          As OleDbCommand

    try

        cResult := self:OpenConnection()
        if !empty(cResult)
            throw ApplicationException{"Error Opening Connection"}
        endif
        // get the timestamp
        SELF:SubmitDate := DateTime.Now:ToString()

        // build the SQL to write the record
        cSQL := "insert into PendingRegistration
(eventID,name,addr1,addr2,city,state,zip,phone,email,dogname,dogage,dogbreed,amt,SystemTS)"
        cSQL += " Values ("
        cSQL += " " + ConstClass.SQLString(self:cID)
        cSQL += ", '" + ConstClass.SQLString(self:cfullname) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:caddr1) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:caddr2) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cCity) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cState) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cpostal) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cphone) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cEmail) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cdogname) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cdogage) + "'"
        cSQL += ", '" + ConstClass.SQLString(self:cdogbreed) + "'"
        cSQL += ", " + self:nAmt:ToString()
        cSQL += ", #" + SubmitDate + "#"
        cSQL += " )"


        oCmd                := OleDbCommand{}
        oCmd:Connection     := self:oConn
        oCmd:CommandText    := cSQL
        oCmd:CommandTimeout := 300
        oCmd:ExecuteNonQuery()  // executes the command without a return
value

        cResult         := ""
    catch e as System.Exception
        cResult := e:Message
    end try
    RETURN cResult


<%@ Page Language="VB" ClassName="EventsMain" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OLEDB" %>
<%@ Import Namespace="System.collections" %>

<script runat="server">
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs)
        If Not IsPostBack Then
            Me.EventsGrid.DataSource = FillGrid()
            Me.EventsGrid.DataBind()
        End If
    End Sub

    Sub Grid_Change(ByVal sender As Object, ByVal e As
DataGridPageChangedEventArgs)
        Me.EventsGrid.CurrentPageIndex = e.NewPageIndex
        Me.EventsGrid.DataBind()
    End Sub 'Grid_Change

    Function FillGrid() As ICollection
        Dim connStr As String
        Dim conn As OleDbConnection

        Dim cSQL As String
        Dim adapter As New OleDbDataAdapter()
        Dim oDS As New DataSet()
        Dim title_col As New BoundColumn()
        Dim date_col As New BoundColumn()
        Dim loc_col As New BoundColumn()
        Dim description_col As New BoundColumn()
        Dim Cost_col As New BoundColumn()
        Dim Info_col As New ButtonColumn()

        ' put the user into the database
        connStr =
System.Configuration.ConfigurationManager.AppSettings("DSN")
        conn = New OleDbConnection(connStr)
        conn.Open()

        cSQL = "select * from Events"
        cSQL = cSQL & " Order by EventDate Desc"

        adapter.SelectCommand = New OleDbCommand(cSQL, conn)
        adapter.Fill(oDS)

        ' Add out columns
        title_col.DataField = "EventTitle"
        title_col.HeaderText = "Event"
        Me.EventsGrid.Columns.Add(title_col)

        date_col.DataField = "EventDate"
        date_col.HeaderText = "Date"
        date_col.DataFormatString = "{0:MM-dd-yyyy}"
        Me.EventsGrid.Columns.Add(date_col)

        loc_col.DataField = "EventLocation"
        loc_col.HeaderText = "Loction"
        Me.EventsGrid.Columns.Add(loc_col)

        description_col.DataField = "EventDescription"
        description_col.HeaderText = "Description"
        description_col.ItemStyle.Width = 250
        description_col.ItemStyle.Wrap = "True"
        Me.EventsGrid.Columns.Add(description_col)

        Cost_col.DataField = "EventCost"
        Cost_col.HeaderText = "Cost"
        Cost_col.DataFormatString = "{0:c}"
        Me.EventsGrid.Columns.Add(Cost_col)

        Info_col.DataTextField = "EventOpen"
        Info_col.ButtonType = ButtonColumnType.LinkButton
        'Info_col.Text = "cancel"
        Info_col.CommandName = "cancel"
        Info_col.HeaderText = " "
        Me.EventsGrid.Columns.Add(Info_col)

        Return oDS.Tables(0).DefaultView

    End Function 'FillGrid

    Sub Item_Bound(ByVal sender As Object, ByVal e As DataGridItemEventArgs)
        Dim item As DataGridItem
        Dim rv As DataRowView
        Dim b As New HyperLink
        Dim cUrl As String
        Dim cEventID As String
        Dim EventDate As Date

        item = e.Item
        If item.ItemType = ListItemType.AlternatingItem Or item.ItemType =
ListItemType.Item Then
            rv = item.DataItem
            If rv.Row.Item("EventOpen") = False Then
                item.Cells(5).Visible = False
                'item.Visible = False
            Else
                cEventID = rv.Row.Item("EventID")
                EventDate = rv.Row.Item("EventDate")
                cUrl = rv.Row.Item("EventUrl") & ".aspx?EventID=" & cEventID
& "&EventDate=" & Server.HtmlEncode(EventDate.ToString("D"))
                cUrl = cUrl & "&EventLoc=" &
Server.HtmlEncode(rv.Row.Item("EventLocation"))
                cUrl = cUrl & "&EventCost=" &
Server.HtmlEncode(rv.Row.Item("EventCost"))
                cUrl = cUrl & "&Eventtime=" &
Server.HtmlEncode(rv.Row.Item("EventStartTime"))
                cUrl = cUrl & "&Eventtitle=" &
Server.HtmlEncode(rv.Row.Item("EventDescription"))
                b.ID = "BtnClick"
                b.Text = "Open"
                b.NavigateUrl = cUrl
                b.Enabled = True
                item.Cells(5).Controls.RemoveAt(0)
                item.Cells(5).Controls.Add(b)
            End If
        End If

    End Sub 'Item_Bound

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html;
charset=windows-1252" />
        <meta http-equiv="Content-Language" content="en-us" />
        <meta name="keywords" content="flyball, dogs, birmingham, alabama,
team, bandits, agility, obedience, tracking, herding, daycare" />
        <meta name="description" content="Manners in Motion utilized the
click and treat method of training " />
        <meta name="robots" content="FOLLOW,INDEX" />
        <title>Manners In Motion</title>
        <link href="/mannersinmotion/include/mim.css" rel="stylesheet"
title="styles" type="text/css" />
    </head>
    <body>
        <table width="100%" cellspacing="0" cellpadding="0" border="0"
runat="server">
            <tr>
                <td width="97%" align="left" valign="top">
                    <p align="center">
                        <a href="http://www.mannersinmotion.com"
target="blank"><asp:image id="Logo"
ImageUrl="/mannersinmotion/pictures/web%20dog%20with%20bouncing%20ball.jpg"
class="logo" runat="server" ImageAlign="Top" /></a>
<br /> <br />
                        <font size="5"><b>  Upcoming Events at Manners in
Motion</b></font>
                    </p>

                   <form id="Form1" method="post" runat="server"
style="text-align: center">
                        <asp:datagrid id="EventsGrid"
OnItemDataBound="item_bound" runat="server" OnPageIndexChanged="Grid_Change"
PageSize="15" BorderWidth="4px" BorderStyle="Double" ForeColor="Black"
AutoGenerateColumns="False" CellPadding="10" AllowPaging="True">
                            <HeaderStyle font-underline="True"
font-bold="True" backcolor="#FFFF66"></HeaderStyle>
                            <PagerStyle borderwidth="4px"
borderstyle="Solid" mode="NumericPages"></PagerStyle>
                        </asp:datagrid>
                       &nbsp;&nbsp; &nbsp;</form>


                    <div align="center">
                        <font size="2"><a
href="javascript:history.back()">Return to Previous Page</a></font>
                        <br /><br />
                        <hr style="width: 85%;" />
                        <font size="1">Last Updated: September 13th, 2007<br
/></font>
                    </div>

                </td>
            </tr>
        </table>
    <!--#include virtual="/mannersinmotion/include/sys-footer.inc" -->

    </body>
</html>

"Kevin"  wrote in message news:vKdnp.933$K4.436@news.indigo.ie...

Willie,

I had thought my issue was with the SQL syntax and could be fixed with a
change in it.  That's why I said nothing about VO or Vulcan.

Your suggestion would work fine for a single value but, as far as I can see,
not when opening the table to browse. Again, this is something I had not
mentioned.

My solution was to code a table manually including a DateTime column and
then use and OleDbAdapter to fill it. This has worked and given me the
desired results.

Can I ask two other questions?

1. Is the best way to use dates in Date or DateTime fields in the table to
surround it with '#'?

2. One or two of the columns in the table are look up values to another
table and showing the 'code'. If I wish to show the value instead is JOIN
the best SQL table to use?

Thanks.

Kevin


"Willie Moore"  wrote in message news:ini7rv$57m$1@speranza.aioe.org...

Kevin,

I thought we were talking about VO that is why I made the statement that I
did. BTW I have VN2ADO and it works fine with Vulcan 2. I do not use VN2ADO
for any new work but it is great for my legacy code. When I read in data
from ADO.Net, I usually do something like this to make sure that it doesn’t
cause a runtime error:
        LOCAL oDate as DateTime
        LOCAL cDate := "04/01/2011" as String // us date format
        if DateTime.TryParse(cDate,oDate)
           // do something
        else
           // handle error
        endif
You can also do this oDate := DateTime.Parse(cDate)
If you are reading out of an SQL table, you can do something like this:
    oDate := (datetime)oRow:Item["reg_date"]    // this coms in from SQL as
an object so you have to cast
    oDate := Convert.ToDateTimeoRow:Item["reg_date"])

Regards,
Willie

"Kevin"  wrote in message news:RY%mp.932$K4.423@news.indigo.ie...

Willie,

I am not using VO2ADO as it is not a VO app. I have Vn2ADO for Vulcan 1 but
don't know if if exists for Vulcan 2. There has been some discussion about
it in the past and I have heard nothing to say there was an update. I think
some people have got it to work but I would not be confident to try that
myself.

Kevin


"Willie Moore"  wrote in message news:inhnsn$s3h$1@speranza.aioe.org...

Kevin,

If you are using VO2ADO, the recordset will return a VO date or an
ADODateTime object depending the ADODateTimeAdDate function. Set it to FALSE
at the beginning of your app to always return datetime objects instead of VO
date.

Regards,
Willie

"Kevin"  wrote in message news:TcWmp.929$K4.384@news.indigo.ie...

Geoff,

> It is a VO app so I have chosen to display the date as a VO date using the 
> current regional settings.

I was not certain whether it was the way you chose to interpret the date
columns or there was something you were doing in the SQL that I was missing.

> Same deal. They have to format it some way and they chose that way. But 
> you can display and use the date in any format you wish or add an 
> expression column to the dataset to show it another way.

This I understand and was looking for some way of changing the way the value
was getting returned. I had a look around the MSDN help and could find info
on what column types were available. What I could not find was how to get
the date column returned as a DateTime object rather than a String.

> Of course there is. Do you have a T-SQL book? Just look at date formats. 
> There is a whole table of them to return the value back in a multitude of 
> ways.

Simple answer, no I don't have a T-SQL book. Can you suggest any?
The only way that I could find to get the date column to return a date
rather than a string was a CAST. For example:
--> SELECT ClaimID, CAST(PurchaseDate as date) as PurDate From Purchases

The code runs and the columns display but unfortunately the PurchaseDate is
still a String. I'm probably missing something.

I think I have found a way to get what I am after. That is to create a
DataTable first with a DateTime field and then fill it from an
OleDbDataAdapter. In my original post I had been thinking that the issue may
have been with my lack of understanding of SQL and that the solution might
be there.

Kevin 

[toc] | [prev] | [next] | [standalone]


#750

From"Geoff Schaller" <geoffx@softwarexobjectives.com.au>
Date2011-04-07 22:28 +0000
Message-ID<4d9e3a7e$0$11192$c30e37c6@exi-reader.telstra.net>
In reply to#712
Kevin,

The result of an SQL query is a string. But you can format it any way 
you choose. You do that in the query. Look at the Convert() function you 
can convert dates to all sorts of standard or template strings.

You will convert to a date value back in VO. I assume you use VO2Ado.

Geoff



"Kevin" <kdmurphy@eircom.net> wrote in message 
news:TcWmp.929$K4.384@news.indigo.ie:

> Geoff,
>
>
> > It is a VO app so I have chosen to display the date as a VO date using the
> > current regional settings.
>
>
> I was not certain whether it was the way you chose to interpret the date
> columns or there was something you were doing in the SQL that I was missing.
>
>
> > Same deal. They have to format it some way and they chose that way. But
> > you can display and use the date in any format you wish or add an
> > expression column to the dataset to show it another way.
>
>
> This I understand and was looking for some way of changing the way the value
> was getting returned. I had a look around the MSDN help and could find info
> on what column types were available. What I could not find was how to get
> the date column returned as a DateTime object rather than a String.
>
>
> > Of course there is. Do you have a T-SQL book? Just look at date formats.
> > There is a whole table of them to return the value back in a multitude of
> > ways.
>
>
> Simple answer, no I don't have a T-SQL book. Can you suggest any?
> The only way that I could find to get the date column to return a date
> rather than a string was a CAST. For example:
> --> SELECT ClaimID, CAST(PurchaseDate as date) as PurDate From Purchases
>
> The code runs and the columns display but unfortunately the PurchaseDate is
> still a String. I'm probably missing something.
>
> I think I have found a way to get what I am after. That is to create a
> DataTable first with a DateTime field and then fill it from an
> OleDbDataAdapter. In my original post I had been thinking that the issue may
> have been with my lack of understanding of SQL and that the solution might
> be there.
>
> Kevin

[toc] | [prev] | [next] | [standalone]


#769

From"Kevin" <kdmurphy@eircom.net>
Date2011-04-08 17:06 +0100
Message-ID<goGnp.935$K4.408@news.indigo.ie>
In reply to#750
Geoff,

I had tried the CAST but had not come across the CONVERT. CAST did not seem 
to be doing what I was looking for.

It is not a VO app so VO2Ado is not relavent. I'm not using Vn2Ado either. 
The reason I did not mention this is explained in an earlier reply I gave.

Kevin


"Geoff Schaller"  wrote in message 
news:4d9e3a7e$0$11192$c30e37c6@exi-reader.telstra.net...

Kevin,

The result of an SQL query is a string. But you can format it any way
you choose. You do that in the query. Look at the Convert() function you
can convert dates to all sorts of standard or template strings.

You will convert to a date value back in VO. I assume you use VO2Ado.

Geoff



"Kevin" <kdmurphy@eircom.net> wrote in message
news:TcWmp.929$K4.384@news.indigo.ie:

> Geoff,
>
>
> > It is a VO app so I have chosen to display the date as a VO date using 
> > the
> > current regional settings.
>
>
> I was not certain whether it was the way you chose to interpret the date
> columns or there was something you were doing in the SQL that I was 
> missing.
>
>
> > Same deal. They have to format it some way and they chose that way. But
> > you can display and use the date in any format you wish or add an
> > expression column to the dataset to show it another way.
>
>
> This I understand and was looking for some way of changing the way the 
> value
> was getting returned. I had a look around the MSDN help and could find 
> info
> on what column types were available. What I could not find was how to get
> the date column returned as a DateTime object rather than a String.
>
>
> > Of course there is. Do you have a T-SQL book? Just look at date formats.
> > There is a whole table of them to return the value back in a multitude 
> > of
> > ways.
>
>
> Simple answer, no I don't have a T-SQL book. Can you suggest any?
> The only way that I could find to get the date column to return a date
> rather than a string was a CAST. For example:
> --> SELECT ClaimID, CAST(PurchaseDate as date) as PurDate From Purchases
>
> The code runs and the columns display but unfortunately the PurchaseDate 
> is
> still a String. I'm probably missing something.
>
> I think I have found a way to get what I am after. That is to create a
> DataTable first with a DateTime field and then fill it from an
> OleDbDataAdapter. In my original post I had been thinking that the issue 
> may
> have been with my lack of understanding of SQL and that the solution might
> be there.
>
> Kevin 

[toc] | [prev] | [next] | [standalone]


#779

From"Geoff Schaller" <geoffx@softwarexobjectives.com.au>
Date2011-04-09 09:29 +0000
Message-ID<4da02710$0$11191$c30e37c6@exi-reader.telstra.net>
In reply to#769
Type date conversion into the SQL help or google and you will get the 
syntax for the SELECT statement. That is all you need.



"Kevin" <kdmurphy@eircom.net> wrote in message 
news:goGnp.935$K4.408@news.indigo.ie:

> Geoff,
>
> I had tried the CAST but had not come across the CONVERT. CAST did not seem
> to be doing what I was looking for.
>
> It is not a VO app so VO2Ado is not relavent. I'm not using Vn2Ado either.
> The reason I did not mention this is explained in an earlier reply I gave.
>
> Kevin
>
>
> "Geoff Schaller"  wrote in message
> news:4d9e3a7e$0$11192$c30e37c6@exi-reader.telstra.net...
>
> Kevin,
>
> The result of an SQL query is a string. But you can format it any way
> you choose. You do that in the query. Look at the Convert() function you
> can convert dates to all sorts of standard or template strings.
>
> You will convert to a date value back in VO. I assume you use VO2Ado.
>
> Geoff
>
>
>
> "Kevin" <kdmurphy@eircom.net> wrote in message
> news:TcWmp.929$K4.384@news.indigo.ie:
>
>
> > Geoff,
> >
> >
>
> > > It is a VO app so I have chosen to display the date as a VO date using
> > > the
> > > current regional settings.
> >
> >
>
> > I was not certain whether it was the way you chose to interpret the date
> > columns or there was something you were doing in the SQL that I was
> > missing.
> >
> >
>
> > > Same deal. They have to format it some way and they chose that way. But
> > > you can display and use the date in any format you wish or add an
> > > expression column to the dataset to show it another way.
> >
> >
>
> > This I understand and was looking for some way of changing the way the
> > value
> > was getting returned. I had a look around the MSDN help and could find
> > info
> > on what column types were available. What I could not find was how to get
> > the date column returned as a DateTime object rather than a String.
> >
> >
>
> > > Of course there is. Do you have a T-SQL book? Just look at date formats.
> > > There is a whole table of them to return the value back in a multitude
> > > of
> > > ways.
> >
> >
>
> > Simple answer, no I don't have a T-SQL book. Can you suggest any?
> > The only way that I could find to get the date column to return a date
> > rather than a string was a CAST. For example:
> > --> SELECT ClaimID, CAST(PurchaseDate as date) as PurDate From Purchases
> >
> > The code runs and the columns display but unfortunately the PurchaseDate
> > is
> > still a String. I'm probably missing something.
> >
> > I think I have found a way to get what I am after. That is to create a
> > DataTable first with a DateTime field and then fill it from an
> > OleDbDataAdapter. In my original post I had been thinking that the issue
> > may
> > have been with my lack of understanding of SQL and that the solution might
> > be there.
> >
> > Kevin

[toc] | [prev] | [standalone]


Back to top | Article view | comp.lang.clipper.visual-objects


csiph-web