Board index » delphi » batch move to query help

batch move to query help

On the advice of several here, i am using a query component to try and
insert record from one table into another. I have a query component on
the form with nothing in its tstrings. i use the following code when a
radio button is checked.
begin
if radiobutton2.checked then
try
if active then
query1.close;
if not query1.prepared then
query1.prepare;
query1.sql.add('insert into contract.db(awardnumber) select awardnumber
from procurement.db') ;
query1.ExecSQL;
  query1.open;
I get a unexpected end of command line one error, does this statement
need to be in the tstrings(itried it and it didn't seem to work)
two, with the if statement will it only pull those records that are
checked or will i have to include a while radiobutton2=checked in the
sql statement.
three how do i make sure that the records are committed thanks for the
help, i truly appreciate it.
 

Re:batch move to query help


On Tue, 27 Oct 1998 22:05:50 -0500, Bill Stockey

Quote
<Bill_Stoc...@ao.uscourts.gov> wrote:
>On the advice of several here, i am using a query component to try and
>insert record from one table into another. I have a query component on
>the form with nothing in its tstrings. i use the following code when a
>radio button is checked.
>begin
>if radiobutton2.checked then
>try
>if active then
>query1.close;
>if not query1.prepared then
>query1.prepare;
>query1.sql.add('insert into contract.db(awardnumber) select awardnumber
>from procurement.db') ;
>query1.ExecSQL;
>  query1.open;
>I get a unexpected end of command line one error, does this statement
>need to be in the tstrings(itried it and it didn't seem to work)

You have multiple questions, and multiple problems. The basic most question
that must be asked is what version of Delphi you are using. If you are
using Delphi 1, your SQL statement will never work. The 16-bit BDE that
came with Delphi 1 did not support subqueries.

Assuming you are using Delphi 2 or above, the SQL statement looks
syntactically correct. The error could just have to do with how it is being
constructed (specifically, whitespace between tokens) or how the table
names are expressed. For the former, ensure that there is at least one
space between tokens or put the elements of the statement on different
lines in the TQuery.SQL property. For the latter, if you are going to use a
filename extension in the table reference, you should surround the
reference with quotation marks.

  if radiobutton2.checked then
    try
      with Query1 do begin
        if active then Close;
        if not Prepared then Prepare;
        with SQL do begin
          Add('INSERT INTO "contract.db"');
          Add('(awardnumber)');
          Add('SELECT awardnumber FROM "procurement.db"') ;
        end; { with SQL }
        ExecSQL;
      end; {with Query1 }
    except
      ...
    end; { try }

Your calling the Open method of the TQuery after calling ExecSQL (in your
original example) is both unnecessary and will generate an exception. The
Open method is only for SELECT queries. Using just ExecSQL is sufficient
for this SQL statement (an INSERT query).

Quote
>two, with the if statement will it only pull those records that are
>checked or will i have to include a while radiobutton2=checked in the
>sql statement.

Your SQL statement, as composed, will insert all rows from the table
procurement.db (with just data from its AwardNumber column). There is
nothing in the query to limit the rows transferred to the table
contract.db. Any limiting condition (based on check boxes or whatever)
would need to be in a WHERE clause for the SELECT subquery.

Quote
>three how do i make sure that the records are committed thanks for the
>help, i truly appreciate it.

As you are using Paradox (i.e., local tables), the new rows inserted via
SQL are committed. You need not explicitly commit (as in a call to the
TDatabase.Commit method) unless you are also explicitly doing transactions
(through a TDatabase component; StartTransaction, Commit, and Rollback
methods).

If you are in a multi-user environment, any dataset components on other
users' computers would need to be refreshed before the new rows would be
visible.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                 "What is success in this world? I would say
Technical Publications         it consists of four simple things: to live
INPRISE Corporation            a lot, to love a lot, to laugh a lot, and
http://www.inprise.com/delphi  from it all, to learn a lot."
                                                     -- Richard J. Needham

Re:batch move to query help


Steve, I tried this and I get an error message that says invalid use of
keyword, line 5 Insert? I am using Delphi 4 c/s Any ideas? I filled the sql
editor in the query with the same statements and when i try and make it active,
i get an error creating cursor handle I have used query in other programs
(usually a select) so i am stumped here and this transfer is a vital function.
Again, thanks for the help
Quote
Steve Koterski wrote:
> On Tue, 27 Oct 1998 22:05:50 -0500, Bill Stockey
> <Bill_Stoc...@ao.uscourts.gov> wrote:

> >On the advice of several here, i am using a query component to try and
> >insert record from one table into another. I have a query component on
> >the form with nothing in its tstrings. i use the following code when a
> >radio button is checked.
> >begin
> >if radiobutton2.checked then
> >try
> >if active then
> >query1.close;
> >if not query1.prepared then
> >query1.prepare;
> >query1.sql.add('insert into contract.db(awardnumber) select awardnumber
> >from procurement.db') ;
> >query1.ExecSQL;
> >  query1.open;
> >I get a unexpected end of command line one error, does this statement
> >need to be in the tstrings(itried it and it didn't seem to work)

> You have multiple questions, and multiple problems. The basic most question
> that must be asked is what version of Delphi you are using. If you are
> using Delphi 1, your SQL statement will never work. The 16-bit BDE that
> came with Delphi 1 did not support subqueries.

> Assuming you are using Delphi 2 or above, the SQL statement looks
> syntactically correct. The error could just have to do with how it is being
> constructed (specifically, whitespace between tokens) or how the table
> names are expressed. For the former, ensure that there is at least one
> space between tokens or put the elements of the statement on different
> lines in the TQuery.SQL property. For the latter, if you are going to use a
> filename extension in the table reference, you should surround the
> reference with quotation marks.

>   if radiobutton2.checked then
>     try
>       with Query1 do begin
>         if active then Close;
>         if not Prepared then Prepare;
>         with SQL do begin
>           Add('INSERT INTO "contract.db"');
>           Add('(awardnumber)');
>           Add('SELECT awardnumber FROM "procurement.db"') ;
>         end; { with SQL }
>         ExecSQL;
>       end; {with Query1 }
>     except
>       ...
>     end; { try }

> Your calling the Open method of the TQuery after calling ExecSQL (in your
> original example) is both unnecessary and will generate an exception. The
> Open method is only for SELECT queries. Using just ExecSQL is sufficient
> for this SQL statement (an INSERT query).

> >two, with the if statement will it only pull those records that are
> >checked or will i have to include a while radiobutton2=checked in the
> >sql statement.

> Your SQL statement, as composed, will insert all rows from the table
> procurement.db (with just data from its AwardNumber column). There is
> nothing in the query to limit the rows transferred to the table
> contract.db. Any limiting condition (based on check boxes or whatever)
> would need to be in a WHERE clause for the SELECT subquery.

> >three how do i make sure that the records are committed thanks for the
> >help, i truly appreciate it.

> As you are using Paradox (i.e., local tables), the new rows inserted via
> SQL are committed. You need not explicitly commit (as in a call to the
> TDatabase.Commit method) unless you are also explicitly doing transactions
> (through a TDatabase component; StartTransaction, Commit, and Rollback
> methods).

> If you are in a multi-user environment, any dataset components on other
> users' computers would need to be refreshed before the new rows would be
> visible.

> //////////////////////////////////////////////////////////////////////////
> Steve Koterski                 "What is success in this world? I would say
> Technical Publications         it consists of four simple things: to live
> INPRISE Corporation            a lot, to love a lot, to laugh a lot, and
> http://www.inprise.com/delphi  from it all, to learn a lot."
>                                                      -- Richard J. Needham

Re:batch move to query help


On Wed, 28 Oct 1998 18:11:55 -0500, Bill Stockey

Quote
<Bill_Stoc...@ao.uscourts.gov> wrote:
>Steve, I tried this and I get an error message that says invalid use of
>keyword, line 5 Insert? I am using Delphi 4 c/s Any ideas? I filled the sql
>editor in the query with the same statements and when i try and make it active,
>i get an error creating cursor handle I have used query in other programs
>(usually a select) so i am stumped here and this transfer is a vital function.

Oops. I bet I know what *this* problem is. The error cites line 5 of the
offending SQL statement and the INSERT keyword *should be on line 1. This
can only mean that you have been adding this statement onto an
already-existing statement, resulting in _two_ statements being in the
TQuery.SQL property at the time it is executed.

I erroneously left one necessary line out of my example: a call to the
string list object method Clear. This removes all currently existing
content of a string list object (of which the TQuery.SQL property is one).
This call needs to be made prior to calling the Add method to add more/new
lines.

  if radiobutton2.checked then
    try
      with Query1 do begin
        if active then Close;
        if not Prepared then Prepare;
        with SQL do begin
          Clear; { <-- Ommitted line! }
          Add('INSERT INTO "contract.db"');
          Add('(awardnumber)');
          Add('SELECT awardnumber FROM "procurement.db"') ;
        end; { with SQL }
        ExecSQL;
      end; {with Query1 }
    except
      ...
    end; { try }

//////////////////////////////////////////////////////////////////////////
Steve Koterski                 "What is success in this world? I would say
Technical Publications         it consists of four simple things: to live
INPRISE Corporation            a lot, to love a lot, to laugh a lot, and
http://www.inprise.com/delphi  from it all, to learn a lot."
                                                     -- Richard J. Needham

Re:batch move to query help


Thanks, I will let you know if this works. I appreciate the help
Quote
Steve Koterski wrote:
> On Wed, 28 Oct 1998 18:11:55 -0500, Bill Stockey
> <Bill_Stoc...@ao.uscourts.gov> wrote:

> >Steve, I tried this and I get an error message that says invalid use of
> >keyword, line 5 Insert? I am using Delphi 4 c/s Any ideas? I filled the sql
> >editor in the query with the same statements and when i try and make it active,
> >i get an error creating cursor handle I have used query in other programs
> >(usually a select) so i am stumped here and this transfer is a vital function.

> Oops. I bet I know what *this* problem is. The error cites line 5 of the
> offending SQL statement and the INSERT keyword *should be on line 1. This
> can only mean that you have been adding this statement onto an
> already-existing statement, resulting in _two_ statements being in the
> TQuery.SQL property at the time it is executed.

> I erroneously left one necessary line out of my example: a call to the
> string list object method Clear. This removes all currently existing
> content of a string list object (of which the TQuery.SQL property is one).
> This call needs to be made prior to calling the Add method to add more/new
> lines.

>   if radiobutton2.checked then
>     try
>       with Query1 do begin
>         if active then Close;
>         if not Prepared then Prepare;
>         with SQL do begin
>           Clear; { <-- Ommitted line! }
>           Add('INSERT INTO "contract.db"');
>           Add('(awardnumber)');
>           Add('SELECT awardnumber FROM "procurement.db"') ;
>         end; { with SQL }
>         ExecSQL;
>       end; {with Query1 }
>     except
>       ...
>     end; { try }

> //////////////////////////////////////////////////////////////////////////
> Steve Koterski                 "What is success in this world? I would say
> Technical Publications         it consists of four simple things: to live
> INPRISE Corporation            a lot, to love a lot, to laugh a lot, and
> http://www.inprise.com/delphi  from it all, to learn a lot."
>                                                      -- Richard J. Needham

Other Threads