I want to prevent users from entering a product if it already exist in the database.  So I used demo 12 as my guide to create a form completion event however; it do not appear the steps I have followed is accomplishing my goal.  Below you will find the store procedure query and the stored procedure call.  What am I doing wrong?

 

This is my stored procedure

 

SELECT COUNT(*) As IsValid FROM tbl_Platform WHERE PlatformName != @PlatformName

I also tried changing the stored procedure query to the following variations, but no luck.

 

SELECT COUNT(*) As IsValid FROM tbl_Platform WHERE PlatformName = @PlatformName

 

SELECT COUNT(*) As IsValid FROM tbl_Platform WHERE PlatformName like @PlatformName

 

This is my stored procedure call under module configuration, validation configuration section:

 

usp_CheckPlatform '$(Platfom)'

After reading several post, I realize the IsValid has to return 0 or 1, so I modified my stored procedure to the below. This modification should give me the results I am looking for in the form however; it's not. For example, if the platform 'WindowsXp' exist in the table and user attempts to add this very same platform upon form execution the stored procedure will return 0, which should display the error message and if they type 'Windows", the stored procedure should return 1 and not display the message, which would allow the user to move forward. What the form is doing instead is simply allowing all entries, even the ones that exist to move forward. I have tested the query and I do get the desired results. What am I doing wrong?


Modified Stored Procedure:

CREATE PROCEDURE usp_CheckPlatform


@PlatformName nvarchar(100)

AS

IF (SELECT COUNT(*)
FROM tbl_Platform
WHERE PlatformName = @PlatformName) = 1
SELECT 0 AS IsValid /* Display Error Message, platform exist */
else
SELECT 1 AS IsValid /* Do not display Error Message */


Host / SQl test result:

Query "false results":
IF (SELECT COUNT(*)
FROM tbl_Platform
WHERE PlatformName = 'Windowsxp') = 1
SELECT 0 AS IsValid /* Display Error Message, platform exist */
else
SELECT 1 AS IsValid /* Do not display Error Message */

Results:
IsValid
0

Query "true results":
IF (SELECT COUNT(*)
FROM tbl_Platform
WHERE PlatformName = 'Windows') = 1
SELECT 0 AS IsValid /* Display Error Message, platform exist */
else
SELECT 1 AS IsValid /* Do not display Error Message */

Results:
IsValid
1

HI, do you have both client side and server side validation on? Also check to make sure "Enable Custom SQL Validation Script" is checked.

If possible, turn on debug mode as well. Append a ?debug=true at the end of the URL and then go through your routine of anwering questions and see value is being passed to the stored procedure. -- David

Below you will find the debug results and all the fields I have checked off under module configuration / Validation Configuration.

I have a question for my stored procedure call, should I be putting exec in front of the stored procedure call ie exec usp_CheckPlatform '$(Platfom)'?


***DYNAMIC FORMS DEBUG MODE ON***
Dynamic Forms Version: 3.1.0.13946

DotNetNuke Version: 05.00.-01

Initial Javascript:

SQL Debug (Parse SQL Text For Variables):usp_CheckPlatform '$(Platfom)'
SQL Debug (Parse SQL Text For Variables):The platform / product already exist in the database.
Saving Forms Responses: 8:49:57 AM
Processing Events: 8:49:57 AM

Processing Redirect Event: evt_Platform -8:49:57 AM
*** Ending Debug Mode for Dynamic Forms ***
Quick Add (Drag / Drop)


The below options are check enable:
Enable Client Side Validation
Enable Server Validation
Display * next to field label
Set focus on first validation error field
Enable custom SQL Validation script

Is your shortfield name $(Platfom) or is it $(Platform)? -- David

wow, I can't believe I missed that simple problem. You won't believe all the complex solutions I attempted to resolve this problem and I can't believe it was this simple.

Thanks for the support, I guess I needed a second pair of eyes, because my eyes clearly glazed over that mistake.

ps: this is what happens when one works on these delicate stuff late at night, huge simple errors occurs.