gdecuir
Posts:60
river guide
 |
| 06/08/2009 6:51 PM |
|
Help needed!!!!
So far I'm enjoying using Dynamic Register.
I have created a checkbox group with 10 options. One of the option is "Other" in which the user can add additional information. I need to combine the selected options and the "Other" option into one DNN Core field. I am struggling to do so. In the client Side event I have $(cert)=$(itcert)+', '+$(certother) Note: cert is the DNN Core field. itcert and certother are Dynamic Registry fields.
The output should look something like. e.g. PMP, ITLT, MSE, MSA
(PMP,ITIL and MSE are checkbox options. MSA is a "Other" added in option)
Where am I going wrong???? |
|
|
|
|
David To
Posts:2745
river guide
|
| 06/09/2009 8:41 AM |
|
| For this process, it's better to create a stored procedure to update / insert a record into the userprofile table for the DNN core field "Cert" that combines the rest of the fields you want. There are several forums here regarding SQL store procedure to add roles / DNN core property profiles,etc. -- David |
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/09/2009 8:59 AM |
|
Hi David,
Could you point me to where the forms are. I've been searching endlessly and can't find one. Being new to all this is a real drag!!!!
thanks |
|
|
|
|
Chad Nash
Posts:3865
river guide
|
| 06/09/2009 11:27 AM |
|
Hi Gary. Glad to hear you are making some progress on your implementation.
Instead of using the client side events, I would probably use a SQL Event. Accessing the field properties for check box lists is tricky in client side events to referencing those fields such as that will not produce the value you want. If your profile property field ID was 500 it would look something like this:
I would get this first:
Update UserProfile Set PropertyValue = 'test' Where PropertyDefinitionID = 500 and UserID = $(UserID)
Once you have that working, then basically change it to use the cert and itcert and certother fields. This should get you what you need. So...
Update UserProfile Set PropertyValue = '$(itcert)' + ',$(certother)' Where PropertyDefinitionID = 500 and UserID = $(UserID)
If you need help finding the profile property definition ID, you can hover over the link under Admin, User Accounts, Manage PRofile Properties. When you hover over the link the link should have something like /propertydefinitionID/500/default.aspx etc... This is how you can tell what that property definition ID was.
-Chad
|
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/09/2009 2:30 PM |
|
Hi Chad,
Where do I place this SQL event? In the client side event box or elsewhere? Not sure where it should go. |
|
|
|
|
Chad Nash
Posts:3865
river guide
|
| 06/09/2009 2:42 PM |
|
Hi Gary,
Hi. These are one of the event types under 'Completion Events'. Similar to email events, redirection events, etc... you just need to select 'SQL Event' when creating the completion event. You will probably need two events, one for an insert and another for an update. Each completion event can be setup for either a new registration or an updated registration.
Also... Here is a great way of testing this to make sure its working properly. Instead of using a SQL Event you can.
1. Use an email event and in the email use the SQL that I suggested (or similar/tweaked with your profile property ID).
2. Then, you will see the full SQL that is genearted in the email. From there copy/paste it under Host/SQL within DotNetNuke and click 'Execute Query'. This way you will be able to easily tell if there are any errors in the query or if the query is working as you would expect it to.
Thanks!
-Chad
|
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/09/2009 2:54 PM |
|
Hi Chad,
Thanks for the quick reply. I will give it a try.
Thanks again,
Gary |
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/09/2009 5:58 PM |
|
Hi Chad,
THANKS IT WORKED AND I'M OFF AND RUNNING!!!!!! |
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/10/2009 12:23 AM |
|
Hi Chad,
The email event worked and the 'Execute Query' checked ok. But when I change it to a SQL event. It's not saving the information to the DNN core field. I followed your instructions and got the PropretyDefinitionID (which is 75). There is also no error messages. |
|
|
|
|
David To
Posts:2745
river guide
|
| 06/10/2009 8:34 AM |
|
| HI, can you paste your SQL query here for us to look at? Also, if you turn debug mode on, you can see exactly what the SQL query is executing and you can paste that query under Host / SQL to see if there's any errors. -- David |
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/10/2009 9:51 AM |
|
| Hi, How do you turn on debuging? |
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/10/2009 9:54 AM |
|
Hi David,
Here is the SQL query
Update UserProfile Set PropertyValue = 'ITIL,PMP,Other' + ',Test' Where PropertyDefinitionID = 75 and UserID = 3 |
|
|
|
|
David To
Posts:2745
river guide
|
| 06/10/2009 10:24 AM |
|
Two ways to turn debug mode on.
1) in the URL, add in the querystring ?debug=true
2) click on Enable Debug Mode which is located next to the control panel
IF you go to Host / SQL and type in your SQL update statement there, does it do anything?
Also, under Host / SQL, type in:
select * from userprofile where propertydefinitionID = 75 and userid = 3
and see if it returns any results. -- David |
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/10/2009 11:34 AM |
|
With debug on and typing in the in the SQL update statement in Host/SQL
I get the following
System.Data.SqlClient.SqlException: Incorrect syntax near '&'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) Update UserProfile Set PropertyValue = '$(cert)' + ',$(certother)' Where PropertyDefinitionID = 75 and UserID = $(UserID)?debug=true
Also,
When I type in under Host / SQL, :
select * from userprofile where propertydefinitionID = 75 and userid = 3
Results are: The Query completed successfully! |
|
|
|
|
David To
Posts:2745
river guide
|
| 06/10/2009 11:52 AM |
|
1) Hi, the update sql statement with the token values only work when you use it as part of the SQL form completion event; you can't use those tokens under Host / SQL since it doesn't know what those tokens are
2) You also cannot use debug=true in the Host / SQL area. The debug=true is a querystirng to append to the URL link (what's shown in the address bar on the top of the browser such as http://www.google.com
Do this for now and get back with what you see: Under Host / SQL, type in:
select * from userprofile where propertydefinitionID = 75 and userid = 3
but DO NOT CHECK the checkbox "Run as Script". I just want to know if the select sql query returns any values because if it doesn't, then you cannot perform an Update SQL query in your SQL form completion event because you will also have to check for empty records, and if so, add the record as opposed to updating the record. -- David |
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/10/2009 12:47 PM |
|
This is the message
The query did not return any data |
|
|
|
|
David To
Posts:2745
river guide
|
| 06/10/2009 2:15 PM |
|
| That's why the update sql form completion event isn't triggering. There's nothing to update. You need to modify your SQL to check if the record doesn't exist, to add it and if it does exist, then update it. -- David |
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/10/2009 3:52 PM |
|
Hi David, How would I do this? I'm not familiar with SQL at all. Chad was helping me accomplish this task. This the last hurtle in trying to finish my registration page.
Thanks,
Gary |
|
|
|
|
David To
Posts:2745
river guide
|
|
gdecuir
Posts:60
river guide
|
| 06/13/2009 10:42 AM |
|
Hi David,
After modifying the SQL procedure I'm still having problems getting this to work correctly. FUSTRATING!!!! I just need the final piece to finish my registration page. PLEASE HELP!!!!!!!!!!!!!!!
begin
declare @allcertsVar nvarchar(500)
If (@allcerts = '') set @allcertsVar = '$(cert)' + ',$(certother)' Where PropertyDefinitionID = 75 and UserID = $(UserID) else set @allcertsVar = @allcerts
declare @allcertsID int
set @allcertsID = (select PropertyDefinitionID from ProfilePropertyDefinition where PropertyName = 'allcerts')
-- if record doesn't exist, insert a new record
if not exists(select userid from UserProfile where userid = @UserID and PropertyDefinitionID = @allcerts)
begin
insert into UserProfile(UserID,PropertyDefinitionID,PropertyValue,Visibility) values(@UserID,@allcerts,@allcertsVar,2())
end
else
-- record exists, update it instead
begin
update UserProfile set PropertyValue = @allcertsVar where UserId = @UserID
end
end |
|
|
|
|