AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of Ramona
Ramona
Flag for United States of America asked on

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=*******;" />
ASP.NETWeb DevelopmentMicrosoft SQL Server
Avatar of undefined
Last Comment
Ramona
8/22/2022 - Mon
zephyr_hex (Megan)
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.
sammySeltzer
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.
Ramona
ASKER
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
sammySeltzer
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.
Ramona
ASKER
I already added it to report.aspx
sammySeltzer
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sammySeltzer
Also, can you post the aspx code here so I see how report properties are being displayed?
Ramona
ASKER
<%@ 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>
Ramona
ASKER
now I'm getting this error and I do not know what to do.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
sammySeltzer
what error are you getting?
Ramona
ASKER
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. "
sammySeltzer
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ramona
ASKER
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.
sammySeltzer
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.
Ramona
ASKER
Thank you. Will you post the screenshots here please
Your help has saved me hundreds of hours of internet surfing.
fblack61
sammySeltzer
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
Ramona
ASKER
Thank a lot I will try when I get to my work Pc
Ramona
ASKER
I cannot get the aspx page to recognize my table adapter
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sammySeltzer
send me your .xsd file
Ramona
ASKER
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
ASKER CERTIFIED SOLUTION
sammySeltzer
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ramona
ASKER
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;
    }

}
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Ramona
ASKER
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.
sammySeltzer
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

Ramona
ASKER
Sammy provided excellent feedback. The answer provided were percise
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent