×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SQL Insert

SQL Insert

SQL Insert

(OP)
Must have used the sql insert command thousands of times but clearly doing something silly. Inserting a record with 6 fields but the _tally kept showing 0. Decided to just add one field and still no luck. If I create a new table and use the same code it works fine. If I use the append blank command it works fine on the real table.

Just telling it to insert into stations (code) values (oapp.temp). Oapp.temp is a memory variable from a class. If I try changing oapp.temp to say '998' it still fails. 'Code' field is a simple 3 character field.
Nothing unusual in the real table. No default values and no primary key.
I must be missing something very basic. Any help much appreciated.

RE: SQL Insert

INSERT updates _TALLY only if it includes a SELECT clause.

CODE --> VFP

CREATE CURSOR t1 (f1 Integer)
CREATE CURSOR t2 (f2 Integer)

INSERT INTO t2 VALUES (2)
INSERT INTO t2 VALUES (3)
INSERT INTO t2 VALUES (4)

* make sure _TALLY's value is not contaminated by previous operations
_TALLY = 0

INSERT INTO t1 VALUES (1)

MESSAGEBOX(_TALLY)

INSERT INTO t1 (f1) SELECT f2 FROM t2

MESSAGEBOX(_TALLY) 

RE: SQL Insert

(OP)
Many thanks. Guess I'm used to just checking everything's good with the tableupdate(.T.) command. Probably most of the updates occur with the select command anyway so this never came up.

RE: SQL Insert

https://docs.microsoft.com/en-us/previous-versions...) tells what influences _TALLY.

And since VFP doesn't support INSERT INTO table VALUES (valuelist1),(valuelist2),... you always would have _TALLY = 1, the only way for multiple inserts is via TABLEUPDATE forwarding new records of the buffer to the DBF or remote database or INSERT ... FROM SELECT.

I don't use _TALLY often anyway, as VFP doesn't have SET ROWCOUNT OFF to let triggers or other stored procs not change _TALLY for the highest level commands happening. You always risk getting a wrong number because of triggers doing cascading deletes or updates or doing anything else, even further inserts into totally different tables. Then _TALLY will reflect a count of changes by this and not what you wanted to count.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: SQL Insert

Of course, there are other ways of determining how many records you have inserted. The easiest is to compare the record count of the target table immedidatley before the update with its value immediately afer (but if this is a physical table [rather than a cursor] and it is shared with other users, you would have to take that into account).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: SQL Insert

You could roll your own _TALLY about DBFs when strictly applying an insert trigger to all DBFs going through a procedure keeping a count variable for each ALIAS(), ID(), _VFP.ProcessID and transaction the application code will need to reset this to 0 before doing any operation.

But of course that only works for DBFs in DBCs, free DBFs are not counting, and obviously, this doesn't work with remote data.

When counting whats done in buffers, you can iterate with GETNEXTMODFIED() and determine what's in the pipeline via GETFLDSTATE() of the deleted flag and the value of it.

I wouldn't use such complicated solutions, though. When this is all about knowing how much of a buffer was successfully processed, you have the field state functions and GETNEXTMODIFIED to see whether the buffer is cleared or something remained, you also have the error array of TABLEUPDATE().

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: SQL Insert

(OP)
Thanks to all. Always impressed and grateful for the help and knowledge of the VFP community.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close