Hi,

Can you please give an example of a SQL validation script (i.e. stored procedure)? In particular what should the parameter be named - assuming the question value can be passed as a parameter?

thanks,
Tim

I, too, am trying to figure out the proper syntax for SQL validation.  I tried the following syntax for a dynamic query (for testing purposes) for the CompanyCode field:

select count(*) as IsValid
from wm_Companies
where code = '$(CompanyCode)'

The validation error message ("You must enter your company's identity code") does display if I leave the textbox blank or enter gibberish AS LONG AS I fail to enter a username and password (if I fill in the latter fields then I can successfully register without specify a company code or any of the other fields like First name).

I failed to find suitable examples in the user manual or forum but I'll keep looking.

Troy

Yes, server-side validation is checked.  It *is* validating the field somewhat since I receive the validation error message but since it isn't a required field, I can register without entering a valid company code.

I guess my real question should be: how do I make fields required (even First Name isn't required)?  I checked the Required Field & Enable Custom SQL Validation Script checkboxes on Question Validation for CompanyCode. 

Currently, to register I need only enter data for username, password and confirm password.  I'd like to make all fields mandatory (first name, last name, display name, captcha, etc) as well as validate Company Code.

I've made some progress and learned a few things along the way.

First, I've made all fields required.

Second, I can successfully use "select 0 as IsValid" to prevent a user from registering since SQL validation fails (testing purposes only). 

Third, if I change the SQL query to "select count(*) as IsValid from my_nonexistent_table" then the user can still register even though there's no such SQL table.  Why is that?

For my situation I don't think the problem is the backend (db) but rather the configuration of either the dynamic field or DNN itself.  Here are the pertinent settings for the dynamic field:

Question Type:  textbox (single line)
(no boxes in Advanced Field Options are checked)
Required Field:  checked
Required Text: Company Registration Code is required
Required Field Error: * Required
Enable custom SQL validation script: checked
Validation SQL query: exec spValidateCompanyCode '$(CompanyCode)'
(also tried dynamic query)
Validation Error Message: Invalid company registration code

The user cannot register without providing a company code but DNN accepts any text in the box as legitimate.

The big question is the one I previously posted: why does the following query not prevent a user from registering?  "select count(*) as IsValid from my_nonexistent_table"

p.s.  I like the option to immediately validate the chosen username but this option prevents tabbing to the next textbox from working correctly (it steals the focus, which is not user friendly).

I uninstalled & reinstalled the module and now the SQL validaton works for the Company Code field.  However, if the SQL query is bogus the user is still able to register.

For example, I can set the SQL query to be "abcd" (no quotes) and the user can register by entering any garbage text in the Company Code textbox (no error message about the malformed SQL query is displayed).  Obviously, this should not be allowed.

My question is the following

One user wrote that these are his option for field validation below

Question Type:  textbox (single line)
(no boxes in Advanced Field Options are checked)
Required Field:  checked
Required Text: Company Registration Code is required
Required Field Error: * Required
Enable custom SQL validation script: checked
Validation SQL query: exec spValidateCompanyCode '$(CompanyCode)'
(also tried dynamic query)
Validation Error Message: Invalid company registration code

UNTILL required filed error is fine

The option Enable SQL validation script is missing from mine question options im using Dynamic Registration version 03.03.50.

IS this option available on any latter versions of Dynamic Registration

Hi Yes this was not available until version 3.2 and you are running on 3.0 (3.03.50) but that is actually still lower then 3.2.

-Chad

Ugh, this problem has raised its head again, this time on a different installation.  So I did what I did before--uninstall & reinstall the module and configure it once again.  Still the same problem.

My steps:

- installed enterprise module (version 3.20.60)

- added module to a page

- ran through setup wizard, taking the 7 default fields

- clicked Update Registration

- enabled client-side/server-side/AJAX/email validation and enabled Display Validation Summary Message via Module Configuration-->Validation Configuration

- added a new dynamic field, RsvpCode, as a short, singleline textbox

- configured things like required text, etc

- set new field to required and enabled custom SQL validation via wm_ValidateCompanyRsvpCode '$(RsvpCode)'

- clicked Update Field link

- tested it out using a fake rsvp code--it succeeded even though it shouldn't since the stored procedure called is set to 'select 0 as IsValid'

I'm open to ideas/suggestions.

Troy

I think I'm zeroing in on the problem. 

I tried executing the stored procedure via Host-->SQL and received the following error.

System.Data.SqlClient.SqlException: Could not find stored procedure 'wm_ValidateCompanyRsvpCode'

Since I'm 100% positive the stored procedure is present and is spelled correctly, I assume the problem is permissions.  The source database is Database.mdf via the file system, with a connection string = "Data Source=.\SQLExpress;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient".  Not sure why it was done that way but it's what I'm stuck with.

Any thoughts on proper permissions or other ideas?  Danke!

Troy

Update: I figured out the whole permissions issue.  It was an issue with using SQL Server Managements Studio (SSMS) to access the SQLEXPRESS database while DNN had active connections to it.  I fixed the problem by following the steps outlined via http://www.dotnetnuke.com/Community/BlogsDotNetNuke/tabid/825/EntryID/1202/Default.aspx.

I still don't understand why executing a bad SQL query allows a user to register--the Dynamic Registration module should display an error message and prevent registration (essentially IsValid = 0).  This is one of those "What were they thinking?" situations.