Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.clipper.visual-objects > #743
| From | "Willie Moore" <williem@wmconsulting.com> |
|---|---|
| Newsgroups | comp.lang.clipper.visual-objects |
| Subject | Re: Opening Date columns |
| Date | 2011-04-07 11:08 -0500 |
| Organization | Aioe.org NNTP Server |
| Message-ID | <inknhn$oco$1@speranza.aioe.org> (permalink) |
| References | (4 earlier) <RY%mp.932$K4.423@news.indigo.ie> <ini7rv$57m$1@speranza.aioe.org> <vKdnp.933$K4.436@news.indigo.ie> <inkj57$dju$1@speranza.aioe.org> <44lnp.934$K4.351@news.indigo.ie> |
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>
</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
Back to comp.lang.clipper.visual-objects | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread
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
csiph-web