Auto-increment field rule fails when writing to a table in code

[alexmuir]

I have a table Custlab with a field Rateid set as auto-increment. In a script I have this:


Tsr = table.open(�StdRates�)

Tcl = table.open(�Custlab�)


While .not. tsr.fetch_eof()

Tcl.enter_begin()

Tcl.Rate = tsr.Rate

Tcl.Enter_end()



Tsr.fethc_next()

End while

Even if you replace Tcl.enter_begin with Tcl.enter_begin(.T.) and the same with Tcl.enter_end, the auto-increment field rule is not followed. The workaround is to set the Rateid manually.

Has anyone else hit this?

Many thanks

Alex

[Tom Cone Jr]

Re: Auto-increment field rule fails when writing to a table in code

Nope. Seems to be working fine here. See the attached example.

Perhaps there's something wrong with your script?

In my own work, for example, I'd say

tcl.enter_end(.t.)

rather than

tcl.enter_end()

-- tom

[enstorms]

Re: Auto-increment field rule fails when writing to a table in code

Alex,
Yes, I have. I usually use a numeric for auto-increment field and have the "Simple Default" (in field rules) set to 1 and set at beginning of entry. However, sometimes a value of "0" gets entered in the first record. I use a "work around" to get: recs = tbl.records_get(). Then when adding records I use:

if recs = 0 then
    tbl.auto_field = 1
    recs = 1
end if

I may be doing something wrong in the field rules, but it seems to work for me.
Ernie

[Mike Wilson]

Re: Auto-increment field rule fails when writing to a table in code

I have had problems with autoincrement using a numeric field as you describe. I always make it a character field and have no problems. I do not believe a the value in a field given the auto incrment field rule will ever be used for mathmatical calculations, and therefore do not believe there is any reason not to make it a character field.

[metadyne]

Re: Auto-increment field rule fails when writing to a table in code

My auto-increment fields are also character as I have read that this is more reliable.

Here is a sample of the code I use to write a set of standard labour rates on one table to each customer who uses the rates as opposed to special rates:

parentform.Commit()
tcl = table.open("custlab")
tcl.delete_range("Stdlab = .T.")

dim nRateid as N
tcl.fetch_last()
nRateid = tcl.Rateid

tl = table.open("labchg")

tc = table.open("customer")
query.options = ""
query.order = "recno()"
query.filter = "Stdlab = .T."
qc = tc.query_create()

if qc.records_get() > 0 then
while .not. tc.fetch_eof()
tl.fetch_first()
while .not. tl.fetch_eof()
tcl.enter_begin(.T.)
tcl.Rateid = nRateid
tcl.Custid = tc.Custid
tcl.Chgrate = tl.Chgrate
tcl.Flatrate = tl.Flatrate
tcl.Details = tl.Details
tcl.enter_end(.T.)

tl.fetch_next()
nRateid = nRateid + 1
end while

tc.fetch_next()
end while
end if

tc.query_detach()
tc.close()
tl.close()
tcl.close()

As you can see I tried the .T. option on table.enter_begin/end in the hope that it would follow the field rules for the auto-increment field, but it makes no difference. I also 2 other instances with different tables where the same is true.

[Tom Cone Jr]

Re: Auto-increment field rule fails when writing to a table in code

John, it's very hard for these old eyes to decipher your code without indented formatting. The message board supports code tags that greatly enhance readibility.

Having said that I suggest you submit sample data. Your script may be failing because of timing issues. Code that requires interaction with the hard drive will run in its own process, while the script keeps running. i.e. the script does not pause while the range is deleted, or the query is run... windows multi-tasks... if the logic of your script assumes that the script itself will pause after each statement resuming again only after all disk activity is finished then your logic is based on a false assumption.

[metadyne]

Re: Auto-increment field rule fails when writing to a table in code

Sorry the original text I pasted in was indented so I don't know why the internet lost it.

I run larger scripts using functions but not something this small and simple.

Are you saying that I should use functions even at this simple level? I understand that some code does not run in the order it is written but where do you stop using functions? You could use them every few lines otherwise...

[Al Buchholz]

Re: Auto-increment field rule fails when writing to a table in code

Sorry the original text I pasted in was indented so I don't know why the internet lost it.

As Tom noted, use the code tag option for highlighted text, or the 'internet' will lose the formatting....

