[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

Read email address from database

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
ndr-itsolutions
Asked:
ndr-itsolutions
  • 2
  • 2
1 Solution
 
esolveCommented:
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
 
ndr-itsolutionsAuthor Commented:
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
 
esolveCommented:
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
 
ndr-itsolutionsAuthor Commented:
Spot ON
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now