FoxPro Help and Support - Execute "INSERT INTO" command against Remote View

Author

Execute "INSERT INTO" command against Remote View

Mike

2005-10-27, 8:34 am

Hi. I'm trying to execute an "INSERT INTO" command against a remote view.
My code runs without errors, but no record is inserted into my database. My
remote connects to SQL Server. The code is below. Could someone please let
me know what I'm doing wrong here?
------------------------------------------------------------------------------------------
*routingnum, amt, and date are variables created earlier in the code
INSERT INTO TableOne (CountryOrigin, RoutingNum, Amt, TransDate, LocalSt)
VALUES ("USA", routingnum, amt, date, "NZLNWD1")
------------------------------------------------------------------------------------------

Thanks.

Dan Freeman

2005-10-27, 8:34 am

Are you calling TableUpdate() to commit your changes?

If not, you're just updating the local copy of the remote data.

Dan

Mike wrote:
> Hi. I'm trying to execute an "INSERT INTO" command against a remote
> view. My code runs without errors, but no record is inserted into my
> database. My remote connects to SQL Server. The code is below.
> Could someone please let me know what I'm doing wrong here?
> --------------------------------------------------------------------------
----------------
> *routingnum, amt, and date are variables created earlier in the code
> INSERT INTO TableOne (CountryOrigin, RoutingNum, Amt, TransDate,
> LocalSt) VALUES ("USA", routingnum, amt, date, "NZLNWD1")
> --------------------------------------------------------------------------
----------------
>
> Thanks.

Lee Mitchell

2005-10-27, 8:34 am

Hi Mike:

Does adding a Tableupdate=.t. after the INSERT command help?

I hope this helps.

This posting is provided "AS IS" with no warranties, and confers no rights.

Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell

*-- VFP9 HAS ARRIVED!! --*
Read about all the new features of VFP9 here:
http://msdn.microsoft.com/vfoxpro/

*--Purchase VFP 9.0 here:
http://www.microsoft.com/PRODUCTS/i...cid=54787e64-52
69-4500-8bf2-3f06689f4ab3&type=ovr

Keep an eye on the product lifecycle for Visual FoxPro here:
http://support.microsoft.com/gp/lifeselectindex
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retired Sept. 30th, 2003

>Hi. I'm trying to execute an "INSERT INTO" command against a remote view.

>My code runs without errors, but no record is inserted into my database.
My
>remote connects to SQL Server. The code is below. Could someone please
let
>me know what I'm doing wrong here?
>---------------------------------------------------------------------------
---------------
>*routingnum, amt, and date are variables created earlier in the code
>INSERT INTO TableOne (CountryOrigin, RoutingNum, Amt, TransDate, LocalSt)
>VALUES ("USA", routingnum, amt, date, "NZLNWD1")
----------------------------------------------------------------------------
--------------

>Thanks.

Mike

2005-10-27, 8:34 am

Thank you both for the prompt responses. I'm looking into the TableUpdate
command now.

"Lee Mitchell" wrote:

> Hi Mike:
>
> Does adding a Tableupdate=.t. after the INSERT command help?
>
> I hope this helps.
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> Sincerely,
> Microsoft FoxPro Technical Support
> Lee Mitchell
>
> *-- VFP9 HAS ARRIVED!! --*
> Read about all the new features of VFP9 here:
> http://msdn.microsoft.com/vfoxpro/
>
> *--Purchase VFP 9.0 here:
> http://www.microsoft.com/PRODUCTS/i...cid=54787e64-52
> 69-4500-8bf2-3f06689f4ab3&type=ovr
>
> Keep an eye on the product lifecycle for Visual FoxPro here:
> http://support.microsoft.com/gp/lifeselectindex
> - VFP5 Mainstream Support retired June 30th, 2003
> - VFP6 Mainstream Support retired Sept. 30th, 2003
>
>
> My
> let
> ---------------
> ----------------------------------------------------------------------------
> --------------
>
>
>
>

Mike

2005-10-27, 8:34 am

