How to set up database mail in SQL Server 2005

One of the great improvements in SQL Server 2005 is that it no longer requires an Exchange Server in order to send email. One of the most basic uses of email in SQL Server is to notify someone if a job fails – a backup, for example.

Here is a link to a most excellent article that explains exactly how to set up database mail in SQL Server 2005.

Once you’ve set up database mail, you need to go to SQL Server Agent properties, and on the Alert System tab, enable the mail profile you have set up. Then make sure you restart SQL Server agent. If you don’t restart SQL Server Agent, your test emails will work, but your job notifications will not.

Blank popups in Joomla

If you are using Joomla and your popups come up blank (for example, the add a link window or the HTML window) here is a solution.

In the configuration.php file, edit the $mosConfig_live_site line so that it reads ‘http://www.yoursite.com’ instead of ‘http://yoursite.com’. In other words, add the www to the URL.

For a more in-depth discussion of this issue, click here.

Recursive method to reset controls in a ControlCollection

This is a shared public method used to recursively interate through the controls in a control collection and set specified controls to an empty or unselected state.

First, I build two private shared helper methods to deal with an individual control. The first one checks to see what kind of control it is:

‘Helper function to check for type of control in a collection
Private Shared Function CheckControlType(ByVal ctl As Control) As Boolean

If TypeOf (ctl) Is TextBox Or TypeOf (ctl) Is DropDownList Or _
TypeOf (ctl) Is CheckBox Or TypeOf (ctl) Is RadioButton Or _
TypeOf (ctl) Is ListBox Or _
(TypeOf (ctl) Is Label And ctl.ID.StartsWith("lbl")) Then

Return True

End If
End Function

You’ll notice that for a label control I also test to see if the label’s ID starts with the characters ‘lbl’. This is because the ASP.NET Validation Controls are treated as Labels when looping through the collection, and I don’t want to evaluate those. So I make sure I name labels I want to check lblSomething so I can specify that only those will be checked.

The second helper method resets the control:

‘Helper function to reset fields
Private Shared Sub ResetFields(ByVal ctl As Control)

If TypeOf (ctl) Is TextBox Then
CType(ctl, TextBox).Text = “”
ElseIf TypeOf (ctl) Is Label Then
CType(ctl, Label).Text = “”
ElseIf TypeOf (ctl) Is DropDownList Then
CType(ctl, DropDownList).SelectedValue = “-1”
ElseIf TypeOf (ctl) Is CheckBox Then
CType(ctl, CheckBox).Checked = False
ElseIf TypeOf (ctl) Is RadioButton Then
CType(ctl, RadioButton).Checked = False
ElseIf TypeOf (ctl) Is ListBox Then
CType(ctl, ListBox).SelectedValue = “”
End If

End Sub

Once these helpers are in place, I recursively loop through the control collection and call the helpers as needed.

‘Clear fields in a collection
Public Shared Sub ClearFields(ByVal Controls As ControlCollection)

‘Iterate through control collection – if type of control is textbox,
‘dropdownlist, checkbox, radiobutton, label or listbox, clear control
For Each ctl As Control In Controls

‘clear main controls in collection
If CheckControlType(ctl) Then
ResetFields(ctl)

‘Recursion – if control has its own control collection (e.g., panel, div, etc)
‘method calls itself to clear that collection
ElseIf ctl.HasControls Then
ClearFields(ctl.Controls)

End If
Next

End Sub

I keep this method in a class called Presentation, which contains a variety of utility methods having to do with formatting and configuring pages and controls. I can then call this public shared method from anywhere in my code, passing it whatever ControlCollection I want it to process:

Presentation.ClearFields(divEdit.Controls)

Using System.Net.Mail namespace to send email

Here’s a little demo of how to use the System.Net.Mail namespace to have your application automatically send an email. This sample is a class method that sends an email notification to the developer/webmaster/website owner/whoever when a new user registers on the site.

First, some set up. I keep the values for the From email address and the host IP address in the appSettings section of the web.config file. To retrieve them, I have a couple shared ReadOnly properties in my class, like so:

Public Shared ReadOnly Property Host() As String
Get
Return System.Configuration.ConfigurationManager.AppSettings("Host").ToString
End Get
End Property

Public Shared ReadOnly Property FromAddress() As String
Get
Return System.Configuration.ConfigurationManager.AppSettings("From").ToString
End Get
End Property

Now that I can easily get to these settings, I build my method, as below:

”’ <summary>
”’ Send email notification when a new user registers
”’ </summary>
”’ <param name=”ToAddress”>System.String containing the email
”’ address that the message should go to
”’ </param>
Public Shared Sub NotifyEmail(ByVal ToAddress As String)

Try

‘instantiate new MailMessage object and pass in from and to
’email addresses (FromAddress is a readonly property pulling from
‘web.config file)
Using message As New System.Net.Mail.MailMessage(FromAddress, ToAddress)

‘set rest of message properties
message.Subject = "New User"
message.IsBodyHtml = True ‘set to true if you want to use html in body
message.Body = "A new user has registered"

‘instantiate new SmtpClient object and pass in host information
‘(Host is a readonly property pulling from web.config file)
Dim smtp As New System.Net.Mail.SmtpClient(Host)

‘send message
smtp.Send(message)

‘dispose of MailMessage object
End Using

Finally

‘dispose of SmtpClient object
smtp = Nothing

End Try

End Sub