Link to home
Create AccountLog in
Avatar of Ashwin_shastry
Ashwin_shastry

asked on

crystal report using stored procedure

Hi:

I want to generate a report b/w some time...I am using stored procedure for that...But i m not getting the desired result.Here is my code

SqlConnection sqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
                  SqlCommand sqlCommand = new SqlCommand("gal_report_personnel", sqlConnection);
                  sqlCommand.CommandType = CommandType.StoredProcedure;
                  SqlDataAdapter sqlAdapter = new SqlDataAdapter();
                  sqlAdapter.SelectCommand = sqlCommand;
                  DataSet ds = new DataSet();

                  sqlCommand.Parameters.Add("@EffectiveDate1",SqlDbType.SmallDateTime,4);
                  sqlCommand.Parameters["@EffectiveDate1"].Value="1/1/2005";

                  sqlCommand.Parameters.Add("@EffectiveDate2",SqlDbType.SmallDateTime,4);
                  sqlCommand.Parameters["@EffectiveDate2"].Value="2/1/2005";
                  
                  sqlAdapter.Fill(ds,"USER DATA");
                  creport.SetDataSource(ds);
                  CrystalReportViewer1.ReportSource = creport;
                  CrystalReportViewer1.DataBind();

I am bind the dataset using SetDataSource(ds).But when i run the report I see all the values....but  it should show me just 6 results but its showing everything. Where am i doing wrong...
Pls help me.

Thanks
Ashwin
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

If you step through your code...what are the values of

sqladapter.selectcommand.parameters["@EffectiveDate1"]
and
sqladapter.selectcommand.parameters["@EffectiveDate2"]

right before the fill?
Avatar of Mike McCracken
Mike McCracken

Shouldn't the parameters be added before this line?

            sqlAdapter.SelectCommand = sqlCommand;

The adapter command doesn't have the parameters.

mlmcc
Avatar of Ashwin_shastry

ASKER

BriCrowe:

I want the records between 1/1/2005 and 2/1/2005. Those are Effective date 1 and 2. For right now i have hard coded.
Also I tried displaying those values by giving the datasource as datagrid....It Displayed all the values b/w 1/1/2005 and 2/1/2005.
I asked about the parameters because I was thinking as mlmcc indicated that the parameters applied to sqlcommand may not carry to the selectcommand.
Since the values I wanted are in the dataset...I m wondering am i making wrong in giving the datasource to the crystalreport....? I mean

 creport.SetDataSource(ds);
 CrystalReportViewer1.ReportSource = creport;
 CrystalReportViewer1.DataBind();

How about this code...?
Does the report have saved data?

mlmcc
i dont know what is saved data....pls help me to solve this...
Please help me to solve this errorrrrrrr......I m ready to give even 500 points...If my error gets solved today.

Thanks
I'm curious why you are setting the datasource of the crystal report from your code.  Doesn't the report have its own datasource defined.  I don't know if this is the problem but I use stored procedures for several of my reports and display them in a vb.net app and all i have to do is load the .rpt file, establish the connection, set the parameter values, and set the crystalreportviewer source

private m_crReport as new reportdocument

...

m_crReport.Load("PriceSheet.rpt")
(define connection info)
...

m_crReport.SetParameterValue("@SLPriceGroup", cboSLPriceGroup.Text)
m_crReport.SetParameterValue("@IGPriceGroup", cboIGPriceGroup.Text)
m_crReport.SetParameterValue("@CustomerID", Nothing)
m_crReport.SetParameterValue("@CustomerName", String.Empty)
crvPricesheet.ReportSource = m_crReport
Saved Data
Open the report
Click FILE
Ensure SAVE DATA WITH REPORT is not checked.

mlmcc
I could fix the problem....The error was due was due to authentication of database. I used to connect to database for report from my local server and the values i was getting from stored procedure was from production server. To work fine...I should be using the local server for both or production server.

The mistake was i used localserver for reports and to fill dataset i was using production server. Finally could resolve this....by using local server for both.

Thanks for everyone for answering my question.
PAQ/Refund
ASKER CERTIFIED SOLUTION
Avatar of GhostMod
GhostMod
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer