| View previous topic :: View next topic |
| Author |
Message |
Balachmar
Newbie
Joined: 15 May 2006
Posts: 4
|
 Posted: Tue May 16, 2006 7:14 am Post subject: Get Date from DateField and store it in a database |
 |
|
I want to get the date from a DateField (from a Form) and store that date in the database.
The table in question has only three colums:
ID (autoincrement)
date (date)
naam (varchar)
This is my macro until now...
| Code: |
sub AddEvent( oEv as Object )
dim query as String
dim form
dim eventDate
dim eventName as String
dim Statement as Object
' get the form that is using our xRef table
'form = ThisComponent.DrawPage.Forms.GetByIndex(0)
form = oEV.Source.getModel().getParent()
' get the key fields for curent record of the rowset attached to this form
eventName = form.GetbyName("Naam").text
eventDate = form.GetbyName("DateField").Date
form.getByName("TextBox").setString(eventDate)
query = "INSERT INTO ""Evenementen"" (""datum"", ""naam"") VALUES (" & eventDate & ", " & eventName & ")"
Statement = form.ActiveConnection.CreateStatement
Statement.executeUpdate( query )
End Sub
|
It fails.
It complains about Wrong Data Type in the SQL syntax. |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed May 17, 2006 3:14 pm Post subject: |
|
|
form.GetbyName("DateField").Date returns an UNO-struct. Pass the string of the control or if the string has an inappropriate format use something like this:
| Code: |
Function Date2ISO(vDt) as String
Dim y$,m$,d$,dtVal as Date
'conversion to basic-date dtVal
If vartype(vDt) = 9 Then 'object/UNO-struct
dtVal = dateSerial(vDt.year,vDt.month,vDt.day)
Else
dtVal = cDate(vDt)
End If
y = cStr(year(dtVal)) &"-"
m = cStr(Month(dtVal))&"-"
d = cStr(Day(dtVal))
if len(y) < 4 then y = string(4-len(y),"0")
if len(m) = 1 then m = "0"& m
if len(d) = 1 then d = "0"& d
Date2ISO = y & m & d
End Function
|
It converts an UNO-date to 2000-12-31, which may work with SQL. I use this with CalcCell.setFormulaLocal.
EDIT: This API is full of inconsistancies. Your date-value is of type long.
Changed the function. Now you may pass anything convertable to basic-type date. No error-handling implemented!
StarBasic assumes 1899-12-30 as day zero when converting a number to date. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu May 18, 2006 11:31 am Post subject: |
|
|
OK, you may have noticed that the function above insists on getting an object-variable. Seems to be a Starbasic-issue http://www.openoffice.org/issues/show_bug.cgi?id=65555
I simplified this function and split it in two:
| Code: |
Sub testDateConversion
Dim oDt as new com.sun.star.util.Date
oDt.Year = 2000
oDt.Month = 12
oDt.Day =31
print IsoFromDateStruct(oDt)
print IsoFromDateValue(39000)
End Sub
'pass a number or date-string
Function IsoFromDateValue(vDt)as string
Dim sDt$
sDt = cDateToISO(cDate(vDt))
IsoFromDateValue = left(sDt,4)&"-"& mid(sDt,5,2)&"-"& right(sDt,2)
End Function
'pass an UNO-struct com.sun.star.util.Date or DateTime.
Function IsoFromDateStruct(oDt as Object)as string
Dim dtVal as Date
If isUNOStruct(oDt) then
dtVal = DateSerial(oDt.Year,oDt.Month,oDt.Day)
IsoFromDateStruct = IsoFromDateValue(dtVal)
endif
End Function
|
EDIT: meanwhile I learned by answering another thread that those form-controls return dates and times in a ridiculous format (sequence of digits as long).
So you may use this with your SQL:
| Code: |
eventDate = form.GetbyName("DateField").Date
form.getByName("TextBox").setString(eventDate)
sDate = "{D'"& left(eventDate(4,) &"-"& mid(eventDate,5,2) &"-"& right(eventDate,2) &"'}"
query = "INSERT INTO ""Evenementen"" (""datum"", ""naam"") VALUES (" & eventDate & ", " & eventName & ")"
|
See online-help on date/time in queries.{D'YYYY-MM-DD'} |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
