Automating A Report on SQL server using a webform in c#

I have been charged with the task of Automating a report that is on a server in rackspace. I only have the following information. I do not have direct access to the server I only have the ip and the following information.

The following is the SQL the analysts run every morning. We’d like for you to create a web application in C# that will automate this process.

 

    Create a screen where they can input their start and end dates.
    Retrieve the data based on the dates provide.
    Return the results.
    Display the results on the screen.
    There is no design/specific look & feel, just make sure the form and report displayed is user friendly.
    Export report to Excel.

 

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

 

SQL:

SELECT [FirstName]

      ,[LastName]

      ,[Amount]

      ,[Fund]

      ,[PaymentType]

      ,[CCNum]

      ,[CCType]

      ,[ACHNum]

      ,[Frequency]

      ,[FrequencyDate]

      ,[TransDate]

      ,[TransID]

  FROM [database].[dbo].[tblName]

  where TransDate >= '07/27/2015' and TransDate < '07/28/2015'

  order by TransDate

 

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

 

Database connection in web.cofig:

<add name="servername" providerName="System.Data.SqlClient" connectionString="server=111.111.111.111;database=databaseName;Integrated Security=FALSE;user=*******;pwd=*******;" />
Rhiaanon44Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zephyr_hex (Megan)DeveloperCommented:
Do  you have a specific question regarding the implementation of this, or are you looking for someone to do the job for you?  If it's the latter, may I suggest you look at Gigs.

If you have a specific question or problem you've encountered, please give us more details about where you're stuck.
sammySeltzerCommented:
Hi,

Do you have visual studio?

If yes, then that's the best place to start.

Fire up VS, create report by right-clicking on project, choose Add new Item and select Report or Report Wizard, depending on your comfort level with report viewer.

That's where you use the query you wrote above.

That's first step.

Accomplish that and come back for the next step.
Rhiaanon44Author Commented:
Okay was able to add the report with the report viewer and run it successfully.  However it did not prompt me for the query input.  I have two textboxes to enter start and end date and a search button.  When the user presses the search button I need the query to perform the action above.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

sammySeltzerCommented:
Now, don't forget that when you create this report we will call report.rdlc, you will need to ensure that date values you are using in your query is replaced with a variable, something like:

where TransDate >= @fromDate and TransDate < @todate

Open in new window


Then next step is to create a .aspx file.

I usually give it the same name I call my .rdlc report.

So, if you have reports.rdlc then name this reports.aspx

Right -click on project, select Add New Item, click on Web form and give it a name say report.aspx

Next, click on the Design tab at the bottom of your visual studio. click on the toolbox at left of Visual Studio, go down to Reporting section, drag report viewer to your report.aspx form.

Follow the instructions to add the report.rdlc you created.

Then come back after that.
Rhiaanon44Author Commented:
I already added it to report.aspx
sammySeltzerCommented:
Ok, did you create parameters as you were adding the report?

If you did, then you should be able to pass some dates and get results.
sammySeltzerCommented:
Also, can you post the aspx code here so I see how report properties are being displayed?
Rhiaanon44Author Commented:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Reports.aspx.cs" Inherits="Reports" %>

<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="Label1" runat="server" Text="Start Date:"></asp:Label><asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:Label ID="Label2" runat="server" Text="End Date:"></asp:Label><asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><asp:Button ID="Button1" runat="server" Text="Search" Width="99px" /><asp:Button ID="Button2" runat="server" Text="Reset" Width="99px" />

        <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
        <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Width="779px">
            <LocalReport ReportPath="Report.rdlc">
                <DataSources>
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource1" Name="DataSet1" />
                </DataSources>
            </LocalReport>
        </rsweb:ReportViewer>
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetData" TypeName="BWMv87ProdRelaunchDataSetTableAdapters.tblLWCCDonationsTableAdapter"></asp:ObjectDataSource>
    </div>
    </form>
</body>
</html>
Rhiaanon44Author Commented:
now I'm getting this error and I do not know what to do.
sammySeltzerCommented:
what error are you getting?
Rhiaanon44Author Commented:
Oh sorry this is the error "     Cannot create a connection to data source 'BWMv87ProdRelaunchDataSet'.
        ObjectDataSource 'ObjectDataSource1' could not find a non-generic method 'GetData' that has no parameters. "
sammySeltzerCommented:
I am leaving a few minutes.

I might check back later tonight or perhaps tomorrow on this but as for your error message, it means that your data source is not reaching your database.

Check the connection string you have on web.config file and use it to connect your report.

This is usually done when adding your report to your .aspx file.

You can try to modify your .aspx file to fix this or try this:

    <asp:SqlDataSource runat="server" ID="ObjectDataSource1" 
            ConnectionString="<%$ ConnectionStrings:YourConnectionString %>" />

Open in new window


Put this code just on top of this line:
        <asp:Label ID="Label1" runat="server" Text="Start Date:"></asp:Label><asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

Open in new window


Be sure to replace YourConnectionString with the connection string you have on your web.config file.

