SQL Question with Crystal Reports

[lothos12345]

I have written a sql query that a crystal report is using the query I wrote is as follows:

 

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

SELECT TOP 100 PERCENT dbo.tblPtProgReportMain.PtProgHeader,

dbo.tblPtProgReportMain.PtProgFooter,

dbo.tblPtProgReportMain.PtProgSalutation,

dbo.tblPtProgReportMain.PtProgSigBlock, dbo.tblPtProgReportMain.PatientName, dbo.tblPtProgReportMain.PatientSex,

dbo.tblPtProgReportMain.PatientProfileId, dbo.tblPtProgReportMain.PatientSSN, dbo.tblPtProgReportMain.PatientDOB,

dbo.tblPtProgReportMain.PatientDOI, dbo.tblPtProgReportMain.nextvisitdate, dbo.tblPtProgReportMain.TreatingDoc,

dbo.tblPtProgReportMain.TreatmentsNum, dbo.tblPtProgReportMain.TreatmentDates, dbo.tblPtProgReportMain.OtherRx,

dbo.tblPtProgReportMain.Medication, dbo.tblPtProgReportMain.PhysicianComment, dbo.tblPtProgReportMain.PriorHx, '' AS DiagDesc, dbo.tblPtProgReportDetail.NoteValue,

dbo.tblPtProgReportDetail.sortorder AS Detailsortorder,

case dbo.tblPtProgReportMain.initeval

when 0 then tblPTProgLists.Listtext

WHEN 1 then tblPTProgLists.Listtext2

WHEN 2 then tblPTProgLists.Listtext3

end AS GroupName,

tblPTProgLists.sortorder AS Groupsortorder,

dbo.tblPtProgReportMain.ChiefComplaint, dbo.tblPtProgReportMain.initeval AS SupressCC, dbo.tblPtProgReportMain.Diagnosis,

case dbo.tblPtProgReportMain.initeval

when 0 then 'Physical Therapy Progress Note'

when 1 then 'Physical Therapy Initial Evaluation'

when 2 then 'Physical Therapy Daily Note'

end AS LtrType, dbo.tblPtProgReportMain.printsig, ' ' AS docSigPic

FROM dbo.tblPTProgLists WITH (NOLOCK) INNER JOIN

dbo.tblPtProgReportDetail WITH (NOLOCK) ON dbo.tblPTProgLists.PtProgListid = dbo.tblPtProgReportDetail.PtProgGrpId Right outer JOIN

dbo.tblPTProgReportMain WITH (NOLOCK) ON dbo.tblPtProgReportDetail.PtProgReportId = dbo.tblPTProgReportMain.PtProgReportId

WHERE (dbo.tblPTProgReportMain.PtProgReportId = @rptid)

UNION ALL

SELECT '' as PtProgHeader, '' as PtProgFooter, '' as PtProgSalutation,

'' as PtProgSigBlock, '' as PatientName, '' as PatientSex,

'' as PatientProfileId, '' as PatientSSN, '' as PatientDOB,

'' as PatientDOI, '' as nextvisitdate, '' as TreatingDoc,

'' as TreatmentsNum, '' as TreatmentDates, '' as OtherRx,

'' as Medication, '' as PhysicianComment, '' as PriorHx, '' AS DiagDesc, '' as NoteValue,

'' as Detailsortorder,

'' as GroupName,

'' as Groupsortorder,

'' as ChiefComplaint, '' as SupressCC, '' as Diagnosis,

'' AS LtrType, '' as printsig, practicemanager.dbo.tblDocSigs.DocSigPic

FROM Practicemanager.dbo.tblDocSigs INNER JOIN

Practicemanager.dbo.PatientVisit ON Practicemanager.dbo.PatientVisit.DoctorId = Practicemanager.dbo.tblDocSigs.DoctorFacilityId INNER JOIN

dbo.tblPtProgReportMain ON Practicemanager.dbo.PatientVisit.PatientVisitId = dbo.tblPtProgReportMain.PVID

WHERE (dbo.tblPtProgReportMain.PtProgReportId = @rptid)

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

 

The crystal report is the groupname to group by, my problem is that the information that is static such PatientName does not display. It is blank in the report I browsed the data in the field in the Field Explorer and the information is there it is just on the second line. And my signature does not come out at all it is the item in the second query of the union. I am not sure what to do the only thing that does work right is the displaying of the details using the group by. Please help and as always any help given is greatly appreciated.

[robertsams23]

connectionString = "data source=SERVERNAME;initial catalog=DATABASENAME;user id=USERNAME;password=PASSWORD;";

cnn = new SqlConnection(connectionString);

cnn.Open();

sql = "SELECT Product_id,Product_name,Product_price FROM Product";

SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);

DataSet1 ds = new DataSet1();

dscmd.Fill(ds, "Product");

MessageBox.Show (ds.Tables[1].Rows.Count.ToString());

cnn.Close();

 

full source code

 

http://csharp.net-informations.com/crystal-reports/csharp-crystal-reports-sql-query.htm

 

robert.