Gentlemen,
I've added TableUpdate command but I'm still missing something because the
inserts are not commited to the database. Here's what I currently have:
------------------------------------------------------------------------------------------
SET MULTILOCKS ON
CURSORSETPROP('Buffe
ring', 5, 'TableOne')
INSERT INTO TableOne (CountryOrigin, RoutingNum, Amt, TransDate, LocalSt)
VALUES ("USA", routingnum, amt, date, "NZLNWD1")
TABLEUPDATE=.T.
------------------------------------------------------------------------------------------
My Remote View (TableOne) is the same name as it's datasource (TableOne).
Must I somehow further specify that the INSERT should occur against the
datasource?

Thanks for the help. I do appreciate any assistance you can provide.

"Lee Mitchell" wrote:

> Hi Mike:
>
> Does adding a Tableupdate=.t. after the INSERT command help?
>
> I hope this helps.
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> Sincerely,
> Microsoft FoxPro Technical Support
> Lee Mitchell
>
> *-- VFP9 HAS ARRIVED!! --*
> Read about all the new features of VFP9 here:
> http://msdn.microsoft.com/vfoxpro/
>
> *--Purchase VFP 9.0 here:
> http://www.microsoft.com/PRODUCTS/i...cid=54787e64-52
> 69-4500-8bf2-3f06689f4ab3&type=ovr
>
> Keep an eye on the product lifecycle for Visual FoxPro here:
> http://support.microsoft.com/gp/lifeselectindex
> - VFP5 Mainstream Support retired June 30th, 2003
> - VFP6 Mainstream Support retired Sept. 30th, 2003
>
>
> My
> let
> ---------------
> ----------------------------------------------------------------------------
> --------------
>
>
>
>

Jack Jackson

2005-10-27, 8:34 am

You did not add a TABLEUPDATE command (there is no such command), you
set a variable named TABLEUPDATE. TABLEUPDATE is a function call:

=TABLEUDATE(.T., .T., "tableone")

or

SELECT tableone
=TABLEUPDATE(.T., .T.)

If that isn't it:

What is the SQL statement for the remote view?

Is the remote view set as updatable with the appropriate primary
key(s) specified?

This won't keep the record from being inserted, but you shouldn't use
unqualified names i the VALUES clause that match field names in the
cursor you are inserting into - at the time the VALUES clause is
evaluated, the current work area is the cursor you are inserting into,
so you will insert default values for 'routingnum' and 'amt'. You
need to qualify them by the cursor name they are in if they are in a
cursor, or m. if they are variables.

On Mon, 3 Oct 2005 14:59:05 -0700, "Mike"
<Mike@discussions.microsoft.com> wrote:
[color=darkred]
>Gentlemen,
>I've added TableUpdate command but I'm still missing something because the
>inserts are not commited to the database. Here's what I currently have:
>------------------------------------------------------------------------------------------
>SET MULTILOCKS ON
> CURSORSETPROP('Buffe
ring', 5, 'TableOne')
>INSERT INTO TableOne (CountryOrigin, RoutingNum, Amt, TransDate, LocalSt)
>VALUES ("USA", routingnum, amt, date, "NZLNWD1")
>TABLEUPDATE=.T.
>------------------------------------------------------------------------------------------
>My Remote View (TableOne) is the same name as it's datasource (TableOne).
>Must I somehow further specify that the INSERT should occur against the
>datasource?
>
>Thanks for the help. I do appreciate any assistance you can provide.
>
>"Lee Mitchell" wrote:
>

Mike

2005-10-27, 8:34 am

It turns out I needed to enable "Send SQL Updates" in the remote view. After
doing this and implementing the previous suggested logic, the updates work.

Thank you all very much for your help!!

"Jack Jackson" wrote:

> You did not add a TABLEUPDATE command (there is no such command), you
> set a variable named TABLEUPDATE. TABLEUPDATE is a function call:
>
> =TABLEUDATE(.T., .T., "tableone")
>
> or
>
> SELECT tableone
> =TABLEUPDATE(.T., .T.)
>
> If that isn't it:
>
> What is the SQL statement for the remote view?
>
> Is the remote view set as updatable with the appropriate primary
> key(s) specified?
>
> This won't keep the record from being inserted, but you shouldn't use
> unqualified names i the VALUES clause that match field names in the
> cursor you are inserting into - at the time the VALUES clause is
> evaluated, the current work area is the cursor you are inserting into,
> so you will insert default values for 'routingnum' and 'amt'. You
> need to qualify them by the cursor name they are in if they are in a
> cursor, or m. if they are variables.
>
> On Mon, 3 Oct 2005 14:59:05 -0700, "Mike"
> <Mike@discussions.microsoft.com> wrote:
>
>

Sponsored Links