Page 1 of 1

dbpVarToField Possible?

PostPosted: Tue Aug 11, 2009 1:00 pm
by datadon
Regarding nbdbpPro database:

I see we have the popular Field to Var action, but do not see a Var to Field action. I want to copy a whole field of information from a field in one sheet to another field in another sheet. I suppose it is possible by adding records and transfering information one record at a time, but was hoping for a whole field at a time transfer. Any possibility?

PostPosted: Wed Aug 12, 2009 9:47 am
by Neosoft Support
I think you can do something like that with SQL's "update" command.

PostPosted: Thu Aug 13, 2009 6:19 am
by datadon
Ok, in reading about UPDATE, it sounds like it will do the job. The problem is that I have not been able to find any code that shows me how to do what I want to do.

First, I am using two spreadsheets (databases). The column names are the same in both sheets (Tables in nbdbPro) I have 3 columns I want to transfer from one table in one spreadsheet to another table in another spreadsheet.

I call one database Elect (table=Task) and the other GanttChart (table=Sheet1$). The columns in both are: Task, StartDate, FinishDate.

I can find examples when the tables are in the same spreadsheet, but not for two different spreadsheets. I have tried a few possible examples, but they all say the syntax is wrong when run. Something does not look right or complete to me. Both spreadsheets are open.


Code: Select all
UPDATE GanttChart.Sheet1$ p, Elect.Task$ pp
SET pp.TASK = p.TASK
SET pp.STARTDATE = p.STARTDATE
SET pp.FINISHDATE = p.FINISHDATE


Another thing that bothers me: Does the dbpExec statement recognize the database ID from Neobook or does it need something different?

Anybody have an idea how I would create the code for UPDATE that can use two different databases and tables? I don't think a where clause is needed as I want everything in the columns.

PostPosted: Thu Aug 13, 2009 10:20 am
by Neosoft Support
I'm certain that this can be done with SQL. Unfortunately, I'm not an SQL expert and I don't have time to research this for you. Let's try an old school solution - loop through each record and copy the field data manually:

Code: Select all
dbpFirst "Test1" "Table1"
dbpFirst "Test1" "Table2"
Loop "1" "[Test1.Table1.$RecCount]" "[Count]"
  SetVar "[Test1.Table2.Field1]" "[Test1.Table1.Field1]"
  dbpNext "Test1" "Table1"
  dbpNext "Test1" "Table2"
EndLoop


Another thing that bothers me: Does the dbpExec statement recognize the database ID from Neobook or does it need something different?


The first parameter is DBPro's database ID. The second parameter should include only valid SQL syntax. For example, you can (and should) use table names, but there would be no reason to use a database ID in an SQL statement.