Is it possible to use DR to add someone to a role as Vendor and Affiliate?

Can anyone show me how to do this?

Thanks,
Tom

 

Hi guys. Yes I think vendors/affiliates can be done but they are handled differently than roles. You would actually need to use SQL Events to do this and insert the values/update the values directly in the Vendors/Affiliates tables directly. Ill see if we can get an example posted soon but basically you would have to:

1. Create a Dynamic Completion Event with a type of SQL
2. Assuming you were just wanting to set it up as a vendor first (since I think affiliates must be listed as vendors first anyway) the SQL event might look like this:

exec AddVendor 0, 'Vendor NAme', '', '555 W 5th Street', '$(MyCity' etc...

Basically this would be assuming you were inserting into the vendors table using the core AddVendor stored procedure. The etc... is based on all of the parameters below. The AddVendor stored procedure has these parameters below that need to be passed to it. Also, keep in mind that you can use all of the tokens so if you wanted 'Vendor Name' to be a field you can use '$(YourVendorFieldName') etc...

You might first setup a vendor and check out everything that needs to be passed to the table (i.e. Select * from Vendors under Host, SQL). You should also test using the AddVendor stored procedure under Host, SQL as well before using it in a completion event. I would advise that you get the vendor update working first and then focus on the affiliate part next.

One other note of advise, we don't typically advise clients to start updating CORE tables directly. Although this uses the core stored procedure we still would back up your system and/or use a development system to test against. Although I see minimal risk here that is my disclaimer


Parameters that need to be passed to the stored procedure AddVendor:


@PortalId int,
@VendorName nvarchar(50),
@Unit nvarchar(50),
@Street nvarchar(50),
@City nvarchar(50),
@Region nvarchar(50),
@Country nvarchar(50),
@PostalCode nvarchar(50),
@Telephone nvarchar(50),
@Fax nvarchar(50),
@Cell nvarchar(50),
@Email nvarchar(50),
@Website nvarchar(100),
@FirstName nvarchar(50),
@LastName nvarchar(50),
@UserName nvarchar(100),
@LogoFile nvarchar(100),
@KeyWords text,
@Authorized bit

Tom - If you do implement this you might post your exact SQL query to help other users out as well.

-Chad

Chad,

I'm not SQL savvy, but I gave it my best shot. 

1. I added a vendor through Admin Vendors

2. I ran the SQL query SELECT * from VENDORS and got

VendorId VendorName Street City Region Country PostalCode Telephone PortalId Fax Email Website ClickThroughs Views CreatedByUser CreatedDate LogoFile KeyWords Unit Authorized FirstName LastName Cell 1 MasteryTV, Inc. 1603 S. Escondido Blvd. Escondido California United States 92025 760-230-0076 0 760-230-0075 tom@masterytv.com http://www.masterytv.com 0 0 1 8/7/2007 2:58:08 PM     2 True Thomas Wood 760-445-4588 <!-- End_Module_325 -->

3. I tried to run AddVendor with the following:

exec AddVendor '2', 'Tom Wood', '123 Main St.', 'Milton', 'MA', '02360', '760-230-0076', '0', '760-230-0076', 'tom@masterytv.com', 'http://www.wealthcoachweb.com', '0', '0', '1', '', '', '', '76', 'True', 'Tom', 'Wood', '760-45-4588'

but I got

There is an error in your query! <!-- End_Module_325 -->

which is not a lot of detail for debugging.

4. I am waiting to get it right in SQL before attempting to add it to the Completion Event in DR.

I hope it's just a syntax error. Let me know if you can help.

Thanks,

Tom Wood
CEO/Founder
MasteryTV.com

Tom,

Hi.No you are on the right track. I just tested against our betasprings.com site under Host, SQL (make sure you box below the multi-line textbox that its a query) and got this error:

System.Data.SqlClient.SqlException: Procedure or function AddVendor has too many arguments specified. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at...

What this could mean is that the stored procedure/parameters I gave you is a different stored procedure than what you have OR just means you added too many parameters.