charles
Posts: 59
|
| Posted: 10/16/2006, 9:27 AM |
|
I have two tables,accounts and transactions in my DB.
There is a relationship between these tables that prevents an account_no from being deleted once there is a record of it in the transactions table.In my edit account page when a user attempts to delete an account with transaction records, the DB calls up a very unfriendly error message.
How can i validate using ASP so that the form can return a more user-friendly message.
I am thinking in line of CCDlookup that will look up the transactions table and return an error once there is an instance of the account_no the user is attempting to delete.
Regards,
Charles
|
 |
|
gesto
Posts: 42
|
| Posted: 10/17/2006, 6:13 AM |
|
What database are you using?
Many databases let you customize you errors messages, and it is much more recomended than codes at the application.
|
|
|
|
kevind
Posts: 251
|
| Posted: 10/17/2006, 10:52 AM |
|
Hi,
I just worked on similar 'friendlier' thing yesterday.
Do this, make sure Validation for the form record is 'on'.
Add code in the On Validate Event:
Dim ChildTransactionCount
ChildTransactionCount=CCDLookup("count(*)","ChildTableNameHere","ParentID=".....rest of syntax for this function....
if ChildTransactionCount>0 then
MyForm.Errors.addError("There are child transactions - cannot delete parent account")
end if
=========
The above will present an error at the top of the Record Form and prevent deleting the record. You can make the text anything you want and add a 2nd one if needed for two lines of error msg text.
kd
_________________
thanks
Kevin
======================
CCS 3.2.x / ASP / MS Access /mySQL / PHP / jQuery / jQuery UI / jQuery Mobile
|
|
|
|
charlesg
|
| Posted: 10/17/2006, 8:45 PM |
|
Gesto,I am using MS SQL Server.
Kevind, the part of the CCDlookup function you ommited is where i am actually struggling with
This is the code i have that is not working
Dim transactionscount
transactioncount=CCDlookup("count(*)","transactions","account_no=???this is the area of i can't figure,DBconnection1)
if transactionscount>0 then
accounts.errors.adderrror("You cannot delete an account head once an instance is in the General ledger!")
Please kindly help with the remaining part of this function
Regards,
charles
|
|
|
|
gesto
Posts: 42
|
| Posted: 10/18/2006, 10:06 AM |
|
So you can use a trigger to solve your problem
ask me in msn or skype that i give you more instructions
msn:felipesilva012@hotmail.com
skype: felipe.sabino
Bye
|
|
|
|
kevind
Posts: 251
|
| Posted: 10/24/2006, 1:05 PM |
|
Quote charlesg:
Gesto,I am using MS SQL Server.
Kevind, the part of the CCDlookup function you ommited is where i am actually struggling with
This is the code i have that is not working
Dim transactionscount
transactioncount=CCDlookup("count(*)","transactions","account_no=???this is the area of i can't figure,DBconnection1)
if transactionscount>0 then
accounts.errors.adderrror("You cannot delete an account head once an instance is in the General ledger!")
Please kindly help with the remaining part of this function
Regards,
charles
are you still looking for an answer ?
let me know
kevind
_________________
thanks
Kevin
======================
CCS 3.2.x / ASP / MS Access /mySQL / PHP / jQuery / jQuery UI / jQuery Mobile
|
|
|
|
charles
Posts: 59
|
| Posted: 10/25/2006, 8:03 AM |
|
Yes,kevind.i am still looking for an answer.
regards,
charles
|
|
|
|
kevind
Posts: 251
|
| Posted: 10/25/2006, 8:09 AM |
|
Ok,
here you go:
Assuming you have a field in the Form Record called 'AccountNo' and in the DB it is a text field.
transactioncount=CCDlookup("count(*)","transactions","account_no="& CCToSQL(MyFormName.AccountNo.value, ccsText),DBMyConnectionName)
- if AccountNo is integer use ccsInteger instead of ccsText
- for the DB connection (3rd part of CCDlookup function) put the connection name that you are using in the Form Record and include DB in front of the name
That should do it. Please let me know if this helps.
kd
_________________
thanks
Kevin
======================
CCS 3.2.x / ASP / MS Access /mySQL / PHP / jQuery / jQuery UI / jQuery Mobile
|
|
|
|
charles
Posts: 59
|
| Posted: 10/26/2006, 9:57 AM |
|
Thanks kevind.I really appreciate your help.
The code works fine.
Once again thanks for your willingness to share.
Regards,
charles
|
|
|
|
ducati996guy
Posts: 46
|
| Posted: 10/26/2006, 6:24 PM |
|
Hi Guys,
Glad you got a solution, we do it slightly differently over here.
We use a ccdlookup as described above to count for matching related records, but put it in the before show event for the grid. Then if there are related records, we hide the delete button and give the user the message "Delete disabled because blah blah" and a link to the related records. This means they can never delete as there is no delete button.
users seem to like it and is easy to implement. Also pro-active instead of reactive.
Just thought i would share an alternative method.
Regards
David
_________________
"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former."
- Albert Einstein (1879-1955)
www.CustomerCommunity.com.au
|
|
|
|
charles
Posts: 59
|
| Posted: 10/28/2006, 9:48 AM |
|
Quote :users seem to like it and is easy to implement. Also pro-active instead of reactive.
Absolutely! i agree with you.
For the benefit of anyone interested in the David's way,this is how i implemented it for my project and it works fine.
dim sentaccount
dim counttrans
sentaccount=CCGetparam("account_no",empty)
counttrans=CCDlookUP("count(*)","transactions","account_no="&CCToSQL(sentaccount,ccsinteger),DBconnection1)
accounts.label1.value=countrans
if accounts.label1.value>0 then
accounts.Button_delete.visible=false
End if
if accounts.label1.value>0 then
label2.visible=true
Else
label2.visible=false
End if
label2.value="The Delete button has been disabled for this account because there are instances of this account has been posted to the GL"
Thanks david for teaching us something new and good.
regards,
charles
|
|
|
|
|
 |
|
|
|
