Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sending Email based on Dropdownlist SelectedItem & Also Insert the Value of dropdown list into SQL Database

Posted on 2011-03-21
6
Medium Priority
?
269 Views
Last Modified: 2012-05-11
I have a web form with dropdown list, named dpRecruiter. I have two SQL tables named GT_Perm_Lookup & GT_UserList.

I joined the two tables and able to pull the 3 field that I need (pk_LookupID, LookupValue & UserEmail) via SQL Inner Join...

What I want to achieve is this: When a user select let's say "John Doe" from the Recruiter dropdown list, I want to send out an email to John Doe alerting him that a form has been submitted and at the same time insert the selected value (pk_LookupID) into the database.

I see that the dropdown list has two properties named: DataTextField="LookupValue" and DataValueField="pk_LookupID but how to get the User_Email is my major problem. Below is my SQL query and my ASP.NET VB code behind...

SELECT  GT_Perm_Lookup.pk_LookupID, GT_Perm_Lookup.LookupValue, GT_UserList.User_Email
FROM GT_Perm_Lookup INNER JOIN GT_UserList ON GT_Perm_Lookup.LookupValue2 = GT_UserList.GM_Username AND Department <> 'Ex Employee'
order by LookupValue asc


 Public Sub BindDropDownListData()
        ' connection string
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("XXXConnectionString").ConnectionString

        ' Sql connection object initialized with connection string used to connect it with Trustaff_MedConnectionString
        Using mySqlConnection As New SqlConnection(connectionString)
            Try
                ' open the Sql connection
                mySqlConnection.Open()
               
                ' Sql Command object initialized with SQL query t
                '  Dim mySqlCommand As New SqlCommand(" SQL Select statement here", mySqlConnection)
                Dim mySqlDataAdapter As New SqlDataAdapter()

                mySqlDataAdapter.SelectCommand = mySqlCommand
                Dim myDataSet As New DataSet
                ' mySqlConnection.Open()


                ' Sql Data Reader to fetch the records row by row in forward direction.
                Dim myDataReader As SqlDataReader = mySqlCommand.ExecuteReader()
               
                If myDataReader.HasRows Then
                    ' read each row fetched using DataReader
                    While myDataReader.Read()
                        Dim li As New ListItem()
                        li.Value = myDataReader("pk_LookupID")
                        li.Text = myDataReader("LookupValue")
                        DropDownList1.Items.Add(li)
                        ' Dim RecEmailArray() As String = myDataReader("User_Email")
                        ' email = RecEmailArray.ToString
                    End While
                End If
                myDataReader.Close()
            Catch ex As Exception
                Label1.Text = ex.Message
            Finally
                ' close the Sql Connection
                mySqlConnection.Close()
            End Try

            DropDownList1.Items.Insert(0, New ListItem("Select Recruiter"))
        End Using
    End Sub

SELECT  GT_Perm_Lookup.pk_LookupID, GT_Perm_Lookup.LookupValue, GT_UserList.User_Email 
FROM GT_Perm_Lookup INNER JOIN GT_UserList ON GT_Perm_Lookup.LookupValue2 = GT_UserList.GM_Username AND Department <> 'Ex Employee'
order by LookupValue asc

Open in new window

0
Comment
Question by:robmulvey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 2

Accepted Solution

by:
apgourlay earned 1000 total points
ID: 35181414
One way to solve this is to combine the Value of the dropdown to have the ID and the email address by making the value something like this ID|joe@test.com

When getting the drop down value you can then split the value on the |
eg
string[] s = drp.SelectedValue.Split("|");
string str_Email = s[1];
string str_ID = s[0];

Or something along these lines
0
 

Author Comment

by:robmulvey
ID: 35182506
Thanks for your solution.. Though this works  but I'm concern about security...if I view the source code on the client side...It displays the recruiter email which I don't to display....

<option selected="selected" value="53|test123y@company.com">John Doe</option>


0
 
LVL 2

Expert Comment

by:apgourlay
ID: 35184856
Makes sense. I think your only option then is to make another call the database and get the email address once you know which ID you are dealing with either on the selected index change event of the drop down or on a button click etc
0
 

Author Comment

by:robmulvey
ID: 35199823
After trying to make another call to the database based on your suggestion..I decided to stick with your first solution since the form is an internal form,....Only company employee(certain people) have access to it..Thanks for your help
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35872820
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

670 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