Solved

Read email address from database

Posted on 2013-01-22
4
337 Views
Last Modified: 2013-01-22
I have a web form created in Web Dev express and one of the fields is a drop down for a resource, in the database the table has resource name and resource email address, so to select it I can run the following:

select email from Resource_Ref_data where resource = ('strResource')

strResource is the drop down text and is populated from the Resource name field.

I want, when the submit button is clicked on this form to send an email automatically to the selected resource, by using the above statement.

My issue is, how can i run this statement and then assign the output to strResourceEmail ?

So that in turn I can modify my send mail routine:
 mail.To.Add("strResourceEmail")
0
Comment
Question by:ndr-itsolutions
  • 2
  • 2
4 Comments
 
LVL 6

Expert Comment

by:esolve
ID: 38805196
You html:

      <asp:DropDownList ID="ddlResource" runat="server" AppendDataBoundItems="True">
        <asp:ListItem Selected="True">Please Select</asp:ListItem>
      </asp:DropDownList>
      <asp:Button ID="btnSubmit" runat="server" OnClick="btnSubmit_Click" Text="Submit" />
   
Your code behind:

 protected void Page_Load(object sender, EventArgs e)
    {
      if (!Page.IsPostBack)
      {
        //populate ddl (Should be in a BusinessLayer)
        DataSet ds = new DataSet();
        DataTable dt = new DataTable("Resource");
        dt.Columns.Add("ResourceName");
        dt.Columns.Add("ResourceEmail");
        ds.Tables.Add(dt);

        SqlConnection cn = new SqlConnection("SERVER=.;DATABASE=usersDB;UID=sa;PWD=whatever;");
        SqlCommand cm = new SqlCommand();
        cm.CommandText = "SELECT Name as ResourceName, Email as ResourceEmail FROM members";
        cm.Connection = cn;
        SqlDataAdapter da = new SqlDataAdapter(cm);
        da.Fill(ds, "Resource");

        ddlResource.DataSource = ds;
        ddlResource.DataMember = "Resource";
        ddlResource.DataTextField = "ResourceName";
        ddlResource.DataValueField = "ResourceEmail";
        ddlResource.DataBind();
      }

    }

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
      if (ddlResource.SelectedValue != null)
      {
        string sEmail = ddlResource.SelectedValue;

        //code to send email
      }
    }

Not sure if this is what you want. Otherwise paste some code

Hope it helps
0
 

Author Comment

by:ndr-itsolutions
ID: 38805340
Dim strBuild, strEnv, strComponent, strSubComponent, strResource, strStatus, strComments, strDate As String
        strBuild = TextBox1.Text
        strEnv = EnvironmentDropDown.Text
        strComponent = ComponentDropDown.Text
        strSubComponent = SubComponentDropdown.Text
        strResource = ResourceDropDown.Text
        strStatus = StatusDropDown.Text
        strDate = Format(CDate(Calendar1.SelectedDate), "MM-dd-yyyy")
        strComments = Comments.Text
        'declaring sql connection. 
        Dim sqlcon As New SqlConnection("Data Source=SQL01;Initial Catalog=EMA;Persist Security Info=True;User ID=EMA_Service;Password=********")
        'checking if connection is open. if open close it and open again.
        If sqlcon.State = ConnectionState.Open Then
            sqlcon.Close()
        End If
        sqlcon.Open()
        Dim strcommand As String
        'the insert command is stored in strcommand
        'Table Name : Release_Tracker
        'Column of table : Environment, Current_Version, Component, SubComponent, Resource, Status, Date, Comments
        strcommand = "Insert into Release_Tracker(Environment,Current_Version,Component,SubComponent,Resource,Status,Date,Comments) values ('" + strEnv + "','" + strBuild + "','" + strComponent + "','" + strSubComponent + "','" + strResource + "','" + strStatus + "','" + strDate + "','" + strComments + "')"
        Dim sqlcomm As New SqlCommand(strcommand, sqlcon)
        Dim o As String = sqlcomm.ExecuteNonQuery()
        SubmitLabel.Text = "Entry Added!"
        'Send Email
        'create the mail message
        'Read in the email address
        If sqlcon.State = ConnectionState.Open Then
            sqlcon.Close()
        End If
        
        Dim mail As New MailMessage()
        'set the addresses
        mail.From = New MailAddress("test@email.com")
        mail.To.Add("strResourceEmail")
        'set the content
        mail.Subject = "EMA - Task Awaiting"
        mail.Body = "The following task has been added: <br></br><b>Environment: " + strEnv + "</b><br></br>" + strBuild + "<br></br>" + strComponent + "<br></br>" + strSubComponent + "<br></br>" + strResource + "<br></br>" + strComments + "<br></br>" + strDate
        mail.IsBodyHtml = True
        'send the message
        Dim smtp As New SmtpClient("smtp.server")
        smtp.Send(mail)

    End Sub

