|
|
|
|
|
|
|
To help you navigate to pages you most recently visited, select from the links below.
|
|
|
Product Forums
|
|
DotNetNuke Forum Module - Install & Config
|
|
Sharepoint Forums
|
|
Presentation Archive
|
|
Dynamic User Directory Demonstration Page 1
|
|
Dynamic User Directory
|
|
Page Tags
|
|
Out-of-the-Box Modules of DotNetNuke
|
|
Stock Quote
|
|
Dynamic Forms Demonstration #8
|
|
|
|
|
|
|
|
| Login | Register | Thursday, December 04, 2008
|
|
|
| Data Springs Product Forums...
|
|
|
|
|
| Author |
Messages |
|
Michael
Posts:8
skipping stones
 |
| 06/30/2007 7:17 PM |
|
Does anyone have some SQL that they could pass my way so that I can insert data I collect on my Dynamic Forms module into the Opt-In Email tables?
Thanks. |
|
|
|
|
Chad
Posts:2271
river guide
|
| 07/03/2007 8:40 AM |
|
Michael,
Hi. I was working on getting you the SQL for this but decided it would be easier to write a stored procedure to handle this (and you just pass the details to the stored procedure). I will try and get this to you today.
-Chad
|
|
|
|
|
Chad
Posts:2271
river guide
|
| 07/03/2007 9:06 AM |
|
Ok, here you go. This example below is assuming you have a category name 'DotNetNuke' and also that your site does not use object qualifiers. If your site uses object qualifiers you will need to add the qualifier name in front of any database objects. Just replace the text when you called the stored procedure 'DotNetNuke' with whatever your opt in email category name is. This is also assuming that this query is running on portalID 0. If you are inserting the data into another portalID just change the last parameter at the bottom when calling the stored procedure.
Execute this under Host, SQL:
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.[OptInEmail_AddOptInEmailFromDynamicForms]
@CategoryName nvarchar(300),
@FirstName nvarchar(255),
@LastName nvarchar(255),
@EmailAddress nvarchar(255),
@IsVerified int,
@PortalID int
AS
Declare @OptInEmailID uniqueidentifier
Declare @OptInEmailCategoryID uniqueidentifier
--Sets the Unique IDentifier, this will be used to store the opt in email address and also used in the linking opt in/category table
Set @OptInEmailID = NewID()
--Gets the unique identifier of the category
Set @OptInEmailCategoryID = (select OptInCategoryID from DataSprings_OptInCategory where Category like '%' + @CategoryName + '%')
--Select @OptInEmailCategoryID
--insert the information into the opt in email table
insert into DataSprings_OptInEmail (OptInEmailID,FirstName, LastName, EmailAddress, IsVerified, PortalID)
values (@OptInEmailID,@FirstName, @LastName, @EmailAddress, @IsVerified, @PortalID)
--Uses existing stored procedure to insert the information into the relational opt in email/category table
execute OptInEmail_AddOptInCategoryEmail @OptInEmailCategoryID, -1, @OptInEmailID
GO
-----------------------------------------------------------------------------------------------------
Once you have created the stored procedure you can test it under Host, SQL and finally insert it into the Dynamic Forms SQL Event.
To Test, execute this under Host, SQL:
OptInEmail_AddOptInEmailFromDynamicForms 'DotNetNuke', 'Test', 'User', 'test@theiremail.com', -1, 0
After testing to see that the email address test@testtheiremail.com (with a first name of test and a last name of user) was added to the opt in email tables and also Opted Into the category you specified you can use the stored procedure in a SQL Event. Something like this:
OptInEmail_AddOptInEmailFromDynamicForms 'DotNetNuke', '$(fname)', '$(lname), '$(email)', -1, 0
|
|
|
|
|
Michael
Posts:8
skipping stones
|
| 07/08/2007 7:43 PM |
|
| Thanks Chad - this works like a champ! Got it working well.... |
|
|
|
|
Stephen
Posts:16
wading in the water
|
| 05/11/2008 4:03 PM |
|
Hi Chad,
I am obviously not a SQL guru, but I am trying to do the above procedure and i believe the stored procedure is working, because it is inserting data into the optin list. but the "Fisrt Name", "Last Name" and "Email Address" are showing "$(FirstName)","$(LastName),"$(Email)" - NOT john,doe,jdoe@example.com. I have tried removing the ', and then it errors out. Can you point me in the right direction? |
|
|
|
|
Chad
Posts:2271
river guide
|
| 05/12/2008 3:14 PM |
|
Well, these are all based on the 'short field name' of each of your forms fields. So for example, if you setup a field called 'First Name', under the question textbox what is the property for Short Field Name? If its defaulting this and you never change it then its something like FName or FirstN or something like that. This means that instead of $(FirstName) you would want to do '(FirstN)' etc... Same thing for all of the other fields, maybe $(LastN) or $(EmailAddress). This all depends on what your fields short field name is set to.
-Chad
|
|
|
|
|
Stephen
Posts:16
wading in the water
|
| 05/12/2008 3:26 PM |
|
Hi Chad,
Thanks for the reply. I am also trying a different approach using dynamic registration (i posted the scenario in that forum) if you get a chance to look at it, might be easier? I did rename all the short fields to the corresponding field in the sproc. The Email Address is a hidden field, I think that might be a/the problem. Is the --select @OptInEmailCategoryID supposed to be included in the sproc? Mine is not. |
|
|
|
|
|
| You are not authorized to post a reply. |
|
|
|
ActiveForums 3.7
|
|
|
|
|
|
|
|
|
|
|