Hi Chad,

Here is the query results from the email event message.

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

That doesn't look right. You should have something like: usp_certinsert 5, 'Certificates','Cert',''. Can you turn on debug mode? At the top of the URL, append ?debug=true and then continue to fill out your form and then hit submit. You should see what it's actually passing:
usp_certinsert etc.

-- David

Here is the email response

?debug=true
usp_certinsert @UserID, '$(allcerts), 'ITIL,GSE,OCA,MCSD,MCSE,Other', 'MP3'

The UserID should be an integer. That's why it's not executing. You're passing it incorrectly. Also, your $(allcerts) signifies you're missing an ending single quote. Because otherwise it would have a value or a blank ''.
Your passing of parameters should be:

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

please double-check. -- David

Try this query... I don't think you need the Where clause when setting the value. I also think you had 2() in the query instead of just 2.

begin
declare @allcertsVar nvarchar(500)
declare @allcerts 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 = @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 Chad,

Got the following error message

System.Data.SqlClient.SqlException: Incorrect syntax near '('. Incorrect syntax near '('. Incorrect syntax near 'UserID'. 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) begin declare @allcertsVar nvarchar(500) declare @allcerts 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 = @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

Thanks,
Gary

HI, let's try to resolve this issue asap. I know the SQL stored procedure should work. Can you include a screenshot of your Form completion event so I can verify what you are passing through to the stored procedure because based on the discussion above, the parameters are not being passed correctly. -- David

Hi David,

You have a email address I can send the screen shoot to? The forum is not alowing me to paste or attach it.

My email is gdecuir777@verizon.net Also, if you need to log into my site let me know.

Thanks,
gary

HI Gary, after logging in and checking our your webpage, I finally found out what the problem is. In your DR form, you do not have a shortname of "allcerts" anywhere. Hence, when you are passing it to the stored procedure, it's value is actually $(allcerts). Hence, if you look carefully at the stored procedure:

If (@allcerts = '') set @allcertsVar = (@cert + ' , ' + @certother) else set @allcertsVar = @allcerts

@allcertsVar will always be $(allcerts) and this would not work the way it supposed to work. You need to create a field with shortname of "allcerts" in your form somewhere. Note that it cannot be a Text / HTML field since this field cannot be passed their value. -- David

Hi David,

I thought because I had a DNN Core user profile field "allcerts" it wasn't needed in the DS form. I thought it would pass the stored procedure to the DNN Core field.

Now I understand. I created a field with the shortname "allcerts" on the DS form. However, the field still didn't update.

The filed is a Singleline textbox (long)

Thanks,
Gary

Hi David/Chad

Chad per our conversation, here is the message from the host/sql query.

System.Data.SqlClient.SqlException: Could not find stored procedure 'usp_certinsert'. 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) usp_certinsert 1,'','MCTS,BCPIP,PMP,SAS', 'MPS'

System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'LastUpdatedDate', table 'C7917_gpct.dbo.UserProfile'; column does not allow nulls. INSERT fails. The statement has been terminated. 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) usp_certsinsert 1,'','MCTS,BCPIP,SAS,RHCE', 'test'

Okay, in the store procedure I posted earlier, I left out the "LastUpdatedDate" column which cannot be left empty. Here's the final store procedure that does work with the "LastUpdatedDate" included:

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,LastUpdatedDate) values(@UserID,@allcertsID,@allcertsVar,2,getdate())
end
else
-- record exists, update it instead
begin
update UserProfile set PropertyValue = @allcertsVar, LastUpdatedDate = getdate()

where UserId = @UserID and PropertyDefinitionID = @allcertsID

end

end

It is almost working, I believe I discovered a bug in Dynamic Registration, please check the tickets in Snowcovered. I would love to get some feedback on this in the morning (Australian time)

Thanks
Thomas