G'day
I've got a series of yearly data bases that I'm trying to transfer records between. However, I get a 'near "/": syntax error', which I cannot resolve.
My code is below. Any suggestions please?
Regards
Santa
on dbTransferJob
if stackName = empty then
put "Jobs_Calculations" into stackName
end if
if SwitchDataBase is true or tDatabaseID is empty then
databaseConnect
put false into SwitchDataBase
end if
put specialFolderPath("documents") & "/runrevPDQuote"& (label of button "Yearly Stacks" of card 1 of stack "Menu" ) &".sqlite" into SourceDB
put specialFolderPath("documents") & "/runrevPDQuote"& (label of button "Transfer List" ) &".sqlite" into DestinationDB
put "INSERT INTO" && DestinationDB & ".." & stackName && "(" into tSQL
put " jobcard" after tSQL
put ", word3ofcard" after tSQL
put ", word4ofcard" after tSQL
put ", jobdescription" after tSQL
put ", jobdate" after tSQL
put ", squares" after tSQL
put ", meterssquared" after tSQL
put ", cost1" after tSQL
put ", cost2" after tSQL
put ", cost3" after tSQL
put ", cost4" after tSQL
put ", squaresrate" after tSQL
put ", subtotal" after tSQL
put ", grandtotal" after tSQL
put ", hiddengrandtotal" after tSQL
put ", keepgrandtotal" after tSQL
put ", invoicenumber" after tSQL
put ", totalinvoicenumber" after tSQL
put ", special" after tSQL
put ", specialvisible" after tSQL
put ", lockjob" after tSQL
put ", joboftotaljobs" after tSQL
repeat with x = 1 to 4
repeat with xxx = 1 to 18
repeat with xx = 1 to 4
put ", J_"&x&"_"&xx&"_"&xxx after tSQL
end repeat
put ", S_"&x&"_"&xxx after tSQL
end repeat
end repeat
repeat with x = 1 to 4
put ", J_"&x&"_6" after tSQL
put ", Total"&x after tSQL
end repeat
put ") SELECT " after tSQL
put " jobcard" after tSQL
put ", word3ofcard" after tSQL
put ", word4ofcard" after tSQL
put ", jobdescription" after tSQL
put ", jobdate" after tSQL
put ", squares" after tSQL
put ", meterssquared" after tSQL
put ", cost1" after tSQL
put ", cost2" after tSQL
put ", cost3" after tSQL
put ", cost4" after tSQL
put ", squaresrate" after tSQL
put ", subtotal" after tSQL
put ", grandtotal" after tSQL
put ", hiddengrandtotal" after tSQL
put ", keepgrandtotal" after tSQL
put ", invoicenumber" after tSQL
put ", totalinvoicenumber" after tSQL
put ", special" after tSQL
put ", specialvisible" after tSQL
put ", lockjob" after tSQL
put ", joboftotaljobs" after tSQL
repeat with x = 1 to 4
repeat with xxx = 1 to 18
repeat with xx = 1 to 4
put ", J_"&x&"_"&xx&"_"&xxx after tSQL
end repeat
put ", S_"&x&"_"&xxx after tSQL
end repeat
end repeat
repeat with x = 1 to 4
put ", J_"&x&"_6" after tSQL
put ", Total"&x after tSQL
end repeat
put " FROM " & SourceDB &".."&stackName after tSQL
put " WHERE word3ofcard = '"& field "invoice number" &"';"after tSQL
revExecuteSQL tDatabaseID, tSQL
answer the result
end dbTransferJob
Requesting help with SQL error
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
Re: Requesting help with SQL error
Hi Santa,
please post the final SQL string -> tSQL
To put it into the message box, ready to copy, do this:
...
put " WHERE word3ofcard = '"& field "invoice number" &"';"after tSQL
### Add this line:
PUT tSQL
###
revExecuteSQL tDatabaseID, tSQL
answer the result
end dbTransferJob
...
If in doubt it is always a good idea to let you "ANSWER tSQL" and take a look at the resulting string!
Best
Klaus
please post the final SQL string -> tSQL
To put it into the message box, ready to copy, do this:
...
put " WHERE word3ofcard = '"& field "invoice number" &"';"after tSQL
### Add this line:
PUT tSQL
###
revExecuteSQL tDatabaseID, tSQL
answer the result
end dbTransferJob
...
If in doubt it is always a good idea to let you "ANSWER tSQL" and take a look at the resulting string!
Best
Klaus
Re: Requesting help with SQL error
Thanks Klaus.
I tried Answer tSQL but with my limited knowledge could not see anything wrong.
Here's the tSQL string
INSERT INTO /Users/ozsanta/Documents/runrevPDQuote2015.sqlite..Jobs_Calculations ( jobcard, word3ofcard, word4ofcard, jobdescription, jobdate, squares, meterssquared, cost1, cost2, cost3, cost4, squaresrate, subtotal, grandtotal, hiddengrandtotal, keepgrandtotal, invoicenumber, totalinvoicenumber, special, specialvisible, lockjob, joboftotaljobs, J_1_1_1, J_1_2_1, J_1_3_1, J_1_4_1, S_1_1, J_1_1_2, J_1_2_2, J_1_3_2, J_1_4_2, S_1_2, J_1_1_3, J_1_2_3, J_1_3_3, J_1_4_3, S_1_3, J_1_1_4, J_1_2_4, J_1_3_4, J_1_4_4, S_1_4, J_1_1_5, J_1_2_5, J_1_3_5, J_1_4_5, S_1_5, J_1_1_6, J_1_2_6, J_1_3_6, J_1_4_6, S_1_6, J_1_1_7, J_1_2_7, J_1_3_7, J_1_4_7, S_1_7, J_1_1_8, J_1_2_8, J_1_3_8, J_1_4_8, S_1_8, J_1_1_9, J_1_2_9, J_1_3_9, J_1_4_9, S_1_9, J_1_1_10, J_1_2_10, J_1_3_10, J_1_4_10, S_1_10, J_1_1_11, J_1_2_11, J_1_3_11, J_1_4_11, S_1_11, J_1_1_12, J_1_2_12, J_1_3_12, J_1_4_12, S_1_12, J_1_1_13, J_1_2_13, J_1_3_13, J_1_4_13, S_1_13, J_1_1_14, J_1_2_14, J_1_3_14, J_1_4_14, S_1_14, J_1_1_15, J_1_2_15, J_1_3_15, J_1_4_15, S_1_15, J_1_1_16, J_1_2_16, J_1_3_16, J_1_4_16, S_1_16, J_1_1_17, J_1_2_17, J_1_3_17, J_1_4_17, S_1_17, J_1_1_18, J_1_2_18, J_1_3_18, J_1_4_18, S_1_18, J_2_1_1, J_2_2_1, J_2_3_1, J_2_4_1, S_2_1, J_2_1_2, J_2_2_2, J_2_3_2, J_2_4_2, S_2_2, J_2_1_3, J_2_2_3, J_2_3_3, J_2_4_3, S_2_3, J_2_1_4, J_2_2_4, J_2_3_4, J_2_4_4, S_2_4, J_2_1_5, J_2_2_5, J_2_3_5, J_2_4_5, S_2_5, J_2_1_6, J_2_2_6, J_2_3_6, J_2_4_6, S_2_6, J_2_1_7, J_2_2_7, J_2_3_7, J_2_4_7, S_2_7, J_2_1_8, J_2_2_8, J_2_3_8, J_2_4_8, S_2_8, J_2_1_9, J_2_2_9, J_2_3_9, J_2_4_9, S_2_9, J_2_1_10, J_2_2_10, J_2_3_10, J_2_4_10, S_2_10, J_2_1_11, J_2_2_11, J_2_3_11, J_2_4_11, S_2_11, J_2_1_12, J_2_2_12, J_2_3_12, J_2_4_12, S_2_12, J_2_1_13, J_2_2_13, J_2_3_13, J_2_4_13, S_2_13, J_2_1_14, J_2_2_14, J_2_3_14, J_2_4_14, S_2_14, J_2_1_15, J_2_2_15, J_2_3_15, J_2_4_15, S_2_15, J_2_1_16, J_2_2_16, J_2_3_16, J_2_4_16, S_2_16, J_2_1_17, J_2_2_17, J_2_3_17, J_2_4_17, S_2_17, J_2_1_18, J_2_2_18, J_2_3_18, J_2_4_18, S_2_18, J_3_1_1, J_3_2_1, J_3_3_1, J_3_4_1, S_3_1, J_3_1_2, J_3_2_2, J_3_3_2, J_3_4_2, S_3_2, J_3_1_3, J_3_2_3, J_3_3_3, J_3_4_3, S_3_3, J_3_1_4, J_3_2_4, J_3_3_4, J_3_4_4, S_3_4, J_3_1_5, J_3_2_5, J_3_3_5, J_3_4_5, S_3_5, J_3_1_6, J_3_2_6, J_3_3_6, J_3_4_6, S_3_6, J_3_1_7, J_3_2_7, J_3_3_7, J_3_4_7, S_3_7, J_3_1_8, J_3_2_8, J_3_3_8, J_3_4_8, S_3_8, J_3_1_9, J_3_2_9, J_3_3_9, J_3_4_9, S_3_9, J_3_1_10, J_3_2_10, J_3_3_10, J_3_4_10, S_3_10, J_3_1_11, J_3_2_11, J_3_3_11, J_3_4_11, S_3_11, J_3_1_12, J_3_2_12, J_3_3_12, J_3_4_12, S_3_12, J_3_1_13, J_3_2_13, J_3_3_13, J_3_4_13, S_3_13, J_3_1_14, J_3_2_14, J_3_3_14, J_3_4_14, S_3_14, J_3_1_15, J_3_2_15, J_3_3_15, J_3_4_15, S_3_15, J_3_1_16, J_3_2_16, J_3_3_16, J_3_4_16, S_3_16, J_3_1_17, J_3_2_17, J_3_3_17, J_3_4_17, S_3_17, J_3_1_18, J_3_2_18, J_3_3_18, J_3_4_18, S_3_18, J_4_1_1, J_4_2_1, J_4_3_1, J_4_4_1, S_4_1, J_4_1_2, J_4_2_2, J_4_3_2, J_4_4_2, S_4_2, J_4_1_3, J_4_2_3, J_4_3_3, J_4_4_3, S_4_3, J_4_1_4, J_4_2_4, J_4_3_4, J_4_4_4, S_4_4, J_4_1_5, J_4_2_5, J_4_3_5, J_4_4_5, S_4_5, J_4_1_6, J_4_2_6, J_4_3_6, J_4_4_6, S_4_6, J_4_1_7, J_4_2_7, J_4_3_7, J_4_4_7, S_4_7, J_4_1_8, J_4_2_8, J_4_3_8, J_4_4_8, S_4_8, J_4_1_9, J_4_2_9, J_4_3_9, J_4_4_9, S_4_9, J_4_1_10, J_4_2_10, J_4_3_10, J_4_4_10, S_4_10, J_4_1_11, J_4_2_11, J_4_3_11, J_4_4_11, S_4_11, J_4_1_12, J_4_2_12, J_4_3_12, J_4_4_12, S_4_12, J_4_1_13, J_4_2_13, J_4_3_13, J_4_4_13, S_4_13, J_4_1_14, J_4_2_14, J_4_3_14, J_4_4_14, S_4_14, J_4_1_15, J_4_2_15, J_4_3_15, J_4_4_15, S_4_15, J_4_1_16, J_4_2_16, J_4_3_16, J_4_4_16, S_4_16, J_4_1_17, J_4_2_17, J_4_3_17, J_4_4_17, S_4_17, J_4_1_18, J_4_2_18, J_4_3_18, J_4_4_18, S_4_18, J_1_6, Total1, J_2_6, Total2, J_3_6, Total3, J_4_6, Total4) SELECT jobcard, word3ofcard, word4ofcard, jobdescription, jobdate, squares, meterssquared, cost1, cost2, cost3, cost4, squaresrate, subtotal, grandtotal, hiddengrandtotal, keepgrandtotal, invoicenumber, totalinvoicenumber, special, specialvisible, lockjob, joboftotaljobs, J_1_1_1, J_1_2_1, J_1_3_1, J_1_4_1, S_1_1, J_1_1_2, J_1_2_2, J_1_3_2, J_1_4_2, S_1_2, J_1_1_3, J_1_2_3, J_1_3_3, J_1_4_3, S_1_3, J_1_1_4, J_1_2_4, J_1_3_4, J_1_4_4, S_1_4, J_1_1_5, J_1_2_5, J_1_3_5, J_1_4_5, S_1_5, J_1_1_6, J_1_2_6, J_1_3_6, J_1_4_6, S_1_6, J_1_1_7, J_1_2_7, J_1_3_7, J_1_4_7, S_1_7, J_1_1_8, J_1_2_8, J_1_3_8, J_1_4_8, S_1_8, J_1_1_9, J_1_2_9, J_1_3_9, J_1_4_9, S_1_9, J_1_1_10, J_1_2_10, J_1_3_10, J_1_4_10, S_1_10, J_1_1_11, J_1_2_11, J_1_3_11, J_1_4_11, S_1_11, J_1_1_12, J_1_2_12, J_1_3_12, J_1_4_12, S_1_12, J_1_1_13, J_1_2_13, J_1_3_13, J_1_4_13, S_1_13, J_1_1_14, J_1_2_14, J_1_3_14, J_1_4_14, S_1_14, J_1_1_15, J_1_2_15, J_1_3_15, J_1_4_15, S_1_15, J_1_1_16, J_1_2_16, J_1_3_16, J_1_4_16, S_1_16, J_1_1_17, J_1_2_17, J_1_3_17, J_1_4_17, S_1_17, J_1_1_18, J_1_2_18, J_1_3_18, J_1_4_18, S_1_18, J_2_1_1, J_2_2_1, J_2_3_1, J_2_4_1, S_2_1, J_2_1_2, J_2_2_2, J_2_3_2, J_2_4_2, S_2_2, J_2_1_3, J_2_2_3, J_2_3_3, J_2_4_3, S_2_3, J_2_1_4, J_2_2_4, J_2_3_4, J_2_4_4, S_2_4, J_2_1_5, J_2_2_5, J_2_3_5, J_2_4_5, S_2_5, J_2_1_6, J_2_2_6, J_2_3_6, J_2_4_6, S_2_6, J_2_1_7, J_2_2_7, J_2_3_7, J_2_4_7, S_2_7, J_2_1_8, J_2_2_8, J_2_3_8, J_2_4_8, S_2_8, J_2_1_9, J_2_2_9, J_2_3_9, J_2_4_9, S_2_9, J_2_1_10, J_2_2_10, J_2_3_10, J_2_4_10, S_2_10, J_2_1_11, J_2_2_11, J_2_3_11, J_2_4_11, S_2_11, J_2_1_12, J_2_2_12, J_2_3_12, J_2_4_12, S_2_12, J_2_1_13, J_2_2_13, J_2_3_13, J_2_4_13, S_2_13, J_2_1_14, J_2_2_14, J_2_3_14, J_2_4_14, S_2_14, J_2_1_15, J_2_2_15, J_2_3_15, J_2_4_15, S_2_15, J_2_1_16, J_2_2_16, J_2_3_16, J_2_4_16, S_2_16, J_2_1_17, J_2_2_17, J_2_3_17, J_2_4_17, S_2_17, J_2_1_18, J_2_2_18, J_2_3_18, J_2_4_18, S_2_18, J_3_1_1, J_3_2_1, J_3_3_1, J_3_4_1, S_3_1, J_3_1_2, J_3_2_2, J_3_3_2, J_3_4_2, S_3_2, J_3_1_3, J_3_2_3, J_3_3_3, J_3_4_3, S_3_3, J_3_1_4, J_3_2_4, J_3_3_4, J_3_4_4, S_3_4, J_3_1_5, J_3_2_5, J_3_3_5, J_3_4_5, S_3_5, J_3_1_6, J_3_2_6, J_3_3_6, J_3_4_6, S_3_6, J_3_1_7, J_3_2_7, J_3_3_7, J_3_4_7, S_3_7, J_3_1_8, J_3_2_8, J_3_3_8, J_3_4_8, S_3_8, J_3_1_9, J_3_2_9, J_3_3_9, J_3_4_9, S_3_9, J_3_1_10, J_3_2_10, J_3_3_10, J_3_4_10, S_3_10, J_3_1_11, J_3_2_11, J_3_3_11, J_3_4_11, S_3_11, J_3_1_12, J_3_2_12, J_3_3_12, J_3_4_12, S_3_12, J_3_1_13, J_3_2_13, J_3_3_13, J_3_4_13, S_3_13, J_3_1_14, J_3_2_14, J_3_3_14, J_3_4_14, S_3_14, J_3_1_15, J_3_2_15, J_3_3_15, J_3_4_15, S_3_15, J_3_1_16, J_3_2_16, J_3_3_16, J_3_4_16, S_3_16, J_3_1_17, J_3_2_17, J_3_3_17, J_3_4_17, S_3_17, J_3_1_18, J_3_2_18, J_3_3_18, J_3_4_18, S_3_18, J_4_1_1, J_4_2_1, J_4_3_1, J_4_4_1, S_4_1, J_4_1_2, J_4_2_2, J_4_3_2, J_4_4_2, S_4_2, J_4_1_3, J_4_2_3, J_4_3_3, J_4_4_3, S_4_3, J_4_1_4, J_4_2_4, J_4_3_4, J_4_4_4, S_4_4, J_4_1_5, J_4_2_5, J_4_3_5, J_4_4_5, S_4_5, J_4_1_6, J_4_2_6, J_4_3_6, J_4_4_6, S_4_6, J_4_1_7, J_4_2_7, J_4_3_7, J_4_4_7, S_4_7, J_4_1_8, J_4_2_8, J_4_3_8, J_4_4_8, S_4_8, J_4_1_9, J_4_2_9, J_4_3_9, J_4_4_9, S_4_9, J_4_1_10, J_4_2_10, J_4_3_10, J_4_4_10, S_4_10, J_4_1_11, J_4_2_11, J_4_3_11, J_4_4_11, S_4_11, J_4_1_12, J_4_2_12, J_4_3_12, J_4_4_12, S_4_12, J_4_1_13, J_4_2_13, J_4_3_13, J_4_4_13, S_4_13, J_4_1_14, J_4_2_14, J_4_3_14, J_4_4_14, S_4_14, J_4_1_15, J_4_2_15, J_4_3_15, J_4_4_15, S_4_15, J_4_1_16, J_4_2_16, J_4_3_16, J_4_4_16, S_4_16, J_4_1_17, J_4_2_17, J_4_3_17, J_4_4_17, S_4_17, J_4_1_18, J_4_2_18, J_4_3_18, J_4_4_18, S_4_18, J_1_6, Total1, J_2_6, Total2, J_3_6, Total3, J_4_6, Total4 FROM /Users/ozsanta/Documents/runrevPDQuote2014.sqlite..Jobs_Calculations WHERE word3ofcard = '270';
I tried Answer tSQL but with my limited knowledge could not see anything wrong.
Here's the tSQL string
INSERT INTO /Users/ozsanta/Documents/runrevPDQuote2015.sqlite..Jobs_Calculations ( jobcard, word3ofcard, word4ofcard, jobdescription, jobdate, squares, meterssquared, cost1, cost2, cost3, cost4, squaresrate, subtotal, grandtotal, hiddengrandtotal, keepgrandtotal, invoicenumber, totalinvoicenumber, special, specialvisible, lockjob, joboftotaljobs, J_1_1_1, J_1_2_1, J_1_3_1, J_1_4_1, S_1_1, J_1_1_2, J_1_2_2, J_1_3_2, J_1_4_2, S_1_2, J_1_1_3, J_1_2_3, J_1_3_3, J_1_4_3, S_1_3, J_1_1_4, J_1_2_4, J_1_3_4, J_1_4_4, S_1_4, J_1_1_5, J_1_2_5, J_1_3_5, J_1_4_5, S_1_5, J_1_1_6, J_1_2_6, J_1_3_6, J_1_4_6, S_1_6, J_1_1_7, J_1_2_7, J_1_3_7, J_1_4_7, S_1_7, J_1_1_8, J_1_2_8, J_1_3_8, J_1_4_8, S_1_8, J_1_1_9, J_1_2_9, J_1_3_9, J_1_4_9, S_1_9, J_1_1_10, J_1_2_10, J_1_3_10, J_1_4_10, S_1_10, J_1_1_11, J_1_2_11, J_1_3_11, J_1_4_11, S_1_11, J_1_1_12, J_1_2_12, J_1_3_12, J_1_4_12, S_1_12, J_1_1_13, J_1_2_13, J_1_3_13, J_1_4_13, S_1_13, J_1_1_14, J_1_2_14, J_1_3_14, J_1_4_14, S_1_14, J_1_1_15, J_1_2_15, J_1_3_15, J_1_4_15, S_1_15, J_1_1_16, J_1_2_16, J_1_3_16, J_1_4_16, S_1_16, J_1_1_17, J_1_2_17, J_1_3_17, J_1_4_17, S_1_17, J_1_1_18, J_1_2_18, J_1_3_18, J_1_4_18, S_1_18, J_2_1_1, J_2_2_1, J_2_3_1, J_2_4_1, S_2_1, J_2_1_2, J_2_2_2, J_2_3_2, J_2_4_2, S_2_2, J_2_1_3, J_2_2_3, J_2_3_3, J_2_4_3, S_2_3, J_2_1_4, J_2_2_4, J_2_3_4, J_2_4_4, S_2_4, J_2_1_5, J_2_2_5, J_2_3_5, J_2_4_5, S_2_5, J_2_1_6, J_2_2_6, J_2_3_6, J_2_4_6, S_2_6, J_2_1_7, J_2_2_7, J_2_3_7, J_2_4_7, S_2_7, J_2_1_8, J_2_2_8, J_2_3_8, J_2_4_8, S_2_8, J_2_1_9, J_2_2_9, J_2_3_9, J_2_4_9, S_2_9, J_2_1_10, J_2_2_10, J_2_3_10, J_2_4_10, S_2_10, J_2_1_11, J_2_2_11, J_2_3_11, J_2_4_11, S_2_11, J_2_1_12, J_2_2_12, J_2_3_12, J_2_4_12, S_2_12, J_2_1_13, J_2_2_13, J_2_3_13, J_2_4_13, S_2_13, J_2_1_14, J_2_2_14, J_2_3_14, J_2_4_14, S_2_14, J_2_1_15, J_2_2_15, J_2_3_15, J_2_4_15, S_2_15, J_2_1_16, J_2_2_16, J_2_3_16, J_2_4_16, S_2_16, J_2_1_17, J_2_2_17, J_2_3_17, J_2_4_17, S_2_17, J_2_1_18, J_2_2_18, J_2_3_18, J_2_4_18, S_2_18, J_3_1_1, J_3_2_1, J_3_3_1, J_3_4_1, S_3_1, J_3_1_2, J_3_2_2, J_3_3_2, J_3_4_2, S_3_2, J_3_1_3, J_3_2_3, J_3_3_3, J_3_4_3, S_3_3, J_3_1_4, J_3_2_4, J_3_3_4, J_3_4_4, S_3_4, J_3_1_5, J_3_2_5, J_3_3_5, J_3_4_5, S_3_5, J_3_1_6, J_3_2_6, J_3_3_6, J_3_4_6, S_3_6, J_3_1_7, J_3_2_7, J_3_3_7, J_3_4_7, S_3_7, J_3_1_8, J_3_2_8, J_3_3_8, J_3_4_8, S_3_8, J_3_1_9, J_3_2_9, J_3_3_9, J_3_4_9, S_3_9, J_3_1_10, J_3_2_10, J_3_3_10, J_3_4_10, S_3_10, J_3_1_11, J_3_2_11, J_3_3_11, J_3_4_11, S_3_11, J_3_1_12, J_3_2_12, J_3_3_12, J_3_4_12, S_3_12, J_3_1_13, J_3_2_13, J_3_3_13, J_3_4_13, S_3_13, J_3_1_14, J_3_2_14, J_3_3_14, J_3_4_14, S_3_14, J_3_1_15, J_3_2_15, J_3_3_15, J_3_4_15, S_3_15, J_3_1_16, J_3_2_16, J_3_3_16, J_3_4_16, S_3_16, J_3_1_17, J_3_2_17, J_3_3_17, J_3_4_17, S_3_17, J_3_1_18, J_3_2_18, J_3_3_18, J_3_4_18, S_3_18, J_4_1_1, J_4_2_1, J_4_3_1, J_4_4_1, S_4_1, J_4_1_2, J_4_2_2, J_4_3_2, J_4_4_2, S_4_2, J_4_1_3, J_4_2_3, J_4_3_3, J_4_4_3, S_4_3, J_4_1_4, J_4_2_4, J_4_3_4, J_4_4_4, S_4_4, J_4_1_5, J_4_2_5, J_4_3_5, J_4_4_5, S_4_5, J_4_1_6, J_4_2_6, J_4_3_6, J_4_4_6, S_4_6, J_4_1_7, J_4_2_7, J_4_3_7, J_4_4_7, S_4_7, J_4_1_8, J_4_2_8, J_4_3_8, J_4_4_8, S_4_8, J_4_1_9, J_4_2_9, J_4_3_9, J_4_4_9, S_4_9, J_4_1_10, J_4_2_10, J_4_3_10, J_4_4_10, S_4_10, J_4_1_11, J_4_2_11, J_4_3_11, J_4_4_11, S_4_11, J_4_1_12, J_4_2_12, J_4_3_12, J_4_4_12, S_4_12, J_4_1_13, J_4_2_13, J_4_3_13, J_4_4_13, S_4_13, J_4_1_14, J_4_2_14, J_4_3_14, J_4_4_14, S_4_14, J_4_1_15, J_4_2_15, J_4_3_15, J_4_4_15, S_4_15, J_4_1_16, J_4_2_16, J_4_3_16, J_4_4_16, S_4_16, J_4_1_17, J_4_2_17, J_4_3_17, J_4_4_17, S_4_17, J_4_1_18, J_4_2_18, J_4_3_18, J_4_4_18, S_4_18, J_1_6, Total1, J_2_6, Total2, J_3_6, Total3, J_4_6, Total4) SELECT jobcard, word3ofcard, word4ofcard, jobdescription, jobdate, squares, meterssquared, cost1, cost2, cost3, cost4, squaresrate, subtotal, grandtotal, hiddengrandtotal, keepgrandtotal, invoicenumber, totalinvoicenumber, special, specialvisible, lockjob, joboftotaljobs, J_1_1_1, J_1_2_1, J_1_3_1, J_1_4_1, S_1_1, J_1_1_2, J_1_2_2, J_1_3_2, J_1_4_2, S_1_2, J_1_1_3, J_1_2_3, J_1_3_3, J_1_4_3, S_1_3, J_1_1_4, J_1_2_4, J_1_3_4, J_1_4_4, S_1_4, J_1_1_5, J_1_2_5, J_1_3_5, J_1_4_5, S_1_5, J_1_1_6, J_1_2_6, J_1_3_6, J_1_4_6, S_1_6, J_1_1_7, J_1_2_7, J_1_3_7, J_1_4_7, S_1_7, J_1_1_8, J_1_2_8, J_1_3_8, J_1_4_8, S_1_8, J_1_1_9, J_1_2_9, J_1_3_9, J_1_4_9, S_1_9, J_1_1_10, J_1_2_10, J_1_3_10, J_1_4_10, S_1_10, J_1_1_11, J_1_2_11, J_1_3_11, J_1_4_11, S_1_11, J_1_1_12, J_1_2_12, J_1_3_12, J_1_4_12, S_1_12, J_1_1_13, J_1_2_13, J_1_3_13, J_1_4_13, S_1_13, J_1_1_14, J_1_2_14, J_1_3_14, J_1_4_14, S_1_14, J_1_1_15, J_1_2_15, J_1_3_15, J_1_4_15, S_1_15, J_1_1_16, J_1_2_16, J_1_3_16, J_1_4_16, S_1_16, J_1_1_17, J_1_2_17, J_1_3_17, J_1_4_17, S_1_17, J_1_1_18, J_1_2_18, J_1_3_18, J_1_4_18, S_1_18, J_2_1_1, J_2_2_1, J_2_3_1, J_2_4_1, S_2_1, J_2_1_2, J_2_2_2, J_2_3_2, J_2_4_2, S_2_2, J_2_1_3, J_2_2_3, J_2_3_3, J_2_4_3, S_2_3, J_2_1_4, J_2_2_4, J_2_3_4, J_2_4_4, S_2_4, J_2_1_5, J_2_2_5, J_2_3_5, J_2_4_5, S_2_5, J_2_1_6, J_2_2_6, J_2_3_6, J_2_4_6, S_2_6, J_2_1_7, J_2_2_7, J_2_3_7, J_2_4_7, S_2_7, J_2_1_8, J_2_2_8, J_2_3_8, J_2_4_8, S_2_8, J_2_1_9, J_2_2_9, J_2_3_9, J_2_4_9, S_2_9, J_2_1_10, J_2_2_10, J_2_3_10, J_2_4_10, S_2_10, J_2_1_11, J_2_2_11, J_2_3_11, J_2_4_11, S_2_11, J_2_1_12, J_2_2_12, J_2_3_12, J_2_4_12, S_2_12, J_2_1_13, J_2_2_13, J_2_3_13, J_2_4_13, S_2_13, J_2_1_14, J_2_2_14, J_2_3_14, J_2_4_14, S_2_14, J_2_1_15, J_2_2_15, J_2_3_15, J_2_4_15, S_2_15, J_2_1_16, J_2_2_16, J_2_3_16, J_2_4_16, S_2_16, J_2_1_17, J_2_2_17, J_2_3_17, J_2_4_17, S_2_17, J_2_1_18, J_2_2_18, J_2_3_18, J_2_4_18, S_2_18, J_3_1_1, J_3_2_1, J_3_3_1, J_3_4_1, S_3_1, J_3_1_2, J_3_2_2, J_3_3_2, J_3_4_2, S_3_2, J_3_1_3, J_3_2_3, J_3_3_3, J_3_4_3, S_3_3, J_3_1_4, J_3_2_4, J_3_3_4, J_3_4_4, S_3_4, J_3_1_5, J_3_2_5, J_3_3_5, J_3_4_5, S_3_5, J_3_1_6, J_3_2_6, J_3_3_6, J_3_4_6, S_3_6, J_3_1_7, J_3_2_7, J_3_3_7, J_3_4_7, S_3_7, J_3_1_8, J_3_2_8, J_3_3_8, J_3_4_8, S_3_8, J_3_1_9, J_3_2_9, J_3_3_9, J_3_4_9, S_3_9, J_3_1_10, J_3_2_10, J_3_3_10, J_3_4_10, S_3_10, J_3_1_11, J_3_2_11, J_3_3_11, J_3_4_11, S_3_11, J_3_1_12, J_3_2_12, J_3_3_12, J_3_4_12, S_3_12, J_3_1_13, J_3_2_13, J_3_3_13, J_3_4_13, S_3_13, J_3_1_14, J_3_2_14, J_3_3_14, J_3_4_14, S_3_14, J_3_1_15, J_3_2_15, J_3_3_15, J_3_4_15, S_3_15, J_3_1_16, J_3_2_16, J_3_3_16, J_3_4_16, S_3_16, J_3_1_17, J_3_2_17, J_3_3_17, J_3_4_17, S_3_17, J_3_1_18, J_3_2_18, J_3_3_18, J_3_4_18, S_3_18, J_4_1_1, J_4_2_1, J_4_3_1, J_4_4_1, S_4_1, J_4_1_2, J_4_2_2, J_4_3_2, J_4_4_2, S_4_2, J_4_1_3, J_4_2_3, J_4_3_3, J_4_4_3, S_4_3, J_4_1_4, J_4_2_4, J_4_3_4, J_4_4_4, S_4_4, J_4_1_5, J_4_2_5, J_4_3_5, J_4_4_5, S_4_5, J_4_1_6, J_4_2_6, J_4_3_6, J_4_4_6, S_4_6, J_4_1_7, J_4_2_7, J_4_3_7, J_4_4_7, S_4_7, J_4_1_8, J_4_2_8, J_4_3_8, J_4_4_8, S_4_8, J_4_1_9, J_4_2_9, J_4_3_9, J_4_4_9, S_4_9, J_4_1_10, J_4_2_10, J_4_3_10, J_4_4_10, S_4_10, J_4_1_11, J_4_2_11, J_4_3_11, J_4_4_11, S_4_11, J_4_1_12, J_4_2_12, J_4_3_12, J_4_4_12, S_4_12, J_4_1_13, J_4_2_13, J_4_3_13, J_4_4_13, S_4_13, J_4_1_14, J_4_2_14, J_4_3_14, J_4_4_14, S_4_14, J_4_1_15, J_4_2_15, J_4_3_15, J_4_4_15, S_4_15, J_4_1_16, J_4_2_16, J_4_3_16, J_4_4_16, S_4_16, J_4_1_17, J_4_2_17, J_4_3_17, J_4_4_17, S_4_17, J_4_1_18, J_4_2_18, J_4_3_18, J_4_4_18, S_4_18, J_1_6, Total1, J_2_6, Total2, J_3_6, Total3, J_4_6, Total4 FROM /Users/ozsanta/Documents/runrevPDQuote2014.sqlite..Jobs_Calculations WHERE word3ofcard = '270';
Re: Requesting help with SQL error
Hi Santa,
AHA!
Remove the PATHNAMES from the SQL at the beginning and the end and it should work out fine!
BAD:
INSERT INTO /Users/ozsanta/Documents/runrevPDQuote2015.sqlite..Jobs_Calculations...
...
Total4 FROM /Users/ozsanta/Documents/runrevPDQuote2014.sqlite..Jobs_Calculations WHERE word3ofcard = '270';
Good:
INSERT INTO Jobs_Calculations...
...
Total4 FROM Jobs_Calculations WHERE word3ofcard = '270';
SQL does not like this and just need the name of the table!
Best
Klaus
AHA!
Remove the PATHNAMES from the SQL at the beginning and the end and it should work out fine!
BAD:
INSERT INTO /Users/ozsanta/Documents/runrevPDQuote2015.sqlite..Jobs_Calculations...
...
Total4 FROM /Users/ozsanta/Documents/runrevPDQuote2014.sqlite..Jobs_Calculations WHERE word3ofcard = '270';
Good:
INSERT INTO Jobs_Calculations...
...
Total4 FROM Jobs_Calculations WHERE word3ofcard = '270';
SQL does not like this and just need the name of the table!
Best
Klaus
Re: Requesting help with SQL error
G'day Klaus
That partly worked, but it added the records into the same data base. I need it to point to a different data base, that's why I added the paths.
Something like ...
put "INSERT INTO" && "runrevPDQuote" & label of button "Transfer List" &".sqlite" &" "& stackName && "(" into tSQL
Which doesn't work naturally.
Regards
Santa
That partly worked, but it added the records into the same data base. I need it to point to a different data base, that's why I added the paths.
Something like ...
put "INSERT INTO" && "runrevPDQuote" & label of button "Transfer List" &".sqlite" &" "& stackName && "(" into tSQL
Which doesn't work naturally.
Regards
Santa
Re: Requesting help with SQL error
To elaborate.
I have a series of data bases, based on years. runRevPDQuote2013, runRevPDQuote2014, runRevPDQuote2015, etc
I need to copy records from any one of these that's currently in use, and copy those records to any of the other data bases.
They all have the same tables, and column headers, but I only need to copy records from the 'Jobs_Calculations' table.
How might I point from one data base to another please?
Regards
Santa
I have a series of data bases, based on years. runRevPDQuote2013, runRevPDQuote2014, runRevPDQuote2015, etc
I need to copy records from any one of these that's currently in use, and copy those records to any of the other data bases.
They all have the same tables, and column headers, but I only need to copy records from the 'Jobs_Calculations' table.
How might I point from one data base to another please?
Regards
Santa
Re: Requesting help with SQL error
Hi Snata,
you need to open ALL databases you want to access and use their ID*** to point LC to the correct database!
That's the way LC works: ONE ID for ONE datbase FILE!
*** What you get with revopendatabase...
Best
Klaus
you need to open ALL databases you want to access and use their ID*** to point LC to the correct database!
That's the way LC works: ONE ID for ONE datbase FILE!
*** What you get with revopendatabase...
Best
Klaus
Re: Requesting help with SQL error
Thanks Klaus, but darn, this is difficult!
I cannot get the Id of the database to work. I get a syntax error near 87.
Heres the tSQL code
INSERT INTO 87 Jobs_Calculations ( jobcard, word3ofcard, word4ofcard, jobdescription, jobdate, squares, meterssquared, cost1, cost2, cost3, cost4, squaresrate, subtotal, grandtotal, hiddengrandtotal, keepgrandtotal, invoicenumber, totalinvoicenumber, special, specialvisible, lockjob, joboftotaljobs, J_1_1_1, J_1_2_1, J_1_3_1, J_1_4_1, S_1_1, J_1_1_2, J_1_2_2, J_1_3_2, J_1_4_2, S_1_2, J_1_1_3, J_1_2_3, J_1_3_3, J_1_4_3, S_1_3, J_1_1_4, J_1_2_4, J_1_3_4, J_1_4_4, S_1_4, J_1_1_5, J_1_2_5, J_1_3_5, J_1_4_5, S_1_5, J_1_1_6, J_1_2_6, J_1_3_6, J_1_4_6, S_1_6, J_1_1_7, J_1_2_7, J_1_3_7, J_1_4_7, S_1_7, J_1_1_8, J_1_2_8, J_1_3_8, J_1_4_8, S_1_8, J_1_1_9, J_1_2_9, J_1_3_9, J_1_4_9, S_1_9, J_1_1_10, J_1_2_10, J_1_3_10, J_1_4_10, S_1_10, J_1_1_11, J_1_2_11, J_1_3_11, J_1_4_11, S_1_11, J_1_1_12, J_1_2_12, J_1_3_12, J_1_4_12, S_1_12, J_1_1_13, J_1_2_13, J_1_3_13, J_1_4_13, S_1_13, J_1_1_14, J_1_2_14, J_1_3_14, J_1_4_14, S_1_14, J_1_1_15, J_1_2_15, J_1_3_15, J_1_4_15, S_1_15, J_1_1_16, J_1_2_16, J_1_3_16, J_1_4_16, S_1_16, J_1_1_17, J_1_2_17, J_1_3_17, J_1_4_17, S_1_17, J_1_1_18, J_1_2_18, J_1_3_18, J_1_4_18, S_1_18, J_2_1_1, J_2_2_1, J_2_3_1, J_2_4_1, S_2_1, J_2_1_2, J_2_2_2, J_2_3_2, J_2_4_2, S_2_2, J_2_1_3, J_2_2_3, J_2_3_3, J_2_4_3, S_2_3, J_2_1_4, J_2_2_4, J_2_3_4, J_2_4_4, S_2_4, J_2_1_5, J_2_2_5, J_2_3_5, J_2_4_5, S_2_5, J_2_1_6, J_2_2_6, J_2_3_6, J_2_4_6, S_2_6, J_2_1_7, J_2_2_7, J_2_3_7, J_2_4_7, S_2_7, J_2_1_8, J_2_2_8, J_2_3_8, J_2_4_8, S_2_8, J_2_1_9, J_2_2_9, J_2_3_9, J_2_4_9, S_2_9, J_2_1_10, J_2_2_10, J_2_3_10, J_2_4_10, S_2_10, J_2_1_11, J_2_2_11, J_2_3_11, J_2_4_11, S_2_11, J_2_1_12, J_2_2_12, J_2_3_12, J_2_4_12, S_2_12, J_2_1_13, J_2_2_13, J_2_3_13, J_2_4_13, S_2_13, J_2_1_14, J_2_2_14, J_2_3_14, J_2_4_14, S_2_14, J_2_1_15, J_2_2_15, J_2_3_15, J_2_4_15, S_2_15, J_2_1_16, J_2_2_16, J_2_3_16, J_2_4_16, S_2_16, J_2_1_17, J_2_2_17, J_2_3_17, J_2_4_17, S_2_17, J_2_1_18, J_2_2_18, J_2_3_18, J_2_4_18, S_2_18, J_3_1_1, J_3_2_1, J_3_3_1, J_3_4_1, S_3_1, J_3_1_2, J_3_2_2, J_3_3_2, J_3_4_2, S_3_2, J_3_1_3, J_3_2_3, J_3_3_3, J_3_4_3, S_3_3, J_3_1_4, J_3_2_4, J_3_3_4, J_3_4_4, S_3_4, J_3_1_5, J_3_2_5, J_3_3_5, J_3_4_5, S_3_5, J_3_1_6, J_3_2_6, J_3_3_6, J_3_4_6, S_3_6, J_3_1_7, J_3_2_7, J_3_3_7, J_3_4_7, S_3_7, J_3_1_8, J_3_2_8, J_3_3_8, J_3_4_8, S_3_8, J_3_1_9, J_3_2_9, J_3_3_9, J_3_4_9, S_3_9, J_3_1_10, J_3_2_10, J_3_3_10, J_3_4_10, S_3_10, J_3_1_11, J_3_2_11, J_3_3_11, J_3_4_11, S_3_11, J_3_1_12, J_3_2_12, J_3_3_12, J_3_4_12, S_3_12, J_3_1_13, J_3_2_13, J_3_3_13, J_3_4_13, S_3_13, J_3_1_14, J_3_2_14, J_3_3_14, J_3_4_14, S_3_14, J_3_1_15, J_3_2_15, J_3_3_15, J_3_4_15, S_3_15, J_3_1_16, J_3_2_16, J_3_3_16, J_3_4_16, S_3_16, J_3_1_17, J_3_2_17, J_3_3_17, J_3_4_17, S_3_17, J_3_1_18, J_3_2_18, J_3_3_18, J_3_4_18, S_3_18, J_4_1_1, J_4_2_1, J_4_3_1, J_4_4_1, S_4_1, J_4_1_2, J_4_2_2, J_4_3_2, J_4_4_2, S_4_2, J_4_1_3, J_4_2_3, J_4_3_3, J_4_4_3, S_4_3, J_4_1_4, J_4_2_4, J_4_3_4, J_4_4_4, S_4_4, J_4_1_5, J_4_2_5, J_4_3_5, J_4_4_5, S_4_5, J_4_1_6, J_4_2_6, J_4_3_6, J_4_4_6, S_4_6, J_4_1_7, J_4_2_7, J_4_3_7, J_4_4_7, S_4_7, J_4_1_8, J_4_2_8, J_4_3_8, J_4_4_8, S_4_8, J_4_1_9, J_4_2_9, J_4_3_9, J_4_4_9, S_4_9, J_4_1_10, J_4_2_10, J_4_3_10, J_4_4_10, S_4_10, J_4_1_11, J_4_2_11, J_4_3_11, J_4_4_11, S_4_11, J_4_1_12, J_4_2_12, J_4_3_12, J_4_4_12, S_4_12, J_4_1_13, J_4_2_13, J_4_3_13, J_4_4_13, S_4_13, J_4_1_14, J_4_2_14, J_4_3_14, J_4_4_14, S_4_14, J_4_1_15, J_4_2_15, J_4_3_15, J_4_4_15, S_4_15, J_4_1_16, J_4_2_16, J_4_3_16, J_4_4_16, S_4_16, J_4_1_17, J_4_2_17, J_4_3_17, J_4_4_17, S_4_17, J_4_1_18, J_4_2_18, J_4_3_18, J_4_4_18, S_4_18, J_1_6, Total1, J_2_6, Total2, J_3_6, Total3, J_4_6, Total4) SELECT jobcard, word3ofcard, word4ofcard, jobdescription, jobdate, squares, meterssquared, cost1, cost2, cost3, cost4, squaresrate, subtotal, grandtotal, hiddengrandtotal, keepgrandtotal, invoicenumber, totalinvoicenumber, special, specialvisible, lockjob, joboftotaljobs, J_1_1_1, J_1_2_1, J_1_3_1, J_1_4_1, S_1_1, J_1_1_2, J_1_2_2, J_1_3_2, J_1_4_2, S_1_2, J_1_1_3, J_1_2_3, J_1_3_3, J_1_4_3, S_1_3, J_1_1_4, J_1_2_4, J_1_3_4, J_1_4_4, S_1_4, J_1_1_5, J_1_2_5, J_1_3_5, J_1_4_5, S_1_5, J_1_1_6, J_1_2_6, J_1_3_6, J_1_4_6, S_1_6, J_1_1_7, J_1_2_7, J_1_3_7, J_1_4_7, S_1_7, J_1_1_8, J_1_2_8, J_1_3_8, J_1_4_8, S_1_8, J_1_1_9, J_1_2_9, J_1_3_9, J_1_4_9, S_1_9, J_1_1_10, J_1_2_10, J_1_3_10, J_1_4_10, S_1_10, J_1_1_11, J_1_2_11, J_1_3_11, J_1_4_11, S_1_11, J_1_1_12, J_1_2_12, J_1_3_12, J_1_4_12, S_1_12, J_1_1_13, J_1_2_13, J_1_3_13, J_1_4_13, S_1_13, J_1_1_14, J_1_2_14, J_1_3_14, J_1_4_14, S_1_14, J_1_1_15, J_1_2_15, J_1_3_15, J_1_4_15, S_1_15, J_1_1_16, J_1_2_16, J_1_3_16, J_1_4_16, S_1_16, J_1_1_17, J_1_2_17, J_1_3_17, J_1_4_17, S_1_17, J_1_1_18, J_1_2_18, J_1_3_18, J_1_4_18, S_1_18, J_2_1_1, J_2_2_1, J_2_3_1, J_2_4_1, S_2_1, J_2_1_2, J_2_2_2, J_2_3_2, J_2_4_2, S_2_2, J_2_1_3, J_2_2_3, J_2_3_3, J_2_4_3, S_2_3, J_2_1_4, J_2_2_4, J_2_3_4, J_2_4_4, S_2_4, J_2_1_5, J_2_2_5, J_2_3_5, J_2_4_5, S_2_5, J_2_1_6, J_2_2_6, J_2_3_6, J_2_4_6, S_2_6, J_2_1_7, J_2_2_7, J_2_3_7, J_2_4_7, S_2_7, J_2_1_8, J_2_2_8, J_2_3_8, J_2_4_8, S_2_8, J_2_1_9, J_2_2_9, J_2_3_9, J_2_4_9, S_2_9, J_2_1_10, J_2_2_10, J_2_3_10, J_2_4_10, S_2_10, J_2_1_11, J_2_2_11, J_2_3_11, J_2_4_11, S_2_11, J_2_1_12, J_2_2_12, J_2_3_12, J_2_4_12, S_2_12, J_2_1_13, J_2_2_13, J_2_3_13, J_2_4_13, S_2_13, J_2_1_14, J_2_2_14, J_2_3_14, J_2_4_14, S_2_14, J_2_1_15, J_2_2_15, J_2_3_15, J_2_4_15, S_2_15, J_2_1_16, J_2_2_16, J_2_3_16, J_2_4_16, S_2_16, J_2_1_17, J_2_2_17, J_2_3_17, J_2_4_17, S_2_17, J_2_1_18, J_2_2_18, J_2_3_18, J_2_4_18, S_2_18, J_3_1_1, J_3_2_1, J_3_3_1, J_3_4_1, S_3_1, J_3_1_2, J_3_2_2, J_3_3_2, J_3_4_2, S_3_2, J_3_1_3, J_3_2_3, J_3_3_3, J_3_4_3, S_3_3, J_3_1_4, J_3_2_4, J_3_3_4, J_3_4_4, S_3_4, J_3_1_5, J_3_2_5, J_3_3_5, J_3_4_5, S_3_5, J_3_1_6, J_3_2_6, J_3_3_6, J_3_4_6, S_3_6, J_3_1_7, J_3_2_7, J_3_3_7, J_3_4_7, S_3_7, J_3_1_8, J_3_2_8, J_3_3_8, J_3_4_8, S_3_8, J_3_1_9, J_3_2_9, J_3_3_9, J_3_4_9, S_3_9, J_3_1_10, J_3_2_10, J_3_3_10, J_3_4_10, S_3_10, J_3_1_11, J_3_2_11, J_3_3_11, J_3_4_11, S_3_11, J_3_1_12, J_3_2_12, J_3_3_12, J_3_4_12, S_3_12, J_3_1_13, J_3_2_13, J_3_3_13, J_3_4_13, S_3_13, J_3_1_14, J_3_2_14, J_3_3_14, J_3_4_14, S_3_14, J_3_1_15, J_3_2_15, J_3_3_15, J_3_4_15, S_3_15, J_3_1_16, J_3_2_16, J_3_3_16, J_3_4_16, S_3_16, J_3_1_17, J_3_2_17, J_3_3_17, J_3_4_17, S_3_17, J_3_1_18, J_3_2_18, J_3_3_18, J_3_4_18, S_3_18, J_4_1_1, J_4_2_1, J_4_3_1, J_4_4_1, S_4_1, J_4_1_2, J_4_2_2, J_4_3_2, J_4_4_2, S_4_2, J_4_1_3, J_4_2_3, J_4_3_3, J_4_4_3, S_4_3, J_4_1_4, J_4_2_4, J_4_3_4, J_4_4_4, S_4_4, J_4_1_5, J_4_2_5, J_4_3_5, J_4_4_5, S_4_5, J_4_1_6, J_4_2_6, J_4_3_6, J_4_4_6, S_4_6, J_4_1_7, J_4_2_7, J_4_3_7, J_4_4_7, S_4_7, J_4_1_8, J_4_2_8, J_4_3_8, J_4_4_8, S_4_8, J_4_1_9, J_4_2_9, J_4_3_9, J_4_4_9, S_4_9, J_4_1_10, J_4_2_10, J_4_3_10, J_4_4_10, S_4_10, J_4_1_11, J_4_2_11, J_4_3_11, J_4_4_11, S_4_11, J_4_1_12, J_4_2_12, J_4_3_12, J_4_4_12, S_4_12, J_4_1_13, J_4_2_13, J_4_3_13, J_4_4_13, S_4_13, J_4_1_14, J_4_2_14, J_4_3_14, J_4_4_14, S_4_14, J_4_1_15, J_4_2_15, J_4_3_15, J_4_4_15, S_4_15, J_4_1_16, J_4_2_16, J_4_3_16, J_4_4_16, S_4_16, J_4_1_17, J_4_2_17, J_4_3_17, J_4_4_17, S_4_17, J_4_1_18, J_4_2_18, J_4_3_18, J_4_4_18, S_4_18, J_1_6, Total1, J_2_6, Total2, J_3_6, Total3, J_4_6, Total4 FROM Jobs_Calculations WHERE word3ofcard = '270';
I cannot get the Id of the database to work. I get a syntax error near 87.
Heres the tSQL code
INSERT INTO 87 Jobs_Calculations ( jobcard, word3ofcard, word4ofcard, jobdescription, jobdate, squares, meterssquared, cost1, cost2, cost3, cost4, squaresrate, subtotal, grandtotal, hiddengrandtotal, keepgrandtotal, invoicenumber, totalinvoicenumber, special, specialvisible, lockjob, joboftotaljobs, J_1_1_1, J_1_2_1, J_1_3_1, J_1_4_1, S_1_1, J_1_1_2, J_1_2_2, J_1_3_2, J_1_4_2, S_1_2, J_1_1_3, J_1_2_3, J_1_3_3, J_1_4_3, S_1_3, J_1_1_4, J_1_2_4, J_1_3_4, J_1_4_4, S_1_4, J_1_1_5, J_1_2_5, J_1_3_5, J_1_4_5, S_1_5, J_1_1_6, J_1_2_6, J_1_3_6, J_1_4_6, S_1_6, J_1_1_7, J_1_2_7, J_1_3_7, J_1_4_7, S_1_7, J_1_1_8, J_1_2_8, J_1_3_8, J_1_4_8, S_1_8, J_1_1_9, J_1_2_9, J_1_3_9, J_1_4_9, S_1_9, J_1_1_10, J_1_2_10, J_1_3_10, J_1_4_10, S_1_10, J_1_1_11, J_1_2_11, J_1_3_11, J_1_4_11, S_1_11, J_1_1_12, J_1_2_12, J_1_3_12, J_1_4_12, S_1_12, J_1_1_13, J_1_2_13, J_1_3_13, J_1_4_13, S_1_13, J_1_1_14, J_1_2_14, J_1_3_14, J_1_4_14, S_1_14, J_1_1_15, J_1_2_15, J_1_3_15, J_1_4_15, S_1_15, J_1_1_16, J_1_2_16, J_1_3_16, J_1_4_16, S_1_16, J_1_1_17, J_1_2_17, J_1_3_17, J_1_4_17, S_1_17, J_1_1_18, J_1_2_18, J_1_3_18, J_1_4_18, S_1_18, J_2_1_1, J_2_2_1, J_2_3_1, J_2_4_1, S_2_1, J_2_1_2, J_2_2_2, J_2_3_2, J_2_4_2, S_2_2, J_2_1_3, J_2_2_3, J_2_3_3, J_2_4_3, S_2_3, J_2_1_4, J_2_2_4, J_2_3_4, J_2_4_4, S_2_4, J_2_1_5, J_2_2_5, J_2_3_5, J_2_4_5, S_2_5, J_2_1_6, J_2_2_6, J_2_3_6, J_2_4_6, S_2_6, J_2_1_7, J_2_2_7, J_2_3_7, J_2_4_7, S_2_7, J_2_1_8, J_2_2_8, J_2_3_8, J_2_4_8, S_2_8, J_2_1_9, J_2_2_9, J_2_3_9, J_2_4_9, S_2_9, J_2_1_10, J_2_2_10, J_2_3_10, J_2_4_10, S_2_10, J_2_1_11, J_2_2_11, J_2_3_11, J_2_4_11, S_2_11, J_2_1_12, J_2_2_12, J_2_3_12, J_2_4_12, S_2_12, J_2_1_13, J_2_2_13, J_2_3_13, J_2_4_13, S_2_13, J_2_1_14, J_2_2_14, J_2_3_14, J_2_4_14, S_2_14, J_2_1_15, J_2_2_15, J_2_3_15, J_2_4_15, S_2_15, J_2_1_16, J_2_2_16, J_2_3_16, J_2_4_16, S_2_16, J_2_1_17, J_2_2_17, J_2_3_17, J_2_4_17, S_2_17, J_2_1_18, J_2_2_18, J_2_3_18, J_2_4_18, S_2_18, J_3_1_1, J_3_2_1, J_3_3_1, J_3_4_1, S_3_1, J_3_1_2, J_3_2_2, J_3_3_2, J_3_4_2, S_3_2, J_3_1_3, J_3_2_3, J_3_3_3, J_3_4_3, S_3_3, J_3_1_4, J_3_2_4, J_3_3_4, J_3_4_4, S_3_4, J_3_1_5, J_3_2_5, J_3_3_5, J_3_4_5, S_3_5, J_3_1_6, J_3_2_6, J_3_3_6, J_3_4_6, S_3_6, J_3_1_7, J_3_2_7, J_3_3_7, J_3_4_7, S_3_7, J_3_1_8, J_3_2_8, J_3_3_8, J_3_4_8, S_3_8, J_3_1_9, J_3_2_9, J_3_3_9, J_3_4_9, S_3_9, J_3_1_10, J_3_2_10, J_3_3_10, J_3_4_10, S_3_10, J_3_1_11, J_3_2_11, J_3_3_11, J_3_4_11, S_3_11, J_3_1_12, J_3_2_12, J_3_3_12, J_3_4_12, S_3_12, J_3_1_13, J_3_2_13, J_3_3_13, J_3_4_13, S_3_13, J_3_1_14, J_3_2_14, J_3_3_14, J_3_4_14, S_3_14, J_3_1_15, J_3_2_15, J_3_3_15, J_3_4_15, S_3_15, J_3_1_16, J_3_2_16, J_3_3_16, J_3_4_16, S_3_16, J_3_1_17, J_3_2_17, J_3_3_17, J_3_4_17, S_3_17, J_3_1_18, J_3_2_18, J_3_3_18, J_3_4_18, S_3_18, J_4_1_1, J_4_2_1, J_4_3_1, J_4_4_1, S_4_1, J_4_1_2, J_4_2_2, J_4_3_2, J_4_4_2, S_4_2, J_4_1_3, J_4_2_3, J_4_3_3, J_4_4_3, S_4_3, J_4_1_4, J_4_2_4, J_4_3_4, J_4_4_4, S_4_4, J_4_1_5, J_4_2_5, J_4_3_5, J_4_4_5, S_4_5, J_4_1_6, J_4_2_6, J_4_3_6, J_4_4_6, S_4_6, J_4_1_7, J_4_2_7, J_4_3_7, J_4_4_7, S_4_7, J_4_1_8, J_4_2_8, J_4_3_8, J_4_4_8, S_4_8, J_4_1_9, J_4_2_9, J_4_3_9, J_4_4_9, S_4_9, J_4_1_10, J_4_2_10, J_4_3_10, J_4_4_10, S_4_10, J_4_1_11, J_4_2_11, J_4_3_11, J_4_4_11, S_4_11, J_4_1_12, J_4_2_12, J_4_3_12, J_4_4_12, S_4_12, J_4_1_13, J_4_2_13, J_4_3_13, J_4_4_13, S_4_13, J_4_1_14, J_4_2_14, J_4_3_14, J_4_4_14, S_4_14, J_4_1_15, J_4_2_15, J_4_3_15, J_4_4_15, S_4_15, J_4_1_16, J_4_2_16, J_4_3_16, J_4_4_16, S_4_16, J_4_1_17, J_4_2_17, J_4_3_17, J_4_4_17, S_4_17, J_4_1_18, J_4_2_18, J_4_3_18, J_4_4_18, S_4_18, J_1_6, Total1, J_2_6, Total2, J_3_6, Total3, J_4_6, Total4) SELECT jobcard, word3ofcard, word4ofcard, jobdescription, jobdate, squares, meterssquared, cost1, cost2, cost3, cost4, squaresrate, subtotal, grandtotal, hiddengrandtotal, keepgrandtotal, invoicenumber, totalinvoicenumber, special, specialvisible, lockjob, joboftotaljobs, J_1_1_1, J_1_2_1, J_1_3_1, J_1_4_1, S_1_1, J_1_1_2, J_1_2_2, J_1_3_2, J_1_4_2, S_1_2, J_1_1_3, J_1_2_3, J_1_3_3, J_1_4_3, S_1_3, J_1_1_4, J_1_2_4, J_1_3_4, J_1_4_4, S_1_4, J_1_1_5, J_1_2_5, J_1_3_5, J_1_4_5, S_1_5, J_1_1_6, J_1_2_6, J_1_3_6, J_1_4_6, S_1_6, J_1_1_7, J_1_2_7, J_1_3_7, J_1_4_7, S_1_7, J_1_1_8, J_1_2_8, J_1_3_8, J_1_4_8, S_1_8, J_1_1_9, J_1_2_9, J_1_3_9, J_1_4_9, S_1_9, J_1_1_10, J_1_2_10, J_1_3_10, J_1_4_10, S_1_10, J_1_1_11, J_1_2_11, J_1_3_11, J_1_4_11, S_1_11, J_1_1_12, J_1_2_12, J_1_3_12, J_1_4_12, S_1_12, J_1_1_13, J_1_2_13, J_1_3_13, J_1_4_13, S_1_13, J_1_1_14, J_1_2_14, J_1_3_14, J_1_4_14, S_1_14, J_1_1_15, J_1_2_15, J_1_3_15, J_1_4_15, S_1_15, J_1_1_16, J_1_2_16, J_1_3_16, J_1_4_16, S_1_16, J_1_1_17, J_1_2_17, J_1_3_17, J_1_4_17, S_1_17, J_1_1_18, J_1_2_18, J_1_3_18, J_1_4_18, S_1_18, J_2_1_1, J_2_2_1, J_2_3_1, J_2_4_1, S_2_1, J_2_1_2, J_2_2_2, J_2_3_2, J_2_4_2, S_2_2, J_2_1_3, J_2_2_3, J_2_3_3, J_2_4_3, S_2_3, J_2_1_4, J_2_2_4, J_2_3_4, J_2_4_4, S_2_4, J_2_1_5, J_2_2_5, J_2_3_5, J_2_4_5, S_2_5, J_2_1_6, J_2_2_6, J_2_3_6, J_2_4_6, S_2_6, J_2_1_7, J_2_2_7, J_2_3_7, J_2_4_7, S_2_7, J_2_1_8, J_2_2_8, J_2_3_8, J_2_4_8, S_2_8, J_2_1_9, J_2_2_9, J_2_3_9, J_2_4_9, S_2_9, J_2_1_10, J_2_2_10, J_2_3_10, J_2_4_10, S_2_10, J_2_1_11, J_2_2_11, J_2_3_11, J_2_4_11, S_2_11, J_2_1_12, J_2_2_12, J_2_3_12, J_2_4_12, S_2_12, J_2_1_13, J_2_2_13, J_2_3_13, J_2_4_13, S_2_13, J_2_1_14, J_2_2_14, J_2_3_14, J_2_4_14, S_2_14, J_2_1_15, J_2_2_15, J_2_3_15, J_2_4_15, S_2_15, J_2_1_16, J_2_2_16, J_2_3_16, J_2_4_16, S_2_16, J_2_1_17, J_2_2_17, J_2_3_17, J_2_4_17, S_2_17, J_2_1_18, J_2_2_18, J_2_3_18, J_2_4_18, S_2_18, J_3_1_1, J_3_2_1, J_3_3_1, J_3_4_1, S_3_1, J_3_1_2, J_3_2_2, J_3_3_2, J_3_4_2, S_3_2, J_3_1_3, J_3_2_3, J_3_3_3, J_3_4_3, S_3_3, J_3_1_4, J_3_2_4, J_3_3_4, J_3_4_4, S_3_4, J_3_1_5, J_3_2_5, J_3_3_5, J_3_4_5, S_3_5, J_3_1_6, J_3_2_6, J_3_3_6, J_3_4_6, S_3_6, J_3_1_7, J_3_2_7, J_3_3_7, J_3_4_7, S_3_7, J_3_1_8, J_3_2_8, J_3_3_8, J_3_4_8, S_3_8, J_3_1_9, J_3_2_9, J_3_3_9, J_3_4_9, S_3_9, J_3_1_10, J_3_2_10, J_3_3_10, J_3_4_10, S_3_10, J_3_1_11, J_3_2_11, J_3_3_11, J_3_4_11, S_3_11, J_3_1_12, J_3_2_12, J_3_3_12, J_3_4_12, S_3_12, J_3_1_13, J_3_2_13, J_3_3_13, J_3_4_13, S_3_13, J_3_1_14, J_3_2_14, J_3_3_14, J_3_4_14, S_3_14, J_3_1_15, J_3_2_15, J_3_3_15, J_3_4_15, S_3_15, J_3_1_16, J_3_2_16, J_3_3_16, J_3_4_16, S_3_16, J_3_1_17, J_3_2_17, J_3_3_17, J_3_4_17, S_3_17, J_3_1_18, J_3_2_18, J_3_3_18, J_3_4_18, S_3_18, J_4_1_1, J_4_2_1, J_4_3_1, J_4_4_1, S_4_1, J_4_1_2, J_4_2_2, J_4_3_2, J_4_4_2, S_4_2, J_4_1_3, J_4_2_3, J_4_3_3, J_4_4_3, S_4_3, J_4_1_4, J_4_2_4, J_4_3_4, J_4_4_4, S_4_4, J_4_1_5, J_4_2_5, J_4_3_5, J_4_4_5, S_4_5, J_4_1_6, J_4_2_6, J_4_3_6, J_4_4_6, S_4_6, J_4_1_7, J_4_2_7, J_4_3_7, J_4_4_7, S_4_7, J_4_1_8, J_4_2_8, J_4_3_8, J_4_4_8, S_4_8, J_4_1_9, J_4_2_9, J_4_3_9, J_4_4_9, S_4_9, J_4_1_10, J_4_2_10, J_4_3_10, J_4_4_10, S_4_10, J_4_1_11, J_4_2_11, J_4_3_11, J_4_4_11, S_4_11, J_4_1_12, J_4_2_12, J_4_3_12, J_4_4_12, S_4_12, J_4_1_13, J_4_2_13, J_4_3_13, J_4_4_13, S_4_13, J_4_1_14, J_4_2_14, J_4_3_14, J_4_4_14, S_4_14, J_4_1_15, J_4_2_15, J_4_3_15, J_4_4_15, S_4_15, J_4_1_16, J_4_2_16, J_4_3_16, J_4_4_16, S_4_16, J_4_1_17, J_4_2_17, J_4_3_17, J_4_4_17, S_4_17, J_4_1_18, J_4_2_18, J_4_3_18, J_4_4_18, S_4_18, J_1_6, Total1, J_2_6, Total2, J_3_6, Total3, J_4_6, Total4 FROM Jobs_Calculations WHERE word3ofcard = '270';
Re: Requesting help with SQL error
Well, I've worked out that I need to address the data bases by name, but struck a further problem. Because the database names end in .sqlite then the code throws a 'no table of that name' error. I've tried enclosing the name in single and double quotes, without success.
Anyone able to advise me of the correct way to address a database and table please?
Regards
Santa
put ( "runrevPDQuoteTemp"&".sqlite") into TargetDataBase
put "INSERT INTO" &&"'"&TargetDataBase &"'"&"."&stackName && "(" into tSQL #this fails
Anyone able to advise me of the correct way to address a database and table please?
Regards
Santa
put ( "runrevPDQuoteTemp"&".sqlite") into TargetDataBase
put "INSERT INTO" &&"'"&TargetDataBase &"'"&"."&stackName && "(" into tSQL #this fails
Re: Requesting help with SQL error
Note: if I leave the single quotes off the DataBase name, i get an 'Error near "." '
With the quotes, the error is
'No such table:
runrevPDQuoteTemp.sqlite.Jobs_Calculations'
I've checked, and the database and table definitely exist.
The tSQL code for my efforts is...
INSERT INTO 'runrevPDQuoteTemp.sqlite'.Jobs_Calculations ( jobcard, word3ofcard, word4ofcard, jobdescription, jobdate, squares, meterssquared, cost1, cost2, cost3, cost4, squaresrate, subtotal, grandtotal, hiddengrandtotal, keepgrandtotal, invoicenumber, totalinvoicenumber, special, specialvisible, lockjob, joboftotaljobs, J_1_1_1, J_1_2_1, J_1_3_1, J_1_4_1, S_1_1, J_1_1_2, J_1_2_2, J_1_3_2, J_1_4_2, S_1_2, J_1_1_3, J_1_2_3, J_1_3_3, J_1_4_3, S_1_3, J_1_1_4, J_1_2_4, J_1_3_4, J_1_4_4, S_1_4, J_1_1_5, J_1_2_5, J_1_3_5, J_1_4_5, S_1_5, J_1_1_6, J_1_2_6, J_1_3_6, J_1_4_6, S_1_6, J_1_1_7, J_1_2_7, J_1_3_7, J_1_4_7, S_1_7, J_1_1_8, J_1_2_8, J_1_3_8, J_1_4_8, S_1_8, J_1_1_9, J_1_2_9, J_1_3_9, J_1_4_9, S_1_9, J_1_1_10, J_1_2_10, J_1_3_10, J_1_4_10, S_1_10, J_1_1_11, J_1_2_11, J_1_3_11, J_1_4_11, S_1_11, J_1_1_12, J_1_2_12, J_1_3_12, J_1_4_12, S_1_12, J_1_1_13, J_1_2_13, J_1_3_13, J_1_4_13, S_1_13, J_1_1_14, J_1_2_14, J_1_3_14, J_1_4_14, S_1_14, J_1_1_15, J_1_2_15, J_1_3_15, J_1_4_15, S_1_15, J_1_1_16, J_1_2_16, J_1_3_16, J_1_4_16, S_1_16, J_1_1_17, J_1_2_17, J_1_3_17, J_1_4_17, S_1_17, J_1_1_18, J_1_2_18, J_1_3_18, J_1_4_18, S_1_18, J_2_1_1, J_2_2_1, J_2_3_1, J_2_4_1, S_2_1, J_2_1_2, J_2_2_2, J_2_3_2, J_2_4_2, S_2_2, J_2_1_3, J_2_2_3, J_2_3_3, J_2_4_3, S_2_3, J_2_1_4, J_2_2_4, J_2_3_4, J_2_4_4, S_2_4, J_2_1_5, J_2_2_5, J_2_3_5, J_2_4_5, S_2_5, J_2_1_6, J_2_2_6, J_2_3_6, J_2_4_6, S_2_6, J_2_1_7, J_2_2_7, J_2_3_7, J_2_4_7, S_2_7, J_2_1_8, J_2_2_8, J_2_3_8, J_2_4_8, S_2_8, J_2_1_9, J_2_2_9, J_2_3_9, J_2_4_9, S_2_9, J_2_1_10, J_2_2_10, J_2_3_10, J_2_4_10, S_2_10, J_2_1_11, J_2_2_11, J_2_3_11, J_2_4_11, S_2_11, J_2_1_12, J_2_2_12, J_2_3_12, J_2_4_12, S_2_12, J_2_1_13, J_2_2_13, J_2_3_13, J_2_4_13, S_2_13, J_2_1_14, J_2_2_14, J_2_3_14, J_2_4_14, S_2_14, J_2_1_15, J_2_2_15, J_2_3_15, J_2_4_15, S_2_15, J_2_1_16, J_2_2_16, J_2_3_16, J_2_4_16, S_2_16, J_2_1_17, J_2_2_17, J_2_3_17, J_2_4_17, S_2_17, J_2_1_18, J_2_2_18, J_2_3_18, J_2_4_18, S_2_18, J_3_1_1, J_3_2_1, J_3_3_1, J_3_4_1, S_3_1, J_3_1_2, J_3_2_2, J_3_3_2, J_3_4_2, S_3_2, J_3_1_3, J_3_2_3, J_3_3_3, J_3_4_3, S_3_3, J_3_1_4, J_3_2_4, J_3_3_4, J_3_4_4, S_3_4, J_3_1_5, J_3_2_5, J_3_3_5, J_3_4_5, S_3_5, J_3_1_6, J_3_2_6, J_3_3_6, J_3_4_6, S_3_6, J_3_1_7, J_3_2_7, J_3_3_7, J_3_4_7, S_3_7, J_3_1_8, J_3_2_8, J_3_3_8, J_3_4_8, S_3_8, J_3_1_9, J_3_2_9, J_3_3_9, J_3_4_9, S_3_9, J_3_1_10, J_3_2_10, J_3_3_10, J_3_4_10, S_3_10, J_3_1_11, J_3_2_11, J_3_3_11, J_3_4_11, S_3_11, J_3_1_12, J_3_2_12, J_3_3_12, J_3_4_12, S_3_12, J_3_1_13, J_3_2_13, J_3_3_13, J_3_4_13, S_3_13, J_3_1_14, J_3_2_14, J_3_3_14, J_3_4_14, S_3_14, J_3_1_15, J_3_2_15, J_3_3_15, J_3_4_15, S_3_15, J_3_1_16, J_3_2_16, J_3_3_16, J_3_4_16, S_3_16, J_3_1_17, J_3_2_17, J_3_3_17, J_3_4_17, S_3_17, J_3_1_18, J_3_2_18, J_3_3_18, J_3_4_18, S_3_18, J_4_1_1, J_4_2_1, J_4_3_1, J_4_4_1, S_4_1, J_4_1_2, J_4_2_2, J_4_3_2, J_4_4_2, S_4_2, J_4_1_3, J_4_2_3, J_4_3_3, J_4_4_3, S_4_3, J_4_1_4, J_4_2_4, J_4_3_4, J_4_4_4, S_4_4, J_4_1_5, J_4_2_5, J_4_3_5, J_4_4_5, S_4_5, J_4_1_6, J_4_2_6, J_4_3_6, J_4_4_6, S_4_6, J_4_1_7, J_4_2_7, J_4_3_7, J_4_4_7, S_4_7, J_4_1_8, J_4_2_8, J_4_3_8, J_4_4_8, S_4_8, J_4_1_9, J_4_2_9, J_4_3_9, J_4_4_9, S_4_9, J_4_1_10, J_4_2_10, J_4_3_10, J_4_4_10, S_4_10, J_4_1_11, J_4_2_11, J_4_3_11, J_4_4_11, S_4_11, J_4_1_12, J_4_2_12, J_4_3_12, J_4_4_12, S_4_12, J_4_1_13, J_4_2_13, J_4_3_13, J_4_4_13, S_4_13, J_4_1_14, J_4_2_14, J_4_3_14, J_4_4_14, S_4_14, J_4_1_15, J_4_2_15, J_4_3_15, J_4_4_15, S_4_15, J_4_1_16, J_4_2_16, J_4_3_16, J_4_4_16, S_4_16, J_4_1_17, J_4_2_17, J_4_3_17, J_4_4_17, S_4_17, J_4_1_18, J_4_2_18, J_4_3_18, J_4_4_18, S_4_18, J_1_6, Total1, J_2_6, Total2, J_3_6, Total3, J_4_6, Total4) SELECT jobcard, word3ofcard, word4ofcard, jobdescription, jobdate, squares, meterssquared, cost1, cost2, cost3, cost4, squaresrate, subtotal, grandtotal, hiddengrandtotal, keepgrandtotal, invoicenumber, totalinvoicenumber, special, specialvisible, lockjob, joboftotaljobs, J_1_1_1, J_1_2_1, J_1_3_1, J_1_4_1, S_1_1, J_1_1_2, J_1_2_2, J_1_3_2, J_1_4_2, S_1_2, J_1_1_3, J_1_2_3, J_1_3_3, J_1_4_3, S_1_3, J_1_1_4, J_1_2_4, J_1_3_4, J_1_4_4, S_1_4, J_1_1_5, J_1_2_5, J_1_3_5, J_1_4_5, S_1_5, J_1_1_6, J_1_2_6, J_1_3_6, J_1_4_6, S_1_6, J_1_1_7, J_1_2_7, J_1_3_7, J_1_4_7, S_1_7, J_1_1_8, J_1_2_8, J_1_3_8, J_1_4_8, S_1_8, J_1_1_9, J_1_2_9, J_1_3_9, J_1_4_9, S_1_9, J_1_1_10, J_1_2_10, J_1_3_10, J_1_4_10, S_1_10, J_1_1_11, J_1_2_11, J_1_3_11, J_1_4_11, S_1_11, J_1_1_12, J_1_2_12, J_1_3_12, J_1_4_12, S_1_12, J_1_1_13, J_1_2_13, J_1_3_13, J_1_4_13, S_1_13, J_1_1_14, J_1_2_14, J_1_3_14, J_1_4_14, S_1_14, J_1_1_15, J_1_2_15, J_1_3_15, J_1_4_15, S_1_15, J_1_1_16, J_1_2_16, J_1_3_16, J_1_4_16, S_1_16, J_1_1_17, J_1_2_17, J_1_3_17, J_1_4_17, S_1_17, J_1_1_18, J_1_2_18, J_1_3_18, J_1_4_18, S_1_18, J_2_1_1, J_2_2_1, J_2_3_1, J_2_4_1, S_2_1, J_2_1_2, J_2_2_2, J_2_3_2, J_2_4_2, S_2_2, J_2_1_3, J_2_2_3, J_2_3_3, J_2_4_3, S_2_3, J_2_1_4, J_2_2_4, J_2_3_4, J_2_4_4, S_2_4, J_2_1_5, J_2_2_5, J_2_3_5, J_2_4_5, S_2_5, J_2_1_6, J_2_2_6, J_2_3_6, J_2_4_6, S_2_6, J_2_1_7, J_2_2_7, J_2_3_7, J_2_4_7, S_2_7, J_2_1_8, J_2_2_8, J_2_3_8, J_2_4_8, S_2_8, J_2_1_9, J_2_2_9, J_2_3_9, J_2_4_9, S_2_9, J_2_1_10, J_2_2_10, J_2_3_10, J_2_4_10, S_2_10, J_2_1_11, J_2_2_11, J_2_3_11, J_2_4_11, S_2_11, J_2_1_12, J_2_2_12, J_2_3_12, J_2_4_12, S_2_12, J_2_1_13, J_2_2_13, J_2_3_13, J_2_4_13, S_2_13, J_2_1_14, J_2_2_14, J_2_3_14, J_2_4_14, S_2_14, J_2_1_15, J_2_2_15, J_2_3_15, J_2_4_15, S_2_15, J_2_1_16, J_2_2_16, J_2_3_16, J_2_4_16, S_2_16, J_2_1_17, J_2_2_17, J_2_3_17, J_2_4_17, S_2_17, J_2_1_18, J_2_2_18, J_2_3_18, J_2_4_18, S_2_18, J_3_1_1, J_3_2_1, J_3_3_1, J_3_4_1, S_3_1, J_3_1_2, J_3_2_2, J_3_3_2, J_3_4_2, S_3_2, J_3_1_3, J_3_2_3, J_3_3_3, J_3_4_3, S_3_3, J_3_1_4, J_3_2_4, J_3_3_4, J_3_4_4, S_3_4, J_3_1_5, J_3_2_5, J_3_3_5, J_3_4_5, S_3_5, J_3_1_6, J_3_2_6, J_3_3_6, J_3_4_6, S_3_6, J_3_1_7, J_3_2_7, J_3_3_7, J_3_4_7, S_3_7, J_3_1_8, J_3_2_8, J_3_3_8, J_3_4_8, S_3_8, J_3_1_9, J_3_2_9, J_3_3_9, J_3_4_9, S_3_9, J_3_1_10, J_3_2_10, J_3_3_10, J_3_4_10, S_3_10, J_3_1_11, J_3_2_11, J_3_3_11, J_3_4_11, S_3_11, J_3_1_12, J_3_2_12, J_3_3_12, J_3_4_12, S_3_12, J_3_1_13, J_3_2_13, J_3_3_13, J_3_4_13, S_3_13, J_3_1_14, J_3_2_14, J_3_3_14, J_3_4_14, S_3_14, J_3_1_15, J_3_2_15, J_3_3_15, J_3_4_15, S_3_15, J_3_1_16, J_3_2_16, J_3_3_16, J_3_4_16, S_3_16, J_3_1_17, J_3_2_17, J_3_3_17, J_3_4_17, S_3_17, J_3_1_18, J_3_2_18, J_3_3_18, J_3_4_18, S_3_18, J_4_1_1, J_4_2_1, J_4_3_1, J_4_4_1, S_4_1, J_4_1_2, J_4_2_2, J_4_3_2, J_4_4_2, S_4_2, J_4_1_3, J_4_2_3, J_4_3_3, J_4_4_3, S_4_3, J_4_1_4, J_4_2_4, J_4_3_4, J_4_4_4, S_4_4, J_4_1_5, J_4_2_5, J_4_3_5, J_4_4_5, S_4_5, J_4_1_6, J_4_2_6, J_4_3_6, J_4_4_6, S_4_6, J_4_1_7, J_4_2_7, J_4_3_7, J_4_4_7, S_4_7, J_4_1_8, J_4_2_8, J_4_3_8, J_4_4_8, S_4_8, J_4_1_9, J_4_2_9, J_4_3_9, J_4_4_9, S_4_9, J_4_1_10, J_4_2_10, J_4_3_10, J_4_4_10, S_4_10, J_4_1_11, J_4_2_11, J_4_3_11, J_4_4_11, S_4_11, J_4_1_12, J_4_2_12, J_4_3_12, J_4_4_12, S_4_12, J_4_1_13, J_4_2_13, J_4_3_13, J_4_4_13, S_4_13, J_4_1_14, J_4_2_14, J_4_3_14, J_4_4_14, S_4_14, J_4_1_15, J_4_2_15, J_4_3_15, J_4_4_15, S_4_15, J_4_1_16, J_4_2_16, J_4_3_16, J_4_4_16, S_4_16, J_4_1_17, J_4_2_17, J_4_3_17, J_4_4_17, S_4_17, J_4_1_18, J_4_2_18, J_4_3_18, J_4_4_18, S_4_18, J_1_6, Total1, J_2_6, Total2, J_3_6, Total3, J_4_6, Total4 FROM 'runrevPDQuote2014.sqlite'.Jobs_Calculations WHERE word3ofcard = '270';
With the quotes, the error is
'No such table:
runrevPDQuoteTemp.sqlite.Jobs_Calculations'
I've checked, and the database and table definitely exist.
The tSQL code for my efforts is...
INSERT INTO 'runrevPDQuoteTemp.sqlite'.Jobs_Calculations ( jobcard, word3ofcard, word4ofcard, jobdescription, jobdate, squares, meterssquared, cost1, cost2, cost3, cost4, squaresrate, subtotal, grandtotal, hiddengrandtotal, keepgrandtotal, invoicenumber, totalinvoicenumber, special, specialvisible, lockjob, joboftotaljobs, J_1_1_1, J_1_2_1, J_1_3_1, J_1_4_1, S_1_1, J_1_1_2, J_1_2_2, J_1_3_2, J_1_4_2, S_1_2, J_1_1_3, J_1_2_3, J_1_3_3, J_1_4_3, S_1_3, J_1_1_4, J_1_2_4, J_1_3_4, J_1_4_4, S_1_4, J_1_1_5, J_1_2_5, J_1_3_5, J_1_4_5, S_1_5, J_1_1_6, J_1_2_6, J_1_3_6, J_1_4_6, S_1_6, J_1_1_7, J_1_2_7, J_1_3_7, J_1_4_7, S_1_7, J_1_1_8, J_1_2_8, J_1_3_8, J_1_4_8, S_1_8, J_1_1_9, J_1_2_9, J_1_3_9, J_1_4_9, S_1_9, J_1_1_10, J_1_2_10, J_1_3_10, J_1_4_10, S_1_10, J_1_1_11, J_1_2_11, J_1_3_11, J_1_4_11, S_1_11, J_1_1_12, J_1_2_12, J_1_3_12, J_1_4_12, S_1_12, J_1_1_13, J_1_2_13, J_1_3_13, J_1_4_13, S_1_13, J_1_1_14, J_1_2_14, J_1_3_14, J_1_4_14, S_1_14, J_1_1_15, J_1_2_15, J_1_3_15, J_1_4_15, S_1_15, J_1_1_16, J_1_2_16, J_1_3_16, J_1_4_16, S_1_16, J_1_1_17, J_1_2_17, J_1_3_17, J_1_4_17, S_1_17, J_1_1_18, J_1_2_18, J_1_3_18, J_1_4_18, S_1_18, J_2_1_1, J_2_2_1, J_2_3_1, J_2_4_1, S_2_1, J_2_1_2, J_2_2_2, J_2_3_2, J_2_4_2, S_2_2, J_2_1_3, J_2_2_3, J_2_3_3, J_2_4_3, S_2_3, J_2_1_4, J_2_2_4, J_2_3_4, J_2_4_4, S_2_4, J_2_1_5, J_2_2_5, J_2_3_5, J_2_4_5, S_2_5, J_2_1_6, J_2_2_6, J_2_3_6, J_2_4_6, S_2_6, J_2_1_7, J_2_2_7, J_2_3_7, J_2_4_7, S_2_7, J_2_1_8, J_2_2_8, J_2_3_8, J_2_4_8, S_2_8, J_2_1_9, J_2_2_9, J_2_3_9, J_2_4_9, S_2_9, J_2_1_10, J_2_2_10, J_2_3_10, J_2_4_10, S_2_10, J_2_1_11, J_2_2_11, J_2_3_11, J_2_4_11, S_2_11, J_2_1_12, J_2_2_12, J_2_3_12, J_2_4_12, S_2_12, J_2_1_13, J_2_2_13, J_2_3_13, J_2_4_13, S_2_13, J_2_1_14, J_2_2_14, J_2_3_14, J_2_4_14, S_2_14, J_2_1_15, J_2_2_15, J_2_3_15, J_2_4_15, S_2_15, J_2_1_16, J_2_2_16, J_2_3_16, J_2_4_16, S_2_16, J_2_1_17, J_2_2_17, J_2_3_17, J_2_4_17, S_2_17, J_2_1_18, J_2_2_18, J_2_3_18, J_2_4_18, S_2_18, J_3_1_1, J_3_2_1, J_3_3_1, J_3_4_1, S_3_1, J_3_1_2, J_3_2_2, J_3_3_2, J_3_4_2, S_3_2, J_3_1_3, J_3_2_3, J_3_3_3, J_3_4_3, S_3_3, J_3_1_4, J_3_2_4, J_3_3_4, J_3_4_4, S_3_4, J_3_1_5, J_3_2_5, J_3_3_5, J_3_4_5, S_3_5, J_3_1_6, J_3_2_6, J_3_3_6, J_3_4_6, S_3_6, J_3_1_7, J_3_2_7, J_3_3_7, J_3_4_7, S_3_7, J_3_1_8, J_3_2_8, J_3_3_8, J_3_4_8, S_3_8, J_3_1_9, J_3_2_9, J_3_3_9, J_3_4_9, S_3_9, J_3_1_10, J_3_2_10, J_3_3_10, J_3_4_10, S_3_10, J_3_1_11, J_3_2_11, J_3_3_11, J_3_4_11, S_3_11, J_3_1_12, J_3_2_12, J_3_3_12, J_3_4_12, S_3_12, J_3_1_13, J_3_2_13, J_3_3_13, J_3_4_13, S_3_13, J_3_1_14, J_3_2_14, J_3_3_14, J_3_4_14, S_3_14, J_3_1_15, J_3_2_15, J_3_3_15, J_3_4_15, S_3_15, J_3_1_16, J_3_2_16, J_3_3_16, J_3_4_16, S_3_16, J_3_1_17, J_3_2_17, J_3_3_17, J_3_4_17, S_3_17, J_3_1_18, J_3_2_18, J_3_3_18, J_3_4_18, S_3_18, J_4_1_1, J_4_2_1, J_4_3_1, J_4_4_1, S_4_1, J_4_1_2, J_4_2_2, J_4_3_2, J_4_4_2, S_4_2, J_4_1_3, J_4_2_3, J_4_3_3, J_4_4_3, S_4_3, J_4_1_4, J_4_2_4, J_4_3_4, J_4_4_4, S_4_4, J_4_1_5, J_4_2_5, J_4_3_5, J_4_4_5, S_4_5, J_4_1_6, J_4_2_6, J_4_3_6, J_4_4_6, S_4_6, J_4_1_7, J_4_2_7, J_4_3_7, J_4_4_7, S_4_7, J_4_1_8, J_4_2_8, J_4_3_8, J_4_4_8, S_4_8, J_4_1_9, J_4_2_9, J_4_3_9, J_4_4_9, S_4_9, J_4_1_10, J_4_2_10, J_4_3_10, J_4_4_10, S_4_10, J_4_1_11, J_4_2_11, J_4_3_11, J_4_4_11, S_4_11, J_4_1_12, J_4_2_12, J_4_3_12, J_4_4_12, S_4_12, J_4_1_13, J_4_2_13, J_4_3_13, J_4_4_13, S_4_13, J_4_1_14, J_4_2_14, J_4_3_14, J_4_4_14, S_4_14, J_4_1_15, J_4_2_15, J_4_3_15, J_4_4_15, S_4_15, J_4_1_16, J_4_2_16, J_4_3_16, J_4_4_16, S_4_16, J_4_1_17, J_4_2_17, J_4_3_17, J_4_4_17, S_4_17, J_4_1_18, J_4_2_18, J_4_3_18, J_4_4_18, S_4_18, J_1_6, Total1, J_2_6, Total2, J_3_6, Total3, J_4_6, Total4) SELECT jobcard, word3ofcard, word4ofcard, jobdescription, jobdate, squares, meterssquared, cost1, cost2, cost3, cost4, squaresrate, subtotal, grandtotal, hiddengrandtotal, keepgrandtotal, invoicenumber, totalinvoicenumber, special, specialvisible, lockjob, joboftotaljobs, J_1_1_1, J_1_2_1, J_1_3_1, J_1_4_1, S_1_1, J_1_1_2, J_1_2_2, J_1_3_2, J_1_4_2, S_1_2, J_1_1_3, J_1_2_3, J_1_3_3, J_1_4_3, S_1_3, J_1_1_4, J_1_2_4, J_1_3_4, J_1_4_4, S_1_4, J_1_1_5, J_1_2_5, J_1_3_5, J_1_4_5, S_1_5, J_1_1_6, J_1_2_6, J_1_3_6, J_1_4_6, S_1_6, J_1_1_7, J_1_2_7, J_1_3_7, J_1_4_7, S_1_7, J_1_1_8, J_1_2_8, J_1_3_8, J_1_4_8, S_1_8, J_1_1_9, J_1_2_9, J_1_3_9, J_1_4_9, S_1_9, J_1_1_10, J_1_2_10, J_1_3_10, J_1_4_10, S_1_10, J_1_1_11, J_1_2_11, J_1_3_11, J_1_4_11, S_1_11, J_1_1_12, J_1_2_12, J_1_3_12, J_1_4_12, S_1_12, J_1_1_13, J_1_2_13, J_1_3_13, J_1_4_13, S_1_13, J_1_1_14, J_1_2_14, J_1_3_14, J_1_4_14, S_1_14, J_1_1_15, J_1_2_15, J_1_3_15, J_1_4_15, S_1_15, J_1_1_16, J_1_2_16, J_1_3_16, J_1_4_16, S_1_16, J_1_1_17, J_1_2_17, J_1_3_17, J_1_4_17, S_1_17, J_1_1_18, J_1_2_18, J_1_3_18, J_1_4_18, S_1_18, J_2_1_1, J_2_2_1, J_2_3_1, J_2_4_1, S_2_1, J_2_1_2, J_2_2_2, J_2_3_2, J_2_4_2, S_2_2, J_2_1_3, J_2_2_3, J_2_3_3, J_2_4_3, S_2_3, J_2_1_4, J_2_2_4, J_2_3_4, J_2_4_4, S_2_4, J_2_1_5, J_2_2_5, J_2_3_5, J_2_4_5, S_2_5, J_2_1_6, J_2_2_6, J_2_3_6, J_2_4_6, S_2_6, J_2_1_7, J_2_2_7, J_2_3_7, J_2_4_7, S_2_7, J_2_1_8, J_2_2_8, J_2_3_8, J_2_4_8, S_2_8, J_2_1_9, J_2_2_9, J_2_3_9, J_2_4_9, S_2_9, J_2_1_10, J_2_2_10, J_2_3_10, J_2_4_10, S_2_10, J_2_1_11, J_2_2_11, J_2_3_11, J_2_4_11, S_2_11, J_2_1_12, J_2_2_12, J_2_3_12, J_2_4_12, S_2_12, J_2_1_13, J_2_2_13, J_2_3_13, J_2_4_13, S_2_13, J_2_1_14, J_2_2_14, J_2_3_14, J_2_4_14, S_2_14, J_2_1_15, J_2_2_15, J_2_3_15, J_2_4_15, S_2_15, J_2_1_16, J_2_2_16, J_2_3_16, J_2_4_16, S_2_16, J_2_1_17, J_2_2_17, J_2_3_17, J_2_4_17, S_2_17, J_2_1_18, J_2_2_18, J_2_3_18, J_2_4_18, S_2_18, J_3_1_1, J_3_2_1, J_3_3_1, J_3_4_1, S_3_1, J_3_1_2, J_3_2_2, J_3_3_2, J_3_4_2, S_3_2, J_3_1_3, J_3_2_3, J_3_3_3, J_3_4_3, S_3_3, J_3_1_4, J_3_2_4, J_3_3_4, J_3_4_4, S_3_4, J_3_1_5, J_3_2_5, J_3_3_5, J_3_4_5, S_3_5, J_3_1_6, J_3_2_6, J_3_3_6, J_3_4_6, S_3_6, J_3_1_7, J_3_2_7, J_3_3_7, J_3_4_7, S_3_7, J_3_1_8, J_3_2_8, J_3_3_8, J_3_4_8, S_3_8, J_3_1_9, J_3_2_9, J_3_3_9, J_3_4_9, S_3_9, J_3_1_10, J_3_2_10, J_3_3_10, J_3_4_10, S_3_10, J_3_1_11, J_3_2_11, J_3_3_11, J_3_4_11, S_3_11, J_3_1_12, J_3_2_12, J_3_3_12, J_3_4_12, S_3_12, J_3_1_13, J_3_2_13, J_3_3_13, J_3_4_13, S_3_13, J_3_1_14, J_3_2_14, J_3_3_14, J_3_4_14, S_3_14, J_3_1_15, J_3_2_15, J_3_3_15, J_3_4_15, S_3_15, J_3_1_16, J_3_2_16, J_3_3_16, J_3_4_16, S_3_16, J_3_1_17, J_3_2_17, J_3_3_17, J_3_4_17, S_3_17, J_3_1_18, J_3_2_18, J_3_3_18, J_3_4_18, S_3_18, J_4_1_1, J_4_2_1, J_4_3_1, J_4_4_1, S_4_1, J_4_1_2, J_4_2_2, J_4_3_2, J_4_4_2, S_4_2, J_4_1_3, J_4_2_3, J_4_3_3, J_4_4_3, S_4_3, J_4_1_4, J_4_2_4, J_4_3_4, J_4_4_4, S_4_4, J_4_1_5, J_4_2_5, J_4_3_5, J_4_4_5, S_4_5, J_4_1_6, J_4_2_6, J_4_3_6, J_4_4_6, S_4_6, J_4_1_7, J_4_2_7, J_4_3_7, J_4_4_7, S_4_7, J_4_1_8, J_4_2_8, J_4_3_8, J_4_4_8, S_4_8, J_4_1_9, J_4_2_9, J_4_3_9, J_4_4_9, S_4_9, J_4_1_10, J_4_2_10, J_4_3_10, J_4_4_10, S_4_10, J_4_1_11, J_4_2_11, J_4_3_11, J_4_4_11, S_4_11, J_4_1_12, J_4_2_12, J_4_3_12, J_4_4_12, S_4_12, J_4_1_13, J_4_2_13, J_4_3_13, J_4_4_13, S_4_13, J_4_1_14, J_4_2_14, J_4_3_14, J_4_4_14, S_4_14, J_4_1_15, J_4_2_15, J_4_3_15, J_4_4_15, S_4_15, J_4_1_16, J_4_2_16, J_4_3_16, J_4_4_16, S_4_16, J_4_1_17, J_4_2_17, J_4_3_17, J_4_4_17, S_4_17, J_4_1_18, J_4_2_18, J_4_3_18, J_4_4_18, S_4_18, J_1_6, Total1, J_2_6, Total2, J_3_6, Total3, J_4_6, Total4 FROM 'runrevPDQuote2014.sqlite'.Jobs_Calculations WHERE word3ofcard = '270';
Re: Requesting help with SQL error
Hi Santa,
You CANNOT use the PATHNAME in an SQL query!
And you cannot use the ID, that you got from revopendatabase(), instead of the PATHNAME in the SQL query!
Thd ID is NOT used in the SQL string, but in the Livecode commands!
You ONLY use the TABLENAME in th SQL!
You need to do this:
...
put revOpenDatabase("sqlite","/path/to/runRevPDQuote2013", , , ) into DB_ID_runRevPDQuote2013
...
put revOpenDatabase("sqlite","/path/to/runRevPDQuote2014", , , ) into DB_ID_runRevPDQuote2014
...
put revOpenDatabase("sqlite","/path/to/runRevPDQuote2015", , , ) into DB_ID_runRevPDQuote2014
...
etc...
Then use the APPROPRIATE ID when accessing the datzabase you need!
Create your SQL string and then use the ID to insert into the CORRECT database:...
put "INSER INTO........" into tSQL
revExecuteSQL DB_ID_runRevPDQuote2013, tSQL
OR:
put "INSER INTO........" into tSQL
revExecuteSQL DB_ID_runRevPDQuote2014, tSQL
OR:
put "INSER INTO........" into tSQL
revExecuteSQL DB_ID_runRevPDQuote2015, tSQL
Get the picture?
If in doubt, read the dictionary...
Best
Klaus
You CANNOT use the PATHNAME in an SQL query!
And you cannot use the ID, that you got from revopendatabase(), instead of the PATHNAME in the SQL query!
Thd ID is NOT used in the SQL string, but in the Livecode commands!
You ONLY use the TABLENAME in th SQL!
You need to do this:
...
put revOpenDatabase("sqlite","/path/to/runRevPDQuote2013", , , ) into DB_ID_runRevPDQuote2013
...
put revOpenDatabase("sqlite","/path/to/runRevPDQuote2014", , , ) into DB_ID_runRevPDQuote2014
...
put revOpenDatabase("sqlite","/path/to/runRevPDQuote2015", , , ) into DB_ID_runRevPDQuote2014
...
etc...
Then use the APPROPRIATE ID when accessing the datzabase you need!
Create your SQL string and then use the ID to insert into the CORRECT database:...
put "INSER INTO........" into tSQL
revExecuteSQL DB_ID_runRevPDQuote2013, tSQL
OR:
put "INSER INTO........" into tSQL
revExecuteSQL DB_ID_runRevPDQuote2014, tSQL
OR:
put "INSER INTO........" into tSQL
revExecuteSQL DB_ID_runRevPDQuote2015, tSQL
Get the picture?
If in doubt, read the dictionary...
Best
Klaus
Re: Requesting help with SQL error
Thanks Klaus, now I understand. I had been reading the SQLite site blurb that said you could use a data base name, then a full stop, then a table name.
My final code is this...
#set up variables etc, then,
put true into transferFlag
put empty into errormessage
# GET THE MATCHING DATA
get revDataFromQuery(tab, return, tDatabaseID, "SELECT * FROM "&stackName&" WHERE word3ofcard = "&field "invoice number" ) # could be multiple cards.
put the result into tempRecords
if number of lines of tempRecords = 0 then
put false into transferFlag
put "There was an error with the dataBase. No matching records were found." into errormessage
end if
# CHECK FOR MAXIMUM INVOICE NUMBER, AND ADJUST TO RETURNED MAXIMUM
put SetMaxInvoice() into maximumInvoice # handler returns maximum invoice number across all yearly databases, + 1
put ( label of button "Transfer List" ) into transferYear
put specialFolderPath("documents") & "/runrevPDQuote"& transferYear &".sqlite" into tDatabasePath # allows for indefinite number of years
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tempDatabaseID
put "UPDATE "&stackName&" SET " into tSQL
put "totalinvoicenumber = '"&maximumInvoice&"'" after tSQL
revExecuteSQL tempDatabaseID, tSQL
# NOW ALTER THE SORT NUMBER AND CARD NAMES
set the itemdelimiter to tab
put number of characters of maximumInvoice into tempMax
repeat with x = 1 to number of lines of tempRecords
put item 1 of line x of tempRecords into theSortNumber
put theSortNumber into keepSort
put character (tempMax+1) to -1 of theSortNumber into theSortNumber
put maximumInvoice & theSortNumber into item 1 of line x of tempRecords
put item 2 of line x of tempRecords into theTempName
put maximumInvoice into word 3 of theTempName
put theTempName into item 2 of line x of tempRecords
put maximumInvoice into item 3 of line x of tempRecords
put item 23 of line x of tempRecords into jobofjobs
put maximumInvoice into last word of jobofjobs
put jobofjobs into item 23 of line x of tempRecords
end repeat
# NOW MOVE THE FILES TO THE NEW DATA BASE
repeat for each line tSqlStatement in tempRecords
set the itemdelimiter to tab
put 0 into xx
repeat
put " INSERT INTO " && StackName && " VALUES " into tSQL
put "( " after tSQL
put quote&item 1 of tSqlStatement & quote after tSQL
repeat with x = 2 to 391
put "," "e&item x of tSqlStatement & quote after tSQL
end repeat
put ")" after tSQL
revExecuteSQL tempDatabaseID, tSQL
put the result into tempResult
if tempResult is not a number then
# Should not happen, as unique string is based on 4 digit invoice
#number followed by 4 zeroes, then a job card number.
# but just in case, to ensure saving
if tempResult contains "Unique" then
put item 1 of tSqlStatement +1 into item 1 of tSqlStatement
else
put false into transferFlag
if errormessage is empty then
put tempResult into errormessage
end if
end if
else
exit repeat
end if
put xx +1 into XX
if xx = 100 then
put false into transferFlag
put "multiple tries to Save" into errormessage
exit repeat
end if
end repeat
end repeat
if tempDatabaseID is a number then
revCloseDataBase tempDatabaseID
end if
put empty into tempDatabaseID
if tDatabaseIDTempJob is a number then
revCloseDataBase tDatabaseIDTempJob
end if
put empty into tDatabaseIDTempJob
if tDatabaseID is a number then
revCloseDataBase tDatabaseID
end if
put empty into tDatabaseID
if transferFlag = true then
scrubJob (false)
put setUpInvoiceNumbers() into temp
Answer information "The job has been moved to the "&transferYear& " stack satisfactorily." \
&return & return & "It has an invoice number of "&maximuminvoice&"." &return as sheet
else
Answer warning "This job does not appear to have transferred to the "&transferYear & " stack correctly."\
&return & return & "It should have an invoice number of "&maximuminvoice&"." &return &return &\
"I suggest that you check if it's allright please, and try again." &return &return & "Error:"&& errormessage as sheet
end if
My final code is this...
#set up variables etc, then,
put true into transferFlag
put empty into errormessage
# GET THE MATCHING DATA
get revDataFromQuery(tab, return, tDatabaseID, "SELECT * FROM "&stackName&" WHERE word3ofcard = "&field "invoice number" ) # could be multiple cards.
put the result into tempRecords
if number of lines of tempRecords = 0 then
put false into transferFlag
put "There was an error with the dataBase. No matching records were found." into errormessage
end if
# CHECK FOR MAXIMUM INVOICE NUMBER, AND ADJUST TO RETURNED MAXIMUM
put SetMaxInvoice() into maximumInvoice # handler returns maximum invoice number across all yearly databases, + 1
put ( label of button "Transfer List" ) into transferYear
put specialFolderPath("documents") & "/runrevPDQuote"& transferYear &".sqlite" into tDatabasePath # allows for indefinite number of years
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tempDatabaseID
put "UPDATE "&stackName&" SET " into tSQL
put "totalinvoicenumber = '"&maximumInvoice&"'" after tSQL
revExecuteSQL tempDatabaseID, tSQL
# NOW ALTER THE SORT NUMBER AND CARD NAMES
set the itemdelimiter to tab
put number of characters of maximumInvoice into tempMax
repeat with x = 1 to number of lines of tempRecords
put item 1 of line x of tempRecords into theSortNumber
put theSortNumber into keepSort
put character (tempMax+1) to -1 of theSortNumber into theSortNumber
put maximumInvoice & theSortNumber into item 1 of line x of tempRecords
put item 2 of line x of tempRecords into theTempName
put maximumInvoice into word 3 of theTempName
put theTempName into item 2 of line x of tempRecords
put maximumInvoice into item 3 of line x of tempRecords
put item 23 of line x of tempRecords into jobofjobs
put maximumInvoice into last word of jobofjobs
put jobofjobs into item 23 of line x of tempRecords
end repeat
# NOW MOVE THE FILES TO THE NEW DATA BASE
repeat for each line tSqlStatement in tempRecords
set the itemdelimiter to tab
put 0 into xx
repeat
put " INSERT INTO " && StackName && " VALUES " into tSQL
put "( " after tSQL
put quote&item 1 of tSqlStatement & quote after tSQL
repeat with x = 2 to 391
put "," "e&item x of tSqlStatement & quote after tSQL
end repeat
put ")" after tSQL
revExecuteSQL tempDatabaseID, tSQL
put the result into tempResult
if tempResult is not a number then
# Should not happen, as unique string is based on 4 digit invoice
#number followed by 4 zeroes, then a job card number.
# but just in case, to ensure saving
if tempResult contains "Unique" then
put item 1 of tSqlStatement +1 into item 1 of tSqlStatement
else
put false into transferFlag
if errormessage is empty then
put tempResult into errormessage
end if
end if
else
exit repeat
end if
put xx +1 into XX
if xx = 100 then
put false into transferFlag
put "multiple tries to Save" into errormessage
exit repeat
end if
end repeat
end repeat
if tempDatabaseID is a number then
revCloseDataBase tempDatabaseID
end if
put empty into tempDatabaseID
if tDatabaseIDTempJob is a number then
revCloseDataBase tDatabaseIDTempJob
end if
put empty into tDatabaseIDTempJob
if tDatabaseID is a number then
revCloseDataBase tDatabaseID
end if
put empty into tDatabaseID
if transferFlag = true then
scrubJob (false)
put setUpInvoiceNumbers() into temp
Answer information "The job has been moved to the "&transferYear& " stack satisfactorily." \
&return & return & "It has an invoice number of "&maximuminvoice&"." &return as sheet
else
Answer warning "This job does not appear to have transferred to the "&transferYear & " stack correctly."\
&return & return & "It should have an invoice number of "&maximuminvoice&"." &return &return &\
"I suggest that you check if it's allright please, and try again." &return &return & "Error:"&& errormessage as sheet
end if