How to create Crystal Report with query by linq

Question

0.00/5 (No votes)

See more:

hello everyone, I have a problem when use result from query linq to create crytal report

In here, I query all field in my table view:

C#

CrystalReport2 _crpt2 = new CrystalReport2();
           var result = (from p in DB.vReportScoreSheetStudents
                         select new
                         {
                             StudentID = p.StudentID,
                             LastName = p.LastName,
                             FirstName = p.FirstName,
                             DateOfBirth = p.DateOfBirth == null ? DateTime.Now : p.DateOfBirth,
                             Address = p.Address == null ? "" : p.Address,
                             Class = p.Class == null ? "" : p.Class,
                             CourseID= p.CourseID,
                             SubjectName = p.SubjectName == null ? "" : p.SubjectName,
                             Year = p.Year == null ? DateTime.Now.Year : p.Year,
                             TermNo = p.TermNo == null ? 0 : p.TermNo,
                             Assignment= p.Assignment==null?0.0:p.Assignment,
                             Midterm = p.Midterm==null?0.0:p.Midterm,
                             Practice=p.Practice==null?0.0:p.Practice,
                             Project=p.Project==null?0.0:p.Project,
                             Endterm=p.Endterm==null?0.0:p.Endterm,
                             FinalMatch=p.FinalMatch==null?0.0:p.FinalMatch,
                             Note= p.Note==null?"":p.Note
                         }).ToList();
           if (result == null)
           {
               MessageBox.Show("null");
           }
           else
           {
               _crpt2.Load(@"CrystalReport2.rpt");
               _crpt2.SetDataSource(result);
               crystalReportViewer1.ReportSource = _crpt2;
           }

althuogh all my fields have value which.but I still receive a exception :

C#

DataSet does not support System.Nullable<>.

from

C#

_crpt2.SetDataSource(result);

when I temporarily removed some field then its ok:

C#

ReportDocument _rpt1 = new CrystalReport1();
          var data = (from p in DB.vReportScoreSheetStudents
                      select new
                      {
                          StudentID = p.StudentID,
                          p.LastName,
                          p.FirstName,
                          p.CourseID,
                          p.SubjectName,
                          Address = p.Address == null ? "" : p.Address,
                      }).ToList();
          _rpt1.Load(@"CrystalReport1.rpt");
          _rpt1.SetDataSource(data);
          crystalReportViewer1.ReportSource = _rpt1;

Posted 5-Nov-11 15:50pm

williamVu

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Alexandrovich Kryukov · Accepted Answer · 2011-11-05T16:37:00

Solution 1

This is what it is: you cannot use nullable types. You did not provide declaration of the type of p but by some its members in your code I can guess their declarations are like this:

C#

int? Year;
int? TermNo;
//maybe uint? ulong? or something like this

Otherwise comparison with null I can see in your code would not compile.

You can get rid of '?' in member types and your code should work. If you cannot do it because you need to use the type of p which is not declared in your code, you should convert the types to non-nullable. For example, the lines involving the members Year and TermNo should be modified like this:

C#

//...
Year = p.Year == null ? DateTime.Now.Year : p.Year.Value,
TermNo = p.TermNo == null ? 0 : p.TermNo.Value,
//...

[EDIT]

Also, remember: if you want to use database NULL in ADO.NET, you need to use System.DBNull class, http://msdn.microsoft.com/en-us/library/system.dbnull.aspx[^].

Probably, this was a root cause of the exception you had.
[END EDIT]

A bonus advice: don't use "", use string.Empty instead; in general, avoid immediate constants, especially string immediate constants by all means, instead, declare and use explicit constants, use resources or data read from data files.

—SA

Posted 5-Nov-11 16:37pm

Sergey Alexandrovich Kryukov

Updated 7-Nov-11 3:48am

v5

Comments

williamVu 5-Nov-11 23:02pm

Thanks SAKryukov so much,
I tried fix it a long time but failed. until you help me, it become really easy.
again thanks you!

Sergey Alexandrovich Kryukov 6-Nov-11 10:08am

Well, if it works for you, please formally accept the answer (green button) -- thanks.

Also, please see additional advice in [EDIT]... [END EDIT].
--SA

RaisKazi 7-Nov-11 0:48am

Have seen in past OPs not showing decent courtesy for the Answers which indeed helped them and probably sometimes rescued them from the nightmears. :) 5ed!

Sergey Alexandrovich Kryukov 7-Nov-11 0:50am

Thank you, Rais.
It's more important that OP could use the advice somehow, so this was not a waste of time.
--SA

RaisKazi 7-Nov-11 9:56am

Your advice to OP was absoulutely fine. I too post such often. My point was towards OPs who do not appreciate help. :) Cheers

thatraja 6-Nov-11 1:20am

5!

Sergey Alexandrovich Kryukov 6-Nov-11 10:10am

Thank you, Raja.
--SA

williamVu 7-Nov-11 6:05am

Thanks you,SAKryukov

williamVu 7-Nov-11 8:13am

I've corrected my code according to your guide, but it throw exception
DateOfBirth = p.DateOfBirth == null ? System.DBNull.Value: p.DateOfBirth.Value;
Year = p.Year == null ? System.DBNull.Value : p.Year.Value,
TermNo = p.TermNo == null ? System.DBNull.Value : p.TermNo.Value,

"Type of conditional expression cannot be determined because there is no implicit conversion between 'System.DBNull' and 'System.DateTime' or 'int'

Actually I really don't want to use Datetime.Now, but I don't know replace it by other type data, When you said me use DBNull.Value I very fun, so you can help me on more time. Thanks so much

Sergey Alexandrovich Kryukov 7-Nov-11 9:33am

Yes, I see the problem. It looks like what you do at this point there is no way to conduct null because the data type is to be determined; and DBNull is a substitute for any type nullable from the stand point of DB. Maybe your options here are to skip the null line from the select statement or keep using non-null default. Sorry for misleading piece of code, removed.
--SA

williamVu 7-Nov-11 11:19am

So have any way to can replace DateTime.Now by type value similar null, because when field value is null, I want it empty. assign Datetime.Now is not true real

Sergey Alexandrovich Kryukov 15-May-12 11:45am

There is no null value for DateTime, because this is a structure, a value type. You can change it to nullable type, "DateTime?" and then you can assign null to it.
--SA

[no name] 15-May-12 4:50am

Thank you so much!

Sergey Alexandrovich Kryukov 15-May-12 11:43am

You are welcome.
--SA