Copy (MSSQL) is killing me - anyone had similar issues?

[Stu]

Hi Bruce/Folks,

Okay, here goes.

I've been having problems with a browse+form with COPY functionality, using MSSQL files (using aliases), on the production server.

Two things can happen:
1. Fields in the source record can be altered.
2. The destination record is exactly the same as the source record regardless of what is changed in the (copy) form.

The first one doesn't always happen.

At the moment, the second one seems to always be happening (on test server definitely, not sure about live, I got a different report this morning from a user).

TODAY:
- I created a brand new browse + copy-only form, using the base files (not aliases).
- Have got a custom filter on the new browse, but the rest is pure template driven goodness.
- In the copy form have got "always prime regardless of .." because if I don't have that ticked, an empty record is always created. Always.

With this new browse+copy form I was hopeful, thought it might have something to do with using aliases. Nope.

ISSUE: The destination record after clicking save on the copy form is exactly the same as the source record.

I'm at a loss as to how to do this up in an example, given it's sql. The Accounts (31) example copies fine. No worries. Works great.

Anyone had these issues, or something like it, with SQL?

[Stu]

Next step/issue: The PostCopy routine is not being called.

I'm thinking this is because the "Copy" action is being overridden with Change, but could be wrong about this.

Have attached a shot of my precopy routine.

Should the Ans == Net:ChangeRecord?

[attachment deleted by admin]

[Bruce]

Hi Stu,

Have you got auto-numbering on the table? If so tell me all about that. Are you using server-side auto-numbering, client side, or something else? The more you can tell me about the auto-numbering technique you are using the better...

cheers
Bruce

[Stu]

Okay, yeah cool.

Auto-numbering, on primary key in dictionary (client side). No SQL server side autoinc.

ID LONG is the field.

[Stu]

Next step: Here's a debugview with "send stage to debugview" set.

There are three sections to the screenshot.

First is what appears on loading the page.

Second is me changing the value on a field.

Third is what appears after clicking the save button on the form.

For me, and maybe I'm barking up the wrong tree, the glaring problem is that the PostCopy routine is not run (have got a debug() in there).

[attachment deleted by admin]

[Bruce]

Hi Stu,

So what I would do is check the value of the auto-numbered field, after the call to .PrimeFile (in your first screen-shot.)
At this point the auto-numbered field should be set to the new value. Any other fields in the primary key (hopefully there aren't any) will likely be blank.

This is going to create the new record at this point, and it should be a copy of the other fields as well. I guess possible problems might happen if you had other unique keys on the table - or indeed any server-side constraint that is not met at this point.

The action then changes from Copy to Change because the record has already been created. So yes, it will definitely go out through PostChange not PostCopy (by design).

(This whole process is made a lot more complex by the use of auto-numbering - when making new designed consider not using auto-numbering, but UID fields instead.)

HTH

cheers
Bruce

[Stu]

Thanks very much Bruce.

Yeah, I love UIDs, love them. In every new system I build (and have three current newish ones after this one) they are standard.

I think the most expedient (quick/lazy) way forward is for me to create a memory form and handcode the desired copy functionality.

Thanks for the help and giving of understanding.

Stu

[Bruce]

Hi Stu,

>> I think the most expedient (quick/lazy) way forward is for me to create a memory form and handcode the desired copy functionality.

that sounds like a bad idea to me, for several reasons. Much easier to debug the code that is there and see where it is going wrong. And in the long run sooo much better for the quality of your application....

cheers
Bruce

[Stu]

Bruce, you know I've been banging my head against the wall with this for a while now.

It took me a couple of hours today to get the form up and working the way I need it to.

Solved for the short term.

And weren't you saying that the Nettalk Copy functionality was built around a GUID rather than an autoinc ID .. How am I supposed to do any more than I have?

[Bruce]

hi Stu,

>> And weren't you saying that the Nettalk Copy functionality was built around a GUID rather than an autoinc ID

No, not at all. the copy button works with all systems, including auto-inc. GUIDs are much nicer though because all the hassles involved in auto-inc, and the various ways auto-inc can be implemented, simply go away.

>> How am I supposed to do any more than I have?

an example of the issue is obviously the best way, and fastest way, to a solution.

If an example is not practical for some reason then the best approach is to read the code, see what it is doing, and add trace statements to see where the actual behavior diverges from the expected behavior. All the source code is there allowing you to do this and none of it is being so clever that it's not readable. Ultimately this is all I am going to do with the example anyway...

cheers
Bruce

[Stu]

Hi Bruce,

Okay.

So, when you said -

This is going to create the new record at this point, and it should be a copy of the other fields as well. I guess possible problems might happen if you had other unique keys on the table - or indeed any server-side constraint that is not met at this point.

What kind of problems might happen if there were other unique keys in the table?

[Bruce]

you're making a copy of the record. there going to be at least 1 unique key (the primary key) and so NT generates a unique value for that.
But if you have other unique constraints on the table then you'd need to "prime" those fields to new (unique) values on the Priming tab. In other words the "copy" record has to be legal in order to be added to the database. If you have constraints like uniqueness, or maybe a date field that much = today, that sort of thing, then you need to deal with that on the priming tab.

cheers
Bruce

[Stu]

Thanks Bruce.