GridView SQL , C#, ASP.Net, Calendar

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 ... 

Answer 1

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...

Answer 2

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 

Answer 3

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

Answer 4

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"));

Answer 5

User-752282435 posted

Data Source=xxx.xxx.xxx.xxx 

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

Answer 6

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.

Answer 7

User-752282435 posted

Adjusted but still same error 

Answer 8

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.

Answer 9

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  

Answer 10

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

Answer 11

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.