GridView dates using textboxes 1

[Cpreston]

Hi

I have a gridview that as one text box with a button. When I run it and add in a date and click the button it works fine. This brings in the data bound Date_submitted field for that specific date.

I want to add on another text box and enter a date from and date to and then click the button (if the button is needed) and it then returns the rows with dates from the Date_submitted field.

I can add in the textbox ok but when I run it just brings in the dates from the first textbox entered.

I have tried to change the code to some examples I found, but then the code gets corrupt and I end up back to square one.

Could someone help me add on another textbox and get it working please, a simple step by step guide perhaps. I am fairly new to asp net gridviews so going into the coding is a little problematic for me.

Thanks

 

[jbenson001]

you need to post the code you have tried first. That would be the best place to start

 

[Cpreston]

Hi

I have just added in a second textbox and tried, still only getting the first date, this is the code I think you mean. Thanks

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<asp:Content runat="server" ID="FeaturedContent" ContentPlaceHolderID="FeaturedContent">
<section class="featured">
<div class="content-wrapper">
<hgroup class="title">
<h1><%: Title %>.</h1>
<h2>Web Samples Table</h2>
</hgroup>
</div>
</section>
</asp:Content>

<asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="MainContent">
<h3>
Date1:
<asp:TextBox ID="TexDate" runat="server" Width="295px"></asp:TextBox>
&nbsp; Date2:<asp:TextBox ID="TexDate2" runat="server" Width="282px"></asp:TextBox>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="Column_0" DataSourceID="Websamples" AllowPaging="True"
Height="236px" Width="1288px" BackColor="White" BorderColor="#DEDFDE"
BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black"
GridLines="Vertical">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="Column_0" HeaderText="Column_0" ReadOnly="True"
SortExpression="Column_0" />
<asp:BoundField DataField="Date_Submitted" HeaderText="Date_Submitted"
SortExpression="Date_Submitted" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
<asp:BoundField DataField="Telephone" HeaderText="Telephone"
SortExpression="Telephone" />
</Columns>
<FooterStyle BackColor="#CCCC99" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<RowStyle BackColor="#F7F7DE" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#FBFBF2" />
<SortedAscendingHeaderStyle BackColor="#848384" />
<SortedDescendingCellStyle BackColor="#EAEAD3" />
<SortedDescendingHeaderStyle BackColor="#575357" />
</asp:GridView>
<asp:Button ID="Button1" runat="server" Text="Find" />
<asp:SqlDataSource ID="Websamples" runat="server"
ConnectionString="<%$ ConnectionStrings:wensamplesConnectionString %>"

SelectCommand="SELECT Column_0, Date_Submitted, Name, Email, Telephone FROM WebSamples WHERE (Date_Submitted = @Date_Submitted) OR (Date_Submitted = @Date_Submitted2)"
ConflictDetection="CompareAllValues"
DeleteCommand="DELETE FROM [WebSamples] WHERE [Column_0] = @original_Column_0 AND (([Date_Submitted] = @original_Date_Submitted) OR ([Date_Submitted] IS NULL AND @original_Date_Submitted IS NULL)) AND (([Name] = @original_Name) OR ([Name] IS NULL AND @original_Name IS NULL)) AND (( = @original_Email) OR ([Email] IS NULL AND @original_Email IS NULL)) AND (([Telephone] = @original_Telephone) OR ([Telephone] IS NULL AND @original_Telephone IS NULL))"
InsertCommand="INSERT INTO [WebSamples] ([Column_0], [Date_Submitted], [Name], [Email], [Telephone]) VALUES (@Column_0, @Date_Submitted, @Name, @Email, @Telephone)"
OldValuesParameterFormatString="original_{0}"


