• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

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

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
robmulvey
Asked:
robmulvey
  • 2
  • 2
1 Solution
 
apgourlayCommented:
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
 
robmulveyAuthor Commented:
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
 
apgourlayCommented:
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
 
robmulveyAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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