Hi 

I have a table called The_Glass_Factory_Bin_Numbers  it has 1 column called AccountNumber and  in that table i've added a number (5120) and created a stored procedure called [stp_BinNumberSQLValidation].

In my form i have a field called AccountNumber, i've enable server validation and i've checked the check box to set focus on first validation error field

On my field on the form I've enabled custom SQL validation script and on the validation SQL Query box i've added this code [stp_BinNumberSQLValidation] '$(AccountNumber)'

Please find my stored procedure that i've created on my database below

 
create PROCEDURE [dbo].[stp_BinNumberSQLValidation]
@AccountNumber nvarchar(200)
AS
SELECT COUNT(*) As IsValid
FROM The_Glass_Factory_Bin_Numbers
WHERE AccountNumber = @AccountNumber


I've created this custom registration when people try to register they need to enter their first 4 digits of their back card numbers and if the first 4 digits donot match the ones that I've loaded in the database then the person can not purchase the products on my website,

but now when i test it, it doesn't validate correctly as when i enter the right card number it still tells me that i've entered the incorrect bin number.

Hello David,

So you're attempting to use the SQL Validation feature within Dynamic Forms? You'll need to modify your SQL stored procedure as SQL validations require a returned column named IsValid and it must contain a boolean value of:

* 1
* 0
* True
* False

1 = True
0 = False

The modified procedure may look like this:

create PROCEDURE [dbo].[stp_BinNumberSQLValidation]
(
@AccountNumber nvarchar(200)
)
AS BEGIN

DECLARE @COUNT = (SELECT COUNT(*) As IsValid
FROM The_Glass_Factory_Bin_Numbers
WHERE AccountNumber = @AccountNumber)

IF @COUNT > 0
BEGIN
Select 'True' As IsValid
END

ELSE
BEGIN
Select 'False' As IsValid
END

END

Let me know if this assists you.

Thanks,

Ryan

Hi Ryan

thanks your quick response but it still not working with your stored proc, please find the edited one below maybe i'm missing something, please help.

create PROCEDURE [dbo].[stp_BinNumberSQLValidation]
(
@AccountNumber nvarchar(50)
)
AS BEGIN

DECLARE @COUNT int = 0

SET @COUNT = (SELECT COUNT(*) FROM The_Glass_Factory_Bin_Numbers
WHERE AccountNumber = @AccountNumber)

IF @COUNT > 0
BEGIN
Select 'True' As IsValid
END

ELSE
BEGIN
Select 'False' As IsValid
END

END

Hello David,

This looks correct.

Can you please place the Registration form in DEBUG mode and invoke this SQL Validation? You should see the exact procedure call that's being executed.

You can then debug this query by copy/pasting this to Host -> SQL and executing your stored procedure call. More than likely an error is occurring if it's not operating as you expect.

More than likely there's a data type conversion error or the account number token isn't rendering the value appropriately.

Let me know if you have any questions.

Thanks,

Ryan

Hi Ryan

Please find the form attached, i've tried but i can't find any issue with my form maybe a second eye might pickup where i've gone wrong.

My script for my table is as follows.

USE [theglassfac]
GO

/****** Object:  Table [dbo].[The_Glass_Factory_Bin_Numbers]    Script Date: 12/10/2012 16:33:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[The_Glass_Factory_Bin_Numbers](
[Bin_Id] [int] NOT NULL,
[AccountNumber] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[Bin_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Hello David,

I feel like we're making this too complicated and the issue is much easier than it seems to be.

Can you simply enable DEBUG mode for your Dynamic Registration module, by clicking ENABLE DEBUG MODE(when logged in as a Administration or Host user) or by appending ?Debug=True to the URL.

You should then be able to see all of the client side and server side code that is executing behind the scenes. It shouldn't be overwhelming information.

Then submit your form so the SQL Validation will be invoked. The form will submit and will output the exact SQL that is being attempted to execute for the SQL validation with all Token's being rendered.

You need to analyze this SQL. If you don't see any issues with the syntax or sematics, simply copy this SQL and run it under Host -> SQL and see if it returns a column with the value of "True" or "False"

If it's not returning a value, then you should check the checkbox "Run as Script" under Host -> SQL when you execute this SQL Statement. If an error is present, it will give you a relative error message.

Please let me know if you have any questions.

Thanks,

Ryan

Hi Ryan

I've ran the script with the number that is in a table that i've created and its giving me the same results everytime i run it, the results is showing below.

IsValid

0

Hi Ryan

I've done what you suggested and when i run the script on my website under host its returning a (0) instead of a (1) even when i'm validating the correct information its still returning a (0)

This is what's showing on my form
 
strSQLScript:[dbo].[stp_BinNumberSQLValidation] '5120'

I coppied this, ran it under host sql page on my website and i'm getting IsValid 0, this number (5120) it is in my SQL table but its returning a 0, i'm not sure what is happening now as i'm confused why its doing that.

Hello David,

You can tell that the stored procedure is going into the ELSE segment of code.

This really helps when debugging. You should step through your stored procedure in steps until you locate the issue.

Step 1 of debugging would be to go to Host -> SQL and execute the first statement to ensure that you're getting a value you want:

SELECT COUNT(*) FROM The_Glass_Factory_Bin_Numbers
WHERE AccountNumber = '5120'

Then you should be able to determine what's happening inside of your procedure. Again this is an issue with your procedure and not Dynamic Registration. So please review your stored procedure thoroughly. You'll find the issue.

What we know is that ELSE segment should only be reached if @COUNT is not greater than 0.

Well NULL and 0 would enable the ELSE segment to be reached instead of the IF statement.

Let me know how this goes.

Thanks,

Ryan

Hi Ryan

I don't believe this, the problem was between my keyboard and my chair, i was querying the wrong database the whole time.

Thanks for all your help its sorted now.

Hi Ryan

I don't believe this, the problem was between my keyboard and my chair, i was querying the wrong database the whole time.

Thanks for all your help its sorted now.

Hello David,

Glad to hear that you now have this working.

Unfortunately... computers do exactly as we instruct I can't wait for the day when computers can reason with us confirming if we really meant to perform the command in queue. More than likely this will filter odd behaviors that USERS(we) perform.

Hope you have a great day.

-Ryan