Solved

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

Posted on 2011-03-21
6
267 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 250 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

628 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