Page 1 of 1

Error "cannot perform this operation on a closed dataset"

PostPosted: Wed Aug 09, 2017 12:51 pm
by gusgusl
Al intentar realizar una consulta en una base Mysql me salta el error mencionado "cannot perform this operation on a closed dataset"
La consulta es la siguiente:

dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta=0 LIMIT 10" "[Ventas]"

El Status de la conexion es "Connected" antes de realizar la consulta
Destacar también que antes funcionaba bien y de un momento a otro comenzó a darme ese error

la sintaxis total es la siguiente:

.Guardando ventas en Boleta
If "[ImpresionBoletas]" "=" "Si"
.Tomando NroBoleta
.dbpExecSQL "DumboWeb" "SELECT* FROM Configuraciones" "[Configuraciones]"
.SetVar "[NroBoleta]" "[DumboWeb.Configuraciones.BoletaNro]"

.Actualizando ventas Online con numero de Boleta
dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta=0 LIMIT 10" "[Ventas]"
If "[DumboWeb.Ventas.$RecCount]" ">=" "10"
.dbpExecSQL "DumboWeb" "UPDATE Ventas SET Boleta=[NroBoleta] WHERE Boleta=0 LIMIT 10" ""

.dbpShowGrid "DumboWeb" "Ventas" "Rectangle126"

.Inicializando Variables en boleta
SetVar "[subtotal]" ""
SetVar "[empresa]" ""
SetVar "[rut]" ""
SetVar "[total-boleta]" "0"
DefineVar "[fecha]" "Undefined" "" "Global" ""
DefineVar "[fecha]" "Date" "dd/mm/yyyy" "Global" "[DumboWeb.Ventas.Fecha]"

.dbpSum "DumboWeb" "Ventas" "Valor" "[total-boleta]"

.Aumentando Numero de Boleta
.Math "[NroBoleta]+1" "-1" "[NroBoleta]"
.dbpExecSQL "DumboWeb" "UPDATE Configuraciones SET BoletaNro=[NroBoleta]" "[Configuraciones]"

.Calculando Iva de Boleta a imprimir
Math "([total-boleta])/1.22" "2" "[subtotal]"
Math "[subtotal]*.22%" "2" "[iva]"

.Imprimiendo Boleta
dbpPreviewReport "Dumboweb" "[Pubdir]Reportes/Boleta.dbr" "DisplayMode=FitPage;InitialPage=1"
.dbpPrintReport "Dumboweb" "[Pubdir]Reportes/Boleta.dbr" "PrintDialog=No;StartPage=;EndPage=;Copies=1"
endif
endif

Re: Error "cannot perform this operation on a closed dataset

PostPosted: Thu Aug 10, 2017 7:08 am
by dpayer
This error usually indicates the database or the table within the database have not been opened for operations.

You need to make sure your application is talking to the MySQL database properly (ODBC drivers from MySQL site installed) and that you have used the commands to open the database:

example:

dbpOpenDatabase "databasename" "Provider=MSDASQL.1;Driver={MySQL ODBC 3.51 Driver};[dbserverlocation];Database=databasename;User=databaseusername;Password=databasepassword;Option=3"
dbpOpenTable "databasename" "databasetablename" ""

Re: Error "cannot perform this operation on a closed dataset

PostPosted: Thu Aug 10, 2017 8:55 am
by gusgusl
Estoy conectado a la base de datos Mysql porque puedo trabajar con otras tablas de la misma sin problemas
ademas si verifico el estado de la conexion me da que esta "Conectada"
Puede ser que la compilacion del programa la hize en una maquina que tuve que reinstalar el driver de conexion a Mysql?
y eso me afecta a todas las mquinas donde quiero correr el programa?

.Abriendo conexion remota
dbpOpenDatabase "DumboWeb" "Provider=MSDASQL.1;Driver={MySQL ODBC 5.2 UNICODE Driver};Server=guaviyu.org;Database=guaviyuo_Dumbo;User=My user;Password=My pass;Option=3"
While "[DumboWeb.$Status]" "<>" "Connected"
dbpOpenDatabase "DumboWeb" "Provider=MSDASQL.1;Driver={MySQL ODBC 5.2 UNICODE Driver};Server=guaviyu.org;Database=guaviyuo_Dumbo;User=My user;Password=My pass;Option=3"
dbpShowErrors "No"
EndWhile