This should work but in the event that it doesn't, then modify the .aspx to pick up the correct datasource that points to your database.
Rhiaanon44Author Commented:
I did try that code but it did not work.  The peculiar thing is when I set the query where TransDate >= @fromDate and TransDate < @todate that's when I get the error i.e. it does not see the datasource suddenly. However if I remove it suddenly I see the report.
sammySeltzerCommented:
When you are trying to put the report on report.aspx, to the left is where you add parameter.

I will send you screenshots of how you do this.

That's where you are having problem.
Rhiaanon44Author Commented:
Thank you. Will you post the screenshots here please
sammySeltzerCommented:
Sorry been really busy.

Please see screenshot.

Let's say your report name is reports.rdlc.

Double-click on it since you have already created it.

It will bring you to the screen that I just attached.

On that report, to the left of it, you see Parameters. (circled in red).

Right-click on Parameters, click Add parameter.

On the next screen, enter parameter name. It must be the same as the parameter name you gave to the sql query you wrote.

Let's say for instance that you have two parameters called @fromDate and @EndDate.

You will create two parameters with same name and case sensitive.

Next, select the data source, either one from your web.config file or a new one you created when building the query.

Recompile, refresh and try again.
reportParam.png
Rhiaanon44Author Commented:
Thank a lot I will try when I get to my work Pc
Rhiaanon44Author Commented:
I cannot get the aspx page to recognize my table adapter
sammySeltzerCommented:
send me your .xsd file
Rhiaanon44Author Commented:
Here is my aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Reports.aspx.cs" Inherits="Reports" %>

<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

        <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
        <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Width="804px">
            <LocalReport ReportPath="Report.rdlc">
                <DataSources>
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource2" Name="DataSet1" />
                </DataSources>
            </LocalReport>
        </rsweb:ReportViewer>

        <asp:ObjectDataSource ID="ObjectDataSource2" runat="server" SelectMethod="GetData" TypeName="BWMv87ProdRelaunchDataSetTableAdapters.tblLWCCDonationsTableAdapter"></asp:ObjectDataSource>

        <asp:Label ID="Label1" runat="server" Text="Start Date:"></asp:Label><asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:Label ID="Label2" runat="server" Text="End Date:"></asp:Label><asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><asp:Button ID="Button1" runat="server" Text="Search" OnClick="Button1_Click" Width="100px" /><asp:Button ID="Button2" runat="server" Text="Reset" OnClick="Button2_Click" Width="100px" />



        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetData" TypeName="BWMv87ProdRelaunchDataSetTableAdapters.tblLWCCDonationsTableAdapter" OldValuesParameterFormatString="original_{0}" DeleteMethod="Delete" InsertMethod="Insert" UpdateMethod="Update">
            <DeleteParameters>
                <asp:Parameter Name="Original_AutoNum" Type="Int32" />
            </DeleteParameters>
            <InsertParameters>
                <asp:Parameter Name="UserID" Type="Int64" />
                <asp:Parameter Name="Amount" Type="String" />
                <asp:Parameter Name="Fund" Type="String" />
                <asp:Parameter Name="PaymentType" Type="String" />
                <asp:Parameter Name="CCNum" Type="String" />
                <asp:Parameter Name="CCType" Type="String" />
                <asp:Parameter Name="ACHNum" Type="String" />
                <asp:Parameter Name="Frequency" Type="String" />
                <asp:Parameter Name="FrequencyDate" Type="String" />
                <asp:Parameter Name="TransDate" Type="DateTime" />
                <asp:Parameter Name="TransID" Type="String" />
                <asp:Parameter Name="IPAddress" Type="String" />
                <asp:Parameter Name="PageSource" Type="String" />
                <asp:Parameter Name="FirstName" Type="String" />
                <asp:Parameter Name="LastName" Type="String" />
            </InsertParameters>
            <SelectParameters>
                <asp:QueryStringParameter DefaultValue="TransDate" Name="startdate" QueryStringField="TransDate" Type="DateTime" />
                <asp:QueryStringParameter DefaultValue="" Name="enddate" QueryStringField="TransDate" Type="DateTime" />
            </SelectParameters>
            <UpdateParameters>
                <asp:Parameter Name="UserID" Type="Int64" />
                <asp:Parameter Name="Amount" Type="String" />
                <asp:Parameter Name="Fund" Type="String" />
                <asp:Parameter Name="PaymentType" Type="String" />
                <asp:Parameter Name="CCNum" Type="String" />
                <asp:Parameter Name="CCType" Type="String" />
                <asp:Parameter Name="ACHNum" Type="String" />
                <asp:Parameter Name="Frequency" Type="String" />
                <asp:Parameter Name="FrequencyDate" Type="String" />
                <asp:Parameter Name="TransDate" Type="DateTime" />
                <asp:Parameter Name="TransID" Type="String" />
                <asp:Parameter Name="IPAddress" Type="String" />
                <asp:Parameter Name="PageSource" Type="String" />
                <asp:Parameter Name="FirstName" Type="String" />
                <asp:Parameter Name="LastName" Type="String" />
                <asp:Parameter Name="Original_AutoNum" Type="Int32" />
            </UpdateParameters>
           
        </asp:ObjectDataSource>





       



    </div>
    </form>
