Create your stored procedure:
CREATE PROCEDURE [dbo].[ediParamDistEmailsByOwner]
@batch varchar(50),
@ownerid varchar(2)
AS
SELECT DISTINCT E1.WITOWNER, E2.NOTIFYEMAIL, E2.WEBDIR
FROM WCO.dbo.EDI00100 E1 JOIN WCO.dbo.EDI10100 E2
ON E1.WITOWNER=E2.OWNERID
WHERE BATCHNO = @batch
AND E2.OWNERID = @ownerid
ORDER BY WITOWNER
Create your function to return the data:
''' <summary>
''' Return a list of email address for ownerid to populate the TO: field in the email being sent
''' </summary>
''' <param name="strDbConnection"></param>
''' <param name="strOwnerID"></param>
''' <param name="strBatchNumber"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function GetEmailAddressForOwnerID(strDbConnection As String, strOwnerID As String, strBatchNumber As String) As String
Dim strToEmailAddresses As String = String.Empty
Dim strCon As String = GetConnectionString(strDbConnection)
Dim con As New SqlConnection(strCon)
Try
con.Open()
Dim cmd As New SqlCommand("ediParamDistEmailsByOwner", con)
cmd.CommandType = CommandType.StoredProcedure
Dim batchno As New SqlParameter("@BATCH", SqlDbType.Char, 20)
batchno.Value = strBatchNumber
cmd.Parameters.Add(batchno)
Dim oid As New SqlParameter("@OWNERID", SqlDbType.Char, 2)
oid.Value = strOwnerID
cmd.Parameters.Add(oid)
Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
strToEmailAddresses = reader("NOTIFYEMAIL").ToString()
End While
Return strToEmailAddresses
Catch Ex As SqlException
MessageBox.Show("The following database error occured: " + Ex.Message, "Error - Database", MessageBoxButtons.OK, MessageBoxIcon.[Error])
'write error to log
Er.WriteLogFile(Me.[GetType]().Name, "DataAccess-GetEmailAddressForOwnerID", Ex.Message)
strToEmailAddresses = "DBERROR"
Return strToEmailAddresses
Catch EEX As Exception
MessageBox.Show("The following program error occured: " + EEX.Message, "Error - Program", MessageBoxButtons.OK, MessageBoxIcon.[Error])
'write error to log
Er.WriteLogFile(Me.[GetType]().Name, "DataAccess-GetEmailAddressForOwnerID", EEX.Message)
strToEmailAddresses = "APPERROR"
Return strToEmailAddresses
Finally
con.Close()
con.Dispose()
End Try
End Function
display information on label
lblEmail.Text = GetEmailAddressForOwnerID("PRODEDI", OwnerID, Batchno)