El driver de conexion que tengo instalado es:
mysql-connector-odbc-5.2.5-win32

Re: Error "cannot perform this operation on a closed dataset

PostPosted: Thu Aug 10, 2017 11:00 am
by dpayer
You appear to use a newer driver (unicode capable) for MySQL than I do.

I don't know if this is an issue or not. I do know that Neobook is NOT unicode capable.

Try the older MySQL ODBC driver and see if that solves the issue.

Re: Error "cannot perform this operation on a closed dataset

PostPosted: Thu Aug 10, 2017 12:20 pm
by gusgusl
Reinstale el driver anterior que tenia y sigue el mismo problema, el mensaje de error sigue mostrandose
El test de conexion me dice que se conecta perfectamente al momento de conectar a la base de datos
Lo raro es que si puedo hacer consultas a otras tablas de la base de datos sin problemas, como asi tambien agregar registros a la base involucrada en este ejemplo que es "Ventas" desde otra parte del programa

Re: Error "cannot perform this operation on a closed dataset

PostPosted: Thu Aug 10, 2017 1:50 pm
by Gaev
gusgusl:

Here is your initial code properly indented ...
Code: Select all
.Guardando ventas en Boleta
If "[ImpresionBoletas]" "=" "Si"
   .Tomando NroBoleta
   .dbpExecSQL "DumboWeb" "SELECT* FROM Configuraciones" "[Configuraciones]"
   .SetVar "[NroBoleta]" "[DumboWeb.Configuraciones.BoletaNro]"

   .Actualizando ventas Online con numero de Boleta
   dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta=0 LIMIT 10" "[Ventas]"
   If "[DumboWeb.Ventas.$RecCount]" ">=" "10"
      .dbpExecSQL "DumboWeb" "UPDATE Ventas SET Boleta=[NroBoleta] WHERE Boleta=0 LIMIT 10" ""

      .dbpShowGrid "DumboWeb" "Ventas" "Rectangle126"

      .Inicializando Variables en boleta
      SetVar "[subtotal]" ""
      SetVar "[empresa]" ""
      SetVar "[rut]" ""
      SetVar "[total-boleta]" "0"
      DefineVar "[fecha]" "Undefined" "" "Global" ""
      DefineVar "[fecha]" "Date" "dd/mm/yyyy" "Global" "[DumboWeb.Ventas.Fecha]"

      .dbpSum "DumboWeb" "Ventas" "Valor" "[total-boleta]"

      .Aumentando Numero de Boleta
      .Math "[NroBoleta]+1" "-1" "[NroBoleta]"
      .dbpExecSQL "DumboWeb" "UPDATE Configuraciones SET BoletaNro=[NroBoleta]" "[Configuraciones]"

      .Calculando Iva de Boleta a imprimir
      Math "([total-boleta])/1.22" "2" "[subtotal]"
      Math "[subtotal]*.22%" "2" "[iva]"

      .Imprimiendo Boleta
      dbpPreviewReport "Dumboweb" "[Pubdir]Reportes/Boleta.dbr" "DisplayMode=FitPage;InitialPage=1"
      .dbpPrintReport "Dumboweb" "[Pubdir]Reportes/Boleta.dbr" "PrintDialog=No;StartPage=;EndPage=;Copies=1"
   endif
endif


1) try and run your application after commenting out this command ...
Code: Select all
dbpPreviewReport "Dumboweb" "[Pubdir]Reportes/Boleta.dbr" "DisplayMode=FitPage;InitialPage=1"

a) if it works ... reinstate command using ...

- DumboWeb instead of Dumboweb
- inserting an ! before [PubDir]

b) if it still does not work, try and insert ...
Code: Select all
dbpOpentable "DumboWeb" "Ventas"

... before ...
Code: Select all
dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta=0 LIMIT 10" "[Ventas]"

... if it STILL does NOT work ...

- insert space between SELECT and *
- check value of variable [Ventas] before this command s serviced

Re: Error "cannot perform this operation on a closed dataset

PostPosted: Fri Aug 11, 2017 7:23 am
by gusgusl
Lamentablemente nada funciono
Unfortunately nothing worked

si intento abrir la base de datos "Ventas" antes de hacer el "Query" me da el error "The table Ventas could not be opened"
If I try to open the database "Ventas" before doing the "Query" gives me the error "The table Sales could not be opened"

