Report Design
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Report Design
Message Icon Topic: MySQL Stored Procedure, asp.net (c#), CR Post Reply Post New Topic
Author Message
botete
Newbie
Newbie
Avatar

Joined: 16 Sep 2007
Location: United States
Online Status: Offline
Posts: 15
Quote botete Replybullet Topic: MySQL Stored Procedure, asp.net (c#), CR
    Posted: 19 Sep 2007 at 4:01am
Good Day! I have a stored procedure (with 2 parameters) from MySQL which I would like to call in my aspx.net.
 
In the Database Fields under the Field Explorer (Crystal Report) I used my created View (similar fields with my stored procedure) just a reference to my report. When I run the page, it seems it only reads the View and not the Stored Procedure.
 
In my example I used 3 tables, 1st - "offer" , 2nd - "blines", 3rd - "broker".
my View is named "vofferprinting" (I limit it with only 10 records as an example)...
 
If I use in my "da.Fill(ds, "vofferprinting")", the report sends no records, if I use "da.Fill(ds, "offer")", it sends 10 records. Does this mean if I use the "offer" table , the report reads the View and still not the Stored Procedure with 2 parameters?
 
this is the code:

OdbcDataAdapter da = new OdbcDataAdapter();

DataSet ds = new DataSet();

da.SelectCommand = com;

da.Fill(ds, "vofferprinting"); 

... or could I ask for a working code which I could make a pattern with my report. Please help!

Thank you and God Bless!!
 
BOSmile
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 19 Sep 2007 at 10:38am
If this is all your data access code, then it appears to missing stuff. 
 
It would help those on the forum if you could post all the data access code you have created to use the stored procedure in the report. Once we can see what you have done so far,  we will be in a better position to respond to your request for help.
 
In the data adapter you load the dataset. The dataset contains tables objects.  Those can be identified by name or a 0 based index number.
 
Regards,
 
John W.
 
IP IP Logged
botete
Newbie
Newbie
Avatar

Joined: 16 Sep 2007
Location: United States
Online Status: Offline
Posts: 15
Quote botete Replybullet Posted: 20 Sep 2007 at 1:37am
Here's the code of the calling page (Default.aspx):
 
protected void Button1_Click(object sender, EventArgs e)
    {
        Context.Items["rptfile"] = @"C:\ASP temp1\Report1.rpt";
        Context.Items["brokercode1"] = DropDownList1.SelectedValue;
        Context.Items["codate"] = 20500101;
        Server.Transfer("Default2.aspx", true);
    }
 
and here's the code of the called page (Default2.aspx):
 
protected void Page_Load(object sender, EventArgs e)
    {
        string strcon = "Dsn=Pres1_ver3.5;database=pres;server=Pres1;uid=xxxx;pwd=xxxx";
        OdbcConnection con = new OdbcConnection(strcon);
        con.Open();
        OdbcCommand com = new OdbcCommand("CALL sp_offerprinting(?)", con);
        com.CommandType = CommandType.StoredProcedure;
 
/*    OdbcCommand com = new OdbcCommand();
        com.CommandType = CommandType.Text;
        com.CommandText = "SELECT acno, assured, expiry, blines.name " +
                                         "as name, " +
                                        "offer.code, tsi, tsr, offdate, acdate, " +
                                        "status as status1, approved FROM offer " +
                                        "right join blines on blines.code = offer.code " +
                                        "right join broker on broker.code = offer.code " +
                                        "WHERE tsi <> 0 AND " +
                                        "offdate <= codate AND " +
                                        "offer.code = brokercode1 AND" +
                                        "approved <> space(30) AND status <>  'C' AND " +
                                        "status <>  'M' AND status <>  'D' AND bnderno = ' ' " +
                                        "order by offer.code, blines.name, acno limit 10";  
*/
 
        com.Parameters.Add(new OdbcParameter("?brokercode1",  OdbcType.Int, 5));
        com.Parameters["?brokercode1"].Direction = ParameterDirection.Input;
        com.Parameters["?brokercode1"].Value = DropDownList1.SelectedValue;
        com.Parameters.Add(new OdbcParameter("?codate", OdbcType.Int, 8));
        com.Parameters["?codate"].Direction = ParameterDirection.Input;
        com.Parameters["?codate"].Value = TextBox1.Text;
        string rptfilename = @"C:\ASP temp1\Report1.rpt";
   
    if (this.Context.Items.Count > 2)
       {
            rptfilename = this.Context.Items["rptfile"].ToString();
            DropDownList1.SelectedValue = this.Context.Items["brokercode1"].ToString();
            com.Parameters["?brokercode1"].Value = Convert.ToInt32(this.Context.Items["brokercode1"]);
            TextBox1.Text = this.Context.Items["codate"].ToString();
            com.Parameters["?codate"].Value = this.Context.Items["codate"].ToString();
        }
 
        OdbcDataAdapter da = new OdbcDataAdapter();
        DataSet ds = new DataSet();
        da.SelectCommand = com;
        da.Fill(ds, "offer");
 
        CrystalDecisions.CrystalReports.Engine.ReportDocument myReportDocument;
        myReportDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
        myReportDocument.Load(rptfilename);
        myReportDocument.SetDataSource(ds);
        CrystalReportViewer1.ReportSource = myReportDocument;
        CrystalReportViewer1.DataBind();
        con.Close();
}
 
.. or could anyone present this in an easier coding format (sending 2 or more parameters to a page and crystal report)?
 
Thank you and God Bless! Smile
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 20 Sep 2007 at 3:43pm
I sent you a PM response with some sample code. It will not be a working solution but may help you sort out some problems.
 
Regards,
 
John W.
IP IP Logged
botete
Newbie
Newbie
Avatar

Joined: 16 Sep 2007
Location: United States
Online Status: Offline
Posts: 15
Quote botete Replybullet Posted: 20 Sep 2007 at 6:23pm
Thank you sir! Big%20smile
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.