Justin Davis
wading in the water Posts:21
|
04-17-2009 03:28 PM |
|
I need to be able to validate a person's response from a textbox and compare that to a list of companies. If they enter an invalid company name I dont want them to be able to register.
I am pretty sure this can be done with the sql option, but I am not a sql expert and really do not know where to start. I am a network guy who was asked to build a company website.
Any is help is MUCH appreciated.
Thank you, |
|
|
|
|
Chad Nash Posts:5260
|
04-18-2009 09:31 AM |
|
Justin, Hi. Yes you can use the 'SQL Validation' feature under the validation section for the field. Do you know the table name where this information is stored? If you would like us to write the query / stored procedure to handle this we also offer this under our Premium Support hours at http://www.datasprings.com/Premium-Support. Otherwise you can post some of the details here and we will try and help with the query. -Chad |
|
|
|
|
Justin Davis
wading in the water Posts:21
|
04-19-2009 06:07 PM |
|
thanks Chad, I may have to take you up on that... Is there anyway to do a compare validation against a listbox? I am assuming no, but thought I would ask anyway. |
|
|
|
|
David To
river guide Posts:2719
|
04-20-2009 06:59 AM |
|
HI Justin, I don't think you can do a compare validation against a listbox since there are multiple choices a person can choose from a listbox. -- David |
|
|
|
|
Justin Davis
wading in the water Posts:21
|
04-21-2009 06:27 AM |
|
OK so here is what I got from our DBA is this close? This may work if you can pass the value from the web form: Declare @Employer varchar(50) SELECT Employer_Name FROM dbo.Employer_Name WHERE Employer_Name = @Employer |
|
|
|
|
Justin Davis
wading in the water Posts:21
|
04-21-2009 08:26 AM |
|
Declare @Employer varchar(50) select Employer_Lookup as QuestionOption, Employer_Name as QuestionOptionValue from Employer where Employer_lookup= @Employer So we got the employer lookup from the database, how do I pass this to allow the question event to assign per dnn role |
|
|
|
|
Justin Davis
wading in the water Posts:21
|
04-22-2009 09:44 AM |
|
OKay OKay Now I am stuck: Here is what I have a textbox that users MUST enter their company name, then I want to add a sql completion event to automatically add users to the corresponding dnn role. I also dont want users to be able to register if they company doesnt participate with us. So here is what I got: I created a table called Employers with columns: ID, Employer_ID, Employer_Name, RoleID.. The Role ID is from dnn table roles. Then I created this Stored Procedure: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO /* Insert UserRole from UserRegisterModule */ ALTER procedure [dbo].[usp_AddUserRole] ( @UserID int, @Employer varchar(20) ) as begin declare @EmpID int Set @EmpID = (Select Distinct Employer_ID From dbo.Employer Where Employer_Lookup like (ltrim(rtrim(@Lookup))+'%')) declare @RoleID int set @RoleID = (Select Distinct Employer_ID From dbo.Employer Where Employer_ID = @EmpID) if exists(select 1 from userroles where userid = @UserID and roleid = @RoleID) begin update UserRoles Set RoleID = @RoleID Where userid = @UserID end else begin insert into UserRoles(UserID,RoleID,IsTrialUsed,EffectiveDate) values(@UserID,@RoleID,'True',getdate()) end -- set the three DNN core profile fields (PrefStoryCommentNotification,NotificationEmails,InvitationEmails) -- in table "UserProfile" to true; note: need to retrieve PropertyDefinitionID from "ProfilePropertyDefinition" -- table declare @PrefStoryCommentNotificationID int declare @NotificationEmailsID int declare @InvitationEmailsID int set @PrefStoryCommentNotificationID = (select PropertyDefinitionID from ProfilePropertyDefinition where PropertyName = 'PrefStoryCommentNotification') set @NotificationEmailsID = (select PropertyDefinitionID from ProfilePropertyDefinition where PropertyName = 'NotificationEmails') set @InvitationEmailsID = (select PropertyDefinitionID from ProfilePropertyDefinition where PropertyName = 'InvitationEmails') if not exists(select userid from UserProfile where userid = @UserID and PropertyDefinitionID in (@PrefStoryCommentNotificationID,@NotificationEmailsID,@InvitationEmailsID)) begin insert into UserProfile(UserID,PropertyDefinitionID,PropertyValue,Visibility,LastUpdatedDate) values(@UserID,@PrefStoryCommentNotificationID,'True',2,getdate()) insert into UserProfile(UserID,PropertyDefinitionID,PropertyValue,Visibility,LastUpdatedDate) values(@UserID,@NotificationEmailsID,'True',2,getdate()) insert into UserProfile(UserID,PropertyDefinitionID,PropertyValue,Visibility,LastUpdatedDate) values(@UserID,@InvitationEmailsID,'True',2,getdate()) end end But I have NO IDEA how to setup the completion event itself.... Please Please HELP!!! Thank you |
|
|
|
|
Justin Davis
wading in the water Posts:21
|
04-23-2009 06:07 AM |
|
This is for anyone who wants to know how to automatically assign roles to DNN using a Dynamic SQL event..... First Create your table with the necessary fields as you can see my table is a list of employers with Employer Name Employer Lookup and Role ID ( Role ID copied from the DNN roles table ) then I created a dynamic sql completion statement with the following::: Basicly this scripts say if employer name is "such and such" give userrole ID #XX if it doesnt not find the employer then autmatically assign a default "no company" role ID and then I use the Dynamic Login to redirect those users to another page. Hope this helps anyone looking... Declare @Employer varchar(50) Set @Employer = ltrim(rtrim('$(Employer)'))+'%' If exists(select 1 from UserRoles where userid = '$(UserID)') Begin Update UserRoles Set RoleID = isnull((Select Distinct RoleID From Employer Where Employer_lookup like @Employer),20) Where UserID = '$(UserID)' End Else BEGIN Insert Into UserRoles(UserID, RoleID) Select '$(UserID)', isnull((Select Distinct RoleID From Employer Where Employer_lookup like @Employer),20) END |
|
|
|
|
David To
river guide Posts:2719
|
04-23-2009 06:50 AM |
|
HI Justin, sorry I didn't get back sooner but yesterday was a very busy day for me working on other projects. To set up your form completion event, pick option SQL and call your stored procedure passing the tokens from DR to match the parameters of your stored procedure. Based on your stored procedure, it would be something like: usp_AddUserRole $(UserID) '$(Employer)' -- David |
|
|
|
|
Justin Davis
wading in the water Posts:21
|
04-23-2009 07:30 AM |
|
No worries, I ended up just doing a sql query from the sql completion event that looks at a employer table I had created works GREAT!! Declare @Employer varchar(50) Set @Employer = ltrim(rtrim('$(Employer)'))+'%' If exists(select 1 from UserRoles where userid = '$(UserID)') Begin Update UserRoles Set RoleID = isnull((Select Distinct RoleID From Employer Where Employer_lookup like @Employer),20) Where UserID = '$(UserID)' End Else BEGIN Insert Into UserRoles(UserID, RoleID) Select '$(UserID)', isnull((Select Distinct RoleID From Employer Where Employer_lookup like @Employer),20) END |
|
|
|
|
Justin Davis
wading in the water Posts:21
|
04-24-2009 07:44 AM |
|
Okay Now I am trying to validate a company name BEFORE the user hits submit. If the company does not exist I dont want them to be able to register... This is my sql validation script set on company name:: Select Employer_Name as IsValid from Employer where Employer_Lookup like '$(Employer)'+'%' I have client side validation turned on, I have also ran test in my sql management studio and this script works fine, but it acts as if it isn't getting kicked off.. Any ideas? |
|
|
|
|
Justin Davis
wading in the water Posts:21
|
04-24-2009 10:00 AM |
|
Nevermind, got it working...... You should put in your directions that it MUST be a stored Procedure. Once I created the stored procedure it worked fine.
GO
/****** Object: StoredProcedure [dbo].[usp_ValidateEmployer] Script Date: 04/24/2009 14:31:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Insert UserRole from UserRegisterModule
Create Date: 04/22/2009
*/
CREATE procedure [dbo].[usp_ValidateEmployer]
(
@Employer varchar(20)
)
as
begin
If Exists (Select 1 from Employer where Employer_Lookup like @Employer+'%')
Begin
Select '1' as IsValid
End
Else
Begin
Select '0' as IsValid
End
END |
|
|
|
|
Chad Nash Posts:5260
|
04-24-2009 03:54 PM |
|
Thanks for the info and post... Not sure why it would have worked any different though, there is nothing in the code that would force it to be a stored procedure. Maybe an extra GO at the end or something was throwing it off? -Chad |
|
|
|
|