Como dije antes, yo puedo acceder a esa tabla "Ventas" desde otras paginas del programa sin problemas, agregar registros en la misma, hacer consultas,etc
As I said before, I can access that table "Ventas" from other pages of the program without problems, add records in it, make queries, etc.

Ejemplo de esto:
Example of this:

Consulta realizada en la misma tabla desde diferentes lugares de la aplicacion,
Consultation carried out in the same table from different places of the application,

dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Detalle='[Departamento]' AND Fecha='[Fecha1a]';" "[Ventas]" Este codigo funciona bien....This code works fine
dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta=0 LIMIT 10" "[Ventas]"


el problema del mensaje me lo da solo en ese codigo que publique mas arriba
The problem of the message gives me only in that code that publishes above

probe cambiar el tipo de consulta y funciono:
I did the test to change the type of query and it worked:

dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta>0 LIMIT 10;" "[Ventas]"

El problema es que si miro la base de datos en el servidor existen "Boletas" con valor "0" lo digo por si fuera un error que surge al intentar buscar datos que no existen en la tabla
The problem is that if I look at the database on the server there are "Boletas" with value "0" I say it if it were an error that arises when trying to search for data that does not exist in the table

Re: Error "cannot perform this operation on a closed dataset

PostPosted: Fri Aug 11, 2017 2:50 pm
by Gaev
gusgusl:

Does Not Work -
dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta=0 LIMIT 10" "[Ventas]"

Works -
dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta>0 LIMIT 10;" "[Ventas]"

Try ...

1) adding a semi-colon (;) at the end of the SQL command (with Boleta=0)

2) this logic for Boleta=0 ...
Code: Select all
dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE NOT Boleta>0 AND NOT Boleta<0 LIMIT 10;" "[Ventas]"


3) this command using another field that is like Boleta (i.e. integer) ...with/without trailing semi-colon ...
Code: Select all
dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE FieldXYZ=0 LIMIT 10" "[Ventas]"
dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE FieldXYZ=0 LIMIT 10;" "[Ventas]"

Re: Error "cannot perform this operation on a closed dataset

PostPosted: Sat Aug 12, 2017 6:53 am
by gusgusl
descubri algo muy importante
todas las expresiones funcionan correctamente si el numero a buscar no es "0"
si pongo otro valor, ejemplo "1" ya funciona bien

I discovered something very important
All expressions work correctly if the number to search is not "0"
If I put another value, example "1" already works well

No funciona..........dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta=0 LIMIT 10" "[Ventas]"
Funciona..........dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta=1 LIMIT 10" "[Ventas]"

Cual podria ser el problema, algun cambio en el servidor por parte de la empresa de hosting en la version del Mysql?
What could be the problem, some change in the server by the hosting company in the version of Mysql?

La empresa de hosting me dice que no hizieron cambios de ningun tipo

Re: Error "cannot perform this operation on a closed dataset

PostPosted: Sat Aug 12, 2017 8:52 am
by Gaev
gusgusl:

I discovered something very important
All expressions work correctly if the number to search is not "0"
If I put another value, example "1" already works well

No funciona..........dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta=0 LIMIT 10" "[Ventas]"
Funciona..........dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta=1 LIMIT 10" "[Ventas]"

Progress ... in zooming in on the issue.

What could be the problem, some change in the server by the hosting company in the version of Mysql?

One of two possibilities ...

1) The records on your server are NOT stored with a zero value ("null" ?) ... the external tool you use shows them when you query Boleta=0 ... but NeoBookDBPro/ODBC does not

2) Boleta=0 is being incorrectly parsed


The collective results of these queries will help to find out more ...

NOT Boleta=0
Boleta<0
Boleta>0
NOT Boleta<0 AND NOT Boleta>0

Re: Error "cannot perform this operation on a closed dataset

PostPosted: Sat Aug 12, 2017 10:06 am
by gusgusl
Realize un "update" en la base de datos desde el servidor cambiando las "Boletas" con valor "0" a valor "1" para luego intentar hacer nuevamente la consulta desde Neobook y de nuevo me vuelve el error.
Perform an "update" in the database from the server by changing the "Boleta" with value "0" to value "1" then try again from Neobook and again I get the error.

