Page 1 of 1

Index error message

PostPosted: Sat Apr 27, 2013 8:02 am
by BRobinsonS
I have a membership database program.
I have created a unique index for member number.
What I need is an elegant error message when I try to enter a duplicate member number.
i.e. "Member Number exists. etc"
The Help file refers to Error variable, but not sure how to use it.

Any suggestions?

Re: Index error message

PostPosted: Sat Apr 27, 2013 8:31 am
by smokinbanger
Probably the easiest way to do this is set the unique index field to be AutoInc type. Every time a new record is added to the database it will auto increment the number. Even if a reord in deleted it will not reuse the number.
Example If creating the table:
Code: Select all
dbpCreateTable "DBID" "Table1" "RecordID AutoInc PrimaryKey"

Example to add a AutInc field(MAY NOT WORK ON ALL DATABASES!)
Code: Select all
dbpAddField "DBID" "Table1" "RecordID" "AutoInc PrimaryKey"

Re: Index error message

PostPosted: Sat Apr 27, 2013 8:35 am
by Gaev
Brian:
What I need is an elegant error message when I try to enter a duplicate member number.
i.e. "Member Number exists. etc"

Error:
A record with this Member Number already exists
Duplicate Member Numbers are not allowed
Please choose another Member Number and then try again


The Help file refers to Error variable, but not sure how to use it.
Take a look at the dbpShowErrors command ... the note about ...

- the variable [dbp_Error]
- the subroutine called DBPro_OnError

Re: Index error message

PostPosted: Sat Apr 27, 2013 8:50 am
by smokinbanger
Gaev wrote:- the subroutine called DBPro_OnError

Never noticed that one, Gaev! Halfway fills this suggestion: http://neosoftware.com/community/viewtopic.php?f=5&t=19954

Re: Index error message

PostPosted: Sun Apr 28, 2013 8:59 am
by BRobinsonS
I am trying to eliminate this error message when I try to put in a duplicate member number:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.


The following doesn't create a Error so Alertbox never happens.

dbpShowErrors "No"
dbpSaveEdits "AddrBook" "Contacts"
If "[dbpError]" ">" ""
AlertBox "Sorry" "Duplicate MemNum found."
EndIf
dbpShowErrors "Yes"

As suggested here and in help using Subroutine :
DBPro_OnError
PlayTone "440"
AlertBox "Sorry" "Duplicate MemNum found."
Return

What am I doing wrong or how to solve this?

Re: Index error message

PostPosted: Sun Apr 28, 2013 9:27 am
by Gaev
Brian robinson:

One man's Error is another man's Warning !!! ... perhaps the database does not consider such a request to be an error :shock:

Following the saying that "An ounce of Prevention is better than a pound of Cure", I would suggest that you "check for existence of a duplicate number" BEFORE you "request the new record to be added" i.e. do a Query on the Table for the intended number ... if the Query Count is zero, you are good to go.

Another way to prevent such duplicates might be to Query all records (sorted in descending order of the number field and limit of 1) ... that would give you the highest used number ... add one to it and then add the new record ... of course, you have to allow for the very first such request ... i.e. no records in the Table ... in which case you use number 1.

Finally, in your code, see what happens without the ...

dbpShowErrors "No"

...command ... perhaps you need it to be on ("Yes") ... and the subroutine then causes the message to be suppressed.

Re: Index error message

PostPosted: Sun Apr 28, 2013 10:47 am
by BRobinsonS
Gaev,

Your first suggestion is excellent:
"check for existence of a duplicate number" first.

My program doesn't generate the membership number. Sorry I wasn't clear here.
Your second suggestion doesn't work for me because Member Number is assigned by the Head Organization.

I'll try your third suggestion first. Just out of curiosity, but I still like your first suggestion.

Thanks for the help (again),
Brian