Overview of Creating an Event Receiver

This is a response to a request to sharepointdiscussions@yahoo.com. The poster was looking for a way to send alerts only when changes to a list would affect a particular view of a list. There is no built-in capability, but an Event Receiver could do it...

------ Forwarded Message
From: Paul Stork 
Date: Fri, 5 Sep 2008 08:31:45 -0500
To: "sharepointdiscussions@yahoogroups.com"

Subject: RE: [sharepointdiscussions] Re: Email alerts on a list

Sorry, no.  Here's an overview of how to create an Event Receiver.  You'll
need to write the code that would be used to send the email, but this will
give you the basic shell.

<http://blah.winsmarts.com/2006-7-sharepoint_2007__list_events_practical_example__creating_a_rigged_survey.aspx>

Paul Papanek Stork /
paul.stork@mindsharp.com / 216-272-0573 /
Know More. Do More.
SharePoint Server MVP, MCT, MCSE+I, MCSA, MCSD, MCDBA, MCITP, MCPD
MVP Profile

SharePoint does not support reverse-proxy path modification

Using a load balancer to rewrite an incoming URL can be an attractive option for some situations (for example, where an incorrect URL has been widely advertised). However, it may not be a good idea for SharePoint sites.

From Technet http://technet.microsoft.com/en-us/library/cc288609.aspx:

Some reverse proxy devices can modify the path of a request (the portion of the URL that comes after the hostname and port number) in such a way that a request sent by the user to http://www.contoso.com/sharepoint/default.aspx, for example, is forwarded to the Web server as http://sharepoint.perimeter.example.com/default.aspx.

This is referred to as an asymmetrical path. Microsoft Office SharePoint Server 2007 does not support asymmetrical paths. The path of the URL must be symmetrical between the public URL and the internal URL. In the preceding example, this means that the "/sharepoint/default.aspx" portion of the URL must not be modified by the reverse proxy device.

Consider having the load balancer send a redirect response back to the browser instead of rewriting on the way to the server.

Using formulas in SharePoint

Pointers from sharepointdiscussions@yahoo.com to answers for a question I have often asked myself but never researched: just what can you put into a SharePoint calculated column formula?

------ Forwarded Message 
From: Peter Brunone 
Date: Tue, 29 Jul 2008 10:02:06 -0500 
To: "sharepointdiscussions@yahoogroups.com" 

Subject: Re: [sharepointdiscussions] using formulas in sharepoint 

Hi Liz, 

    The functions look (to me) a lot like XPath, but I'm at a loss to put a 
name on them.  You can find an overview and a complete function list, 
respectively, at the following locations: 

Summary: 

http://office.microsoft.com/en-us/sharepointtechnology/CH011711541033.aspx 

Function List: 

http://office.microsoft.com/en-us/sharepointtechnology/CH011711171033.aspx 

    As you will discover, the "if" condition isn't quite what you'd expect 
as a .NET developer.  There's actually a function that performs a test and 
returns one of two parameters based on the result of the test; you can read 
the explanation of the IF function at the following location: 

http://office.microsoft.com/en-us/sharepointtechnology/HA011610101033.aspx 

    Yell if you need more help. 

Cheers, 

*Peter Brunone * 

*Improving*  – It's what we do. | www.improvingenterprises.com 


On Tue, Jul 29, 2008 at 9:01 AM, Liz.Pumilia wrote: 

> Hello all, 
> 
> 
> 
> I am brand new to using formulas in SharePoint. First question is what 
> language is the formula written in? Next question is how would I write a 
> formula to fill in a field called "Completed date"  that looks at a 
> field [status] and uses the current date if the status is "06-Complete". 
> The following is what I tried, but again it is just a shot in the dark. 
> 
> 
> 
> If [status]="06-Complete" then {Completed date]=current 
> 
> 
> 
> Any guidance would be greatly appreciated. 
> 
> 
> 
> Thanks, 
> 
> 
> 
> Liz Pumilia 
> 
------ End of Forwarded Message

Exit vs. Close - Reduce Login Prompts

A customer support FAQ entry, which skips the the technobabble about why you get the prompts and gets right to the mitigation strategy…

 

One frequently-asked question is, "Why does SharePoint asked repeatedly for my username and password? " Both Microsoft and WSU are working to make the problem go away altogether. In the meantime, if you use Internet Explorer, there is one simple trick you can do yourself to minimize the number of login prompts you get when opening Microsoft Office documents (Word, Excel).

When you open a Word or Excel document from a SharePoint site, Internet Explorer doesn't open the document itself. It hands the request off to Word or Excel, and the application contacts the server directly. The server asks the application for your username and password, and the application asks you. This prompt is unavoidable with today's technology, configuration, and requirements.

However, once Word or Excel has your username and password, it keeps them until the application "exits". If you don't exit the application, the next time the SharePoint server asks for them, the application can pass them along without bothering you about it.

So when you are done viewing (and possibly editing) a document from a SharePoint server, don't use the "X" in the upper-right corner of the window; that exits the application and throws away your login information. Instead, if you are using Excel 2003, Excel 2007, or Word 2003, use the "close document" icon that's just below the "Exit" "X".

If you are using Word 2007, there is no "Close" icon; but you can (1) click on the "Office button", and (2) click on the "Close" item at the bottom of the drop-down menu.

 

Either way, you only need to log in once for each application, not once for each document.

Arithmetic on Times in SharePoint

A note about how to calculate a field as the sum of two times. The catches
are the format of the "Length" field (probably better titled "Duration"), and the use of the "TEXT" function.

------ Forwarded Message
From: Tom <tomsrig@hotmail.com>
Reply-To: "sharepointdiscussions@yahoogroups.com"
<sharepointdiscussions@yahoogroups.com>
Date: Wed, 11 Mar 2009 00:50:43 -0000
To: "sharepointdiscussions@yahoogroups.com"
<sharepointdiscussions@yahoogroups.com>
Subject: [sharepointdiscussions] Re: Useing Time in SharePoint

Hi, Peter,

You gave me some additional information I did not know, but here is what I
came up with to make this time thing work:

=TEXT([Air Date & Time]+[Show Length],"h:mm:ss")

The [Air Date & Time] column could be set to either text or a date & time
format. [Show Length] must be in this format; hh,mm,sec. For example
01:10:30. The calculated value with then be the air date and time + show
length. Works good.

Your added tip .....""m/d/yyyy hh:MM:ss") gives me the way to combine the
date and time.

Thanks much, Peter

Tom

--- In sharepointdiscussions@yahoogroups.com, Peter Brunone
<peter.brunone@...> wrote:
>
> Hi Tom,
>
> Does the calculated field save if you use this formula? According to the
> reference at
> http://office.microsoft.com/en-us/sharepointtechnology/HA011610841033.aspx ,
> TIME(0, 0, 30) would yield a decimal value of something like 0.000347. To
> add this to a DateTime field, you'd probably have to convert the date/time
> stamp using TIMEVALUE to get a similar decimal value, e.g.
>
> =TIMEVALUE([Date & Time]) + TIME(0,0,30)
>
> Another option would be to use VALUE([Date & Time]) + VALUE("0:0:30") and
> see if that would work.
>
> Of course to re-convert that into a meaningful time figure, you'll have
> to use the TEXT function. I'm not sure of its ability to display seconds,
> but you should have that available (note that I'm extrapolating the time
> syntax below based on my knowledge of past and current Microsoft date
> nomenclature):
>
> =TEXT(TIMEVALUE([Date & Time]) + TIME(0,0,30), "m/d/yyyy hh:MM:ss")
>
> Something like this *should* work, if anything does. Let me know how it
> goes.
>
> Cheers,
>
> Peter
>
> On Mon, Mar 9, 2009 at 3:37 PM, Tom <tomsrig@...> wrote:
>
> > Hi, Peter,
> >
> > I use the TIME formula, like below.
> >
> > =[Date & Time]+TIME(0,0,[Offset Number])
> >
> > [Date & Time] in this example would be 3/5/2009 1:00 PM - but notice I
> > can't display seconds in a list.
> >
> > The [offset number] in this example would be 30 (meaning 30 secoonds)- This
> > number is set as a NUMBER. SharePoint has no way of setting seconds that I
> > know of, and that may be my dilema.
> >
> > Yet, if you look at the formulas in the Help file, it does show a way to do
> > hours, minutes and seconds.
> >
> > I'm stumped.
> >
> > --- In sharepointdiscussions@yahoogroups.com, Peter Brunone
> > <peter.brunone@> wrote:
> > >
> > > What is your formula, and how does it display?
> > >
> > > Is there a chance that the seconds are somehow rendered but not visible
> > in
> > > the browser?
> > >
> > > Peter
> > >
> > > On Mon, Mar 9, 2009 at 2:42 PM, Tom <tomsrig@> wrote:
> > >
> > > > Hi, all,
> > > >
> > > > I have a case where I need to take a Time like: 10:10:10 (ten hours,
> > ten
> > > > minues, and 10 seconds, and subract (or add) a number like 1:10 (one
> > minute
> > > > and 20 seconds. The result should be 10:10:09, (ten hours, nine
> > minutes.
> > > >
> > > > So far, I can't get seconds to appear using the calculated column, even
> > > > though the formulas do support seconds.
> > > >
> > > > Any ideas?
> > > >
> > > > Tom
> >
>
>
> [Non-text portions of this message have been removed]
>




------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/sharepointdiscussions/

<*> Your email settings:
Individual Email Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/sharepointdiscussions/join
(Yahoo! ID required)

<*> To change settings via email:
mailto:sharepointdiscussions-digest@yahoogroups.com
mailto:sharepointdiscussions-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
sharepointdiscussions-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/


------ End of Forwarded Message

ASP and ADO: When the Table Name is a Parameter

I am particularly proud (probably unreasonably) of this little piece of work; after a long afternoon and evening of googling, I was unable to find a useful solution on the web – so I worked one out myself! The solution itself has the merit of simplicity, and (as far as I can tell) originality. It may also be obvious to anyone but a dolt…

I have been working on hardening some of our ASP/SQL Server applications against SQL injection attacks. Typically, this involves identifying SQL commands that are built by concatenation with user-supplied variables and replacing them with SQL commands that are "parameterized", and setting the parameters to the user-supplied variables via ADO. Parameters can never be interpreted as database commands, so there is no danger of command injection.

Note that the term "user-supplied" here means _any_ information that is included in a request from the browser, regardless of its initial source. For example, information sent from the server and returned by the browser in a "cookie" must be considered "user-supplied", because malicious client software (or, absent SSL, a "man-in-the-middle") could modify that information before it re-arrives at the server.

To use parameters in ADO, you must utilize the Command object, rather than just the Connection object's "Execute" method or the recordset's Open method. Here's some sample code for a simple case (the code assumes that the connection objConn is open):

set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = objDBConn
objCmd.CommandType = adCmdText
objCmd.NamedParameters = True
objCmd.CommandText = "select field1, field2 from tblSurveyList where strSurveyNumber= ? "
objCmd.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strSurveyNumber), strSurveyNumber)
on Error Resume Next
set rsSurveyIndex = objCmd.Execute
On Error GoTo 0
if objDBConn.Errors.count <> 0 then
call handleError (objDBConn.Errors, SQLQuery, "survey list query")
end if

The parameter value given in "CreateParameter" call effectively replaces the "?" in the command text when the SQL is executed; but the technique is _not_ concatenation. Instead, ADO creates an SQL stored procedure and passes the value as a parameter to that procedure.

But what happens when the parameter you need to pass is actually a table name? For example, in our Silhouette survey system, each survey has its own table to store responses. When we record responses, we need to use the survey number as a variable to update the appropriate table. I tried something like this:


set objCmd1 = Server.CreateObject("ADODB.Command")
objCmd1.ActiveConnection = objDBConn
objCmd1.CommandType = adCmdText
' passing table name as parameter (fails)
objCmd1.CommandText = "select * from ? where strRespID = ?"
objCmd1.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strRespTable), strRespTable)
objCmd1.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strRespID), strRespID)
...

What I got for my pains was an error message: "The tablename variable @P1 must be declared." Google searches didn't answer the question directly, but I did find numerous posts about problems with passing table names into stored procedures. The solutions all used "dynamic SQL" -- EXEC statements plus concatenation, our old enemy. What to do?

I already had an "existence check" in the code to make sure that the response table exists before calling it for update. This check uses the "INFORMATION_SCHEME.tables" table maintained by SQL Server, in which the table name is an ordinary variable with a value that can be passed as a parameter:


'test if the response table exists
objCmd.CommandType = adCmdText
objCmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.tables where table_name = ?"
objCmd.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strRespTable), strRespTable)

on Error Resume Next
set rsTableExists = objCmd.Execute
On Error GoTo 0
if objDBConn.Errors.count <> 0 then
call handleError (objDBConn.Errors, SQLQuery, "response table does not exist")
end if


If this check is successful, I reasoned, the value of the response table name must be valid, and can be included in the command text via concatenation without concern about injection.

The final query looks like:


objCmd1.ActiveConnection = objDBConn
objCmd1.CommandType = adCmdText
' cannot pass table name as parameter (apparently) -- but strSurveyResponse is a table that exists
' (per success of Response Table Existence query above) so concatenation is safe
objCmd1.CommandText = "select * from " & strRespTable & " where strRespID = ?"
objCmd1.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strRespTable), strRespTable)
objCmd1.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strRespID), strRespID)