Source language: Translate to:

Sql update possible

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Sql update possible

Postby BRobinsonS » Sat Mar 28, 2015 2:27 pm

Is this statement possible?
Code: Select all
dbpExecSQL "AddrBook" "UPDATE Contacts SET DrawNumber = [AddrBook.Contacts.$RecNum] Where ActiveStatus = True " ""


Not sure if you can use $RecNum variable in this statement.
I want to generate numbers from 1 to Total records after a Query.
These are draw numbers for Loonie draw for a membership.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sql update possible

Postby Gaev » Sat Mar 28, 2015 3:24 pm

Brian Robinson:

Not sure if you can use $RecNum variable in this statement.

I don't think so ... at best you might get the value of the current record when the command is issued.

I want to generate numbers from 1 to Total records after a Query.

In SQL, there is a value called ROW_NUMBER() ... but it looks like it returns the physical row number (not the row number within the Query).

You can try ...
Code: Select all
dbpExecSQL "AddrBook" "UPDATE Contacts SET DrawNumber = ROW_NUMBER() Where ActiveStatus = True " ""

... to see if it returns contiguous numbers or not.

Otherwise, you can always setup a Loop/EndLoop in NeoBook ... how many total/active records does the Table have ?
User avatar
Gaev
 
Posts: 3735
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Sql update possible

Postby BRobinsonS » Sat Mar 28, 2015 3:46 pm

Currently approx 380 for my Legion, but for other Legions might be more.

I'll try your suggestions about Loop but is there a way so you don't see all the activity (records changing) as it goes through the records?
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sql update possible

Postby Gaev » Sat Mar 28, 2015 5:34 pm

Brian Robinson:

is there a way so you don't see all the activity (records changing) as it goes through the records?

If you are refering to the Grid, how about dbpHideGrid ? ... or HideObject the "Rectangle hosting the Grid" ?

Code: Select all
dbpQuery ""AddrBook"" "Contacts" "ActiveStatus = True"
SetVar "[QueryRecordCount]" "[AddressBook.Contacts.$RecCount]"

dbpFirst "AddrBook" "Contacts"

Loop "1" "[QueryRecordCount]" "[NextDrawNumber]"
   SetVar "[AddressBook.Contacts.DrawNumber]" "[NextDrawNumber]"
   dbpNext "AddrBook" "Contacts"
EndLoop
User avatar
Gaev
 
Posts: 3735
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Sql update possible

Postby BRobinsonS » Sun Mar 29, 2015 11:00 am

I used the Loop to do the job.
I am not using a grid but fields on the screen where the activity would occurs.
At least when the option is picked the user would know something is happening.

BTW, The Row_Number() came up as undefined.

I like the quietness of SQL. I use SQL first to replace non-active members with DrawNumber = 0 THEN the loop....
So SQL just does its job quietly before the Loop does its job.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sql update possible

Postby Gaev » Sun Mar 29, 2015 12:45 pm

Brian Robinson:

I am not using a grid but fields on the screen where the activity would occurs.

Make up a CustomWindow that is large enough to cover the offending fields ... with perhaps a Text box with the message "Please Wait" ... and code its Subroutine like so ...
Code: Select all
:Container1_OnOpen

dbpQuery ""AddrBook"" "Contacts" "ActiveStatus = True"
SetVar "[QueryRecordCount]" "[AddressBook.Contacts.$RecCount]"

dbpFirst "AddrBook" "Contacts"

Loop "1" "[QueryRecordCount]" "[NextDrawNumber]"
   SetVar "[AddressBook.Contacts.DrawNumber]" "[NextDrawNumber]"
   dbpNext "AddrBook" "Contacts"
EndLoop

CloseCustomWindow "Container1"
Return


BTW, The Row_Number() came up as undefined.

Perhaps it is picky ... try (all caps) ROW_NUMBER()
User avatar
Gaev
 
Posts: 3735
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest