Database datetime field update error

  1. 2 weeks ago

    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

  2. Dale A

    Jun 19 San Diego, California, USA

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

  3. 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.

  4. 4 days ago

    Does anyone see what I've done wrong?

    Thanks

  5. Jean-Yves P

    Jun 29 Testers, Xojo Pro Europe (France, Besançon)

    what is inside the "d" value ?

  6. Dale A

    Jun 29 San Diego, California, USA

    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.

  7. 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.

  8. Jean-Yves P

    Jun 29 Testers, Xojo Pro Europe (France, Besançon)
    Edited 4 days ago

    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.

  9. 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

  10. 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.

  11. Jean-Yves P

    Jun 30 Testers, Xojo Pro Europe (France, Besançon)

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

  12. 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.

or Sign Up to reply!