|
|
Forums » .NET » ASP.NET »
|
How to insert with the system date |
Posted Date: 21 Jul 2012 Posted By:: Moe Member Level: Bronze Member Rank: 1899 Points: 5
Responses:
22
|
Hello everyone,
I am new at programming in asp.net and C#
Recently, I have been trying to create a web application using .net
I am doing well so far but i have a problem
I have a page for employees to fill their information about some activities they have assigned to do.
I already designed a page to insert into the database and it's working fine
but what I want is when the employee wants to display a report "there is another page for displaying results from the database using gridview" I want to display the period for when the employee have inserted the data outside the gridview.
for example, after the employee finish entering his data, he will enter the report page. I want to display outside the gridview the period in years.
Like: Period from 1/1/2012 to 31/12/2012
and if we entered the new year 2013 every new data he enters will be in period from 1/1/2013 to 31/12/2013 and so on..
How can I do that?
|
Responses
|
#681294 Author:  Ajatshatru Upadhyay Member Level: Gold Member Rank: 17 Date: 21/Jul/2012 Rating:  Points: 0 |
Hi,
Is there columns in database for the "periods"?
Hope it'll help you.
Regards
Ajatshatru
|
| #681295 Author: Ajatshatru Upadhyay Member Level: Gold Member Rank: 17 Date: 21/Jul/2012 Rating: Points: 4 |
Hi,
I am assuming that you have two columns for the period to which report is being generated.
Assuming one column is "PeriodFrom" and another is "PeriodTo".
Take a label control to your page.
Now when you after fetching the data from database in DataTable, you can do something like:
DateTime from, to;
foreach (DataRow dr in dt.Rows)
{
from = Convert.ToDateTime(dr["PeriodFrom"]);
to = Convert.ToDateTime(dr["PeriodTo"]);
Label1.Text = from.ToShortDateString() + " to" + to.ToShortDateString();
}
Hope it'll help you.
Regards
Ajatshatru
|
| #681329 Author: Moe Member Level: Bronze Member Rank: 1899 Date: 21/Jul/2012 Rating: Points: 1 |
Ok Prasad i think you got the idea
But what about displaying it?
when the user is going to request the report for display
I want a label at the top of the page that says:
"period from 1/1/2012 to 31/12/2012"
or
"Evaluation period 2012"
Also the user can choose any year period he wants by clicking in a calender or any other way, and it will display records that has been done during that year
|
| #681452 Author: Moe Member Level: Bronze Member Rank: 1899 Date: 23/Jul/2012 Rating: Points: 1 |
Anyone?
It's really important
|
#681454 Author:  RayalaHariKishore Member Level: Gold Member Rank: 52 Date: 23/Jul/2012 Rating: Points: 4 |
hi,
Think you inserted the records into database with datetime. refer the post by prasad. now you want to display the records in the select statement you can prepare where condition which takes the datetime which is selected by user. and display the date in page by using controlname.Text=dateselected. or you can change the message like "period from 1/1/2012 to 31/12/2012"
or
"Evaluation period 2012"
as you want.
select username from tablename where date=somethinguserselected.
refer this you can get good idea how to do clearly step by step.
Rayala HariKishore
try..try..try...you achieved it.
http://rayalaharikishore.wordpress.com/
|
| #681478 Author: Moe Member Level: Bronze Member Rank: 1899 Date: 23/Jul/2012 Rating: Points: 1 |
ok I almost got it
but regarding to insert with system date, how can insert with year only?
I dont want to save time and date. I want to save the year only
Instead if mm/dd/yyyy
I want it to be something like yyyy
|
| #681485 Author: Ajatshatru Upadhyay Member Level: Gold Member Rank: 17 Date: 23/Jul/2012 Rating: Points: 2 |
Hi,
You don't need to save only year in database. You can fetch only year part from it with below query anyway:
select DATEPART(YEAR, BirthDate) from employees where city = 'seattle'
Hope it'll help you.
Regards
Ajatshatru
|
| #681689 Author: Moe Member Level: Bronze Member Rank: 1899 Date: 24/Jul/2012 Rating: Points: 1 |
What I did is:
- I have added system time and date. if the user is going to enter the data, the system time and date will be stored in the database
-I have created a text box and a button that will filter the database based on the year, and I have used Ajatshatru's code:
select RDate(YEAR) from employees where RDate = '<% TextBox1.Text %>'
but it didnt work, it will display nothing !
I think my idea is wrong
Is there a better solution ? Please I have a deadline I must finish
|
| #681693 Author: RayalaHariKishore Member Level: Gold Member Rank: 52 Date: 24/Jul/2012 Rating: Points: 3 |
hi,
select RDate(YEAR) from employees where RDate = '<% TextBox1.Text %>'
what is this?
select DATEPART(YEAR, BirthDate) from employees where city = 'seattle'
instead of using datepart you used RDate DATEPART is inbuilt function of sql
select DATEPART(year,RDate) from employees where RDate = '<% TextBox1.Text %>' here try to give the data entered date and then you will get he date if data is insert then you can perform logic for displaying like you want showing 1 year difference.
Rayala HariKishore
try..try..try...you achieved it.
http://rayalaharikishore.wordpress.com/
|
| #681709 Author: Ajatshatru Upadhyay Member Level: Gold Member Rank: 17 Date: 24/Jul/2012 Rating: Points: 1 |
Hi,
What data is getting passed in the textbox? Is it year only?
Secondly, the code you posted in your last post will not work.
See below code for SELECT statement when the data in the textbox is year only:
SqlCommand cmd = new SqlCommand();
Cmd.CommandText = "SELECT DATEPART(YEAR, RDate) FROM employees WHERE DATEPART(YEAR, RDate) = @year";
cmd.parameters.AddWithValue("@year", TextBox1.Text.Trim());
Hope it'll help you.
Regards
Ajatshatru
|
| #681712 Author: Moe Member Level: Bronze Member Rank: 1899 Date: 24/Jul/2012 Rating: Points: 1 |
I think this code is for insertion?
should I store system date and time first to display the year only?
because what I want is to let the user choose the year period to display
for example the user want's to see his data that has been stored in 2011
he will choose the year (textbox or droplist)
and the report will display the results that have been saved in 2011
|
| #681728 Author: Ajatshatru Upadhyay Member Level: Gold Member Rank: 17 Date: 24/Jul/2012 Rating: Points: 1 |
Hi,
Yes, you should store system's date time and the above code is a "SELECT" query, not an "INSERT" one.
It is doing exactly what your requirement is. You only need to add the column name into the select command which you want to fetch in order to display.
See below modified query:
SELECT * FROM employees WHERE DATEPART(YEAR, RDate) = @year
The above query would match the year passes with the query to table's RDate column by extracting year part from it (RDate)
Pass the year to the above query either by textbox or drop down list.
Hope it'll help you.
Regards
Ajatshatru
|
| #681859 Author: Moe Member Level: Bronze Member Rank: 1899 Date: 25/Jul/2012 Rating: Points: 1 |
I'm sorry but i'm having some problems
should i add another column in the database called YEAR?
should I put the select statement "SELECT * FROM employees WHERE DATEPART(YEAR, RDate) = @year" in SQLDataSource? or where to put in behind code?
because what I did is to put it in sqldatasource and I had an error says "Must declare the scalar variable "@year"."
Is DATEPART is considered as a column ?
can you tell me what to do to step by step to be in the same track with you?
thank you
|
| #681878 Author: Ajatshatru Upadhyay Member Level: Gold Member Rank: 17 Date: 25/Jul/2012 Rating: Points: 1 |
Hi,
Okay, so in case of SqlDataSource, use textbox text directly as you were doing.
Use such coding convention in code behind.
You do not need to add additional column.
Hope it'll help you.
Regards
Ajatshatru
|
| #681891 Author: Moe Member Level: Bronze Member Rank: 1899 Date: 25/Jul/2012 Rating: Points: 1 |
I dont have behind code
Should I only have a gridview
Should i add behind code?
Tell me what to add exactly and where plz
Thank you
|
| #682018 Author: Ajatshatru Upadhyay Member Level: Gold Member Rank: 17 Date: 26/Jul/2012 Rating: Points: 1 |
Hi,
See the below example, in which I used code behind to extract year from DateTime column "RDate".
My aspx page
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="txtYear" runat="server"></asp:TextBox><br /><br />
<asp:Button ID="btnShow" runat="server" Text="Show record"
onclick="btnShow_Click" /><br /><br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
Width="836px" CellPadding="4"
ForeColor="#333333" GridLines="Both"
AllowSorting="True">
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<Columns>
<asp:BoundField DataField="ActivityID" HeaderText="ID"
SortExpression="ActivityID" />
<asp:BoundField DataField="UserName" HeaderText="Staff ID"
SortExpression="UserName" />
<asp:BoundField DataField="Process" HeaderText="Process"
SortExpression="Process" />
<asp:BoundField DataField="Activity" HeaderText="Activity"
SortExpression="Activity" />
<asp:BoundField DataField="HowItWillBeMeasured"
HeaderText="How It Will Be Measured" SortExpression="HowItWillBeMeasured" />
<asp:BoundField DataField="Weight" HeaderText="Weight"
SortExpression="Weight" ItemStyle-CssClass="weight" />
<asp:BoundField DataField="Completion" HeaderText="Completion"
SortExpression="Completion" />
<asp:BoundField DataField="Score" HeaderText="Score" ItemStyle-CssClass="score"
SortExpression="Score" />
<asp:BoundField DataField="DeliveryDate" HeaderText="Delivery Date"
SortExpression="DeliveryDate" />
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
<br />
<asp:Label ID="lblWeight" runat="server"></asp:Label>
<br />
<asp:Label ID="lblScore" runat="server"></asp:Label>
<br />
<asp:Label ID="Label3" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
Below is the code behind (C#). Observer the select query
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DotNetSpider
{
public partial class TestCode : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnShow_Click(object sender, EventArgs e)
{
LoadFilteredData();
}
private void LoadFilteredData()
{
SqlConnection conn;
SqlCommand cmd;
SqlDataAdapter sda;
DataTable dt;
conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["DOTNETSPIDERConnectionString"].ConnectionString;
cmd = new SqlCommand();
cmd.CommandText = "SELECT ActivityID, UserName, Process, Activity, HowItWillBeMeasured, Weight, Completion, Score, DeliveryDate FROM StaffActivity WHERE DATEPART(YEAR, RDate) = @rdate";
cmd.Parameters.AddWithValue("@rdate", txtYear.Text.Trim());
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
dt = new DataTable();
sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
sda.Fill(dt);
GridView1.DataSource = dt.DefaultView;
GridView1.DataBind();
sda.Dispose();
cmd.Dispose();
conn.Dispose();
}
}
}
Hope it'll help you.
Regards
Ajatshatru
|
| #682257 Author: Moe Member Level: Bronze Member Rank: 1899 Date: 28/Jul/2012 Rating: Points: 1 |
I have these three errors
The name 'txtYear' does not exist in the current context
The name 'Gridview1' does not exist in the current context
The name 'Gridview1' does not exist in the current context
What should I do?
|
| #682270 Author: Ajatshatru Upadhyay Member Level: Gold Member Rank: 17 Date: 28/Jul/2012 Rating: Points: 1 |
Hi,
Check the grid view ID. You must have given a different ID to grid view (aspx page). Same with the textbox. Check the ID of the textbox by which you are taking year as input.
See my code below:
<asp:TextBox ID="txtYear" runat="server"></asp:TextBox><br /><br />
<asp:Button ID="btnShow" runat="server" Text="Show record" onclick="btnShow_Click" /><br /><br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
.........
.........
Observer the "id" of grid view is "GridView1" and "id of textbox is "txtYear".
Hope it'll help you.
Regards
Ajatshatru
|
| #682299 Author: Moe Member Level: Bronze Member Rank: 1899 Date: 29/Jul/2012 Rating: Points: 1 |
I already checked it, it's the same
I've copied the same code you gave me
|
| #682300 Author: Ajatshatru Upadhyay Member Level: Gold Member Rank: 17 Date: 29/Jul/2012 Rating: Points: 1 |
Hi,
Please attach the code with your post (both aspx and code behind)
Hope it'll help you.
Regards
Ajatshatru
|
| #682301 Author: Moe Member Level: Bronze Member Rank: 1899 Date: 29/Jul/2012 Rating: Points: 1 |
Ajatshatru , it's working now
It was a stupid mistake with the namespace, I wrote Webapplication instead of WebApplication
Thank you very much Ajatshatru
and thank you all for helping
|
|
| Post Reply |
|
|
|
You must Sign In to post a response.
|
|
|
|
|
Active MembersTodayLast 7 Daysmore... Talk to Webmaster Tony John |