David To
Posts:2120
river guide
 |
| 06/13/2009 8:38 PM |
|
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 |
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/14/2009 9:03 AM |
|
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 |
|
|
|
|
David To
Posts:2120
river guide
|
| 06/14/2009 10:04 AM |
|
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 |
|
|
|
|
David To
Posts:2120
river guide
|
| 06/14/2009 10:04 AM |
|
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 |
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/14/2009 11:26 AM |
|
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 |
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/14/2009 11:29 AM |
|
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 |
|
|
|
|
David To
Posts:2120
river guide
|
| 06/14/2009 1:14 PM |
|
| 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 |
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/15/2009 10:13 AM |
|
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 |
|
|
|
|
Chad Nash
Posts:3339
river guide
|
| 06/15/2009 10:45 AM |
|
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
|
|
|
|
|
Chad Nash
Posts:3339
river guide
|
| 06/15/2009 10:49 AM |
|
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
|
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/15/2009 11:18 AM |
|
Hi Chad,
Here is the query results from the email event message.
usp_certinsert @UserID, '$(allcerts), '$(cert)', '' |
|
|
|
|
David To
Posts:2120
river guide
|
| 06/15/2009 12:17 PM |
|
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 |
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/15/2009 12:26 PM |
|
Here is the email response
?debug=true
usp_certinsert @UserID, '$(allcerts), 'ITIL,GSE,OCA,MCSD,MCSE,Other', 'MP3'
|
|
|
|
|
David To
Posts:2120
river guide
|
| 06/15/2009 12:33 PM |
|
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 |
|
|
|
|
Chad Nash
Posts:3339
river guide
|
| 06/15/2009 12:47 PM |
|
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
|
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/15/2009 5:03 PM |
|
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 |
|
|
|
|
David To
Posts:2120
river guide
|
| 06/16/2009 8:47 AM |
|
| 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 |
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/16/2009 9:27 AM |
|
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 |
|
|
|
|
David To
Posts:2120
river guide
|
| 06/16/2009 1:23 PM |
|
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
|
|
|
|
|
gdecuir
Posts:60
river guide
|
| 06/17/2009 10:31 AM |
|
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 |
|
|
|
|