Michael Man
river guide Posts:152
|
10-09-2012 09:52 AM |
|
On my form design to submit 2 pieces of data into my database when click Submit button. Those two pieces are: type and group and both of the are integers.
How do I use SQL validation to check if two identical pieces of information about to submit is already in the database. If these two values are matched and already in the database, then return with an error message and no submission.
I used the Dynamic Form long time but never used the SQL validation, so I have no clue how to sue this feature at all
Thanks |
|
|
|
|
Ryan Bakerink
river guide Posts:1900
|
10-09-2012 10:13 AM |
|
Hello Michael, I would encourage you to look into our Dynamic Forms User Guide to learn how to configure a SQL Validation: http://www.datasprings.com/userguid...rGuide.pdf These following pages cover SQL Validation: 51, 124, 200 Please let me know if you have any questions. Thanks, Ryan |
|
|
|
|
Michael Man
river guide Posts:152
|
10-09-2012 12:04 PM |
|
Posted By Ryan Bakerink on 10-09-2012 12:13 PM
Hello Michael,
I would encourage you to look into our Dynamic Forms User Guide to learn how to configure a SQL Validation:
http://www.datasprings.com/userguid...rGuide.pdf
These following pages cover SQL Validation: 51, 124, 200
Please let me know if you have any questions.
Thanks,
Ryan
Hy Ryan,
Thanks for pointing me to the Document, I read it a few times and still have no clue how to do what I wanted to do there.
I mentioned about "store procedure" ...what does it mean?
|
|
|
|
|
M.Pratt
wading in the water Posts:28
|
10-24-2012 03:04 PM |
|
Michael, I have set these up and I can explain it a bit. A Stored Procedure is just an SQL query that is stored on the server. You can see them in SQL Managment studio if you expand the folder called Programmability. The reason you would call on a stored procedure is so that you are not exposing your SQL code to the public (they could use it to attack your site).
Now, to use a stored procedure to validate, you would check the box in the validation section that says "Enable custom SQL Validation script for this specific field" and in the "Validation SQL Query" section you should call the stored procedure like this: usp_validate_ins '$(InsAmt)','$(Insurance)'
usp_validate_ins is the name of my stored procedure and it's job is to compare the contents of 2 fields and return a value called IsValid with a response of 0 or 1. As you can see above, the SP is called and passes the values of my two fields InsAmt and Insurance.
I need the validator to check the values and return a 0 or 1 depending on what it finds (0 is not valid 1 is valid). I needed it to check if there was a value for InsAmt (a dollar amount) and if it was there (not blank) then the Insurance had to be selected as Waiver (customer waived the insurance). If there is an entry in InsAmt, then the user needs to pick a valid insurance code (CDI, CLI, etc.)
So I had to work out my SQL to check the entries and return the values. Below is my SP along with the leading code that creates it and stores it on the server. You just run it as a query and it makes the Stored proc.
CREATE procedure [dbo].[usp_validate_ins] (@InsAmt nvarchar(50),@Insurance nvarchar(50)) as begin if @InsAmt = '' and @Insurance = 'Waiver' select 1 as IsValid else if @InsAmt != '' and @Insurance = 'CDI' select 1 as IsValid else if @InsAmt != '' and @Insurance = 'CLI' select 1 as IsValid else if @InsAmt != '' and @Insurance = 'CI' select 1 as IsValid else if @InsAmt = '' and @Insurance = 'CDI' select 0 as IsValid else if @InsAmt = '' and @Insurance = 'CLI' select 0 as IsValid else if @InsAmt = '' and @Insurance = 'CI' select 0 as IsValid else if @InsAmt != '' and @Insurance = '' select 0 as IsValid else if @InsAmt = '' and @Insurance = '' select 0 as IsValid else if @InsAmt != '' and @Insurance != 'Waiver' select 0 as IsValid end
-------- Notice the entries: if @InsAmt = '' - This means If InsAmt is blank if @InsAmt != '' - This means If InsAmt is not blank
So, there you have some smarts in your validation. I hope it helps you out. -MP
v |
|
"Badges?....We don't need no stinking badges!" |
|
|
Ryan Bakerink
river guide Posts:1900
|
11-06-2012 12:01 PM |
|
Hello MP, Thank you for assisting Michael, this helps us out greatly. Hope you have a great day. -Ryan
|
|
|
|
|
M.Pratt
wading in the water Posts:28
|
11-30-2012 09:54 AM |
|
No problem Ryan, Just to update this method. In newer verisons of DF, I noticed that I can no longer test for a blank value. Therefore, I have made my fields use zero as a default, then test for zero values. This method works good. Example: if @InsAmt = '0' and @Insurance = 'CI' select 0 as IsValid else |
|
"Badges?....We don't need no stinking badges!" |
|
|
Joe Clohessy
skipping stones Posts:8
|
12-02-2012 07:02 PM |
|
I have the same problem. SQL Validation will not resolve the short field name token if the field is blank. Unfortunately I cannot default my field value because it shouldn't be defaulted. It is necessary sometimes to validate a field that is not required... i.e. IF the user enters a value, validate it... This appears to be a bug. Are there plans to get it fixed? |
|
|
|
|
Ryan Bakerink
river guide Posts:1900
|
12-03-2012 05:35 AM |
|
Hello Joe, I have developed a simple technique for this issue after testing. You can do a check in your SQL validation as the following: * Lets say I have an optional textbox with the shortname of "FirstName". I have a SQL validation in place to execute SQL and perform a check. Since it's optional, it's possible that a value may not be provided, my guess is that if a value isn't provided then you'll get the $(FirstName) as the exact value in your SQL statement. To workaround this you can do the following: IF '$(FirstName)' <> '' AND '$(FirstName)' NOT LIKE '%(FirstName)%' BEGIN /* Do stuff here */ END ELSE BEGIN /* Do stuff here */ END By adding the NOT LIKE '(FirstName)' this won't render the token and checks to see if the token wasn't rendered. Please test and let me know if this helps. Thanks, Ryan
|
|
|
|
|