QGilbert
skipping stones
Posts:8
 |
| 10-26-2009 05:29 PM |
|
Do you have any examples of using Dynamic SQL Completion events to assign roles. The query below, which appears to have no affect, is setup on "any field response", with cbSpecial as a combo-box option of roles that the user may choose from DNN.
DECLARE @Role_Id int
BEGIN
SET @Role_Id = $(cbSpecial)
IF (@Role_Id IS NOT NULL)
EXEC dbo.AddUserRole
@PortalID = 0,
@UserID = $(UserID),
@RoleID = @Role_Id,
@CreatedByUserID = -1 --N/A
END
go
Note that I've confirmed that cbSpecial is the role-ID from DNN. |
|
|
|
|
David To
river guide
Posts:2719
|
| 10-27-2009 06:25 AM |
|
HI, in order to debug your issue, can you state your SQL completion event here.
That way, I can see what fields you are passing to the stored procedure.
Also, state the dbo.AddUserRole here as well. -- David |
|
|
|
|
QGilbert
skipping stones
Posts:8
|
| 10-27-2009 08:33 AM |
|
This is the SQL to setup the question/response, essentially a list of roles:
SELECT
r.RoleID as QuestionOptionValue
,r.Description as QuestionOption
FROM
Roles r JOIN RoleGroups g ON
(r.RoleGroupID = g.RoleGroupID)
WHERE
g.RoleGroupName != 'Insights Workflow'
The SQL completion was changed to include the text (roleID) but I'm not sure the _Text is available:
SET @Role_Id = $(cbgSpecial_Text) |
|
|
|
|
QGilbert
skipping stones
Posts:8
|
| 10-27-2009 09:42 AM |
|
It appears the issue was in the SQL itself; removing "go" now works. This was discovered when error events were created. So my question now is how to obtain the individual values for a check-box group? |
|
|
|
|
David To
river guide
Posts:2719
|
| 10-28-2009 06:58 AM |
|
HI, for a checkbox group, it stores the data as one long string separated by ",". You need to use SQL or javascript to extract the individual selected values. SQL would be something like this:
SET @Delimeter = ','
SET @Currentdate = getdate()
--Parse the string
DECLARE @StartPos int, @Length int
WHILE LEN(@FreeOnline) > 0
BEGIN
SET @StartPos = CHARINDEX(@Delimeter, @FreeOnline)
IF @StartPos < 0 SET @StartPos = 0
SET @Length = LEN(@FreeOnline) - @StartPos - 1
IF @Length < 0 SET @Length = 0
IF @StartPos > 0
BEGIN
SET @Class = SUBSTRING(@FreeOnline, 1, @StartPos - 1)
SET @FreeOnline = SUBSTRING(@FreeOnline, @StartPos + 1, LEN(@FreeOnline) - @StartPos)
END
ELSE
BEGIN
SET @Class = @FreeOnline
SET @FreeOnline = ''
END
if @Class = 'Enchanted Learning' SET @FOL_EL = 1
if @Class = 'Hot Math' SET @FOL_HM = 1
IF @Class = 'Study Island' SET @FOL_SI = 1
IF @Class = 'United Streaming' SET @FOL_US = 1
END
NOTE: the values above just demo names and you will need to modify the script to accomplish what you want. Please try it out and if you still have problem, just post your SQL here and we'll take a look at the issue. -- David |
|
|
|
|
QGilbert
skipping stones
Posts:8
|
| 10-28-2009 07:42 AM |
|
Is there a way to obtain the actual RoleID values - are they also in a comma-separated list? |
|
|
|
|
David To
river guide
Posts:2719
|
| 10-28-2009 08:04 AM |
|
No, your RoleID is set as a combo box which only returns one value since you cannot choose multiple selections for a combo box. -- David |
|
|
|
|
QGilbert
skipping stones
Posts:8
|
| 10-28-2009 08:12 AM |
|
Ok, actually it was a check-box group but switched to combo-box since completion values were not working.  I'm confused on what parameters are available and when? On Dynamic Email setup I see $(cbgSpecial), $(cbgSpecial_Text) & $(cbySpecial_FullResults), $(CaclulateList:cbgSpecial). On DNN Role, only $(cbgSpecial)? |
|
|
|
|
David To
river guide
Posts:2719
|
| 10-28-2009 08:29 AM |
|
Here's the difference:
$(cbgSpecial): will display the value entered
$(cbgSpecial_Text): will display the option question text
$(cbgSpecial_FullResults): will display both
You can test out by emailing to yourself and having those tokens in your email message to see what they will display.
-- David |
|
|
|
|
Candace
river guide
Posts:2431
|
| 10-28-2009 08:41 AM |
|
Hi Q,
I see David responded to your question but let add more details as this will benefit others also.
The most commonly used is the $(ShortName) token. You can work this into your email in different ways like 'Dear $(FirstName) $(LastName)," to start your email.
When you use fields like a radio button or a checkbox group, the option value may not be the same as the option text. For example, if a person selects Product A, you might set the option value to be the price of the product. In this case, you can enter Products Selected: $(Products_Text) and Price: $ $(Products). This will then show up as:
Products Selected: Product A
Price: $ 10.00
The $(ShortName_FullResults) will always show up with the label. If you put $(Email_FullResults), it will show up like this: Email: you@emal.com
Hope this helps!
Candace
<!--Session data-->
|
|
|
|
|
QGilbert
skipping stones
Posts:8
|
| 10-28-2009 08:46 AM |
|
This is helpful, thanks. My point was that Dynamic Email events show all the parameters available but the other (Role) event only shows a subset. Are all parameters always available, despite the list provided? |
|
|
|
|
Candace
river guide
Posts:2431
|
| 10-28-2009 09:07 AM |
|
Yes, the tokens are always available within Dynamic Registration but the role event is not designed to use any tokens at all so it doesn't offer the same token list.
|
|
|
|
|
QGilbert
skipping stones
Posts:8
|
| 10-28-2009 10:02 AM |
|
Well, Candace, I'm not sure what you meant in that last response. Anyhow, the error now has to do with syntax, which is acceptable in SQLSvr but apparently not here. Since there's no way to test the SQL, I have to wait for emails:
An error occurred when attempting to run this SQL query:
DECLARE @roleID int,@roleIDs varchar(50), @curStart int,@curLen int, @delim varchar(1),@portalID int SET @delim = ',' SET @portalID = 0 SET @roleIDs = 22,55,49,53,54,50,23,63,52,51 WHILE LEN(@roleIDs) > 0 BEGIN SET @curStart = CHARINDEX(@delim,@roleIDs) IF @curStart < 0 SET @curStart = 0 SET @curLen = LEN(@roleIDs) - @curStart - 1 IF @curLen < 0 SET @curLen = 0 IF @curStart > 0 BEGIN SET @roleID = SUBSTRING(@roleIDs,1,@curStart - 1) SET @roleIDs = SUBSTRING(@roleIDs,@curStart + 1, LEN(@roleIDs) - @curStart) END ELSE BEGIN SET @roleID = @roleIDs SET @roleIDs = '' END EXEC dbo.AddUserRole @PortalID = @portalID, @UserID = (UserID), @RoleID = @roleID, @CreatedByUserID = -1 END
The error returned is:Incorrect syntax near ','. Incorrect syntax near '('. |
|
|
|
|
QGilbert
skipping stones
Posts:8
|
| 10-28-2009 11:53 AM |
|
Ok, I've got it now. There were additional syntax errors since the comma-separated list is not always a string. I strongly suggest a method for testing the SQL from the UI. |
|
|
|
|
David To
river guide
Posts:2719
|
| 10-29-2009 07:14 AM |
|
HI, you can enable debug mode by clicking on the link "Enable debug mode" or append at the end of your URL ?debug=true
Then when you fill out your form and submit, it will render the SQL statement with the actual values being passed. Copy and paste this complete statement to Host / SQL and execute it there and see if you get any errors. -- David |
|
|
|
|