UpdateCommand="UPDATE [WebSamples] SET [Date_Submitted] = @Date_Submitted, [Name] = @Name, [Email] = @Email, [Telephone] = @Telephone WHERE [Column_0] = @original_Column_0 AND (([Date_Submitted] = @original_Date_Submitted) OR ([Date_Submitted] IS NULL AND @original_Date_Submitted IS NULL)) AND (([Name] = @original_Name) OR ([Name] IS NULL AND @original_Name IS NULL)) AND (([Email] = @original_Email) OR ([Email] IS NULL AND @original_Email IS NULL)) AND (([Telephone] = @original_Telephone) OR ([Telephone] IS NULL AND @original_Telephone IS NULL))">
<DeleteParameters>
<asparameter Name="original_Column_0" Type="String" />
<asparameter Name="original_Date_Submitted" Type="String" />
<asparameter Name="original_Name" Type="String" />
<asparameter Name="original_Email" Type="String" />
<asparameter Name="original_Telephone" Type="String" />
</DeleteParameters>
<InsertParameters>
<asparameter Name="Column_0" Type="String" />
<asparameter Name="Date_Submitted" Type="String" />
<asparameter Name="Name" Type="String" />
<asparameter Name="Email" Type="String" />
<asparameter Name="Telephone" Type="String" />
</InsertParameters>
<SelectParameters>
<asp:ControlParameter ControlID="TexDate" Name="Date_Submitted"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TexDate2" Name="Date_Submitted2"
PropertyName="Text" />
</SelectParameters>
<UpdateParameters>
<asparameter Name="Date_Submitted" Type="String" />
<asparameter Name="Name" Type="String" />
<asparameter Name="Email" Type="String" />
<asparameter Name="Telephone" Type="String" />
<asparameter Name="original_Column_0" Type="String" />
<asparameter Name="original_Date_Submitted" Type="String" />
<asparameter Name="original_Name" Type="String" />
<asparameter Name="original_Email" Type="String" />
<asparameter Name="original_Telephone" Type="String" />
</UpdateParameters>
</asp:SqlDataSource>
</h3>
</asp:Content>

 

[JRB-Bldr]

Lets start by clarifying your terminology so that WE can understand you better.

I have a gridview that as one text box with a button

Based on the ASPX code you posted above you have a Form (Not merely a GridView)
On that Form you have 2 Textboxes, a Button, and a Gridview
(Note - the Textbox is NOT on the Gridview)

Now you still have not posted your Button's Click code from your "Code Behind".

ASP.NET is a framework generally written in one of two languages - VB script or C#. ASP .NET is a server-side scripting language. Your .aspx pages are just scripts that tell IIS how to render a page.

In fact since on your ASPX page you define your first Textbox

TextBox ID="TexDate" runat="server"​

So you really don't even need to use a Button to make changes happen.
You should have available a TexDate_Click() method in your "Code Behind" that you could use, by itself, to make things occur.

Without the code from your "Code Behind", we do not know what you are having the Button's Click event do, nor what you have (or do not have) in your TexDate_Click() event code.

Good Luck,
JRB-Bldr

 

[Cpreston]

Thank you for your reply and correcting my terminology.

As i mentioned I am new to this and have been trying to follow on line tutorials and trying to adapt things and by sounds of it have learnt nothing and got no where.

So with your greater knowledge how do you suggest I have a grid view and the search between 2 dates from the date field so it displays the results. I then want to export these results to Excel but trying to take it one step at s time.

Thanks

 

[JRB-Bldr]

First of all, I most likely cannot give you code, since there are multiple ways of approaching this and I still don't know what "Code Behind" language you are working in.

I work in VB.ASP. For all I know you could be working in C#.ASP.

However, in general, I put very little 'working code' into my ASPX files and put the bulk of it into the associated ASPX.VB file. In that way I can use methods/events associated with Form objects to make things occur.
AGAIN - different people have different approaches and most of them all work.

While I am on this let me correct a typo in my previous post. There is No Click method associated with a Textbox (as I suggested for TexDate). Instead there is a TextChanged method.

Doing a Google Search for: asp 2 dates update grid will turn up some suggestions.
If you are working in C#, one of them you might look at is:

http://forums.asp.net/t/2090607.aspx?Show+the+records+between+two+dates+in+Gridview+using+asp+net+c+

Note the amount of 'work' being done in the "Code Behind" file: FilterEmpDetails_Report.aspx.cs

Regardless of your approach, in your Button_Click() method, you:
1. Read the separate values from your 2 dates
2. Build a new Query Command Select String using the dates as selection criteria
3. Run your Query
4. Update your Grid.

Good Luck,
JRB-Bldr

 

[Cpreston]

HI

Thanks for the reply. I am using C#. To try and make things easier I have just created a new web project and added in the grid wand linked it to the SQL database

I have ran this and it is listing all the rows from the SQL table. I do not want to do lots of coding (as I am a novice)

I am not sure what code you would need to look at, if I sue the Split, it shows the Grid and also the client Objects and Events code.

Thanks

 

[JRB-Bldr]

I am using C#.

I am not sure what code you would need to look at

Well, as I indicated above, I am Not working in C#.
Yes I could most likely go through C# code and figure out what it going on, but I am working in VB.ASP.

I have already given you a very general approach that is not language specific.

Look over what is being done on those pages referenced by the Google Search.
They should point you in the right direction.

I do not want to do lots of coding

But that is how you learn.
Taking 'shortcuts' and using things like 'wizards' might get you an answer, but they eliminate your opportunity to discover HOW/WHY things work the way they do.
If you try to avoid it, you will only limit your learning.
And, those web site references do not have LOTS of coding on them.

Good Luck,
JRB-Bldr

 

[jbenson001]

As JRB has stated, DO NOT use any type of wizard builders or DataSource controls. Microsoft likes to use them in examples, but in a real world situation, they are garbage. You will only be fighting them once you need to do something more complicated.
There are plenty of examples on line as stated. Take one step at time like you mentioned and post back with questions. I can help you with C# code. But more importantly, it is the approach you take that is important.

I would simply start by creating a new webpage. Place 2 textboxes on it (From Date: and To Date or however you want to label them. Also place a button on the page. Next, write the code (in the code behind page, as stated) for the Click event of the button. In there, either construct the sql and use a command object to execute it OR better yet, I always suggest using a stored procedure and call that.

Good luck and post back with any questions. But first get started with something and do research on Google, there are so many examples out there, it will be overwhelming.

 

[Cpreston]

Hi

Jbenson001 I have looked hard and wide on google but I either find code far to complex or something fasilry basic. However, this is where I am now.

1. I crested the new page and added the grid, linked it to the SQL database, rant it and all works
2. Added in a textbox and called this Textdate1, ran this entered the date and nothing happened.
3. Went into the Configure data source and ran the query builder and added in an @date1 in the filter for date_submitted field and then added it to the control for textdate1. Entered a date and it brought back the rows from that date.
4. Working on that principle I added another text box called textdate2, went into the query builder and used between @date1 and @date2 and made the control for @date2 for textdate2. I tested this in the query builder and all works great and I got the right results back.
5. When I went into running the web page and entered both dates and pressed return it gave me nothing back at all.

That is as far as I am now. I am not sure how to put the code in a button to work with the date text boxes (click event). So fi you could perhaps help here, I think I am nearly there. Thansk

 

[jbenson001]

Again, do NOT use the datasource controls. They may work for simple things, but at some point your query will get too complex and it will be a bear trying to get them to work.
Again, I would write a stored procedure. What DB are you using? A simple sql query you can write to do what you want would be something like this:

SELECT <columns you need>
FROM <the table name>
Where <date col> BETWEEN @date1 AND @date2

This can be a simple stored procedure with 2 parameters of type datetime (@date1 and @date2)
The other things in the <> you have to fill in with the names of your table, etc....

In the button click event, you set up a command object, and call your stored procedure (passing in your parameters)

All the things I am suggesting are easily found doing a simple search.
First thing is to write a stored procedure to get your data. Forget the datasource controls and wizards.

 

[JRB-Bldr]

You seem to want to continue to follow the path you have already set up instead of listening to our advice.

Both jbenson001 and I have advised you to go in a different direction.

If you want to change course and follow our advice, you might want to look at:

https://www.youtube.com/watch?v=Tr7Bwdm-FAk

or

https://www.youtube.com/watch?v=HwzrPQi_d0E

Otherwise - Good Luck,
JRB-Bldr

 

[Cpreston]

Hi

Not at all, I prefer to go in the right direction and will look into doing the SQL Stored Procedure way and try to use the methods you suggest. To do this I will start a fresh, the more I do it the more it will sink in, this is a huge learning curve for me. I appreciate all your advise of course and know you are telling me the right way to do things.

I will give it another go tomorrow using the SP, thanks for the posts

 

[Cpreston]

Hi

Ok Went through both links for tutorials and gave ne better understanding of things.

I have also created a SQL stored procedure.

I will google about how to run an SP from the Button on click.

Thanks

 

[JRB-Bldr]

Personally I never needed to execute a Stored Procedure from within my VB.ASP code.
Instead I built my own SQL Query Command string in the VB code and executed it such that it returned a DataTable which my VB code then worked on.

However I have indeed used SQL Server Stored Procedures in other code projects.

Regardless, to execute a SQL Server Stored Procedure from ASP, you might want to look at:

https://support.microsoft.com/en-us/kb/164485

or

http://www.sitepoint.com/forums/showthread.php?766624-How-to-call-sql-stored-procedure-in-asp

or

http://www.psacake.com/web/hk.asp

Good Luck,
JRB-Bldr

 

[Cpreston]

Thanks I will take a look at the link's as I am struggling at the moment.
The stored procedure works ok within SQL Management Studio but how the heck I get it into a web form is beyond me at the moment.

Thanks

 

[jbenson001]

The reason to use Stored Procedures, even for the simplest of tasks, is for performance and maintainability. the code exists in one place, the sql server. If a change needs to be done, it is one place, not on several pages where you would now have to change code and redeploy your project. Would you rather change multiple pages and redeploy, possibly having to bring down the site while this is done, or rather change one stored procedure and and not have to roll out any code? Also there is a performance aspect to this as well.​

As for executing the SP in a page, it is very simple. Take a look at this example:

https://msdn.microsoft.com/en-us/library/d7125bke.aspx

Scroll down to the part that uses a Command object.

Now, the example uses a datatread because that is what is send back from the ExecuteReader method, there is no ExecuteDataSet or ExecuteDataReader.
However you can easily load a DataTable from the reader which is what I would do in your case. I like to work with datatables in code. So you can do something like this:

//Make sure you have these using statements at the top of your code:
using System.Data.SqlClient;
using System.Data;

SqlCommand cmd = new SqlCommand();
var dr = cmd.ExecuteReader();
var dt = new DataTable();
dt.Load(dr);

this.yourgridview.DataSource = dt;
this.yourgridview.DataBind();

That should get you started

 

[Cpreston]

Hi

I found a good walk through in this link

https://msdn.microsoft.com/en-us/library/k10148y1.aspx

However, I have just found the table. for whatever reason as the date_submitted as a string field and not datetime.

The stored procedure is using this code

 SELECT [Column 0] AS Column_0, [Date Submitted] AS Date_Submitted, Name, Email, Telephone 
    FROM AllSamples 
    WHERE [Date Submitted] BETWEEN @date1 AND @date2

I am getting an Error converting data type nvarchar to datetime when running it, but works in SQL Manamgement Studio. So need a way to convert the field to datetime now. All fun this.

 

[jbenson001]

Please Please Please DO NOT use the DataSourceControls.. Please.. write the code yourself

 

[Cpreston]

Hi

Yes I intend to try and write the code. I have changed the SQL table and also the tested the SP again, this is now working with datetime.

Ok now to get it into ASP net. Just so I am following the right path

1. Create New Web Site and use ASP.NET Empty Web site
2. Add a Datasource
3. Add a web form

This is far as I understand and probably got his bit wrong, so lets get 1 to 3 going first, si this the right way