I have the following problem. I am trying to execute a rather complex sql statement. The first sql statement below returns the desired rows, the second one wont return anything. Both sql statements work perfectly when run from the database. I dont understand why the second sql says that nothing is returned.... it wont go into the while....
not sure if that made sense.....
i tried to bind the second and problematic sql statement to a datagrid and it returns nothing either...
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;
namespace ConsoleApplication1
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class dacariMail
{
/// <summary>
/// The main entry point for the application.
/// Its used as a proof of concept for the dacari email service
/// </summary>
[STAThread]
static void Main(string[] args)
{
SqlConnection con;
SqlCommand myCommand;
SqlDataReader myDataReader;
string strConn = ConfigurationSettings.AppSettings.Get("ConnectionString");
con = new SqlConnection(strConn);
string sql="";
/**THIS WORKS...
sql =" select 'RCRF' as DacType, R.Id, R.AmendmentInstructions as Comments, 'Comment' as TypeOfComment, D.ExternalID as RiskId,D.AmendedByUserId as UserId, D.AmendedDateTime ";
sql+=" from dbo.ITRR_RCRFDetailComments R, RCRFDetail D ";
sql+=" where R.Id *= D.Id";
sql+=" and R.AmendmentInstructions *=D.AmendmentInstructions ";
sql+=" union";
sql+=" select 'RCRF' as DacType, R.Id, R.ApproverComments as Comments,'RejectedComment' as TypeOfComment,D.ExternalID as RiskId, D.AmendedByUserId as UserId, D.AmendedDateTime ";
sql+=" from ITRR_RCRFApproverComments R, RCRFDetail D ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.ApproverComments *=D.ApproverComments ";
sql+=" union ";
sql+=" select 'DAC' as DacType, R.Id, R.Comments, 'Comment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, D.AmendedDateTime from ITRR_DacDetailComments R, DacDetail D ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.Comments *=D.Comments ";
sql+=" union ";
sql+=" select 'DAC' as DacType, R.Id, R.RejectedComments as Comments,'RejectedComment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, D.AmendedDateTime ";
sql+=" from ITRR_DacDetailRejectedComments R, DacDetail D ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.RejectedComments *=D.RejectedComments ";
sql+=" union ";
sql+=" select 'CAP' as DacType, R.CapId, R.Comments, 'Comment' as TypeOfComment, D.CurrentRiskId,D.AmendedByUserId as UserId, D.AmendedDateTime from dbo.ITRR_CAPDetailComments R, CapDetail D ";
sql+=" where R.CapId *= D.CapId ";
sql+=" and R.Comments *=D.Comments ";
sql+=" union ";
sql+=" select 'CAP' as DacType, R.CapId, R.RejectedComments as Comments,'RejectedComment' as TypeOfComment,D.CurrentRiskId, D.AmendedByUserId as UserId, D.AmendedDateTime ";
sql+=" from ITRR_CapDetailRejectedComments R, CapDetail D ";
sql+=" where R.CapId *= D.CapId ";
sql+=" and R.RejectedComments *=D.RejectedComments ";
sql+=" union ";
sql+=" select 'MODAC' as DacType, R.Id, R.MiddleOfficeComments as Comments, 'MiddleOfficeComment' as TypeOfComment, D.RiskId,D.AmendedByUserId as UserId, D.AmendedDateTime ";
sql+=" from ITRR_MODacDetailComments R, MODacDetail D ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.MiddleOfficeComments *=D.MiddleOfficeComments ";
sql+=" union ";
sql+=" select 'MODAC' as DacType, R.Id, R.RequestorComments as Comments,'RequestorComment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, D.AmendedDateTime ";
sql+=" from ITRR_RequestorComments R, MODacDetail D ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.RequestorComments *= D.RequestorComments; ";
**/
/**THIS DOES NOT...
sql=" select '1' as CommentId, 'RCRF' as DacType, R.Id as SheetId, R.AmendmentInstructions as Comments, 'Comment' as TypeOfComment, D.ExternalID as [RiskId\\ExternalId],D.AmendedByUserId as UserId,";
sql+=" (select FullName from SecurityUsers where UserId = D.AmendedByUserId) FullName, D.AmendedDateTime ";
sql+=" from dbo.ITRR_RCRFDetailComments R, RCRFDetail D ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.AmendmentInstructions *=D.AmendmentInstructions ";
sql+=" and R.AmendmentInstructions not in ('', NULL) ";
sql+=" union ";
sql+=" select '1' as CommentId,'RCRF' as DacType, R.Id as SheetId, R.ApproverComments as Comments,'RejectedComment' as TypeOfComment,D.ExternalID as [RiskId\\ExternalId], D.AmendedByUserId as UserId, ";
sql+=" (select FullName from SecurityUsers where UserId = D.AmendedByUserId) FullName,D.AmendedDateTime ";
sql+=" from ITRR_RCRFApproverComments R, RCRFDetail D, SecurityUsers S ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.ApproverComments *=D.ApproverComments ";
sql+=" and R.ApproverComments not in ('', NULL) ";
sql+=" union ";
sql+=" select '1' as CommentId,'DAC' as DacType, R.Id as SheetId, R.Comments, 'Comment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, ";
sql+=" (select FullName from SecurityUsers where UserId = D.AmendedByUserId) FullName,D.AmendedDateTime ";
sql+=" from ITRR_DacDetailComments R, DacDetail D ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.Comments *=D.Comments ";
sql+=" and R.Comments not in ('', NULL) ";
sql+=" union ";
sql+=" select '1' as CommentId,'DAC' as DacType, R.Id as SheetId, R.RejectedComments as Comments,'RejectedComment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, ";
sql+=" (select FullName from SecurityUsers where UserId = D.AmendedByUserId) FullName,D.AmendedDateTime ";
sql+=" from ITRR_DacDetailRejectedComments R, DacDetail D ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.RejectedComments *=D.RejectedComments ";
sql+=" and R.RejectedComments not in ('', NULL) ";
sql+=" union ";
sql+=" select '1' as CommentId,'CAP' as DacType, R.CapId as SheetId, R.Comments, 'Comment' as TypeOfComment, D.CurrentRiskId,D.AmendedByUserId as UserId, ";
sql+=" (select FullName from SecurityUsers where UserId = D.AmendedByUserId) FullName, D.AmendedDateTime ";
sql+=" from dbo.ITRR_CAPDetailComments R, CapDetail D ";
sql+=" where R.CapId *= D.CapId ";
sql+=" and R.Comments *=D.Comments ";
sql+=" and R.Comments not in ('', NULL) ";
sql+=" union ";
sql+=" select '1' as CommentId,'CAP' as DacType, R.CapId as SheetId, R.RejectedComments as Comments,'RejectedComment' as TypeOfComment,D.CurrentRiskId, D.AmendedByUserId as UserId, ";
sql+=" (select FullName from SecurityUsers where UserId = D.AmendedByUserId) FullName,D.AmendedDateTime ";
sql+=" from ITRR_CapDetailRejectedComments R, CapDetail D ";
sql+=" where R.CapId *= D.CapId ";
sql+=" and R.RejectedComments *=D.RejectedComments ";
sql+=" and R.RejectedComments not in ('', NULL) ";
sql+=" union ";
sql+=" select '1' as CommentId,'MODAC' as DacType, R.Id as SheetId, R.MiddleOfficeComments as Comments, 'MiddleOfficeComment' as TypeOfComment, D.RiskId, ";
sql+="(select FullName from SecurityUsers where UserId = D.AmendedByUserId) FullName,D.AmendedByUserId as UserId, D.AmendedDateTime ";
sql+="from ITRR_MODacDetailComments R, MODacDetail D ";
sql+="where R.Id *= D.Id ";
sql+="and R.MiddleOfficeComments *=D.MiddleOfficeComments ";
sql+="and R.MiddleOfficeComments not in ('', NULL) ";
sql+="union ";
sql+="select '1' as CommentId,'MODAC' as DacType, R.Id as SheetId, R.RequestorComments as Comments,'RequestorComment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, ";
sql+="(select FullName from SecurityUsers where UserId = D.AmendedByUserId) FullName,D.AmendedDateTime ";
sql+="from ITRR_RequestorComments R, MODacDetail D ";
sql+="where R.Id *= D.Id ";
sql+="and R.RequestorComments *= D.RequestorComments ";
sql+="and R.RequestorComments not in ('', NULL) ";
**/
//string sql="select * from securityusers";
con.Open();
myCommand = new SqlCommand(sql,con);
myDataReader = myCommand.ExecuteReader();
// create a writer and open the file
TextWriter tw = new StreamWriter(@"C:\Temp\nsql.txt");
// write a line of text to the file
tw.WriteLine(sql);
// close the stream
tw.Close();
//string table = "";
int i =0;
while (myDataReader.Read())
{
i++;
// table=table+"<tr>"+"<td>"+myDataReader["DacType"]+"</td>"+"<td>"+myDataReader["Id"]+"</td>"+"<td>"+myDataReader["Comments"]+"</td>"+"<td>"+myDataReader["TypeOfComment"]+"</td>"+"<td>"+myDataReader["RiskId"]+"</td>"+"<td>"+myDataReader["UserId"]+"</td>"+"<td>"+myDataReader["AmendedDateTime"]+"</td>"+"</tr>";
}
//table = "<table border='1'>"+"<tr><td><b>DacType</b></td><td><b>Id</b></td><td><b>Comments</b></td><td><b>TypeOfComment</b></td><td><b>RiskId</b></td><td><b>UserId</b></td><td><b>AmendedDateTime</b></td></tr>"+table+"</table>";
con.Close();
// create a writer and open the file
TextWriter tw2 = new StreamWriter(@"C:\Temp\html.txt");
// write a line of text to the file
tw2.WriteLine(i);
// close the stream
tw2.Close();
//net.csfb.app.ldnuat3.EmailService test = new net.csfb.app.ldnuat3.EmailService();
//test.SendEmail("<email><sendTo>noel.nicholson@csfb.com</sendTo><sendFrom>Dacari@csfb-mail....</sendFrom><subject>Dacari Digest 02/09/2005</subject><priority>1</priority><format>1</format><emailBody><html><head></head><body>"+table+"</body></html></emailBody></email>");
}
//End of static method.
}
}
not sure if that made sense.....
i tried to bind the second and problematic sql statement to a datagrid and it returns nothing either...
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;
namespace ConsoleApplication1
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class dacariMail
{
/// <summary>
/// The main entry point for the application.
/// Its used as a proof of concept for the dacari email service
/// </summary>
[STAThread]
static void Main(string[] args)
{
SqlConnection con;
SqlCommand myCommand;
SqlDataReader myDataReader;
string strConn = ConfigurationSettings.AppSettings.Get("ConnectionString");
con = new SqlConnection(strConn);
string sql="";
/**THIS WORKS...
sql =" select 'RCRF' as DacType, R.Id, R.AmendmentInstructions as Comments, 'Comment' as TypeOfComment, D.ExternalID as RiskId,D.AmendedByUserId as UserId, D.AmendedDateTime ";
sql+=" from dbo.ITRR_RCRFDetailComments R, RCRFDetail D ";
sql+=" where R.Id *= D.Id";
sql+=" and R.AmendmentInstructions *=D.AmendmentInstructions ";
sql+=" union";
sql+=" select 'RCRF' as DacType, R.Id, R.ApproverComments as Comments,'RejectedComment' as TypeOfComment,D.ExternalID as RiskId, D.AmendedByUserId as UserId, D.AmendedDateTime ";
sql+=" from ITRR_RCRFApproverComments R, RCRFDetail D ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.ApproverComments *=D.ApproverComments ";
sql+=" union ";
sql+=" select 'DAC' as DacType, R.Id, R.Comments, 'Comment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, D.AmendedDateTime from ITRR_DacDetailComments R, DacDetail D ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.Comments *=D.Comments ";
sql+=" union ";
sql+=" select 'DAC' as DacType, R.Id, R.RejectedComments as Comments,'RejectedComment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, D.AmendedDateTime ";
sql+=" from ITRR_DacDetailRejectedComments R, DacDetail D ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.RejectedComments *=D.RejectedComments ";
sql+=" union ";
sql+=" select 'CAP' as DacType, R.CapId, R.Comments, 'Comment' as TypeOfComment, D.CurrentRiskId,D.AmendedByUserId as UserId, D.AmendedDateTime from dbo.ITRR_CAPDetailComments R, CapDetail D ";
sql+=" where R.CapId *= D.CapId ";
sql+=" and R.Comments *=D.Comments ";
sql+=" union ";
sql+=" select 'CAP' as DacType, R.CapId, R.RejectedComments as Comments,'RejectedComment' as TypeOfComment,D.CurrentRiskId, D.AmendedByUserId as UserId, D.AmendedDateTime ";
sql+=" from ITRR_CapDetailRejectedComments R, CapDetail D ";
sql+=" where R.CapId *= D.CapId ";
sql+=" and R.RejectedComments *=D.RejectedComments ";
sql+=" union ";
sql+=" select 'MODAC' as DacType, R.Id, R.MiddleOfficeComments as Comments, 'MiddleOfficeComment' as TypeOfComment, D.RiskId,D.AmendedByUserId as UserId, D.AmendedDateTime ";
sql+=" from ITRR_MODacDetailComments R, MODacDetail D ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.MiddleOfficeComments *=D.MiddleOfficeComments ";
sql+=" union ";
sql+=" select 'MODAC' as DacType, R.Id, R.RequestorComments as Comments,'RequestorComment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, D.AmendedDateTime ";
sql+=" from ITRR_RequestorComments R, MODacDetail D ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.RequestorComments *= D.RequestorComments; ";
**/
/**THIS DOES NOT...
sql=" select '1' as CommentId, 'RCRF' as DacType, R.Id as SheetId, R.AmendmentInstructions as Comments, 'Comment' as TypeOfComment, D.ExternalID as [RiskId\\ExternalId],D.AmendedByUserId as UserId,";
sql+=" (select FullName from SecurityUsers where UserId = D.AmendedByUserId) FullName, D.AmendedDateTime ";
sql+=" from dbo.ITRR_RCRFDetailComments R, RCRFDetail D ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.AmendmentInstructions *=D.AmendmentInstructions ";
sql+=" and R.AmendmentInstructions not in ('', NULL) ";
sql+=" union ";
sql+=" select '1' as CommentId,'RCRF' as DacType, R.Id as SheetId, R.ApproverComments as Comments,'RejectedComment' as TypeOfComment,D.ExternalID as [RiskId\\ExternalId], D.AmendedByUserId as UserId, ";
sql+=" (select FullName from SecurityUsers where UserId = D.AmendedByUserId) FullName,D.AmendedDateTime ";
sql+=" from ITRR_RCRFApproverComments R, RCRFDetail D, SecurityUsers S ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.ApproverComments *=D.ApproverComments ";
sql+=" and R.ApproverComments not in ('', NULL) ";
sql+=" union ";
sql+=" select '1' as CommentId,'DAC' as DacType, R.Id as SheetId, R.Comments, 'Comment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, ";
sql+=" (select FullName from SecurityUsers where UserId = D.AmendedByUserId) FullName,D.AmendedDateTime ";
sql+=" from ITRR_DacDetailComments R, DacDetail D ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.Comments *=D.Comments ";
sql+=" and R.Comments not in ('', NULL) ";
sql+=" union ";
sql+=" select '1' as CommentId,'DAC' as DacType, R.Id as SheetId, R.RejectedComments as Comments,'RejectedComment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, ";
sql+=" (select FullName from SecurityUsers where UserId = D.AmendedByUserId) FullName,D.AmendedDateTime ";
sql+=" from ITRR_DacDetailRejectedComments R, DacDetail D ";
sql+=" where R.Id *= D.Id ";
sql+=" and R.RejectedComments *=D.RejectedComments ";
sql+=" and R.RejectedComments not in ('', NULL) ";
sql+=" union ";
sql+=" select '1' as CommentId,'CAP' as DacType, R.CapId as SheetId, R.Comments, 'Comment' as TypeOfComment, D.CurrentRiskId,D.AmendedByUserId as UserId, ";
sql+=" (select FullName from SecurityUsers where UserId = D.AmendedByUserId) FullName, D.AmendedDateTime ";
sql+=" from dbo.ITRR_CAPDetailComments R, CapDetail D ";
sql+=" where R.CapId *= D.CapId ";
sql+=" and R.Comments *=D.Comments ";
sql+=" and R.Comments not in ('', NULL) ";
sql+=" union ";
sql+=" select '1' as CommentId,'CAP' as DacType, R.CapId as SheetId, R.RejectedComments as Comments,'RejectedComment' as TypeOfComment,D.CurrentRiskId, D.AmendedByUserId as UserId, ";
sql+=" (select FullName from SecurityUsers where UserId = D.AmendedByUserId) FullName,D.AmendedDateTime ";
sql+=" from ITRR_CapDetailRejectedComments R, CapDetail D ";
sql+=" where R.CapId *= D.CapId ";
sql+=" and R.RejectedComments *=D.RejectedComments ";
sql+=" and R.RejectedComments not in ('', NULL) ";
sql+=" union ";
sql+=" select '1' as CommentId,'MODAC' as DacType, R.Id as SheetId, R.MiddleOfficeComments as Comments, 'MiddleOfficeComment' as TypeOfComment, D.RiskId, ";
sql+="(select FullName from SecurityUsers where UserId = D.AmendedByUserId) FullName,D.AmendedByUserId as UserId, D.AmendedDateTime ";
sql+="from ITRR_MODacDetailComments R, MODacDetail D ";
sql+="where R.Id *= D.Id ";
sql+="and R.MiddleOfficeComments *=D.MiddleOfficeComments ";
sql+="and R.MiddleOfficeComments not in ('', NULL) ";
sql+="union ";
sql+="select '1' as CommentId,'MODAC' as DacType, R.Id as SheetId, R.RequestorComments as Comments,'RequestorComment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, ";
sql+="(select FullName from SecurityUsers where UserId = D.AmendedByUserId) FullName,D.AmendedDateTime ";
sql+="from ITRR_RequestorComments R, MODacDetail D ";
sql+="where R.Id *= D.Id ";
sql+="and R.RequestorComments *= D.RequestorComments ";
sql+="and R.RequestorComments not in ('', NULL) ";
**/
//string sql="select * from securityusers";
con.Open();
myCommand = new SqlCommand(sql,con);
myDataReader = myCommand.ExecuteReader();
// create a writer and open the file
TextWriter tw = new StreamWriter(@"C:\Temp\nsql.txt");
// write a line of text to the file
tw.WriteLine(sql);
// close the stream
tw.Close();
//string table = "";
int i =0;
while (myDataReader.Read())
{
i++;
// table=table+"<tr>"+"<td>"+myDataReader["DacType"]+"</td>"+"<td>"+myDataReader["Id"]+"</td>"+"<td>"+myDataReader["Comments"]+"</td>"+"<td>"+myDataReader["TypeOfComment"]+"</td>"+"<td>"+myDataReader["RiskId"]+"</td>"+"<td>"+myDataReader["UserId"]+"</td>"+"<td>"+myDataReader["AmendedDateTime"]+"</td>"+"</tr>";
}
//table = "<table border='1'>"+"<tr><td><b>DacType</b></td><td><b>Id</b></td><td><b>Comments</b></td><td><b>TypeOfComment</b></td><td><b>RiskId</b></td><td><b>UserId</b></td><td><b>AmendedDateTime</b></td></tr>"+table+"</table>";
con.Close();
// create a writer and open the file
TextWriter tw2 = new StreamWriter(@"C:\Temp\html.txt");
// write a line of text to the file
tw2.WriteLine(i);
// close the stream
tw2.Close();
//net.csfb.app.ldnuat3.EmailService test = new net.csfb.app.ldnuat3.EmailService();
//test.SendEmail("<email><sendTo>noel.nicholson@csfb.com</sendTo><sendFrom>Dacari@csfb-mail....</sendFrom><subject>Dacari Digest 02/09/2005</subject><priority>1</priority><format>1</format><emailBody><html><head></head><body>"+table+"</body></html></emailBody></email>");
}
//End of static method.
}
}