locked
GridView SQL , C#, ASP.Net, Calendar RRS feed

  • Question

  • User-752282435 posted

    We are working to make a gridview of MS SQL in ASP.net C# page, We want to select the data to be present in the gridview between two selected dates (Start and End) 

    The code that we are using with third party provider is as below: 

    SELECT * FROM GetBranchHourly('username', 'password', -1, '20150320', '20150321');

    20150320, 20150321 These are start and end dates but they make it to be written manually so if we run this code we will get the data, but we want to make the dates to be selected from the calendar that we used in the page ... 

    Wednesday, October 23, 2019 4:14 PM

All replies

  • User753101303 posted

    Hi,

    The suggested approach is to use parameterized queries as shown for example at https://www.aspsnippets.com/Articles/Parameterized-Queries-ADO.Net.aspx

    In short from C# you can transmit a string such as SELECT * FROM GetBranchHourly('username', 'password', -1, @StartDate, @EndDate) to SQL Server and run it with the values you want for those parameters...

    Wednesday, October 23, 2019 4:34 PM
  • User-752282435 posted

    The Third party provider of SQL database has created this function 

    SELECT * FROM GetBranchHourly('username', 'password', -1, '20150320', '20150321')

    and we have no permission to create Stored procedure, so the code should be sent from our site as above . 

    I think if we could add the parameters using C# inside the page will solve the issue, but I tried a lot with no success to inject the selected dates inside the gridview selectedCommand above 

    Wednesday, October 23, 2019 4:59 PM
  • User753101303 posted

    Hi,

    This is not a stored procedure and it doesn't have any extra requirement. It is a build in SQL Server feature (and you have this as well in most if not all DBMS).

    In short you can replace a literal value by a "variable" and pass the value of each variable to  SQL Server that will then produce the same result but :
    - you avoided building the string yourself which is error prone and could cause security issues
    - SQL Server can use the same query if you send the same query text with other values

    Wednesday, October 23, 2019 5:36 PM
  • User-752282435 posted

    Thank you dear for your rapid responses... 

    I have done something and get error kindly could you check where is the error ?  Must declare the scalar variable "@SDate".

    This is the Gridview DataSource

    <asp:SqlDataSource ID="Br_Rep" runat="server" ConnectionString="Data Source=xxx.xxx.xxx.xxx ;Initial Catalog=RetailCamControlPanel;
    Persist Security Info=True;User ID=username ;Password=Password" ProviderName="System.Data.SqlClient"
    SelectCommand=" @SDate datetime,
    @EDate datetime
    SELECT Date, BranchId, BranchCode, ValueIn, OutsideTraffic, TurnInRate, NewCustomer, ReturnInWeek, AvgVisitDuration
    FROM dbo.GetBranchDailySummary('username', 'password', - 1, @SDate, @EDate) AS GetBranchDailySummary_1">
    </asp:SqlDataSource>

    This is the Calendar (Start Date) Function 

    protected void From_Date_SelectionChanged(object sender, EventArgs e)
    {
    SqlCommand cmd1 = new SqlCommand();

    cmd1.Parameters.AddWithValue("@StDate", From_Date.SelectedDate.ToString("yyyymmdd"));

    This is the Calendar (EndDate) Function 

    protected void To_Date_SelectionChanged(object sender, EventArgs e)
    {
    SqlCommand cmd1 = new SqlCommand();


    cmd1.Parameters.AddWithValue("@EDate", To_Date.SelectedDate.ToString("yyyymmdd"));

    Wednesday, October 23, 2019 7:48 PM
  • User-752282435 posted

    Data Source=xxx.xxx.xxx.xxx 

    xxx.xxx.xxx.xxx  is the ip address of the server 

    Wednesday, October 23, 2019 7:51 PM
  • User409696431 posted

    You are misspelling your variable.  You are adding Parameter @StDate, but your Select is expecting @SDate.    The two must match: change one name to match the other.

    Wednesday, October 23, 2019 10:57 PM
  • User-752282435 posted

    Adjusted but still same error 

    Thursday, October 24, 2019 5:17 AM
  • User409696431 posted

    We can't see your full code.  Are the parameters you add done before the GridView is databound?  When are you getting the error?   If you are getting it on page load, and your GridView has the datasource assigned declaratively in the page, for example, the parameters are not yet defined.

    Add the parameters declaratively to the datasource in the page, and either assign default values to them, or don't assign the datasource to the gridview until after you have the parameters' values assigned.  Once you have parameters, databind the GridView.  Also, you need some logic to make sure both start and end are assigned before displaying the GridView.

    The logic I would use is in both From_Date_SelectionChanged and  To_Date_SelectionChanged check for valid values for both From_Date.SelectedDate and To_Date.SelectedDate.

    If both have valid dates, set the values of the parameters and databind the GridView.  That way the GridView won't display until you have both dates, and will update if you change one of them.

    Thursday, October 24, 2019 5:41 AM
  • User-752282435 posted

    Thank you dear for the solution the code worked fine as below 

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:FFcon %>" SelectCommand="SELECT Date, BranchId, BranchCode, ValueIn, OutsideTraffic, TurnInRate, NewCustomer, ReturnInWeek, AvgVisitDuration
    FROM dbo.GetBranchDailySummary('username', 'password', -1, @SDate, @EDate) AS GetBranchDailySummary_1">
    <SelectParameters>
    <asp:ControlParameter ControlID="FromDate" DefaultValue="20191010" Name="SDate" PropertyName="SelectedDate" />
    <asp:ControlParameter ControlID="Calendar2" DefaultValue="20191011" Name="EDate" PropertyName="SelectedDate" />
    </SelectParameters>
    </asp:SqlDataSource>

    and for the two calendars

    protected void FromDate_SelectionChanged(object sender, EventArgs e)
    {

    SqlCommand cmd = new SqlCommand();

    cmd.Parameters.AddWithValue("@SDate", FromDate.SelectedDate.ToString("yyyymmdd"));
    }

    protected void Calendar2_SelectionChanged(object sender, EventArgs e)
    {

    SqlCommand cmd = new SqlCommand();

    cmd.Parameters.AddWithValue("@EDate", FromDate.SelectedDate.ToString("yyyymmdd"));}

    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }

    Now this code make the browser refresh every time I select  a date from the two calendars
    Would like to add a button make the grideview refresh rather than refresh everytime I select a date  

    Thursday, October 24, 2019 7:26 AM
  • User379720387 posted

    20150320, 20150321 These are start and end dates but they make it to be written manually so if we run this code we will get the data, but we want to make the dates to be selected from the calendar that we used in the page ... 

    You would use a datepicker for this purpose. Here is a sample using bootstrap:

    https://www.bootply.com/R3Eol8pEoO

    Thursday, October 24, 2019 10:31 AM
  • User409696431 posted

    The easiest way, in Web Forms, to make a section of the page update without the visual impression of refreshing the whole page is to use and Update Panel.  Put the GridView and select dropdowns inside the Update Panel, and make sure you've assigned proper triggers.

    If you want to add a button to make the gridview update, instead, you'd need to set the Calendars to NOT auto postback, and let the button postback update the gridview instead.

    Thursday, October 24, 2019 1:26 PM