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

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

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

Hi Chad,

Where do I place this SQL event? In the client side event box or elsewhere? Not sure where it should go.

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

Hi Chad,

Thanks for the quick reply. I will give it a try.

Thanks again,
Gary

Hi Chad,

THANKS IT WORKED AND I'M OFF AND RUNNING!!!!!!

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.

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

Hi, How do you turn on debuging?

Hi David,
Here is the SQL query

Update UserProfile Set PropertyValue = 'ITIL,PMP,Other' + ',Test' Where PropertyDefinitionID = 75 and UserID = 3

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

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!

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

This is the message

The query did not return any data

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

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

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

HI, try this out:
create procedure usp_certsinsert(@userid int, @allcerts nvarchar(500),@cert nvarchar(50),@certother nvarchar(50))
as
begin
declare @allcertsVar nvarchar(500)
If (@allcerts = '') set @allcertsVar = (@cert + ' , ' + @certother) 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 = @allcertsID)
begin
insert into UserProfile(UserID,PropertyDefinitionID,PropertyValue,Visibility) values(@UserID,@allcertsID,@allcertsVar,2)
end
else
-- record exists, update it instead
begin
update UserProfile set PropertyValue = @allcertsVar where UserId = @UserID and PropertyDefinitionID = @allcertsID

end

end

-- David

Hi David,

Tried it and it didn't work. For some reason it's not updating the field. Just to make sure I'm setting this up correctly , the procedure is located in the Completion Event > SQL Statement. Settings are set to "fire on any response" and (New Registration / Update Registration).

Thanks,
Gary

HI Gary, this is not how you do it. The sql stored procedure, you copy and paste it into HOST / SQL and execute it there; be sure to click on "Execute as script" checkbox. Then in your SQL completion event, you call the stored procedure, passing your parameter tokens:

usp_certinsert @UserID, '$(allcerts), '$(cert)', '$(certother)'

try this out instead. -- David

HI Gary, this is not how you do it. The sql stored procedure, you copy and paste it into HOST / SQL and execute it there; be sure to click on "Execute as script" checkbox. Then in your SQL completion event, you call the stored procedure, passing your parameter tokens:

usp_certinsert @UserID, '$(allcerts), '$(cert)', '$(certother)'

try this out instead. -- David

Hi David,

Question: This may sound dunb because I'm new to all this. But, once I place the sql stored procedure in the Host/SQL and execute it, is it there for good or how do it remember to store this all the time?

Thanks,
gary

Hi Daivd,

I get the following message when I execute the store procedure.

System.Data.SqlClient.SqlException: There is already an object named 'usp_certsinsert' in the database. 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) create procedure usp_certsinsert(@userid int, @allcerts nvarchar(500),@cert nvarchar(50),@certother nvarchar(50)) as begin declare @allcertsVar nvarchar(500) If (@allcerts = '') set @allcertsVar = (@cert + ' , ' + @certother) 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 = @allcertsID) begin insert into UserProfile(UserID,PropertyDefinitionID,PropertyValue,Visibility) values(@UserID,@allcertsID,@allcertsVar,2) end else -- record exists, update it instead begin update UserProfile set PropertyValue = @allcertsVar where UserId = @UserID and PropertyDefinitionID = @allcertsID end end

Then you need to replace "create" with "alter" so go back to Host / SQL and copy and paste the same stored procedure except change the first word from "create" to "alter". And no, once you create the stored procedure, it is permanent in the database and will remember next time you call it unless you delete the procedure by issuing a drop procedure. - -David

Hi David,

Did as instructed and it still didn't work. When executed the stored procedure. I got a successful SQL query statement. However, when I update the registration form the allcerts field is not updated.

Thanks,
gary

Gary,

Hi. Here is a great way to test queries you are executing etc...

Change the query to be an email event instead of a SQL Event. Copy/Paste the exact query (you receive in an email) under host/SQL (feel free to post here as well). This will typically render an error or help identify the problem (maybe a missing ' or maybe an extra END tag or something. At the very least you would see if a token wasn't being replaced etc...

-Chad

Also, just from an initial glance and copying/pasting the query into SQL Management Studio I see the error:

Must declare the scalar variable "@allcerts". I see you are declaring allcertsvar but you are not declaring the @allcerts ever. There might be some other issues too though, ill have to compare this to the thread David mentioned above as I know that query works/we have help implement it for another client.


-Chad