Database datetime field update error

Hi,

I’ve got a Xojo application that was being developed about 2 years ago. I’m having to bring it back into operation and I’m struggling to figure out what may have changed to cause me to have a database error while update a record in an SQL database. The error appears to be related to writing a date time value into the SQLDatetime column.

The odd thing is that I can create a record with a date time value without an error, but then I try and update a record with a new date I get the error “The statement has been terminated”.

I have tried opening the project in both the current XOJO version and the 2018 version that was last used to develop the project but neither works so I’m starting to wonder if this is an SQL issue and not Xojo.

Has anyone had this issue before?

Thanks

It’s hard to determine what’s happening without seeing the code and knowing what OS and database system you are using.

Hi Dale,

Sorry or course that would help!


Dim d as new date

Dim sql As String
sql = “SELECT * FROM D_PCMAP WHERE ID=”+id.ToText

Dim data As RecordSet
data = mDB.SQLSelect(sql)

If mDB.Error Then
MsgBox("DB Error: " + mDB.ErrorMessage)
Return false
End If

data.edit
data.Field(“Brand”).StringValue=BrandColumnName.text
data.Field(“PaperType”).StringValue=TypeColumnName.text
data.Field(“ProductCode”).StringValue=CodeColumnName.text
data.Field(“Colour”).StringValue=ColourColumnName.text
data.Field(“Size1”).StringValue=Size1ColumnName.text
data.Field(“Size2”).StringValue=Size2ColumnName.text
data.Field(“GSM”).StringValue=GSMColumnName.text
data.Field(“Micron”).StringValue=MicronColumnName.Text
data.Field(“Price”).StringValue=Price1ColumnName.text
data.Field(“PriceQty”).StringValue=Price1QtyColumnName.text
data.Field(“PriceUnits”).StringValue=Price1UnitsColumnName.Text
data.Field(“TonnePrice”).StringValue=TonneColumnName.Text
data.Field(“PackSize”).StringValue=PackColumnName.Text
data.Field(“MinOrder”).StringValue=MinColumnName.Text
data.Field(“IntervalQty”).StringValue=IntervalColumnName.Text
data.Field(“Filename”).StringValue=CsvFileName.text
data.Field(“Path”).StringValue=FilePath.text
data.Field(“Leadtime”).StringValue=LeadTimeColumnName.text

//BELOW IS THE FIELD CAUSING THE ERROR
data.Field(“ModifiedDate”).DateValue=d

data.Field(“DefaultLeadTime”).StringValue=DefaultLeadtime.text
data.Field(“DefaultPriceDescription”).StringValue=DefaultPriceDescription.text
data.field(“DescriptionDuplicates”).BooleanValue=DescriptionDuplicates.Value
data.Field(“Description1”).StringValue=Description1ColumnName.Text
data.Field(“Description2”).StringValue=Description2ColumnName.Text
data.Field(“PriceExtract”).StringValue=Price1QtyExtractColumnName.text
data.Field(“PriceExtractFind”).StringValue=PriceExtractFind.Text
data.Field(“KGS”).StringValue=KGsColumnName.Text

data.update
If mDB.Error Then

MsgBox "DB Error: " + mDB.ErrorMessage
Return False
else
mdb.Commit
End If


Below is an extract of the code that is originally writing the date to the database.

Dim row As New DatabaseRecord

row.Column(“Brand”)=BrandColumnName.text
row.Column(“PaperType”)=TypeColumnName.text
row.Column(“ProductCode”)=CodeColumnName.text
row.Column(“Colour”)=ColourColumnName.text
row.Column(“Size1”)=Size1ColumnName.text
row.Column(“Size2”)=Size2ColumnName.text
row.Column(“GSM”)=GSMColumnName.text
row.Column(“Micron”)=MicronColumnName.Text
row.Column(“Leadtime”)=LeadTimeColumnName.text
row.Column(“Price”)=Price1ColumnName.text
row.Column(“PriceQty”)=Price1QtyColumnName.text
row.Column(“PriceUnits”)=Price1UnitsColumnName.Text
row.Column(“TonnePrice”)=TonneColumnName.Text
row.Column(“PackSize”)=PackColumnName.Text
row.Column(“MinOrder”)=MinColumnName.Text
row.Column(“IntervalQty”)=IntervalColumnName.Text
row.Column(“Filename”)=CsvFileName.text
row.Column(“Path”)=FilePath.text
row.Column(“Description”)=Description.text

row.DateColumn(“ModifiedDate”)=d

row.Column(“SupplierID”)=SupplierID.text
row.Column(“DefaultLeadTime”)=DefaultLeadtime.text
row.Column(“DefaultPriceDescription”)=DefaultPriceDescription.text
row.BooleanColumn(“DescriptionDuplicates”)=DescriptionDuplicates.Value
row.Column(“Description1”)=Description1ColumnName.Text
row.Column(“Description2”)=Description2ColumnName.Text
row.Column(“PriceExtract”)=Price1QtyExtractColumnName.text
row.Column(“PriceExtractFind”)=PriceExtractFind.Text
row.Column(“KGS”)=KGsColumnName.Text

mDB.InsertRecord(“D_PCMAP”, row)

This works without error.

The OS is Windows 10 ands the SQL database is on Windows 2012 and is SQL Express.

Does anyone see what I’ve done wrong?

Thanks

what is inside the “d” value ?

Just out of curiosity, if you were to redirect the code to a local SQLite database, does the same issue appear? Unless there is something weird with the contents of “d”, the issue may be with SQL Express.

Another thought, if the app hasn’t been deployed yet, perhaps you could modify the code to store and retrieve SQLDate values rather than a Date object. I don’t know if that will make a difference but it might be worth a try.

Yet another thought, is the app set for 64-bit? If yes, will the same issue arise if you set it back to 32-bit? Maybe the SQL Express or Windows 2012 system is 32-bit and a conversion somewhere isn’t working properly.

The D should be containing just a new Date, as created by the

DIM d as new date

I just tried the 64/32bit options and it doesn’t seem to have fixed the issue.

Next I’ll have to try the SQLite, but that will take a bit longer to test.

could be that one of the string field before the date field has too much characters in it ?
seems the max default is 8000 cars.

also look at the db error message inside the debugger, because this is the last line of the error message
there could be useful informations BEFORE this last sentence.

I originally thought the error was due to a string length, but I went through the code line by line and it was only the date line that caused the error. With the date commented out then I get no error.

It’s really odd as I’m sure the date is in the right format and it’s updating a field that was created with the same date type code originally. It just can’t update the field once it’s written.

I’ll have a look at the error message in the debugger and see if there’s any more info.

Thanks

I checked in the debugger and I can’t see any more text in the error message on the database object, it just says Error Message:The statement has been terminated.

The Error number is 3621.

https://www.sqlserver-dba.com/2014/09/error-3621-the-statement-has-been-terminated.html

Hi, yes I found that article too, but I don’t believe it’s a timeout issue as it returns the error instantaneously and from method call to error is well under a second. And it takes no longer than the original saving of the data which works fine. I also looked at the content of the database record before it attempted the update and the date time field looks fine to me.

It’s very odd though.