VB.NET - how to sql data value show in label - Asked By narendra on 09-Nov-12 12:08 AM

Eric Smith replied to narendra on 31-Dec-12 10:39 AM
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)