Tambien intente cambiando las caracteristicas del campo "Boleta" de "INT" a "TEXT" pero sin resultado alguno
Also try changing the characteristics of the "Boleta" field from "INT" to "TEXT" but without any result

El cotejamiento en la base de datos del campo"Boleta" es "latin1_swedish_ci"
The collation in the database of the "Boleta" field is "latin1_swedish_ci"

Puede ser motivo de esto la reinstalacion de neobook despues de formatear la maquina y que quizas lo instale en esta oportunidad en su version en Ingles y no en espanol
o puede ser que antes de reinstalar neobbok tenia windows 10 y ahora tengo windows 8?
This may be the reason for the reinstallation of neobook after formatting the machine and maybe install it on this occasion in its English version and not in Spanish
Or maybe before reinstalling neobbok had windows 10 and now I have windows 8?

Re: Error "cannot perform this operation on a closed dataset

PostPosted: Sat Aug 12, 2017 12:44 pm
by gusgusl
probé los diferentes query que me pediste:

dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE NOT Boleta=0;" "[Ventas]"........resultado:288455 sin errores
La misma búsqueda trabando directamente en el servidor me da como resultado : 288455


dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta<0;" "[Ventas]"..............resultado:0
La misma búsqueda trabando directamente en el servidor me da como resultado :0


dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta>0;" "[Ventas]"..............resultado: primera ejecucion:cannot perform in a closed dataset
segunda ejecucion:288455
tercera y mas ejecuciones:cannot perform in a closed dataset
La misma búsqueda trabando directamente en el servidor me da como resultado :288455


dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE NOT Boleta<0 AND NOT Boleta>0;" "[Ventas]".........resultado : cannot perform in a closed dataset
La misma búsqueda trabando directamente en el servidor me da como resultado :2573

Re: Error "cannot perform this operation on a closed dataset

PostPosted: Sat Aug 12, 2017 3:24 pm
by Gaev
gusgusl:

Thank you for the results ... to summarize ...

1) Looking directly on your server ...

dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta<0;" "[Ventas]" ... returns 0
dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE NOT Boleta<0 AND NOT Boleta>0;" "[Ventas]" ... this is same as Boleta=0 ... returns 2,573
dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta>0;" "[Ventas]" ... returns 288,455
dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE NOT Boleta=0;" "[Ventas]" ... this is same as Boleta<0 plus Boleta>0 ... returns 288,455

2) Looking at results return from neoBook ...

dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta<0;" "[Ventas]" ... returns 0
dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE NOT Boleta<0 AND NOT Boleta>0;" "[Ventas]" ... this is same as Boleta=0 ... returns "can not perform in a closed dataset"
dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE Boleta>0;" "[Ventas]" ... returns 288,455 in second run ... but "can not perform in a closed dataset" in first, third and fourth runs
dbpExecSQL "DumboWeb" "SELECT* FROM Ventas WHERE NOT Boleta=0;" "[Ventas]" ... this is same as Boleta<0 plus Boleta>0 ... returns 288,455

Very inconsistent (and inexplainable) results ... the only thing I can request you to do is repeat the tests with ...

a) an exclamation mark before the word SELECT (to make sure NeoBook sends exactly the same command text as entered)
b) a space between SELECT and * (in case the new driver is stricter on syntax)

If that does not make a difference, I am not able to suggest where (client side or server side) the bug exists ... but (assuming that Boleta is an integer field and that there are no negative values) I can suggest a work around to obtain the records where Boleta=0 ...

dbpExecSQL "DumboWeb" "!SELECT * FROM Ventas WHERE Boleta<1;" "[Ventas]"

... should return 2,573 records by NeoBook and the server tool ... please try and confirm.

Re: Error "cannot perform this operation on a closed dataset

PostPosted: Sun Aug 13, 2017 7:40 am
by gusgusl
tampoco me funciona esta ultima prueba:

The last test does not work either:

dbpExecSQL "DumboWeb" "!SELECT * FROM Ventas WHERE Boleta<1;" "[Ventas]"

Realmente no entiendo que es lo que sucede, lo raro es que 4 veces al principio funciono sin problemas para luego comenzar a darme ese error

I really do not understand what's going on, the weird thing is that 4 times at first I run smoothly and then start giving myself that mistake