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
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....
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
ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
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