|
|
|
|
|
|
|
To help you navigate to pages you most recently visited, select from the links below.
|
|
|
Product Forums
|
|
Sharepoint Forums
|
|
Securing your Source Code
|
|
Sharepoint 2007 Custom Web Parts
|
|
Overview of Microsoft SharePoint Server 2007
|
|
Overview on Installing Sharepoint 2007
|
|
Taking Advantage of DotNetNuke 4.4
|
|
SharePoint / MOSS 2007 RSS Feeds
|
|
Dynamic Forms Demonstration #8
|
|
Key SQL Performance Situations - Expressions
|
|
|
|
|
|
|
|
| Login | Register | Thursday, November 20, 2008
|
|
|
| Data Springs Product Forums...
|
|
|
|
|
| Author |
Messages |
|
Alberto
Posts:13
wading in the water
 |
| 06/12/2008 8:29 AM |
|
Hello,
is it possible to use a complex syntax (a SQL script) in a SQL Statement?
I'm trying to setup a completion event that inserts some data in some SQL tables of mine; I could successfully do a simple thing like this:
insert into mytable(userid, portalid, someotherfield)
values($(UserID), $(PortalID), 1)
and this is ok. But I'd want to execute a script such as:
insert into mytable(userid, portalid, someotherfield)
values($(UserID), $(PortalID), 1)
declare @bid int, @cc int
set @bid = (select scope_identity())
set @cc = (select ....) -- any other query to retrieve some @cc value
insert into another table(userid, portalid, myidvalue)
values($(UserID), $(PortalID), @bid, @cc)
Is this possible? My first tests apparently stop executing the script after first insert (cannot find records in second table), but I get no errors in eventlog and the user is correctly registered.
Can anybody help?
|
|
|
|
|
Chad
Posts:2206
river guide
|
| 06/12/2008 3:48 PM |
|
Yes, its for sure possible. We would suggest created a stored procedure and then passing all of your parameters to the stored procedure. This is much cleaner as it provides easier ways to test. Within the stored procedure you could include as many lines of code as you wanted to, along with if statements, select case statements, etc... Its not that you can't include these in SQL events, its just that its not as easy to test.
So... Create a stored procedure called stp_InsertIntoMyTable
Then, go to Host, SQL and pass some parameters to the stored procedure and verify that its working properly. Finally, go and within the SQL completion event just execute that same stored procedure passing tokens to the stored procedure instead of live data.
Such as:
stp_InsertIntoMyTable $(UserID), $(PortalID), 1
-Chad
|
|
|
|
|
Alberto
Posts:13
wading in the water
|
| 06/13/2008 1:50 AM |
|
Thanks Chad, very very nice a feature, this solved a lot of problems for me :-)
|
|
|
|
|
Richard
Posts:9
|
| 09/07/2008 12:44 PM |
|
I am trying to exec a stored procedure but cannot get it to work.
In the SQL statement this is my code. Should fire on any field
usp_AddScoutdata $(UserID),$(FatherN),$(MotherN),$(LastNam), $(Address),$(City), $(State),$(Zipcode),$(Email),$(ScoutEm),$(OtherEmail), $(PhoneNu), $(OfficeP),$(CellPho1),$(CellPho2),$(FaxNumb),$(Emergenc), $(Relation),$(ContPhone),$(FamilyP),$(DoctorsAddress),$(Physicia), $(InsurCarrier),$(Vehicle), $(Register),$(Drivers),$(VehicleLN),$(Numbero), $(EachPer),$(EachAcc),$(Property)
Do I need to add |
|
|
|
|
Chad
Posts:2206
river guide
|
| 09/08/2008 11:42 AM |
|
Richard,
Hi. I know we exchanged some emails over the weekend. Did you get this working now? I know from your example you need to add ' marks before and after your tokens but wasn't sure if there were other issues as well.
-Chad
|
|
|
|
|
Richard
Posts:9
|
| 09/09/2008 7:55 AM |
|
Chad, I got it to work but not the way I wanted it to work. I could not get the second page to exec the stored procedure. I must be missing something.
What I did was added all the input fields on the first page (the initial registration page). I did add the single quotes where needed. Once they clicked on "Register" that re-directed them to the payment process page and fired the the event to exec the stored procedure. That worked.
On the payment proceess page, I give users three options for payment by using checkboxes. I want to add another option and that is "Pay by check." When user just selects this option, I don't want them sent to the Paypal payment website. I want to re-direct to another page on my website. I know you mentioned something about disabling the Paypal event. How can I do this? Does this have to be a seperate field just for that?
Thanks, Richard |
|
|
|
|
Richard
Posts:9
|
| 09/09/2008 11:29 AM |
|
| How do you handle null values? I get an error when user leaves an input field blank. The table does allow those field to be null. Richard |
|
|
|
|
Chad
Posts:2206
river guide
|
| 09/09/2008 9:41 PM |
|
Richard,
HI. A couple things...
1. There is no way to automatically redirect them after checking a box, you would have to setup a redirection completion event and have them click Register/Update Registration before it will process the redirect. Then you can have them redirected to 'Page Z' if they checked a specific box etc...
2. There is a feature called EnablePGateway, you should be able to search the forums for this and Candace also recently added a demonstration with Dynamic Forms within the Dynamic Forms demonstrations area (feature works the same in Dynamic Registration). With this feature you must have the payment gateway feature turned on by default, you must then create a field with a short field name called EnablePGateway, and then you must have the value of that field set to False. For example, lets say you had a radio button or a combo box field, one of the values might be 'Skip payment' and the Option Text could be 'Skip Payment' but the option value must be set to 'False'. When the form is submitted it will skip the gateway if a field with that short field name has a value of false.
3. For null values, I would see if your column allows null values within the table. Remember, if you are passing ' around the tokens then its never actually null (its blank). To see exactly what is getting passed you could also create a email event with the exact SQL statement, then when you receive the email you can copy/paste it directly under Host, SQL and see if there are errors.
Also, are you receiving errors if the SQL doesn't execute? The system is supposed to send an email to the portal administrator with the failed SQL if it doesn't execute.
-Chad
|
|
|
|
|
|
| You are not authorized to post a reply. |
|
|
|
ActiveForums 3.7
|
|
|
|
|
|
|
|
|
|
|