Stone Coast Web Design's Weblog

Navigation

Search

Blogroll

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

| |

E-mail

Total Posts: 112
This Year: 50
This Month: 0
This Week: 0
Comments: 0

Tuesday, October 16, 2007

Closing a OleDB Database Connection That is Locked

Tuesday, October 16, 2007 8:02:16 PM (Eastern Standard Time, UTC-05:00) ( ASP.NET | Database )

Often times when I'm developing a site for a client their budget permits that I use a low-cost or free database which means that I usually go with Access or MySQL. Access has this nasty habit of not closing it's connection all of the time and the database is left in a "suspended" or locked state where the pages are not able to open another connection to it. I find that this happens sometimes even when I explicitly close the OleDBConnection. One solution is to run the bit of code below which has worked well for me.

** CloseDatabase.aspx.vb **

Partial Class CloseDatabase
Inherits System.Web.UI.Page
End Class

** CloseDatabase.aspx **

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="CloseDatabase.aspx.vb" Inherits="CloseDatabase" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
Dim Conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("~/App_Data/mydbase.mdb"))

Sub Page_Load()
Dim ConnState As String = Conn.State.ToString()
If ConnState = "Open" Then
Try
Conn.Close()
Response.Write("The database was closed successfully.")
Catch Exc As Exception
Response.Write("The database was opened and could not be closed:<br />" & Exc.ToString().Replace(Environment.NewLine(), "<br />"))
End Try
ElseIf ConnState = "Closed" Then
Response.Write("There must be something else wrong... the database is already closed.")
Else
Response.Write("You should probably take up another career... the database was not opened OR closed... :| ")
End If
End Sub

</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>

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

Comments [0] | Trackback |

Sharepoint 2007 Deployment Solutions

Tuesday, October 16, 2007 2:08:52 PM (Eastern Standard Time, UTC-05:00) ( Sharepoint )

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.

Comments [0] | Trackback |

Thursday, October 04, 2007

Code-blocks are not allowed in this file: Using Server-Side Code with SharePoint

Thursday, October 04, 2007 12:32:55 PM (Eastern Standard Time, UTC-05:00) ( Sharepoint )

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

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>

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

Comments [0] | Trackback |

Wednesday, September 26, 2007

Using SELECT to INSERT records

Wednesday, September 26, 2007 2:51:56 PM (Eastern Standard Time, UTC-05:00) ( Database | SQL )

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

Comments [0] | Trackback |

Wednesday, August 08, 2007

How to Insert Values into an Identity Column in SQL Server

Wednesday, August 08, 2007 9:09:12 AM (Eastern Standard Time, UTC-05:00) ( Database | SQL )

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

Comments [0] | Trackback |

Wednesday, July 25, 2007

Using Explicit Parameters with the ObjectDataSource control

Wednesday, July 25, 2007 2:02:49 PM (Eastern Standard Time, UTC-05:00) ( ASP.NET )

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>

Comments [0] | Trackback |

Tuesday, July 24, 2007

Handling Errors At the Page-Level

Tuesday, July 24, 2007 8:30:11 PM (Eastern Standard Time, UTC-05:00) ( ASP.NET )

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

Comments [0] | Trackback |

Monday, July 23, 2007

Comparing Records from Two Tables

Monday, July 23, 2007 10:09:52 AM (Eastern Standard Time, UTC-05:00) ( SQL )

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)

Comments [0] | Trackback |

Thursday, July 19, 2007

RegEx for validating email addresses with RegularExpressionValidator

Thursday, July 19, 2007 1:34:44 PM (Eastern Standard Time, UTC-05:00) ( ASP.NET )

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>

Comments [0] | Trackback |

Mostly Web Development

Navigation

Search

Categories

On this page

Archive

Blogroll