This is a follow up to an unresolved issue I was having in this thread.
Setup is 2016r3, Win10, MSSQL database.
I have been able to isolate the issue, but not resolve it myself. I have two methods (A and B), which connect to a database and grab a decimal value. Each method returns the decimal value as a Double. I then have a third method (Test), with a local: v As Double, creating a calculation with the returned values of methods A and B (v As Double = A * 12.00 / B). I then take the calculated v and update a record in the same database. When I update the record, it does not save nor does it pass an errorcode.
I have tried assigning the A and B returned values to Properties (pA and pB) and calculating Test with those properties and updating the database, but same failed result. If I give methods A and B a static number to return, or assign a static number to the properties, and calculate Test, it then updates correctly. The v As Double shows the correct calculated value in Debugger. And if I copy the SQL update statement out of Debugger and paste into a SQL Studio, the statement updates correctly.
This code exists in a replicated project. In my existing project, I have the Test method about 50 times for different calculations, and about half of them work correctly, but they’re all executed with the same code (only the database record and value of that record are different from one another, but always a decimal value).
well I was going to take a look, but Filedropper has dozens of “buttons”, of which I’m sure ONE connects to your project and the rest will download something I don’t want, or connect me to a service I don’t need… In any case, I’m sorry, but I won’t run a maze.
The problem is that in method test you connect to the database and expect the connection to persist until the end of the method. However in methods a and b you again connect to the database perform some actions and close that connection. This means that when you update the database later in test the database is closed and so the update is not performed.
@Dave S Sorry, I just chose the first random file sharing site I found from a google search. But it was attached to the feedback case mentioned as well.
@Wayne Golding While that may appear to be the issue, I’ve always understood the database connections to be explicit, so as you open one you must close one. But I am new to Xojo, so I could be wrong. But in that same sense, after the internal methods are called, and the database connection is opened and closed, I wrapped the Update Statement with an “If db.Connect Then…” and it returns true. The code still believes there is a connection to the database. Just for some reason it returns a false true, I also added the database connection before the update statement after the internal methods, and again returns true to Connect but does not update the table. I would also assume the ErrorCode would return a missing database error. I believe I’ve nested database connections before with this method with no issue.
To also note, I moved the internal methods to prior to the primary method (test)'s database connection. So inside the method Test, it executes method A, then method B, then connects to the database and on a db.connect test, it returns true, but the Update statement again does not update the record.
I don’t feel this is a database connection issue, but more how a value from a recordset/database is being encoded by the return of a method that is making it “unusable” for a SQL statement. As the project shows, I can have those methods return a statically assigned Double, and it executes as expected.
I have updated your original project which you can get from here. db is no longer a property of App and exists only in the scope of the methods, so a, b & test now have individual db objects rather than sharing a property of the App object.