Transact SQL with ASP.Net app. Notification email

Question

User-1578352781 posted

SQL Server 2017

I am using the below code in a SQL Server trigger. Everything works fine except I need to separate the lines. The email looks like this when it hits the inbox:

--------------------looks like below--------------

The 5081 is actually the id of the record. I don't know how to use the right syntax or where to put CHAR to separate it by going to another line.

--------------------------------------------------

[e-mail]5081  Testing...Testing-Review JOB HAS BEEN APPROVED AND POSTED...-View job by clicking http://tennisandfitness.us-east-2.elasticbeanstalk.com/Preview_view.aspx

My absolute utopia would be if I could do something like:

---------------best case scenario below----------------

Your email request ID is 5081
Testing...Testing-Review JOB HAS BEEN APPROVED AND POSTED...-View job by clicking http://tennisandfitness.us-east-2.elasticbeanstalk.com/Preview_view.aspx

I'm not a very good sql coder but I think this is possible. Can someone modify my code so that I can achieve this

[dbo].[Req_submitted]
for update
as
 
 Begin
     declare 
        @req_submitted_key           int,
         @submitted_to_email     varchar(50),
        
        @Submitted_to_finance_approver_email     varchar(50),
        --@approved_denied      varchar(100),
        @approved_finance      varchar(100),
        @Job_posted      varchar(3),
        --@TaskAsction_A1_completed   varchar(100),
        @Job_postedby_email     varchar(50),
         @Message                    nvarchar(4000);

      select @req_submitted_key = s.req_submitted_key,
             @submitted_to_email = S.submitted_to_email,
             @Submitted_to_finance_approver_email = s.Submitted_to_finance_approver_email,
             @approved_finance  = s.approved_finance, 
             @Job_posted  = s.Job_posted,
             @Job_postedby_email = s.Job_postedby_email
             --@TaskAsction_A1_completed = s.TaskAsction_A1_completed
      from inserted s;

      select  @Message=concat(cast(@submitted_to_email as nvarchar(100)), (@req_submitted_key), N'  Testing...Testing-Review JOB HAS BEEN APPROVED AND POSTED...-JOB posted at LiveParallel. Click here for approval code. Testing...Testing-Review JOB HAS BEEN APPROVED AND POSTED...-View job by clicking http://tennisandfitness.us-east-2.elasticbeanstalk.com/Preview_view.aspx');
     
    --if update(TaskAction_A1_assigned)

 ------Send notification to TaskAction_A1_owner
          
          if update(Job_posted)

      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Echo System',
      @recipients = @submitted_to_email,
      @subject = 'Echo System Notification',
      @body = @Message;
          

          
          
          ;
End

Answer 1

User-939850651 posted

Hi webdesignernotcoder,

The 5081 is actually the id of the record. I don't know how to use the right syntax or where to put CHAR to separate it by going to another line.

select  @Message=concat(cast(@submitted_to_email as nvarchar(100)), (@req_submitted_key), N'  Testing...Testing-Review JOB HAS BEEN APPROVED AND POSTED...-JOB posted at LiveParallel. Click here for approval code. Testing...Testing-Review JOB HAS BEEN APPROVED AND POSTED...-View job by clicking http://tennisandfitness.us-east-2.elasticbeanstalk.com/Preview_view.aspx');

According to your description, in the @message that was queried, do you want to add line breaks to it?

If you need to display it on the page, you need to add </br> tag in the appropriate position to achieve line breaks.

But if it is just string content, you need to add "\n" characters in the appropriate position to make the string wrap.

Hope this can help you.

Best regards,

Xudong Peng

Answer 2

User-1578352781 posted

I kept at it until I got to this point. 

I modified the code to add some more fields. Note where I added CHAR(300) for @submitted_by_comment and. These are 300 character text fields. 

 select @Message=concat(cast(@submitted_to_email as nvarchar(100)), NCHAR(13) + NCHAR(10), @req_submitted_key, NCHAR(13) + NCHAR(10), @submitted_by_comment, CHAR(300) + CHAR(300), @Payband_compensation, CHAR(300) + CHAR(300), N'  The Potential Opening-Role Requisition request you submitted to Finance has been denied by Finance.  Contact Finance Partner for further information. http://xtest-sample.us-east-2.elasticbeanstalk.com/Approve_Deny.aspx');

I would like for the email to come in like this:

[e-mail]

5091

-------This is the @submitted_by_comment information for reference only

testing OF SYSTEMtesting OF SYSTEMtesting OF SYSTEMtesting OF SYSTEMtesting OF SYSTEMtesting OF SYSTEMtesting OF SYSTEMtesting OF SYSTEMtesting OF SYSTEMtesting OF SYSTEMtesting OF SYSTEMtesting OF SYSTEM SEE THI

--------This is the @Payband_compensation information for reference only

Stesting OF SYSTEM testing OF SYSTEM testing OF SYSTEMtesting OF SYSTEMtesting OF SYSTEMtesting OF SYSTEMtesting OF SYSTEMtesting OF SYSTEMtesting OF SYSTEMtesting OF SYSTEMtesting OF SYSTEMtesting OF SYSTEM SEE THIS  

The Potential Opening-Role Requisition request you submitted to Finance has been denied by Finance.  Contact Finance Partner for further information. http://xtest-sample.us-east-2.elasticbeanstalk.com/Approve_Deny.aspx');

Answer 3

User-939850651 posted

Hi webdesignernotcoder,

I'm not sure what you want to achieve, the email you mentioned, I guess it may be text in Textarea in a page. As you mentioned in the query statement, it is also a good way to use NCHAR(13) + NCHAR(10) to achieve line break . Or you need to display the text on the page, then you need to replace it with a </br> tag.

Something like this:

create proc testProc as
begin
declare @submitted_to_email varchar(50) = '[e-mail]'
declare	@req_submitted_key int = 5091
declare @submitted_by_comment varchar(max) = 'testing OF SYSTEM'
declare @Payband_compensation varchar(max) = 'testing OF SYSTEM'
declare @Message varchar(max)
select concat(cast(@submitted_to_email as varchar(50)), NCHAR(13) + NCHAR(10), NCHAR(13) + NCHAR(10), 
			  cast(@req_submitted_key as varchar(10)), NCHAR(13) + NCHAR(10), NCHAR(13) + NCHAR(10), 
			  @submitted_by_comment,NCHAR(13) + NCHAR(10), NCHAR(13) + NCHAR(10), @Payband_compensation, 
			  NCHAR(13) + NCHAR(10), NCHAR(13) + NCHAR(10), N'  The Potential Opening-Role Requisition request you submitted to Finance has been denied by Finance.  Contact Finance Partner for further information. http://xtest-sample.us-east-2.elasticbeanstalk.com/Approve_Deny.aspx') Message
end

Page code：

<form id="form1" runat="server">
        <div>
            <div runat="server" id="Content">

            </div>

            <textarea runat="server" id="MessageBox" rows="20" cols="150"></textarea>
        </div>
    </form>

Code behind:

DataTable dt = new DataTable();
            string conStr = ConfigurationManager.ConnectionStrings["conStr"].ToString();
            using (SqlConnection conn = new SqlConnection(conStr)) {
                using (SqlCommand cmd = new SqlCommand("testProc", conn)) {
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(dt);
                    MessageBox.InnerText = dt.Rows[0].ItemArray[0].ToString();
                    Content.InnerHtml = dt.Rows[0].ItemArray[0].ToString();
                }
            }

Result:

If I misunderstood something, please let me know.

Best regards,

Xudong Peng

Answer 4

User-939850651 posted

Hi webdesignernotcoder,

Any update?