SQL Insert into Remote Views

i have a data entry form that can add new records to a remote view to a ms
sql table

i can't make any additional changes to the new record because the identity
column is 0

is there a way to get the identity column to be populated without refreshing
the view? or atleast have it returned from the inserting process?

the record shows up right away on the server with its identity column set,
but its isnt in the view...

help? suggestions?

Anders
Fri Dec 29 11:20:55 CST 2006

There are SQL Server functions for this:
IDENT_CURRENT('tablename'): Returns the last identity value generated for a
specified table in any session and any scope.
SCOPE_IDENTITY(): returns the last identity value generated for any table in
the current session and the current scope.
@@IDENTITY returns the last identity value generated for any table in the
current session, across all scopes.

In VFP
SQLExec(lnConhandle, "SELECT @@IDENTITY as NewId", "MyCursor")

-Anders

"CJ" <chebert@rvmags.com> skrev i meddelandet
news:%23DH45C2KHHA.4244@TK2MSFTNGP04.phx.gbl...
>i have a data entry form that can add new records to a remote view to a ms
>sql table
>
> i can't make any additional changes to the new record because the identity
> column is 0
>
> is there a way to get the identity column to be populated without
> refreshing the view? or atleast have it returned from the inserting
> process?
>
> the record shows up right away on the server with its identity column set,
> but its isnt in the view...
>
> help? suggestions?
>
>

CJ
Fri Dec 29 11:58:09 CST 2006

thanks anders....

i am familiar with using SCOPE_IDENTITY(), but can i set the value of an
identity column in my cursor with the value returned or will that generate
an error?

CJ
Fri Dec 29 11:59:35 CST 2006

also...

CJ
Fri Dec 29 13:21:04 CST 2006

am i missing something or.....

ok 1) table on sql server
2) remote view in dbc

it seems i can only make one change at a time to the view and have the
changes saved without requrying the data? is this right???

open data entry/editing form

a) changed a users name
saved the changes (begin tran, tableupdate, end tran)
change shows up in grid
move to another record
move back
change is still there

b) changed another field
saved the changes (begin tran, tableupdate, end tran)
change shows up in grid
move to another record
move back
change not there anymore

why is this?

CJ
Fri Dec 29 13:41:03 CST 2006

ok, i also had the where clause set to key and timestamp

made it just key and it works fine now

Anders
Fri Dec 29 16:35:03 CST 2006

VFP's BEGIN TRANSACTION - END TRANSACTION block have no effect whatsoever on
remote ODBC databases transactions. It only deals with native VFP data. For
SQL Server, see Help for SQLCOMMIT(), SQLROLLBACK( ) and SQLSETPROP().
-Anders

"CJ" <chebert@rvmags.com> skrev i meddelandet
news:%232o%23853KHHA.4000@TK2MSFTNGP06.phx.gbl...
> am i missing something or.....
>
> ok 1) table on sql server
> 2) remote view in dbc
>
> it seems i can only make one change at a time to the view and have the
> changes saved without requrying the data? is this right???
>
> open data entry/editing form
>
> a) changed a users name
> saved the changes (begin tran, tableupdate, end tran)
> change shows up in grid
> move to another record
> move back
> change is still there
>
> b) changed another field
> saved the changes (begin tran, tableupdate, end tran)
> change shows up in grid
> move to another record
> move back
> change not there anymore
>
> why is this?
>

CJ
Tue Jan 02 15:22:21 CST 2007

thanks Anders....

i have everything working, just dont like the view access methodology i have
to use when inserting a new record...

thats ok though since the migration is away from vfp commands like replace,
append, scatter, gather, etc... and use real sql stored procedures with
updates, inserts, etc...

sim
Tue Jan 02 21:30:25 CST 2007

CJ

Can give me guideline how to do INSERT/UPDATE for batch of records ?

Regards
Hoe


"CJ" <chebert@rvmags.com> wrote in message
news:O2N$XQrLHHA.960@TK2MSFTNGP04.phx.gbl...
> thanks Anders....
>
> i have everything working, just dont like the view access methodology i
> have to use when inserting a new record...
>
> thats ok though since the migration is away from vfp commands like
> replace, append, scatter, gather, etc... and use real sql stored
> procedures with updates, inserts, etc...
>
>
>

Anders
Wed Jan 03 05:00:33 CST 2007

Create an updatable remote view. Open the view, set buffering to 5 - Table
or 3 - Row, then modify rows and insert new rows into the view (which is in
effect a VFP cursor). If you have set Table buffering, call TableUpdate() to
have VFP execute the transfer to the backend. If you have set Row buffering,
the transfer is performed when you change rows in the cursor.

Another method uses SQLEXEC(connectionhandle, 'UPDATE or INSERT SQL
command' )
For batches use a loop through the VFP cursor. GetNextModified() and
GetFldState() can specify what has been changed in the VFO buffered cursor.
OldVal() and CurVal() determine if another user has also made changed to the
same row in rthe same table.

-Anders


"sim" <sim3030@streamyx.com> skrev i meddelandet
news:459b2355$1_1@news.tm.net.my...
> CJ
> > Can give me guideline how to do INSERT/UPDATE for batch of records ?
>
> Regards
> Hoe
>
>
> "CJ" <chebert@rvmags.com> wrote in message
> news:O2N$XQrLHHA.960@TK2MSFTNGP04.phx.gbl...
>> thanks Anders....
>>
>> i have everything working, just dont like the view access methodology i
>> have to use when inserting a new record...
>>
>> thats ok though since the migration is away from vfp commands like
>> replace, append, scatter, gather, etc... and use real sql stored
>> procedures with updates, inserts, etc...
>>
>>
>>
>
>