Subject: DateTime field error in Mashup Script - by: Nikolay Ruban

DateTime field error in Mashup Script 22 Hours, 18 Minutes ago After upgrading from TeamTrack 6.6.1.16 to SBM 2009 R3, I am getting an error on a date filed in the script.

I am trying to read a date field on the form and then trying to see if it exists in an Aux table. If it exists in the Aux table, we define this as being in a freeze period. However, when the Aux table is trying to be read with a ReadWithWhere(whereClause) I get the error.

th section of the script that is causing the error is here:

call ext.setCompatibilityVersion(7,0)
~~
~~
if (IssueType = 15 or IssueType = 16 or IssueType = 17) and (TransitionID = 4 or TransitionID = 28 or TransitionID = 88 or TransitionID = 144) or TransitionID = 119 or TransitionID = 106 then
fldOK = Shell.Item.GetFieldValue("Target Date" , intTargetDate)
shell.redomessage="we have a freeze date find :: " & "Target date is " & intTargetDate
tableId = "1015" ' Freeze Date
Set myRecord = Ext.CreateAppRecord(1015)
whereClause = "TS_FREEZE_DATE = " & intTargetDate
if myRecord.ReadWithWhere(whereClause) Then
fldOK = Shell.Item.SetFieldValue("Exception Required" , "1")
fldOK = myRecord.GetFieldValue("Freeze Date End", intFDEnd)
fldOK = Shell.Item.SetFieldValue("Exception Expiration Date" , intFDEnd)
else
fldOK = Shell.Item.SetFieldValue("Exception Required" , "0")
fldOK = Shell.Item.SetFieldValue("Exception Approved" , "0")
intFDEnd = ""
fldOK = Shell.Item.SetFieldValue("Exception Expiration Date" , intFDEnd)
end if
end if

The error message is below:

The record with the 'select U_FREEZE_DATES.TS_ID, U_FREEZE_DATES.TS_UUID, U_FREEZE_DATES.TS_TITLE, U_FREEZE_DATES.TS_FREEZE_DATE, U_FREEZE_DATES.TS_APP_FREEZE_TYPE, U_FREEZE_DATES.TS_FREEZE_DATE_END, U_FREEZE_DATES.TS_DESCRIPTION from U_FREEZE_DATES where TS_Freeze_Date = 2010-11-16T00:00:00+00:00' select statement could not be read in the 'Freeze Dates' database table.
(TTexcIDS_EXC_CANNOT_READ_RECORD_WITH_SELECT_STMT)
Incorrect syntax near '00'.

Do I need to convert to Integer and query that?

The administrator has disabled public write access. Re:DateTime field error in Mashup Script 5 Hours, 30 Minutes ago Nikolay, yes the new db format in SBM is using DateTime for Date fields.
The returned value is an integer '1289952000' and I am trying to do a CreateAppRecord based on this to another table with a date field.
Here is what I am using:

fldOK = Shell.Item.GetFieldValue("Target Date" , intTargetDate)
tableId = "1015" ' Freeze Date
Set myRecord = Ext.CreateAppRecord(1015)
TargetDate = CStr(ext.dblongtodate(intTargetDate))
whereClause = "TS_FREEZE_DATE = " & TargetDate
shell.redomessage="we have to find a freeze date:: " & "Target date integer is " & intTargetDate & " and TargetDate is " & TargetDate
if myRecord.ReadWithWhere(whereClause) Then

This is the error I am getting now:
The record with the 'select U_FREEZE_DATES.TS_ID, U_FREEZE_DATES.TS_UUID, U_FREEZE_DATES.TS_TITLE, U_FREEZE_DATES.TS_FREEZE_DATE, U_FREEZE_DATES.TS_APP_FREEZE_TYPE, U_FREEZE_DATES.TS_FREEZE_DATE_END, U_FREEZE_DATES.TS_DESCRIPTION from U_FREEZE_DATES where TS_FREEZE_DATE = 11/16/2010 7:00:00 PM' select statement could not be read in the 'Freeze Dates' database table.
(TTexcIDS_EXC_CANNOT_READ_RECORD_WITH_SELECT_STMT)
Incorrect syntax near '7'.

The field "Target Date" is a field on the form input by a user. The U_Freeze_Date table are defined Freeze Dates in my company. If the Target Date(from the form) exists in the Freeze Date table I need to set a flag. How do I find if the field "Target Date" exists in the Aux Table?

The administrator has disabled public write access. Re:DateTime field error in Mashup Script 2 Hours, 47 Minutes ago yes we are using MSSQL.
The problem is I get the field value Target_Date as an integer. I am trying to do an ext.CreateAppRecord on an Aux table on for a date based on the Target_Date field that was read.
When I do this in SQL, it works:
select * from U_FREEZE_DATES
where TS_Freeze_DATE = (select TS_TARGET_DATE
from UCR_CHANGE_REQUESTS
where TS_ISSUEID = 29044)

yet when I use the same logic in TeamScript, it returns an error.
we have to find a freeze date:: Target date integer is 1289952000 and TargetDate is 11/16/2010 7:00:00 PM
The record with the 'select U_FREEZE_DATES.TS_ID, U_FREEZE_DATES.TS_UUID, U_FREEZE_DATES.TS_TITLE, U_FREEZE_DATES.TS_FREEZE_DATE, U_FREEZE_DATES.TS_APP_FREEZE_TYPE, U_FREEZE_DATES.TS_FREEZE_DATE_END, U_FREEZE_DATES.TS_DESCRIPTION from U_FREEZE_DATES where TS_FREEZE_DATE = 11/16/2010 7:00:00 PM' select statement could not be read in the 'Freeze Dates' database table.
(TTexcIDS_EXC_CANNOT_READ_RECORD_WITH_SELECT_STMT)
Incorrect syntax near '7'.

using this
if (IssueType = 15 or IssueType = 16 or IssueType = 17) and (TransitionID = 4 or TransitionID = 28 or TransitionID = 88 or TransitionID = 144) or TransitionID = 119 or TransitionID = 106 then
fldOK = Shell.Item.GetFieldValue("Target Date" , intTargetDate)
tableId = "1015" ' Freeze Date
Set myRecord = Ext.CreateAppRecord(1015)
TargetDate = CStr(ext.dblongtodate(intTargetDate))
whereClause = "TS_FREEZE_DATE = " & TargetDate
shell.redomessage="we have to find a freeze date:: " & "Target date integer is " & intTargetDate & " and TargetDate is " & TargetDate
if myRecord.ReadWithWhere(whereClause) Then
fldOK = Shell.Item.SetFieldValue("Exception Required" , "1")
fldOK = myRecord.GetFieldValue("Freeze Date End", intFDEnd)
fldOK = Shell.Item.SetFieldValue("Exception Expiration Date" , intFDEnd)
else
fldOK = Shell.Item.SetFieldValue("Exception Required" , "0")
fldOK = Shell.Item.SetFieldValue("Exception Approved" , "0")
intFDEnd = ""
fldOK = Shell.Item.SetFieldValue("Exception Expiration Date" , intFDEnd)
end if
end if

When I use what you suggested, I get this
RUNTIME ERROR IN SCRIPT "PostTransition" (id=8)
(called from post-transition context)

Numeric overflow -- ERR #2

Line 247> whereClause = "TS_FREEZE_DATE = DATEADD(SECOND," & CInt(intTargetDate) & ",'1970/01/01')"

Please understand I am not that good with TeamScripts.

The administrator has disabled public write access.

View the original article here