Solved

Writing data to SQL then forwarding the info via email

Posted on 2013-07-01
25
327 Views
Last Modified: 2013-07-08
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
0
Comment
Question by:ICantSee
  • 14
  • 11
25 Comments
 
LVL 13

Assisted Solution

by:jonnidip
jonnidip earned 500 total points
ID: 39292731
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
 

Author Comment

by:ICantSee
ID: 39292816
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
 
LVL 13

Assisted Solution

by:jonnidip
jonnidip earned 500 total points
ID: 39293147
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
 

Author Comment

by:ICantSee
ID: 39293174
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
 
LVL 13

Assisted Solution

by:jonnidip
jonnidip earned 500 total points
ID: 39293183
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
 

Author Comment

by:ICantSee
ID: 39293197
Which one should I get rid of? All I am trying to do is send the data collected to my email address.
0
 
LVL 13

Expert Comment

by:jonnidip
ID: 39293207
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
 

Author Comment

by:ICantSee
ID: 39293222
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
 
LVL 13

Assisted Solution

by:jonnidip
jonnidip earned 500 total points
ID: 39293246
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
 

Author Comment

by:ICantSee
ID: 39293411
Thank you again for your efforts. Debug screen shot
0
 

Author Comment

by:ICantSee
ID: 39293498
Here is more...

Quick watch window
0
 

Author Comment

by:ICantSee
ID: 39293514
another...

mymessage empty
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 13

Assisted Solution

by:jonnidip
jonnidip earned 500 total points
ID: 39293520
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
 

Author Comment

by:ICantSee
ID: 39293568
Same exception.

is it o.k. to place the code to write to the database and send an email in the same sub?
0
 
LVL 13

Assisted Solution

by:jonnidip
jonnidip earned 500 total points
ID: 39293583
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
 

Author Comment

by:ICantSee
ID: 39293598
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
 
LVL 13

Accepted Solution

by:
jonnidip earned 500 total points
ID: 39295671
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
 

Author Comment

by:ICantSee
ID: 39295922
I'm sorry, i 'm not sure which file that you mean. Also, what will I be looking for?
0
 
LVL 13

Expert Comment

by:jonnidip
ID: 39295926
"web.config" if this is a web page, or "app.config" if this is a Windows application.
0
 

Author Comment

by:ICantSee
ID: 39297685
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
 
LVL 13

Expert Comment

by:jonnidip
ID: 39299138
What error do you get from your server?
0
 

Author Comment

by:ICantSee
ID: 39307548
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
 

Author Comment

by:ICantSee
ID: 39307552
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
 
LVL 13

Expert Comment

by:jonnidip
ID: 39307562
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
 

Author Comment

by:ICantSee
ID: 39307636
I'm going to start a different question. Thank you for your help
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Find out how to use Active Directory data for email signature management in Microsoft Exchange and Office 365.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The basic steps you have just learned will be implemented in this video. The basic steps are shown to configure an Exchange DAG in a live working Exchange Server Environment and manage the same (Exchange Server 2010 Software is used in a Windows Ser…
This video discusses moving either the default database or any database to a new volume.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now