Sanjay

Send e-mail with attachment from database image column (sql server 2k5)

Sanjay — Thu, 31 Jan 2008 21:44:34 GMT

Soooo...I needed to get files out of my SQL Server table (which has the files in an image column) and attach them to an email to send out. I honestly thought this would be a no-brainer but turned out it was pretty finicky. Anyway...the code to so is below just in case anyone is wondering.

    Function SendEmail()
        'Get your data (filename, content type, image column) from the db
        Dim dt As DataTable = GetYourStuff()
 
        If dt.Rows.Count <= 0 Then Return Nothing
 
        Dim msg As System.Net.Mail.MailMessage = New System.Net.Mail.MailMessage()
        msg.Subject = "Sample Message"
        msg.To.Add(New System.Net.Mail.MailAddress("Receiver@yourDomain.com"))
        Dim fromadx As New System.Net.Mail.MailAddress("Sender@yourDomain.com", "Sender Name")
        msg.From = fromadx
        msg.IsBodyHtml = False
        msg.Body = "Sample body..."
 
        Dim strMem As System.IO.MemoryStream = New System.IO.MemoryStream(CType(dt.Rows(0)("ImageColumn"), Byte()))
        Dim strWriter As System.IO.StreamWriter = New System.IO.StreamWriter(strMem)
        strWriter.Flush()
 
        'this is very important..wont work without it
        strMem.Position = 0
 
        'Filename and content type are hardcoded here, but I assume you get them from GetYourStuff() above
        Dim attachment As System.Net.Mail.Attachment = New System.Net.Mail.Attachment(strMem, "myFile.txt", "text/plain")
        msg.Attachments.Add(attachment)
        Dim smtpClient As System.Net.Mail.SmtpClient = New System.Net.Mail.SmtpClient(ConfigurationManager.AppSettings.Item("smtpserver"), 25)
        smtpClient.Send(msg)
    End Function

Alternatives to FindControl()?

Sanjay — Thu, 24 Jan 2008 17:55:21 GMT

Alright, as much as I try to avoid using FindControl() sometimes I need to get a control by its ID. I came across what seems like a good idea here : http://www.thescripts.com/forum/thread269592.html

It's in C#, but here is the VB.NET adaptation...

  549     Private Sub BuildControlHashTable()
  550         Dim cName As String = String.Empty
  551         For i As Int16 = 0 To Me.Controls.Count - 1
  552             cName = Me.Controls(i).ID
  553             If Not (cName Is Nothing) Then
  554                 cntrlHashTbl.Add(cName, Me.Controls(i))
  555             End If
  556         Next
  557     End Sub
  558     Private Function GetControlByName(ByVal cntrlName As String) As Control
  559         Return CType(Me.cntrlHashTbl(cntrlName), Control)
  560     End Function

Good idea? Bad idea? Thoughts? 

Export to vCard from .NET (VB.NET)

Sanjay — Wed, 31 Oct 2007 21:06:27 GMT

Hi all -
So was poking around a few weeks ago and couldn't find any code that'll allow me to export data to vcard...The class would require a little modification to utilize, but should get you 90% of the way there

Partial Class vCardExport

Inherits System.Web.UI.Page

Private Const nameFirst As String = "FirstName"

Private Const nameLast As String = "LastName"

Private Const nameMiddle As String = "MiddleName"

Private Const nameTitle As String = "Mr"

Private Const email As String = "firstname.lastname@domain.com"

Private Const uRL As String = "www.geekswithblogs.net/sanjayu"

Private Const telephone As String = "555 555 5555"

Private Const fax As String = "555 555 5555"

Private Const mobile As String = "555 555 5555"

Private Const company As String = "My Company"

Private Const department As String = "My Department"

Private Const mtitle As String = "My Title"

Private Const profession As String = "My Profession"

Private Const office As String = "227"

Private Const addressTitle As String = "New York"

Private Const streetName As String = "42nd Street"

Private Const city As String = "New York"

Private Const region As String = "Northeast"

Private Const postCode As String = "00000"

Private Const counTry As String = "USA"

Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

eVCard.VCard(Response, nameFirst, nameLast, nameMiddle, nameTitle, email, uRL, telephone, fax, mobile, company, department, mtitle, profession, office, addressTitle, streetName, city, region, postCode, counTry)

End Sub

End Class

Public Class eVCard

Inherits System.ComponentModel.Component

Public Shared Sub VCard(ByVal response As HttpResponse _

, ByVal nameFirst As String _

, ByVal nameLast As String _

, ByVal nameMiddle As String _

, ByVal nameTitle As String _

, ByVal email As String _

, ByVal uRL As String _

, ByVal telephone As String _

, ByVal fax As String _

, ByVal mobile As String _

, ByVal company As String _

, ByVal department As String _

, ByVal title As String _

, ByVal profession As String _

, ByVal office As String _

, ByVal addressTitle As String _

, ByVal streetName As String _

, ByVal city As String _

, ByVal region As String _

, ByVal postCode As String _

, ByVal counTry As String)

response.Clear()

response.Charset = ""

response.ContentType = "text/x-vCard"

Dim stringWrite As System.IO.StringWriter = New System.IO.StringWriter()

Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite)

stringWrite.WriteLine("BEGIN:VCARD")

stringWrite.WriteLine("VERSION:2.1")

stringWrite.WriteLine("N:" + nameLast + ";" + nameFirst _

+ ";" + nameMiddle + ";" + nameTitle)

stringWrite.WriteLine("FN:" + nameFirst + " " + nameMiddle _

+ " " + nameLast)

stringWrite.WriteLine("ORG:" + company + ";" + department)

stringWrite.WriteLine("URL;WORK:" + uRL)

stringWrite.WriteLine("TITLE:" + title)

stringWrite.WriteLine("ROLE:" + profession)

stringWrite.WriteLine("TEL;WORK;VOICE:" + telephone)

stringWrite.WriteLine("TEL;WORK;FAX:" + fax)

stringWrite.WriteLine("TEL;CELL;VOICE:" + mobile)

stringWrite.WriteLine("EMAIL;PREF;INTERNET:" + email)

stringWrite.WriteLine("ADR;WORK;ENCODING=QUOTED-PRINTABLE:" + ";" + _

office + ";" + addressTitle + "=0D" + streetName + ";" + _

city + ";" + region + ";" + postCode + ";" + _

counTry)

stringWrite.WriteLine("END:VCARD")

response.Write(stringWrite.ToString())

response.End()

End Sub

End Class

"Cannot drop the queue 'SqlQueryNotificationService-{GUID}', because it does not exist or you do not have permission."

Sanjay — Tue, 21 Aug 2007 14:44:53 GMT

Working on my first SQL 2K5 application here...and came across this error trying to implement the SqlNotificationService. I read a lot on this error...However, I was able to avoid this error by simply making the current user of own the DB.

1. Security > Users > [select the user your application is accessing the DB as]
2. Select properties (right click)
3. Find and check your DB schema in the list of "Owned Schemas"
4. Ensure "DB Owner" is checked off in the "Role Members" list

That's it...