Solved

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

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

735 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