Stone Coast Web Design's Weblog

I've found it surprising how tricky it is to run ordinary .aspx pages and applications in Sharepoint.  Chris Johnson from Microsoft offers some interesting alternatives on how to do this:

http://blogs.msdn.com/cjohnson/archive/2006/09/05/740498.aspx

One of the cooler tools I've come across for this is SmartPart which is a generic Web part allows you to host any ASP.NET user control within your SharePoint sites. It allows for a designer-driven approach to developing Web parts instead of very code-intensive approach that is available out-of-the box with SharePoint.  You can download SmartPart here. Jans offers a video and a user's guide to get you started.

I've started using Sharepoint at work and although I've been coding in ASP.NET a couple of years, I'm finding the learning curve a little steep. 

If you use the Microsoft Office SharePoint Designer to add a new page to your site, you will see that it looks just like any other ASP.NET page.  However, if you try an add an inline code block using the standard

<script></script>

tags, you'll receive an error similar to this one:  "An error occurred during the processing of /Pages/test.aspx. Code blocks are not allowed in this file."

Hmm… yeah, code blocks are allowed in ASP.NET pages. SharePoint disables the ability to create server-side script by default, you have to turn it on. You do that in the web.config file, in the configuration/SharePoint/PageParserPaths configuration section:

<PageParserPaths>

  <PageParserPath VirtualPath="/pages/test.aspx" CompilationMode="Always" AllowServerSideScript="true" />

</PageParserPaths>

By the way, there are multiple web.config files and the one you should edit is C:\Inetpub\wwwroot\wss\VirtualDirectories\80\web.config

I found this article on one of my favorite sites on how to insert new records using an embedded SELECT statement.  Here is an example:

INSERT california_authors (au_id, au_lname, au_fname)
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'CA'

http://www.sqlteam.com/article/using-select-to-insert-records

Inserting a value into an identity column (primary key) is something I need to do often but usually forget how to do.  It's actually pretty easy so I made myself some notes on this topic.

If you try and run this in QA in SQL Server, you'll receive the error below because ID is the PK for tableA 

INSERT tableA(ID, TheValue)
VALUES (1, 'First Row')
GO

Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'tableA' when IDENTITY_INSERT is set to OFF.

The trick is to enable IDENTITY_INSERT for the table like this

SET IDENTITY_INSERT tableA ON

INSERT tableA(ID, TheValue)
VALUES (3, 'First Row')

SET IDENTITY_INSERT tableA OFF

The order of the parameters does not matter - just the names of the parameters.  You can specify the type of a parameter using the Type property - Int32, Decimal and DateTime.

Here is a demo of this code.

*** Movies.vb ***

Imports System.Web.Configuration

Public Class Movies

Private ReadOnly _conString As String

Public Sub UpdateMovie(ByVal id As Integer, ByVal title As String, &_
ByVal director As String, ByVal dateReleased As DateTime)
' Create Command
Dim con As New SqlConnection(_conString)
Dim cmd As New SqlCommand()
cmd.Connection = con
cmd.CommandText = "UPDATE Movies SET Title=@Title,Director=@Director,DateReleased=@DateReleased WHERE Id=@Id"

' Add parameters
cmd.Parameters.AddWithValue("@Title", title)
cmd.Parameters.AddWithValue("@Director", director)
cmd.Parameters.AddWithValue("@DateReleased", dateReleased)
cmd.Parameters.AddWithValue("@Id", id)

' Execute command
Using con
con.Open()
cmd.ExecuteNonQuery()
End Using
End Sub

Public Function GetMovies() As SqlDataReader
' Create Connection
Dim con As New SqlConnection(_conString)

' Create Command
Dim cmd As SqlCommand = New SqlCommand()
cmd.Connection = con
cmd.CommandText = "SELECT Id,Title,Director,DateReleased FROM Movies"

' Return DataReader
con.Open()
Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
End Function

Public Sub New()
_conString = WebConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString
End Sub

End Class


*** ExplicitShowMovies.aspx ***

<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Show Movies</title>
</head>
<body>
<form id="form1" runat="server">
<div>

<asp:GridView
id="grdMovies"
DataSourceID="srcMovies"
DataKeyNames="Id"
AutoGenerateEditButton="true"
Runat="server" />

<asp:ObjectDataSource
id="srcMovies"
TypeName="Movies"
SelectMethod="GetMovies"
UpdateMethod="UpdateMovie"
Runat="server">
<UpdateParameters>
<asp:Parameter Name="title" />
<asp:Parameter Name="director" />
<asp:Parameter Name="dateReleased" Type="DateTime" />
<asp:Parameter Name="id" Type="Int32" />
</UpdateParameters>
</asp:ObjectDataSource>

</div>
</form>
</body>
</html>

I just learned about the Page_Error sub which you can use to capture the error that occurred and redirect the user to a page which displays a user-friendly message that an error occurred.  The Page_Error event of the ASP.NET Page object is raised any time an unhandled error occurs in a page.

ASP.NET provides you with the ability to redirect the user to another page when an error occurs. To use this feature, set the ErrorPage property of the Page object to the URL of the page you want the user to see. You can add querystring parameters to the URL to pass specific error messages to the page. For instance, in the code snippets shown next, we've added three querystring parameters to the URL of an error message page: PageHeader, Message1, and Message2. PageHeader is set to the message "Error Occurred." Message1 is set to the message in the lastError exception. This will be the message from the last exception thrown. Message2 is a message we've added to say where the error was processed.

Example:

Protected Sub Page_Error(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Error
            Dim lastError As Exception

            'get the last error that occurred
            lastError = Server.GetLastError( )

            'do any logging, notifications, etc. here

            'set the URL of the page that will display the error and
            'include querystring parameters to allow the page to display
            'what happened
            Page.ErrorPage = "error_page.aspx" &_
                 "?PageHeader=Error Occurred" &_
                 "&Message1=" &lastError.Message &_
                 "&Message2=" &_
                 "This error was processed at the page level"

End Sub

I picked this up from SQLTeam.com and it's come in handy a few times.

CREATE PROCEDURE CompareTables(

@table1 varchar(100), 
@table2 Varchar(100),
@T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = '')
AS

-- Table1, Table2 are the tables or views to compare.
-- T1ColumnList is the list of columns to compare, from table1.
-- Just list them comma-separated, like in a GROUP BY clause.
-- If T2ColumnList is not specified, it is assumed to be the same
-- as T1ColumnList. Otherwise, list the columns of Table2 in
-- the same order as the columns in table1 that you wish to compare.
--
-- The result is all records from either table that do NOT match
-- the other table, along with which table the record is from.

declare @SQL varchar(8000);

IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList

set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +
' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +
@t2ColumnList + ' FROM ' + @Table2

set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +
' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'

exec ( @SQL)

ASP.NET comes with a RegularExpressionValidator which provides for a lot of flexbility when validating user input.  This is the best regular expression for emails I've seen. It tends to error on the side of accepting bad emails rather then rejecting good ones.

^([a-zA-Z0-9_'+*$%\^&!\.\-])+\@(([a-zA-Z0-9\-])+\.)+([a-zA-Z0-9:]{2,4})+$

Here is an example:

E-mail: <asp:textbox id="textbox1" runat="server"/>
<asp:RegularExpressionValidator id="valRegEx" runat="server"
ControlToValidate="textbox1"
ValidationExpression="^([a-zA-Z0-9_'+*$%\^&!\.\-])+\@(([a-zA-Z0-9\-])+\.)+([a-zA-Z0-9:]{2,4})+$"
ErrorMessage="* Your entry is not a valid e-mail address."
display="dynamic">*
</asp:RegularExpressionValidator>

UPDATE table1
SET col1 = t2.col1
FROM table1 AS t1
JOIN table2 AS t2
ON t1.col2 = t2.col2