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

Dynamic Registration 4.0

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 4

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.

XMod Pro 2.7 - Forms and Views for Databases

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,

Smith Shopping Cart v3.88

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.

Event Calendar and Registration 4.0

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

DNN Modules

Forums > Product Discussion - DotNetNuke Modules > Dynamic Registration
Populate field from SQL query based on input value of prior field
Last Post 06-06-2012 04:11 PM by Chad Nash. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
JohnMarionUser is Offline
skipping stones
skipping stones
Posts:7
Avatar

--
06-03-2012 11:22 AM
    Hi 

    In DR 4.1.7/DNN 5.6.3, I have a field, Company, which is a Combo Box whose possible values are populated by an SQL query. This works fine.
    I have a second field, Ticker (a Text Box), which I want to populate based on the value the user chooses in Company and the result of an SQL query on that value. (The table I use for both queries has the Company names and tickers in it.) Specifically, if a user chooses 'Amazon.com' in the Company field, I want to do an SQL query on that value, which returns 'AMZN' and populate this value into Ticker.

    I have tried:
    • This is easy to do in Dynamic Forms with a question event, but DR does not have the ability to use an SQL query to set the default value of a field in a question event. 
    • From what I have read, it is not possible to do the SQL query in a javascript client side event on the Combo Box.
    • I saw how one can do this if the second field is a Combo Box ( http://www.datasprings.com/products...tration-11 ). I have tried doing the same with the second field as a Text Box, but there is apparently no way to make the default value populate a Text Box at the time the field is made visible as one can do with a Combo Box. 
    The second field needs to be a TextBox because the user needs to have the ability to input a value if none is found in the database.

    Ideally, I would also be able to make the Text Box field read only if the value is found in the SQL query and leave it read/write if not. 

    How can I achieve what I need?

    Thanks
    John
    Ryan BakerinkUser is Offline
    river guide
    river guide
    Posts:1900
    Avatar

    --
    06-04-2012 05:45 AM
    Hello John,

    Have you considered using the On Client Side section within the Combo box question type that performs the SQL Default check?

    Perhaps you can assign the value from the Combo Box upon selection, to the textbox. If a value is provided then disable or make the textbox readonly. If a value is absent, then enable the textbox.

    You can achieve this with JavaScript as I have done so for many of our client implementations(as long as there aren't any SQL Dependencies). I hope that my hint can help you resolve this issue.

    Unfortunately I won't be able to provide you the solution as we can't support the programming language "JavaScript" without the use of our Premium Integration Services:

    http://www.datasprings.com/premiumsupport

    Please let me know if you have any questions.

    Thanks,

    Ryan
    JohnMarionUser is Offline
    skipping stones
    skipping stones
    Posts:7
    Avatar

    --
    06-04-2012 06:23 AM
    Hi

    Thanks for your answer. Yes, I did try what you are suggesting, but the problem is that there IS a SQL dependency. The value that goes in the TextBox needs to come from an SQL query based on the result of the Combo Box selection. Once again, here is how it needs to work:

    - SQLQuery1 fills ComboBox Stock with names of stocks on form initiation.
    - User chooses a stock from the ComboBox, e.g. 'Amazon.com'
    - SOMEHOW: SQLQuery2 returns ticker symbol 'AMZN' for 'Amazon.com', e.g. select companyticker as DefaultValue from companylist as cl where cl.companylistname=$(Company). (a question event like the one in DynamicForms that will p[opulate a default value of a field based on a SQL query would be ideal).
    - DefaultValue (if it exists, as it can be blank, as some stocks do not have ticker symbols) is populated into TextBox Ticker
    - ideally if there is a ticker symbol, make the field read only (I suspect that this is possible with javascript and that I can figure out how to do it, but my main focus now is on getting the ticker symbol in the text box.)

    I would love to put this in the client side event for the Combo Box which executes after the user makes his choice, but there is no way to make an SQL query from JavaScript that I can find. Am I wrong?

    I would be very happy if you can tell me how to do this.

    Thanks
    John
    Ryan BakerinkUser is Offline
    river guide
    river guide
    Posts:1900
    Avatar

    --
    06-04-2012 06:48 AM
    Hello John,

    You're going to need to enhance your SQL Query if you'd like to get this to work in Dynamic Registration.

    There will be no need for a SQL Dependency, because you will need to make a subquery to handle the Ticker Symbol value for each Company(i.e. Amazon.com)

    So you can easily use the QuestionOption to be the Company Name and the QuestionOptionValue to handle the Ticker Symbol text.

    If you need to distinguish both values in a Form Completion Email event or any other event you can use $(Company_Text) "_Text" to get the QuestionOption value. If you do $(Company) then you will get the value of the field - QuestionOptionValue.

    Then you could implement the JavaScript into the On Client side section of the Question.

    Like:

    $(TextBox) = $(CompanyName)

    Why do you need a SQL Dependency between the Textbox and Combobox? I don't believe there's a need for it unless there's more to this than you have described.

    Just write out some psuedocode to assist you in building the query:

    IF Amazon.com THEN
    (Select TickerValue from TickerTable where TickerName = 'Amazon.com')
    END
    ETC.....

    Let me know if you have any questions.

    Thanks,

    Ryan
    JohnMarionUser is Offline
    skipping stones
    skipping stones
    Posts:7
    Avatar

    --
    06-04-2012 08:27 AM
    Ryan -

    Thank you very much. That almost has it working. The only problem I have remaining is caused by the fact that the ticker (QuestionOptionValue) can be NULL. Let's say that the results of my query to fill the ComboBox are as follows:

    QuestionOption QuestionOptionValues
    Company1 Ticker1
    Company2 NULL
    Company3 Ticker3
    Company4 NULL
    ...

    If I choose a QuestionOption such that QuestionOptionValue is NULL, the field reverts back to the first row where QuestionOptionValue is NULL. In this example, if I choose Company4 the field snaps back to Company2.

    Can I prevent this?

    Thanks
    John
    Ryan BakerinkUser is Offline
    river guide
    river guide
    Posts:1900
    Avatar

    --
    06-04-2012 08:46 AM
    Sure, when assigning a column to take on the alias of QuesitonOptionValue, use either a COALESCE or a CASE statement.

    For instance(Case Statement):

    (CASE WHEN ColumnName IS NULL THEN 'HardCode Value' WHEN ColumnName IS NOT NULL THEN ColumnName) As QuestionOptionValue

    Or you can Google the T-SQL reserved keyword "COALESCE" to understand how it works.

    Please let me know if you have any questions.

    Thanks,

    Ryan
    JohnMarionUser is Offline
    skipping stones
    skipping stones
    Posts:7
    Avatar

    --
    06-04-2012 08:53 AM
    If I hard code the same value for all cases where the QuestionOptionValue is NULL, I will have the same behavior. It will revert back to the first row with that value. 

    In any case the QuestionOptionValue is back to not being the ticker, which it needs to be so I can assign it to the Textbox. 

    I tried this:
    (CASE WHEN CompanyTicker IS NULL THEN CompanyName ELSE CompanyTicker END) 

    and then in javascript 

    if $(Company) == $(Company_Text) {
    $(Textbox) = "";
    }else {
    $(Textbox) = $(Company);
    }

    But $(Company_Text) is always blank, so the test always fails.

    Or am I missing something?
    JohnMarionUser is Offline
    skipping stones
    skipping stones
    Posts:7
    Avatar

    --
    06-04-2012 10:04 AM
    OK, I have a solution. Here it is, in case it helps someone. But it is extremely klugey. I really, really wish I had the ability in DR to make a question event set the default of a textbox to the result of an SQL query. Would have saved me HOURS.

    Here is what I use to fill the combobox:

    select CompanyName as QuestionOption, 
    (CASE WHEN CompanyTicker IS NULL THEN 'XXXX'+CompanyName ELSE 
    left(rtrim(CompanyTicker)+'     ', 5)+CompanyName END) as QuestionOptionValue from CompanyList

    and in javascript:

    $(CompanyHidden) = $(Company).substring(5); // without ticker prepended
    if ($(Company).substring(0,5) != "XXXXX") {
    $(CompanyTicker) = $(Company).substring(0,6-$(Company).indexOf(" "));
    }
    else
    {
    $(CompanyTicker) = "";
    }

    ugh!
    Chad NashUser is Offline
    Posts:5260
    Avatar

    --
    06-06-2012 04:11 PM
    Thanks for posting this John!

    -Chad
    You are not authorized to post a reply.

    Forums > Product Discussion - DotNetNuke Modules > Dynamic Registration
     
     

    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