Board index » delphi » A SQL problem

A SQL problem

I am trying to fill a Table with rows from another table. I am using DBE a
dBASE. I use a TQuery component with the following SQL statements in the SQL
property:

INSERT INTO "New.DBF"
select * from "Old.DBF"

I get "Error creating cursor handle". Nevetheless, if I look in the folder
where the table is stored, the "New" table looks updated with the records
from the "Old", though it does lose its indexes. I think I am doing right,
but there is obviously a problem I don't know of.
Does anyone ?

Is there any other way to do the task ? What if I wanted to "move" rather
than "copy" records from one table to another, following certain user
specified conditions ?

 

Re:A SQL problem


On Mon, 24 Jun 2002 20:58:09 GMT, "Franco Jommi"

Quote
<franco.jom...@tin.it> wrote:
>I am trying to fill a Table with rows from another table. I am using DBE a
>dBASE. I use a TQuery component with the following SQL statements in the SQL
>property:

>INSERT INTO "New.DBF"
>select * from "Old.DBF"

Can you post your code where you do this?

Quote

>I get "Error creating cursor handle". Nevetheless, if I look in the folder
>where the table is stored, the "New" table looks updated with the records
>from the "Old", though it does lose its indexes. I think I am doing right,
>but there is obviously a problem I don't know of.
>Does anyone ?

>Is there any other way to do the task ? What if I wanted to "move" rather
>than "copy" records from one table to another, following certain user
>specified conditions ?

Do this in two steps. First copy the records as above, then run a
DELETE FROM query on the origination table.

hth,
Dan

Quote

Re:A SQL problem


I only have a form and a  TQuery component on it. The TQuery SQL property
has the sql statement:
INSERT INTO "New.DBF"
select * from "Old.DBF"
At design time, I attempt to set the TQuery Active property to true and the
consequent execution of the SQL statement fires the error message "Error
creating cursor handle".

<DanB [at] CMDCsystems.com (Dan Brennand)> ha scritto nel messaggio
news:3d178a04.18464312@news.dimensional.com...

Quote
> On Mon, 24 Jun 2002 20:58:09 GMT, "Franco Jommi"
> <franco.jom...@tin.it> wrote:

> >I am trying to fill a Table with rows from another table. I am using DBE
a
> >dBASE. I use a TQuery component with the following SQL statements in the
SQL
> >property:

> >INSERT INTO "New.DBF"
> >select * from "Old.DBF"

> Can you post your code where you do this?

> >I get "Error creating cursor handle". Nevetheless, if I look in the
folder
> >where the table is stored, the "New" table looks updated with the records
> >from the "Old", though it does lose its indexes. I think I am doing
right,
> >but there is obviously a problem I don't know of.
> >Does anyone ?

> >Is there any other way to do the task ? What if I wanted to "move" rather
> >than "copy" records from one table to another, following certain user
> >specified conditions ?

> Do this in two steps. First copy the records as above, then run a
> DELETE FROM query on the origination table.

> hth,
> Dan

Re:A SQL problem


On Wed, 26 Jun 2002 14:07:48 GMT, "Franco Jommi"

Quote
<franco.jom...@tin.it> wrote:
>I only have a form and a  TQuery component on it. The TQuery SQL property
>has the sql statement:
>INSERT INTO "New.DBF"
>select * from "Old.DBF"
>At design time, I attempt to set the TQuery Active property to true and the
>consequent execution of the SQL statement fires the error message "Error
>creating cursor handle".

The problem is that TQuery.Active := true (or the equivalent
TQuery.Open command) doesn't work with an INSERT query, because they
don't return a result set. There is no dataset to create the curson
on.

You will have to test this by running your program. Add a button to
the form and double click it. This will generate the code for the
OnClick event handler, which you will fill in. In the handler, add the
statement to execute the insert query:
    myQuery.execSQL;

you will need to have something like a DBgrid to display the resulting
table contents, or use DatabaseDesktop to look at it.

HTH,
Dan

Re:A SQL problem


Thanks. I am going to try what you suggest.

<DanB [at] CMDCsystems.com (Dan Brennand)> ha scritto nel messaggio
news:3d1a463c.21716562@news.dimensional.com...

Quote
> On Wed, 26 Jun 2002 14:07:48 GMT, "Franco Jommi"
> <franco.jom...@tin.it> wrote:

> >I only have a form and a  TQuery component on it. The TQuery SQL property
> >has the sql statement:
> >INSERT INTO "New.DBF"
> >select * from "Old.DBF"
> >At design time, I attempt to set the TQuery Active property to true and
the
> >consequent execution of the SQL statement fires the error message "Error
> >creating cursor handle".

> The problem is that TQuery.Active := true (or the equivalent
> TQuery.Open command) doesn't work with an INSERT query, because they
> don't return a result set. There is no dataset to create the curson
> on.

> You will have to test this by running your program. Add a button to
> the form and double click it. This will generate the code for the
> OnClick event handler, which you will fill in. In the handler, add the
> statement to execute the insert query:
>     myQuery.execSQL;

> you will need to have something like a DBgrid to display the resulting
> table contents, or use DatabaseDesktop to look at it.

> HTH,
> Dan

Other Threads