Urgent Crystal problem with vb.net 2005 - Data disappears

[o9z]

I have a dataset being populated in vb.net with the following code and populating my report:


Dim rpt As New FinalSummary() 'The report you created.
Dim myConnection As SqlConnection
Dim MyCommand As New SqlCommand()
Dim myDA As New SqlDataAdapter()
Dim myDS As New dsSummaryMain()

Try

myConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;" & _
"Initial Catalog=Carcass2;")
MyCommand.Connection = myConnection
MyCommand.CommandText = "SELECT KS.PAY, KS.GRADE_PREM, KS.SORT_ADJ, KS.RE_PAY, KS.P_CHARGE, KS.OTHER_PREM, KS.LOCATION, KS.NPB, KS.NPPC, KS.SLO_NPB, KS.SLO_PAY, KS.SLO , KS.SLO_WEIGHT, KS.SLO_WT_FEE, KS.SLO_NPPC, KS.RE_WT_FEE, KS.RE_NPPC, KS.RE_WEIGHT, KS.RE_NPB, KS.RE, KS.FIN_LOC, KS.DATE, KS.SHIPDATE, KS.FIN_B, KS.NUMB_H, KS.WT_FEE, KS.WEIGHT, KS.PER_LEAN, KS.ACTUAL_YIELD_PER, KS.AVG_BACKF, KS.TOT_C_WEIGHT, KS.DIY, KS.D, KS.D_Pay, , KS.D_WEIGHT, KS.CONDEMN, KS.CONDEMN_WEIGHT, KS.Contract, Price.mkweek, Price.mkprice, Price.mkyear FROM KS LEFT OUTER JOIN price ON KS.Contract = price.contract AND DATEPART(week, KS.KILLDATE) = price.mkweek AND DATEPART(year, KS.DATE) = price.mkyear WHERE (KS.SHIPDATE BETWEEN '12/31/2006' AND '02/14/2009') AND (KS.FIN_LOC = 'Test')"
MyCommand.CommandType = CommandType.Text
myDA.SelectCommand = MyCommand

myDA.Fill(myDS, "KSData")
rpt.SetDataSource(myDS)
cvwSummary.ReportSource = rpt

Catch Excep As Exception
MessageBox.Show(Excep.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub


My select is currently selecting out 1 record, like I want it to. In the reporting side, by going to the database expect, I select dsSummaryMain from the list which was under ADO.NET datasets. I then grab both tables from that list...... KS and Price.

This populates the Database Fields area where I can drag/drop fields onto my report. Everything seems to work fine until I try to use a formula I created. The formula is as follows:

({KS.TOT_C_WEIGHT} / 100) * {price.mkprice}

Whenever I add this formula to the report, I receive no errors BUT when I run the report, all data that was previously on the report disappears and the report appears blank(except for the boxes, etc that I created). If I remove that formula, the report shows data in all fields except the one where I removed the formula.

Any ideas why this happens?

[mrdvt92]

I'd be willing to bet that price.mkprice is null. Left Outer Joins with Null values don't seem to work in Crystal Reports. CR I believe filters the row instead of bringing back a Null value for your report.

If you do get this to work, please add a comment.

Thanks,
Mike (mrdvt92)

[JaganEllis]

You should check whether price.mkprice is null before using it in the formula.
Try changing the formula, e.g.

if isnull({price.mkprice}) then <whatever it is you want to return when there's no price record>
else ({KS.TOT_C_WEIGHT} / 100) * {price.mkprice}