Just curious if anyone else has seen this issue:
I’m building an update SQL string and executing with SQLExecute, with the statement defining the record name as to which record to update. I have about 20 records using this same method but passed different record names to update. Some records update in the table, some don’t. I have a stop right after the SQL Execute in my code, I can then copy the SQL string out of Debugger and paste into a SQLStudio and execute the string and it updates the table, no issue.
An example SQL string (very simple):
UPDATE Table SET Value = 10.00 WHERE Name = 'Requested_Quantity'
I’ve even tried putting in the string to point to the direct ID number of that record, and still no take.
FWIW 2016r3, MSSQL Studio, Name in the DB is a varchar, the Value being updated is a decimal(10,5), and the database error doesn’t catch any errors.
I would suggest switching to a PreparedSQLStatement as its a lot less prone to random problems.
Unfortunately the native client for mssql is buggy in the prepared statement area and it is often necessary to use injection. For example writing to a varchar with more than 255 characters will result in truncation. Null columns frequently return random data too.
Yes, and in this case I have several statements that save the values while some do not. The variables that change in the SQL string are the name of the field (always a predetermined string) and value of the field (a calculated decimal/double). And the most perplexing is those fields that do not update through XOJO’s SQLExecute function, do update when the exact same string is executed in SQL Studio.
Long Shot:
How are you formatting that?
If the number is decimal and you are in the EU, it may represent PI as 3,14 inserting a comma.
Similarly, it may take thousands and do the same 3000 becomes 3,000 on some locales.
That comma would cause issues.
If you dont already, use STR() not CSTR()
http://documentation.xojo.com/index.php/Str
@Jeff Tullin I’ve actually tried formatting it several ways, but to no avail. I tried using the value.StringValue, Str(value), Format(value, “##0.00”) when placing it into the SQL string. When i pull the SQL string out of Debugger, it reads correctly and executes error-lessly in a SQL management studio.
After further meddling, it appears to be an issue with calling Methods and using Variants, but I can’t tell exactly how or what the issue is. I have two methods in the calculations, mA = 80 and mB = 96.00, both returning variants containing decimals. If I leave the calculation as: value = mA * 12.00 / mB, value correctly equals 10, but the table does not get updated from the SQL code. If I just enter: value = 10, the SQL updates the record correctly. I’ve tried setting both method returns and value to type Variant and Double. Neither work when using the methods.
In this conversation you attach a project & FB case.
A very quick analysis shows that you are closing your database connection in your called methods. There may be an issue where you should be getting an error on the database object as it is closed. A sqlselect will return an error.