Writing data to SQL then forwarding the info via email

With the help of experts-exchange we have been able to get our Agency Services Impact Form to write data to our SQL server. THANK YOU.

Next question:

How do have the app email the results after writing them to the DB?

The code I have isnt working I get an exception on Dim myMessage As MailMessage = New MailMessage() that says"

--Begin exception--
System.FormatException was unhandled by user code
  Message=The specified string is not in the form required for an e-mail address.
  Source=System
  StackTrace:
       at System.Net.Mime.MailBnfHelper.ReadMailAddress(String data, Int32& offset, String& displayName)
       at System.Net.Mail.MailAddress.ParseValue(String address)
       at System.Net.Mail.MailAddress..ctor(String address, String displayName, Encoding displayNameEncoding)
       at System.Net.Mail.MailMessage..ctor()
       at ImpactSurvey.Button1_Click(Object sender, EventArgs e) in \\data\kba\Departments\Information Technology\IT Team\websites\Sevice_Forms\ImpactSurvey.aspx.vb:line 69
       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
  InnerException"

--End Exception--

Here is my code:


Imports System.Data.SqlClient
Imports System.Net.Mail
Partial Class ImpactSurvey
    Inherits System.Web.UI.Page
    Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    Private _strsql As String
    Private Property strsql As String
        Get
            Return _strsql
        End Get
        Set(value As String)
            _strsql = value
        End Set
    End Property

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        con = New SqlConnection("Data Source=WEBSQL\WEBDATA;Initial Catalog=Service_Forms;Integrated Security=True")
        con.Open()
        strsql = "insert into AgencyServicesImpactSurvey (SurveyMedical, SurveyResources, SurveyMeetings, SurveyPaperwork, SurveyGroceries, SurveyPrograms, SurveyVision, SurveyIndependence, SurveySatisfied, SurveyComments) values ('" _
        & MedicalDropdownList.SelectedValue & "','" _
        & ResourcesDropdownlist.SelectedValue & "','" _
        & MeetingsDropdownlist.SelectedValue & "','" _
        & PaperworkDropdownlist.SelectedValue & "','" _
        & GroceriesDropdownlist.SelectedValue & "','" _
        & ProgramsDropdownlist.SelectedValue & "','" _
        & visionDropdownlist.SelectedValue & "','" _
        & IndependenceDropdownlist.SelectedValue & "','" _
        & SatisfiedDropdownlist.SelectedValue & "','" _
        & CommentsTextbox.Text & "')"
        cmd.CommandText = strsql
        cmd.Connection = con
        cmd.ExecuteNonQuery()
        MsgBox("Data Saved")
        con.Close()
        con.Dispose()


        'Capture fields for email body

        Dim fileName As String = Server.MapPath("~/App_Data/Survey.aspx")
        Dim mailBody As String = System.IO.File.ReadAllText(fileName)

        mailBody = mailBody.Replace("##MedicalLabel##", MedicalLabel.Text)
        mailBody = mailBody.Replace("##Medical##", MedicalDropdownList.SelectedValue)
        mailBody = mailBody.Replace("##ResourcesLabel##", ResourcesLabel.Text)
        mailBody = mailBody.Replace("##Resources##", ResourcesDropdownlist.SelectedValue)
        mailBody = mailBody.Replace("##MeetingsLabel##", MeetingsLabel.Text)
        mailBody = mailBody.Replace("##Meetings##", MeetingsDropdownlist.SelectedValue)
        mailBody = mailBody.Replace("##PaperworkLabel##", PaperworkLabel.Text)
        mailBody = mailBody.Replace("##Paperwork##", PaperworkDropdownlist.SelectedValue)
        mailBody = mailBody.Replace("##GroceriesLabel##", GroceriesLabel.Text)
        mailBody = mailBody.Replace("##Groceries##", GroceriesDropdownlist.SelectedValue)
        mailBody = mailBody.Replace("##ProgramsLabel##", ProgramsLabel.Text)
        mailBody = mailBody.Replace("##Programs##", ProgramsDropdownlist.SelectedValue)
        mailBody = mailBody.Replace("##VisionLabel##", VisionLabel.Text)
        mailBody = mailBody.Replace("##Vision##", visionDropdownlist.SelectedValue)
        mailBody = mailBody.Replace("##IndependenceLabel##", IndependenceLabel.Text)
        mailBody = mailBody.Replace("##Independence##", IndependenceDropdownlist.SelectedValue)
        mailBody = mailBody.Replace("##SatisifiedLabel##", SatisfiedLabel.Text)
        mailBody = mailBody.Replace("##Satisfied##", SatisfiedDropdownlist.SelectedValue)
        mailBody = mailBody.Replace("##Comments##", CommentsTextbox.Text)



        'Create and send the email:

        Dim myMessage As MailMessage = New MailMessage()
        myMessage.Subject = "Agency Services Imapact Survey"
        myMessage.Body = mailBody

        myMessage.From = New MailAddress("kba_forms@keystoneblind.org")
        myMessage.IsBodyHtml = True
        myMessage.To.Add(New MailAddress("kevin@keystoneblind.org"))
 mySmtpClient.Credentials = New System.Net.NetworkCredential("myusername", "mypassword")

        Dim mySmtpClient As SmtpClient = New SmtpClient()
        mySmtpClient.Credentials = New System.Net.NetworkCredential
        mySmtpClient.Send(myMessage)

        Dim smtpClient As New SmtpClient()
        Dim userState As Object = myMessage

        'Attach event handler for async callback
        AddHandler smtpClient.SendCompleted, AddressOf SmtpClient_OnCompleted

        Try
            'Send the email asynchronously
            smtpClient.SendAsync(myMessage, userState)
        Catch smtpEx As SmtpException
            'Error handling here
        Catch ex As Exception
            'Error handling here
        End Try
    End Sub
    ''' <summary>
    ''' Event handler for processing completion information after asynchronous email sent.
    ''' </summary>
    Public Sub SmtpClient_OnCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.AsyncCompletedEventArgs)

        'Get UserState as MailMessage instance from SendMail()
        Dim mailMessage As MailMessage = CType(e.UserState, MailMessage)

        If (e.Cancelled) Then
            FeedBackLabel.Text = "Sending of email message was cancelled. Address=" '+ mailMessage.To(0).Address
        End If

        If Not (e.Error Is Nothing) Then
            FeedBackLabel.Text = "Error occured. Please contact KBA's technical support at 724.347.5501 ext 252"
        Else
            Response.Redirect("~/default.aspx")
            mailMessage.Dispose()
        End If
    End Sub
End Class

As always, any replies are greatly appreciated
ICantSeeAsked:
Who is Participating?
 
jonnidipConnect With a Mentor Commented:
This should be a problem in your .config, as it may contain bad data.
I have reproduced this error by creating a "mailSettings" section under "system.net" in my config:
<system.net>
  <mailSettings>
    <smtp from="bad.email.address">
      <network host="smtp.myprovider.com"/>
    </smtp>
  </mailSettings>
</system.net>

Open in new window


Please double check your configuration file, as it really seems to cause this error at the point where you declare the "MailMessage" object.


Regards.
0
 
jonnidipConnect With a Mentor Commented:
Your problem is here:
The specified string is not in the form required for an e-mail address
The 2 addresses you wrote in the portion of code above seem to be correct (maybe those are examples?).
So could you please check your "To", "From" and "Cc" addresses?
Additionally, ensure that multiple addresses are being separated by a comma (","), not by semicolon (";").

Regards.
0
 
ICantSeeAuthor Commented:
Thank you for your response.

The email address in the code is the actual address (oversight on my part before posting it).

In any event I agree that error is stating the email address is incorrect, but it is in fact correct. (its my email address).

I have successfully written code for a web app that writes to our SQL server, and have also written code that sends email from a web app. I have never tried to combine them which is what I need to do this time.

Once I had this app writing to the DB, I copy and pasted the code from the app that sends an email from our Exchange server into it. Now it throws an exception for some reason.
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
jonnidipConnect With a Mentor Commented:
I am sorry, but I am not able to find the problem.
I tried to replicate the error, but no luck.
I just want to ask you why you do this 2 times:

(#1st)
Dim mySmtpClient As SmtpClient = New SmtpClient()
mySmtpClient.Credentials = New System.Net.NetworkCredential
mySmtpClient.Send(myMessage)

(#2nd)
Dim smtpClient As New SmtpClient()
Dim userState As Object = myMessage
'Attach event handler for async callback
AddHandler smtpClient.SendCompleted, AddressOf SmtpClient_OnCompleted
Try
  'Send the email asynchronously
  smtpClient.SendAsync(myMessage, userState)
Catch smtpEx As SmtpException
0
 
ICantSeeAuthor Commented:
I am not sure. This is code that was copied from multiple searches on the Internet, a few years ago. Its been working, so I thought it was o.k.

Which instance should I remove?
0
 
jonnidipConnect With a Mentor Commented:
Have you tried to debug your procedure?
I am not sure why you are doing a "smtp.send" and then a "smtp.sendasync" of the same mailmessage...
0
 
ICantSeeAuthor Commented:
Which one should I get rid of? All I am trying to do is send the data collected to my email address.
0
 
jonnidipCommented:
Which one should I get rid of?
Sorry, this is up to you...
Do you want emails to be sent by "smtp.send" or "smtp.sendasync"?

Anyway, this does not seem to be the problem (altough I would say that emails are sent twice)...
The best way to find where your code is wrong is to debug it.
0
 
ICantSeeAuthor Commented:
I have posted the exception. The app is running in debug mode. how do I debug further.

I really don't care how it sends the email nor do I understand the difference between the two options. I just need the email sent to the intended recipient.
0
 
jonnidipConnect With a Mentor Commented:
You can TRY to comment out the second call (the async one), with related handler and handler registration.
Then please add a breakpoint at the line with:       mySmtpClient.Send(myMessage)
and run your procedure.
When you are at the breakpoint you can see the content of myMessage by right-clicking over it and choosing "Quick watch" (it will be opened in a new modal window).
Please look at the parameters if that object, in particular "To", "From" and "Cc" to see if there is something wrong.
If possible, you can attach a print-screen of the watch to a e-e post.

Regards.
0
 
ICantSeeAuthor Commented:
Thank you again for your efforts. Debug screen shot
0
 
ICantSeeAuthor Commented:
Here is more...

Quick watch window
0
 
ICantSeeAuthor Commented:
another...

mymessage empty
0
 
jonnidipConnect With a Mentor Commented:
Your last 2 posts are due to the fact that Send and MyMessage are not yet hit by the debugger, thus have no value.

Could you please try to replace this line:
       Dim myMessage As MailMessage = New MailMessage()
with this:
       Dim myMessage As New MailMessage()
0
 
ICantSeeAuthor Commented:
Same exception.

is it o.k. to place the code to write to the database and send an email in the same sub?
0
 
jonnidipConnect With a Mentor Commented:
s it o.k. to place the code to write to the database and send an email in the same sub?
That should not be a problem.

Same exception.
I am missing something...
Would it be possibile to attach the full .vb file?
0
 
ICantSeeAuthor Commented:
yes
Imports System.Data.SqlClient
Imports System.Net.Mail
Partial Class ImpactSurvey
    Inherits System.Web.UI.Page
    Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    Private _strsql As String
    Private Property strsql As String
        Get
            Return _strsql
        End Get
        Set(value As String)
            _strsql = value
        End Set
    End Property

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        con = New SqlConnection("Data Source=WEBSQL\WEBDATA;Initial Catalog=Service_Forms;Integrated Security=True")
        con.Open()
        strsql = "insert into AgencyServicesImpactSurvey (SurveyMedical, SurveyResources, SurveyMeetings, SurveyPaperwork, SurveyGroceries, SurveyPrograms, SurveyVision, SurveyIndependence, SurveySatisfied, SurveyComments) values ('" _
        & MedicalDropdownList.SelectedValue & "','" _
        & ResourcesDropdownlist.SelectedValue & "','" _
        & MeetingsDropdownlist.SelectedValue & "','" _
        & PaperworkDropdownlist.SelectedValue & "','" _
        & GroceriesDropdownlist.SelectedValue & "','" _
        & ProgramsDropdownlist.SelectedValue & "','" _
        & visionDropdownlist.SelectedValue & "','" _
        & IndependenceDropdownlist.SelectedValue & "','" _
        & SatisfiedDropdownlist.SelectedValue & "','" _
        & CommentsTextbox.Text & "')"
        cmd.CommandText = strsql
        cmd.Connection = con
        cmd.ExecuteNonQuery()
        MsgBox("Data Saved")
        con.Close()
        con.Dispose()


        'Capture fields for email body

        Dim fileName As String = Server.MapPath("~/App_Data/Survey.aspx")
        Dim mailBody As String = System.IO.File.ReadAllText(fileName)

        mailBody = mailBody.Replace("##MedicalLabel##", MedicalLabel.Text)
        mailBody = mailBody.Replace("##Medical##", MedicalDropdownList.SelectedValue)
        mailBody = mailBody.Replace("##ResourcesLabel##", ResourcesLabel.Text)
        mailBody = mailBody.Replace("##Resources##", ResourcesDropdownlist.SelectedValue)
        mailBody = mailBody.Replace("##MeetingsLabel##", MeetingsLabel.Text)
        mailBody = mailBody.Replace("##Meetings##", MeetingsDropdownlist.SelectedValue)
        mailBody = mailBody.Replace("##PaperworkLabel##", PaperworkLabel.Text)
        mailBody = mailBody.Replace("##Paperwork##", PaperworkDropdownlist.SelectedValue)
        mailBody = mailBody.Replace("##GroceriesLabel##", GroceriesLabel.Text)
        mailBody = mailBody.Replace("##Groceries##", GroceriesDropdownlist.SelectedValue)
        mailBody = mailBody.Replace("##ProgramsLabel##", ProgramsLabel.Text)
        mailBody = mailBody.Replace("##Programs##", ProgramsDropdownlist.SelectedValue)
        mailBody = mailBody.Replace("##VisionLabel##", VisionLabel.Text)
        mailBody = mailBody.Replace("##Vision##", visionDropdownlist.SelectedValue)
        mailBody = mailBody.Replace("##IndependenceLabel##", IndependenceLabel.Text)
        mailBody = mailBody.Replace("##Independence##", IndependenceDropdownlist.SelectedValue)
        mailBody = mailBody.Replace("##SatisifiedLabel##", SatisfiedLabel.Text)
        mailBody = mailBody.Replace("##Satisfied##", SatisfiedDropdownlist.SelectedValue)
        mailBody = mailBody.Replace("##Comments##", CommentsTextbox.Text)



        'Create and send the email:

        Dim myMessage As New MailMessage()
        myMessage.Subject = "Agency Services Imapact Survey"
        myMessage.Body = mailBody
        myMessage.From = New MailAddress("kba_forms@keystoneblind.org")
        myMessage.IsBodyHtml = True
        myMessage.To.Add(New MailAddress("kevin@keystoneblind.org"))
        Dim mySmtpClient As SmtpClient = New SmtpClient()
        mySmtpClient.Credentials = New System.Net.NetworkCredential("myusername", "mypassword")
        mySmtpClient.Send(myMessage)

        Dim smtpClient As New SmtpClient()
        Dim userState As Object = myMessage

        'Attach event handler for async callback
        AddHandler smtpClient.SendCompleted, AddressOf SmtpClient_OnCompleted

        'Try
        'Send the email asynchronously
        'smtpClient.SendAsync(myMessage, userState)
        'Catch smtpEx As SmtpException
        'Error handling here
        'Catch ex As Exception
        'Error handling here
        'End Try
    End Sub
    ''' <summary>
    ''' Event handler for processing completion information after asynchronous email sent.
    ''' </summary>
    Public Sub SmtpClient_OnCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.AsyncCompletedEventArgs)

        'Get UserState as MailMessage instance from SendMail()
        Dim mailMessage As MailMessage = CType(e.UserState, MailMessage)

        If (e.Cancelled) Then
            FeedBackLabel.Text = "Sending of email message was cancelled. Address=" '+ mailMessage.To(0).Address
        End If

        If Not (e.Error Is Nothing) Then
            FeedBackLabel.Text = "Error occured. Please contact KBA's technical support at 724.347.5501 ext 252"
        Else
            Response.Redirect("~/default.aspx")
            mailMessage.Dispose()
        End If

    End Sub
End Class

Open in new window

0
 
ICantSeeAuthor Commented:
I'm sorry, i 'm not sure which file that you mean. Also, what will I be looking for?
0
 
jonnidipCommented:
"web.config" if this is a web page, or "app.config" if this is a Windows application.
0
 
ICantSeeAuthor Commented:
Right on. I edited the web.config  network settings and it works...locally.

I just copied the website to our web server and the application fails. Not sure why.

Any thoughts?
0
 
jonnidipCommented:
What error do you get from your server?
0
 
ICantSeeAuthor Commented:
Exception information:
    Exception type: SqlException
    Exception message: Login failed for user 'KEYSTONEBLIND\KBA-WEB-HV2$'.

My web server and sql server are separate machines on the same subnet.
0
 
ICantSeeAuthor Commented:
I have tried creating accounts for network service and a specific sa account for the sql server and gave read /write permissions to the appropriate Db. I am still getting the same error.

Its IIS 7 and SQL 2008 R2 both running on Server 2008 Enterprise.
0
 
jonnidipCommented:
as you understand, it is a very different problem from the initial one.
for this one you should chech the sql machine event log (system) for any failed login.
0
 
ICantSeeAuthor Commented:
I'm going to start a different question. Thank you for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.