</body>
</html>
BWMv87ProdRelaunchDataSet.xsd
sammySeltzerCommented:
There are just a bunch of things missing.

First, when you created the reports.rdcl, at some point during the creation, you would have created a dataset name for it.

For instance, if you look at the screenshot I sent you earlier, there is something called myAccountSet.

That's my dataset name for that report.

Let's see  how this goes for you.

Yours should have something similar.

So, on the code snippet below, replace yourdataset with the your actual dataset.

protected void Button1_Click(object sender, EventArgs e)
{
	ReportViewer1.LocalReport.ReportPath = "";
	which.Value = "R";
}

//Sub Run_Report(ByVal sender As Object, ByVal e As EventArgs)
public void Run_Report(string sel)
{
	ReportViewer1.Reset();
	ReportViewer1.LocalReport.DataSources.Clear();
	ReportParameter[] params_all = new ReportParameter[1];
	ReportParameter[] @params = new ReportParameter[2];
	ReportParameter[] params_date = new ReportParameter[3];
	ReportParameter[] params_locdate = new ReportParameter[4];


	switch (which.Value) {
		case "R":
			ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("YourDataSet", ObjectDataSource2.ID));
			ReportViewer1.LocalReport.ReportPath = "reports.rdlc";
			ReportViewer1.LocalReport.Refresh();
			params_date(0) = new ReportParameter("startdate", startdate.Text, false);
			params_date(1) = new ReportParameter("enddate", enddate.Text, false);
			ReportViewer1.LocalReport.SetParameters(params_date);
			break;

	}
}

Open in new window


Review the code carefully, you will see your objectdatasource ID called ObjectDataSource2.

You also see that you are using two date parameters, param1 and param2.

I am using startdate.Text and enddate.Text

Make sure that on your markup, you have something similar.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rhiaanon44Author Commented:
YAY I finally got it to work. Here is what I used:

    private void ShowReport()
    {
        //Reset
        RptViewer.Reset();

        //DataSource
        DataTable dt = GetData(DateTime.Parse(txtStart.Text), DateTime.Parse(txtEnd.Text));
        ReportDataSource rds = new ReportDataSource("DataSet1", dt);

        RptViewer.LocalReport.DataSources.Add(rds);

        //Path
        RptViewer.LocalReport.ReportPath = "Report.rdlc";

        //Parameters
        ReportParameter[] rptParams = new ReportParameter[] {
            new ReportParameter("startdate", txtStart.Text),
            new ReportParameter("enddate", txtEnd.Text)
        };
        RptViewer.LocalReport.SetParameters(rptParams);
        //Refresh
        RptViewer.LocalReport.Refresh();

       

    }

    private DataTable GetData (DateTime startdate, DateTime enddate)
    {
        DataTable dt = new DataTable();
        string conString = System.Configuration.ConfigurationManager.ConnectionStrings["BWMv87ProdRelaunchConnectionString"].ConnectionString;

        using (SqlConnection cn = new SqlConnection(conString))
        {
            SqlCommand cmd = new SqlCommand("ByDate", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("startdate", SqlDbType.DateTime).Value = startdate;
            cmd.Parameters.Add("enddate", SqlDbType.DateTime).Value = enddate;

            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            adp.Fill(dt);

        }
        return dt;
    }

}
Rhiaanon44Author Commented:
Thank you so much for your help.

I do have one more question in c# I need to add the code so that if the end date textbox is blank the  enddate textbox will automatically populate the startdate value entered +1

Here is what I have. I know I butchered it miserably.
sammySeltzerCommented:
I don't see the code you had but I would handle this differently.

Since your users are performing searches, I think it is better to force them to select a date.

The code below ensures that they are forced to supply start date if they don't enter one.

Same with end date.

Then if end date is blank or start date is greater than end date, they get a message that end date must be greater than start date.

That makes more sense to me.

You can even check to ensure valid date is entered.

<asp:TextBox ID="txtStart" runat="server" />
            <asp:RequiredFieldValidator id="RequiredFieldValidator2"
                 ControlToValidate="txtStart"
                 Display="Static"
                 Width="100%" runat=server>
                 You must supply a start date
            </asp:RequiredFieldValidator>
<asp:TextBox ID="txtEnd" runat="server" />
            <asp:RequiredFieldValidator id="RequiredFieldValidator3"
                 ControlToValidate="txtEnd"
                 Display="Static"
                 Width="100%" runat=server>
                 You must supply an end date
            </asp:RequiredFieldValidator>
<asp:CompareValidator ID="cmpStartAndEndDates" runat="server" Display="Dynamic"
    Operator="GreaterThan" ControlToValidate="txtEnd" ControlToCompare="txtStart"
    ErrorMessage="The end date must be greater than start date" />

Open in new window

Rhiaanon44Author Commented:
Sammy provided excellent feedback. The answer provided were percise
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.