Source language: Translate to:

Error "cannot perform this operation on a closed dataset"

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Error "cannot perform this operation on a closed dataset"

Postby gusgusl » Wed Aug 09, 2017 12:51 pm

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
gusgusl
 
Posts: 254
Joined: Fri Mar 12, 2010 12:44 pm

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

Postby dpayer » Thu Aug 10, 2017 7:08 am

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" ""
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1384
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

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

Postby gusgusl » Thu Aug 10, 2017 8:55 am

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
gusgusl
 
Posts: 254
Joined: Fri Mar 12, 2010 12:44 pm

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

Postby dpayer » Thu Aug 10, 2017 11:00 am

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.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1384
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

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

Postby gusgusl » Thu Aug 10, 2017 12:20 pm

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
gusgusl
 
Posts: 254
Joined: Fri Mar 12, 2010 12:44 pm

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

Postby Gaev » Thu Aug 10, 2017 1:50 pm

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
User avatar
Gaev
 
Posts: 3736
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

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

Postby gusgusl » Fri Aug 11, 2017 7:23 am

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
gusgusl
 
Posts: 254
Joined: Fri Mar 12, 2010 12:44 pm

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

Postby Gaev » Fri Aug 11, 2017 2:50 pm

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]"
User avatar
Gaev
 
Posts: 3736
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

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

Postby gusgusl » Sat Aug 12, 2017 6:53 am

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
gusgusl
 
Posts: 254
Joined: Fri Mar 12, 2010 12:44 pm

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

Postby Gaev » Sat Aug 12, 2017 8:52 am

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
User avatar
Gaev
 
Posts: 3736
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

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

Postby gusgusl » Sat Aug 12, 2017 10:06 am

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?
gusgusl
 
Posts: 254
Joined: Fri Mar 12, 2010 12:44 pm

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

Postby gusgusl » Sat Aug 12, 2017 12:44 pm

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
gusgusl
 
Posts: 254
Joined: Fri Mar 12, 2010 12:44 pm

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

Postby Gaev » Sat Aug 12, 2017 3:24 pm

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.
User avatar
Gaev
 
Posts: 3736
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

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

Postby gusgusl » Sun Aug 13, 2017 7:40 am

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
gusgusl
 
Posts: 254
Joined: Fri Mar 12, 2010 12:44 pm


Return to NeoBookDBPro

Who is online

Users browsing this forum: Bing [Bot] and 3 guests