SQL Validation how?
Last Post 12-03-2012 05:35 AM by Ryan Bakerink. 7 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Michael ManUser is Offline
river guide
river guide
Posts:152
Avatar

--
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 BakerinkUser is Offline
    river guide
    river guide
    Posts:1900
    Avatar

    --
    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 ManUser is Offline
    river guide
    river guide
    Posts:152
    Avatar

    --
    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.PrattUser is Offline
    wading in the water
    wading in the water
    Posts:28
    Avatar

    --
    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 BakerinkUser is Offline
    river guide
    river guide
    Posts:1900
    Avatar

    --
    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.PrattUser is Offline
    wading in the water
    wading in the water
    Posts:28
    Avatar

    --
    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 ClohessyUser is Offline
    skipping stones
    skipping stones
    Posts:8
    Avatar

    --
    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 BakerinkUser is Offline
    river guide
    river guide
    Posts:1900
    Avatar

    --
    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




    ---
  • film izle
  • 720 izle
  • film
  • sinema izle
  • film makinesi
  • T�rk�e dublaj film
  • film izle
  • film izle
  • baglan film izle
  • sinema izle
  • 1080 film izle
  • film mercegi