[Tom Cone Jr]

Re: Auto-increment field rule fails when writing to a table in code

John, the code tags are accessible when using the advanced reply editor. Click the Go Advanced button to open the advanced message reply editor.

I can't say that functions will solve your problem here. I think it's likely. Are you running the code over a network? Is there much latency in the disk activity? Do you get the same results running locally?

Does my simple example also fail on your machine?

What are you doing differently?

[gmeredith17]

Re: Auto-increment field rule fails when writing to a table in code

You could use them every few lines otherwise...

Why would that be a problem? This method of programming has been recomended by Ira and others many times and seems to be very sensible advice especially where there could be timing issues.

[csda1]

Re: Auto-increment field rule fails when writing to a table in code

Hi Geoff,

Why would that be a problem? This method of programming has been recomended by Ira and others many times and seems to be very sensible advice especially where there could be timing issues.

Despite the fact that functions are a good idea (see my CSDA tips page), and help keep things in sync, I doubt that's an issue here. Functions help keep things in order, by forcing the current thread (session) to wait for the function to complete. Some things can start new threads (e.g. opening a form/browse), but most code does not.

What is important here is where is the code invoked from? E.g. from a button press of a form, a modeless dialog box, etc. What is the table that the form/browse (if any) is based on? Is the record being updated currently in change mode? The context and what has locks on records is important here.

[Tom Cone Jr]

Re: Auto-increment field rule fails when writing to a table in code

Ira, I share your concern about things John hasn't mentioned. It's not clear exactly what aspect of his script is failing. I've assumed the records are being entered but that the auto-inc fields are empty in each new record. This assumption may be wrong altogether. He hasn't described the problem in detail. It's also possible that his method of "seeding" the first record in the table might be the issue. Can't say without sample data and the forms, so we can run it in context.

Perhaps he should run the script in debug mode. Because that runs one statement at a time, perhaps that will give us more clues... It's not clear he attempted debug mode yet.

--tom

[metadyne]

Re: Auto-increment field rule fails when writing to a table in code

OK here is the script again (the indents don't seem to work in the preview so I used dots instead)

parentform.Commit()
tcl = table.open("custlab")
tcl.delete_range("Stdlab = .T.")

dim nRateid as N
tcl.fetch_last()
nRateid = tcl.Rateid

tl = table.open("labchg")

tc = table.open("customer")
query.options = ""
query.order = "recno()"
query.filter = "Stdlab = .T."
qc = tc.query_create()

if qc.records_get() > 0 then
...while .not. tc.fetch_eof()
......tl.fetch_first()
......while .not. tl.fetch_eof()
.........tcl.enter_begin(.T.)
.........tcl.Rateid = nRateid
.........tcl.Custid = tc.Custid
.........tcl.Chgrate = tl.Chgrate
.........tcl.Flatrate = tl.Flatrate
.........tcl.Details = tl.Details
.........tcl.enter_end(.T.)

.........tl.fetch_next()
.........nRateid = nRateid + 1
......end while

...tc.fetch_next()
...end while
end if

tc.query_detach()
tc.close()
tl.close()
tcl.close()

The script is run from the save button in the form containing the standard labour rates. I have run the code in debug mode and can confirm that the auto-increment field in the table being written to remains at 0 each time a record is written.

[metadyne]

Re: Auto-increment field rule fails when writing to a table in code

Just to answer some of the questions Ira asks

The script is run from the form containing the standard labour rates table only. The table is saved first before the script is run. The problem is that the auto-increment field in the table to which I am writing is not being filled unless I fill it myself using the variable nRateid in the script.

If I enter a record manually in the destination table, the auto-increment field is filled correctly.

The app is being run on a standalone PC where the testing is done and on a network live. The network uses Windows 2003 SBS and Windows XP workstations to SP2 with enough RAM on each. I do not know about latency.

[gmeredith17]

Re: Auto-increment field rule fails when writing to a table in code

John,

When you reply to a message if you press the go advanced button at the bottom of the page it will display a text editor with advanced options. If you copy and paste your code into it and then while it is still highlighted click on the code button of the advanced editor (#) it will wrap code tags around the script you have inserted. Then if you click on preview post you will see that your text formatting is still in place. Once you are happy with the wording and layout of your reply press the submit reply button. This will save you the time of having to add dots to keep the indents.

Geoff