Open in new window


And the code from the page
<asp:DropDownList ID="ResourceDropDown" runat="server" DataSourceID="Resource" 
        DataTextField="Resource" DataValueField="Resource" Height="23px" 
        Width="217px">
</asp:DropDownList>
	
<asp:SqlDataSource ID="Resource" runat="server" 
        ConnectionString="<%$ ConnectionStrings:EMAConnectionString %>" 
        SelectCommand="SELECT [Resource] FROM [Resource_Ref_Data]">
</asp:SqlDataSource>

Open in new window

0
 
LVL 6

Accepted Solution

by:
esolve earned 500 total points
ID: 38805460
Add the button and hook up an event (double click on it).

Protected Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click

      If (Not ResourceDropDown.SelectedItem Is Nothing) Then
        Dim strSelected As String

        strSelected = ResourceDropDown.SelectedItem.Text

        Dim strEmail As String
        strEmail = String.Empty

        'Retrieve email address
        Dim cm As New SqlCommand("SELECT email FROM table WHERE Name = @FirstName")
        Dim param As New SqlParameter("@FirstName", strSelected)
        cm.Parameters.Add(param)
        Dim cn As New SqlConnection("SERVER=.;DATABASE=usersDB;UID=sa;PWD=****")
        cm.Connection = cn

        Dim oResult As Object

        If cn.State = ConnectionState.Closed Then
          cn.Open()
        End If

        oResult = cm.ExecuteScalar()
        If (Not oResult Is Nothing) Then
          strEmail = oResult.ToString()
        End If

        SendMail(strEmail)

      'close connection
      End If
  End Sub

Create another sub to send the email:


  Protected Sub SendMail(ByVal strEmailAddress As String)

    Dim strBuild, strEnv, strComponent, strSubComponent, strResource, strComments, strDate As String
    strBuild = TextBox1.Text
    strEnv = EnvironmentDropDown.Text
    strComponent = ComponentDropDown.Text
    strSubComponent = SubComponentDropdown.Text
    strResource = ResourceDropDown.Text
    strDate = Format(CDate(Calendar1.SelectedDate), "MM-dd-yyyy")
    strComments = Comments.Text

    Dim mail As New MailMessage()

    'set the addresses
    mail.From = New MailAddress(strEmailAddress)
    mail.To.Add("strResourceEmail")

    'set the content
    mail.Subject = "EMA - Task Awaiting"
    mail.Body = "The following task has been added: <br></br><b>Environment: " + strEnv + "</b><br></br>" + strBuild + "<br></br>" + strComponent + "<br></br>" + strSubComponent + "<br></br>" + strResource + "<br></br>" + strComments + "<br></br>" + strDate
    mail.IsBodyHtml = True

    'send the message
    Dim smtp As New SmtpClient("smtp.server")
    smtp.Send(mail)

  End Sub
0
 

Author Closing Comment

by:ndr-itsolutions
ID: 38806833
Spot ON
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

726 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