Source language: Translate to:

Another SQL question

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Another SQL question

Postby carlos torres » Thu Jul 24, 2008 8:09 pm

Hi everybody

I'm trying to migrate my apps from using DBF to MDB.

After to check if a particular table exist If true then I open it but NB says that it cannot to perform that action with a closed recordset

Here is my code
Code: Select all
 
.../// table exist
dbpOpenTable "DbList" "Db" ""
       .dbpQuery "DbList" "Db" "GRUPO=[#34][dbGrupo][#34] AND CodMat=[#34][CodAsigna][#34]"
       dbpExecSQL "DbList" "SELECT * FROM DB |WHERE GRUPO=[#34][dbGrupo][#34]|AND CODMAT=[#34][CodAsigna][#34]" "Db2"
       dbpOpenTable "DbList" "Db2" ""


I will appreciate your help

regards
carlost
User avatar
carlos torres
 
Posts: 289
Joined: Mon May 02, 2005 8:14 am
Location: Pamplona, Colombia

Postby Gaev » Fri Jul 25, 2008 7:40 am

carlost:
After to check if a particular table exist If true then I open it but NB says that it cannot to perform that action with a closed recordset

a) where is your code that checks to see if the table exists ?

b) which of the commands returns the said error message ?

c)
Code: Select all
dbpExecSQL "DbList" "SELECT * FROM DB |WHERE GRUPO=[#34][dbGrupo][#34]|AND CODMAT=[#34][CodAsigna][#34]" "Db2"

i) try and be consistent with the case (upper/lower) of your table and field names

ii) what are the | characters doing in the command ?


Try ...
Code: Select all
dbpTableExists "DbList" "Db" "[Result]"
If "[Result]" "=" "True"
   AlertBox "After ..." "Db Exists !!!"
   dbpOpenTable "Dblist" "Db" ""
   AlertBox "After ..." "dbpOpen"
   dbpExecSQL "DbList" "SELECT * FROM Db WHERE GRUPO=[#34][dbGrupo][#34] AND CODMAT=[#34][CodAsigna][#34]" "Db2"
   AlertBox "After ..." "dbpExecSQL"
   dbpOpenTable "DbList" "Db2" ""
   AlertBox "After ..." "dbpOpenTable"
EndIf
... and post your results here.
User avatar
Gaev
 
Posts: 3737
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

About SQL Statement

Postby carlos torres » Fri Jul 25, 2008 3:02 pm

Hi dear Gaev

TIA for your wise support.

I was working around/debugging the SQL statement and I found this

1 - The pipe appears within the SQL editor when you double click on the dbpExecSQL. But it works fine, no problem.

2 - Because I was using a couple of strings with [dBGrupo] and [CodAsigna] I put [#34] double quotations instead of using [#39] that is the code for single one (quotation).

3 - Finally I used a single dbpQuery statement and works ok.

regards
carlost
User avatar
carlos torres
 
Posts: 289
Joined: Mon May 02, 2005 8:14 am
Location: Pamplona, Colombia

Postby Wrangler » Sat Jul 26, 2008 7:20 am

I too am switching my programs over from dbf to dbp. I'd like to use SQL wherever possible. I found that querying with SQL doesn't really improve the speed that much over dbpquery. I tested it on a DB with over 13,000 records. It DOES drastically speed up functions such as SUM and AVG.

In fact, when summing and averaging 45 currency fields, SQL took less than a second, where dbpro method took 5-6 seconds.

For summing and avg syntax, search for my post on this subject.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1507
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest