Great Ideas. Always Flowing.

We are not happy until you are happy. Client satisfaction guaranteed. Whatever your needs and requirements, we have the skills and resources for the job!

Quick login...


Or... now make it easy with Facebook Integration
Connect via Facebook



Top Sellers

Frustrated over the lack of customization for your user's registration fields? Dynamically setup your DNN Portal with custom registration fields, layout, questions, and other core integration options......

Ultra Video Gallery is a brother product of Ultra Media Gallery, UVG allows you to upload videos in various format and automatically encode them to flv or H264 format, you also can add videos from internet or record live videos from your webcam.

Build high performance, completely customizable data-entry forms and views driven by your DNN and external databases. New built-in tools make it a snap to quickly create data entry forms, data views, and even database tables. Plus, add your own HTML, CSS, Javascript, SQL commands, stored procedures,

The most advanced DotNetNuke shopping cart on the planet. Easy to use e-Commerce, Secure Shopping Cart Software and SEO friendly. B2C / B2B Ecommerce Sites.

One stop solution for events calendar and events registration! FREE DOWNLOAD is available now!

Registration SQL Validation
Last Post 04-24-2009 03:54 PM by Chad Nash. 12 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Justin DavisUser is Offline
wading in the water
wading in the water
Posts:21
Avatar

--
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 NashUser is Offline
    Posts:5260
    Avatar

    --
    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 DavisUser is Offline
    wading in the water
    wading in the water
    Posts:21
    Avatar

    --
    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 ToUser is Offline
    river guide
    river guide
    Posts:2719
    Avatar

    --
    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 DavisUser is Offline
    wading in the water
    wading in the water
    Posts:21
    Avatar

    --
    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 DavisUser is Offline
    wading in the water
    wading in the water
    Posts:21
    Avatar

    --
    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 DavisUser is Offline
    wading in the water
    wading in the water
    Posts:21
    Avatar

    --
    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 DavisUser is Offline
    wading in the water
    wading in the water
    Posts:21
    Avatar

    --
    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 ToUser is Offline
    river guide
    river guide
    Posts:2719
    Avatar

    --
    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 DavisUser is Offline
    wading in the water
    wading in the water
    Posts:21
    Avatar

    --
    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 DavisUser is Offline
    wading in the water
    wading in the water
    Posts:21
    Avatar

    --
    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 DavisUser is Offline
    wading in the water
    wading in the water
    Posts:21
    Avatar

    --
    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 NashUser is Offline
    Posts:5260
    Avatar

    --
    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
    You are not authorized to post a reply.


     
     

    Join our mailing list...

    Get current news and events the easy way
    Subscribe Me

    Recent Blogs...

     
    Copyright 2005 - 2011 by Data Springs, Inc.
     
  • 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