Last post Feb 09, 2017 03:40 AM by simflex
Feb 09, 2017 02:58 AM|simflex|LINK
Sorry again experts.
I keep coming back for more.
We have two tables, RequestTypes (a look up table) and Work_Request
RequestTypes table has three fields, requestTypeID (PK), RequestType, AssignedTo_email(the employee who will be handling the request)
RequestTypeID RequestType AssignedTo_email
1 Repairs email@example.com
2 Cleanup cleanup#doamin.com
3 Software Development firstname.lastname@example.org
Work_Request has several fields but for the purpose of keeping it streamlined, I will show a few relevant fields. They are work_requestID (PK), rquestTypeID (FK to requestTypes table), entery_date, entry_time, RequestersName, requestersEmail, RequestersPhone,
There is a RequestTypes dropdownlist that is dynamically populated from the RequestTypes table shown below:
<asp:DropDownList ID="Type_of_Request" data-placeholder="Please select request type..." class="chosen-select form-control" style="width:250px;height:178px;" tabindex="3" runat="server" AppendDataBoundItems="True" Width="100px" Enabled="True">
<asp:ListItem value=""> </asp:ListItem>
Then codefile for the dropdownList
Dim requestTypeccmd As New SqlCommand("Select requestTypeID, Type_of_Request from RequestTypes ORDER By Type_of_Request ASC", New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString))
Dim RequestTypeValues As SqlDataReader
RequestTypeValues = requestTypeccmd.ExecuteReader()
Type_of_Request.DataSource = RequestTypeValues
Type_of_Request.DataValueField = "requestTypeID"
Type_of_Request.DataTextField = "Type_of_Request"
So far, so good but here is where we are having problems.
Whenever a work request is submitted, an email is sent to the AssignedTo_email which is the individual who will be handling the request.
What I want to do now is grab the AssignedTo_Email from RequestTypes table based on selectedvalue of RequestTypeID.
For instance, if the selectedValue of requestTypeID is 1 and selectedItem is Repairs, I want to grab the AssignedTo_Email based on the requestTypeID of 1.
How do I use the following query correctly?
I think I know what I wish to do but I can't figure out how to use the dropdownList requestTypeID as parameter.
SELECT AssignedTo_email FROM RequestTypes WHERE RequestTypeID = @id
The @id is the selected requestTypeID with selectedItem of Repairs.
Then I need to do something like:
If HasRows Then
Do While dr.Read
workersEmail = dr("AssignedTo_email")
Then I can send email to the employee using workersEmail.
Thanks for your assistance in advance
Feb 09, 2017 03:25 AM|kaushalparik27|LINK
You can use
SqlCommand.ExecuteScalar Method (System.Data.SqlClient) to fetch Email Address of selected request type from Dropdown, since I believe there will be always one record (Email Address) coming from your query:
If it is possible to outcome multiple email addresses then you can use
SqlCommand.ExecuteReader Method (System.Data.SqlClient)
Once you get Email address, you can use System.Net.Mail Namespace to send email (for example, from Gmail)
Here are some related topics you can refer:
I would also suggest to avoid writing inline query. Here are some related topics you can refer:
Below is a sample code which uses ExecuteScalar() method to return email address and send email from Gmail:
Protected Sub btnSendMail_Click(sender As Object, e As EventArgs)
Dim emailAddr As String = ""
' get email address first
If (Not String.IsNullOrEmpty(Type_of_Request.SelectedValue)) Then
emailAddr = GetEmailAddrByReqTypeId(Convert.ToInt64(Type_of_Request.SelectedValue))
' send email
If (Not String.IsNullOrEmpty(emailAddr)) Then
' fetch email address from database for RequestTypeId
Private Function GetEmailAddrByReqTypeId(RequestTypeId As Int64) As String
Dim emailAddr As String = ""
If RequestTypeId > 0 Then
Using sqlConn As New SqlConnection("data source=(local);initial catalog=myDb;user id=sa;pwd;123")
Using sqlCmd As New SqlCommand("SELECT AssignedTo_email FROM RequestTypes WHERE RequestTypeID = @id", sqlConn)
sqlCmd.CommandType = System.Data.CommandType.Text
Dim reader As Object = sqlCmd.ExecuteScalar()
If (reader <> DBNull.Value) Then
emailAddr = Convert.ToString(reader)
' send mail using Gmail
Private Function SendMail(ToEmailAddr As String) As Boolean
Dim mailSent As Boolean = False
Dim MyMailMessage As New System.Net.Mail.MailMessage("email@example.com", ToEmailAddr, "This is the mail subject", "This is the body text that you want to send")
Dim mailAuthentication As New System.Net.NetworkCredential("firstname.lastname@example.org", "myPassword")
Dim mailClient As New System.Net.Mail.SmtpClient("smtp.gmail.com", 587)
mailClient.EnableSsl = True
mailClient.UseDefaultCredentials = False
mailClient.Credentials = mailAuthentication
MyMailMessage.IsBodyHtml = True
mailSent = True
Catch ex As Exception
hope that helps./.
Feb 09, 2017 03:40 AM|simflex|LINK
Thank you so much.
Sorry I made you write all that code. I should have mentioned I already have the email porting done.
I just wanted how to grab email from the selectedValue of requestTypeID.
I think you have given me enough to get the rest done.