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.
xxsample@llmovingbodies.com5081 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
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_emailas 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
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
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');
-------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');
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) = 'tester@tennisfitness.com'
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
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
None
0 Points
4 Posts
Transact SQL with ASP.Net app. Notification email
Oct 12, 2020 04:56 PM|webdesignernotcoder|LINK
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.
--------------------------------------------------
xxsample@llmovingbodies.com5081 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
Contributor
2070 Points
660 Posts
Re: Transact SQL with ASP.Net app. Notification email
Oct 13, 2020 10:11 AM|XuDong Peng|LINK
Hi webdesignernotcoder,
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
None
0 Points
4 Posts
Re: Transact SQL with ASP.Net app. Notification email
Oct 13, 2020 04:01 PM|webdesignernotcoder|LINK
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.
I would like for the email to come in like this:
tester@tennisfitness.com
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');
Contributor
2070 Points
660 Posts
Re: Transact SQL with ASP.Net app. Notification email
Oct 15, 2020 07:09 AM|XuDong Peng|LINK
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) = 'tester@tennisfitness.com' 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 endPage 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
Contributor
2070 Points
660 Posts
Re: Transact SQL with ASP.Net app. Notification email
Oct 20, 2020 07:23 AM|XuDong Peng|LINK
Hi webdesignernotcoder,
Any update?