Datetime And Timezones In SQL Server 2005
Apr 18, 2008
I am using a SQL Server 2005 database for my ASP.NET 2.0 project.
How does SQL Server 2005 store datetime values? How does it display datetime values in query results?
Does it store datetime values as UTC time by default? If not, how do I control the timezone at which to store datetime values? What if, in my ASP.NET 2.0 application, I set a DateTime object in the SqlCommand Parameters collection? Would the timezone be automatically determined from the DateTime object, allowing SQL Server to make any necessary adjustment before storing the value?
Does it display datetime values in query results at local timezone by default? If so, is it possible to control, at SQL level, the timezone at which to display the datetime value?
View 3 Replies
ADVERTISEMENT
Jan 31, 2006
The situation is that I have a query where a [thing] is supposed toend, in the sense that I don't want it to be pulled back in the query,at a certain time (end_date), but that is determined by the timezonethat [thing] is located in (end_timezone).Basically, I want to say:1) If the current record has a timezone not equal to the current timezone, which is mountain, then change the end_date being pulled back toreflect the time zone2) Then, only pull back records that are scheduled to end before thattime.I know this statement isn't valid, but I'll post it here to try toconvey what I'm attempting:select end_date, end_timezone,CASEend_timezoneWHEN 'ET' THEN DATEADD(hh, 2, end_date)WHEN 'CT' THEN DATEADD(hh, 1, end_date)WHEN 'PT' THEN DATEADD(hh, -1, end_date)ELSE end_dateEND AS theEndDatefrom offerswhere end_timezone = 'PT'and theEndDate >= {ts '2006-01-31 14:01:27'}Of course, this statement fails. Any suggestions on how to do what I'mtrying to do?Thanks!
View 2 Replies
View Related
Mar 6, 2007
Are there any function in SQL Server 2005 which can help to calculate the total no. of days and months? Let's said if I provide 2 dates, 28-Feb-2001 and 01-Mar-2004, it can return 36 Months and 2 Days. The concept is like the function months_between in Oracle. Are there any function in SQL Server 2005 can achieve this?
View 3 Replies
View Related
Mar 19, 2007
Hi there
I have an application running in two development environments, one using a sql server 2005 database and the other using a 2000 database. The application works on the 2000 database but when i try to insert values into the 2005 database the date format is incorrect (mm/dd/yyyy). I've checked the regional data settings on both machines and they are identical. The application (which i inherited) uses inline sql and when i dump the values before the sql command is run i get dd/mm/yyyy for the app running 2005 and mm/dd/yyyy for the app on 2000. I'm trying to determine if this is an issue with the machine itself and the .net framework installed or infact the two different versions on sql server.
thanks
View 5 Replies
View Related
Jul 23, 2005
Does anyone know if SQL Server 2005 will support date values before1/1/1753 in the datetime data type?
View 1 Replies
View Related
Jul 22, 2007
Is it possible to have a DateTime field in a row automatically generated when a row is created?
ie:
INSERT INTO [TableName] ([DateTimeStamp], [UserId], [Title], [Explanation]) VALUES (DateTime.Now(), @UserId, @Title, @Explanation)
or something like that?
View 2 Replies
View Related
Apr 7, 2006
How to replace DateTime field with null value in SQL 2005 server
I create a stored procedure aa, It works well, but sometimes I hope to replace CreateDate field with null value,I don't know how to doIt seems that datetime type is not null value
create aa @CreateDate DatetimeAsUpdate cw set CreateDate=@CreateDate
View 3 Replies
View Related
Nov 22, 2007
hey everyone,
Im trying run a stored procedure, but I am getting the following message when trying to execute it.
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
which seems strange, as there is no integer going to a datetime field. Also, it does not return the value that is incorrect, nor the parameter that is failing. If there is anyway to view this in VS 2005 Team suite, please let me know.
It should also be noted that when I execute this sproc from within sql management studio, it executes fine.
This is the stored procedure im trying to execute...
CREATE PROCEDURE [dbo].[sp_UpdateRequest]
@request_id INT,
@request_client_id INT,
@request_description VARCHAR(150), @request_date DATETIME,
@request_edit_date DATETIME, @request_status VARCHAR(25), @request_approve_date DATETIME,
@request_activity_code VARCHAR(7), @request_department_code VARCHAR(3), @request_participants INT,
@request_activity_date DATETIME, @request_activity_due_date DATETIME,
@request_company_id INT, @request_company_code INT,
@request_notes VARCHAR(2000)
AS
BEGIN
UPDATE invoice_requests
SET request_client_id = @request_client_id,request_description = @request_description,
request_date = @request_date, request_edit_date = @request_edit_date,
request_status = @request_status, request_approve_date = @request_approve_date,
request_activity_code = @request_activity_code,
request_department_code = @request_department_code,
request_participants = @request_participants,
request_activity_date = @request_activity_date,
request_activity_due_date = @request_activity_due_date,
request_company_code = @request_company_code, request_company_id = @request_company_id,
request_notes = @request_notes
WHERE request_id = @request_id
END
GO
in my .net app, all the datetime objects are valid dates, so I could not see why this would be generating the error that it is, so I ran the SQL Profiler, and this was the output. Again, i cannot see where im going wrong...
please note, I have seperated the output from the single slab of text, making sure I didnt remove anything...
exec sp_executesql
N'EXECUTE sp_UpdateRequest
@request_id,
@request_client_id,
@request_description,
@request_date,
@request_edit_date,
@request_status,
@request_approve_date,
@request_activity_code,
@request_participants,
@request_activity_date,
@request_activity_due_date,
@request_company_id,
@request_company_code,
@request_notes',
N'@request_id int,
@request_client_id int,
@request_description varchar(8000),
@request_date datetime,
@request_edit_date datetime,
@request_status varchar(5),
@request_approve_date datetime,
@request_activity_code varchar(8000),
@request_department_code varchar(8000),
@request_participants int,
@request_activity_date datetime,
@request_activity_due_date datetime,
@request_company_id int,
@request_company_code int,
@request_notes varchar(8000)',
@request_id=5,
@request_client_id=1,
@request_description='',
@request_date=''2007-11-22 16:34:32:997'',
@request_edit_date=''2007-11-22 16:34:32:997'',
@request_status='Draft',
@request_approve_date=''1970-01-01 00:00:00:000'',
@request_activity_code='',
@request_department_code='',
@request_participants=0,
@request_activity_date=''2007-11-29 00:00:00:000'',
@request_activity_due_date=''2007-11-23 00:00:00:000'',
@request_company_id=0,
@request_company_code=1,
@request_notes=''
any help on this would be greatly appreciated.
Cheers
View 4 Replies
View Related
Apr 2, 2008
I'm importing data form an Excel file to a Sql Server Database. Some of the data imported represents time as a double type so i convert the times into DateTime to be inserted into the database. The time values that aren't available in the Excel file are 0.. so what i want to do is insert null into the database for all the values that are 0 in the excel file... How do i do that based on this code i have so far:protected void ButtonImport_Click(object sender, EventArgs e){PanelUpload.Visible = false;PanelView.Visible = false;PanelImport.Visible = true;LabelImport.Text = "";OleDbCommand objCommand = new OleDbCommand();objCommand = ExcelConnection(); OleDbDataReader reader;reader = objCommand.ExecuteReader(); while (reader.Read()){DateTime? in_1 = null;DateTime? out_1 = null;DateTime? in_2 = null;DateTime? out_2 = null; int emp_id = Convert.ToInt32(reader["emp_id"]);DateTime date_entry = Convert.ToDateTime(reader["date_entry"]);if (Convert.ToDouble(reader["in_1"]) != 0)in_1 = ConvertDoubleToDateTime((double)reader["in_1"]);if (Convert.ToDouble(reader["out_1"]) != 0)out_1 = ConvertDoubleToDateTime((double)reader["out_1"]);if (Convert.ToDouble(reader["in_2"]) != 0)in_2 = ConvertDoubleToDateTime((double)reader["in_2"]);if (Convert.ToDouble(reader["out_2"]) != 0)out_2 = ConvertDoubleToDateTime((double)reader["out_2"]); ImportIntoAttendance(emp_id, date_entry, in_1, out_1, in_2, out_2);} reader.Close();}protected void ImportIntoAttendance(int emp_id, DateTime date_entry, DateTime? in_1, DateTime? out_1, DateTime? in_2, DateTime? out_2){ SqlDataSource AttendanceDataSource = new SqlDataSource();AttendanceDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["SalariesConnectionString1"].ToString();AttendanceDataSource.InsertCommandType = SqlDataSourceCommandType.Text;AttendanceDataSource.InsertCommand = "INSERT INTO Attendance (emp_id, date_entry, in_1, out_1, in_2, out_2) " +"VALUES ('" + emp_id + "', '" + date_entry + "', '" + in_1 + "', '" + out_1 + "', " +"'" + in_2 + "', '" + out_2 + "')"; int rowsAffected = 0;try{rowsAffected = AttendanceDataSource.Insert();}catch(Exception ex){LabelImport.Text += "<font color=red>" + ex + "</font><br />";} }private DateTime ConvertDoubleToDateTime(double dbTime){string[] SplitTime = dbTime.ToString().Split('.');string hours = SplitTime[0];string minutes = String.Empty;string time = String.Empty; if (dbTime.ToString().IndexOf('.') != -1){if (SplitTime[1].Length >= 1){if (SplitTime[1].Length == 1)minutes = Convert.ToString(Convert.ToDouble(SplitTime[1]) * 10);else if (SplitTime[1].Length > 1)minutes = SplitTime[1];}}elseminutes = "00";time = hours + ":" + minutes;return Convert.ToDateTime(time);}
View 3 Replies
View Related
Jul 9, 2007
Hi,
I'm inserting a datetime values into sql server 2000 from c#
SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime
C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?
thanks,
Mani
View 3 Replies
View Related
Aug 26, 2007
I€™m getting a datetime format problem(mm-dd-yyyy for dd-MM-yyyy), when I install SQL Server 2005 Express. {The exception is: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.}
My windows Regional and Language options €“ English (United Kingdom), Sort date format is dd-MM-yyyy.
When converting the date time in Sql server is using the mm-dd-yyyy format. But I€™m supplying the dd-mm-yyyy format date time.
I tried number of things none of them worked for me
1. Tried changing the default language and get the date time format
- exec sp_configure 'default language', 2057
reconfigure
- did not work
EXEC sp_defaultlanguage 'my user name', 'British'
- did not work
(Ref: http://www.cactushop.com/support/UKUS-date-format-issues-with-MS-SQL---conversion-errors-or-blank-pages__592__.htm)
2. Tried a registry hack by opening regedit, and get the following 3 language keys and change it to decimal 2057:
[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server90ToolsClientSetupCurrentVersion]
[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server90ToolsSetup
[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1Setup]
(Ref: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891)
- did not work
3. Every thing in the Regional and Language options to UK and British with the date time format input language keyboard and every think else I could think of, which could link to US English or US date format --------- Did not work
4. even went into the extend of modifying the date format on a Windows machine for new users account by editing the HKEY_USERS registry key and creating a new user - Did not work
(Ref: http://www.windowsitpro.com/Article/ArticleID/39407/39407.html )
5. Uninstall and reinstall SQL server express several time and did the steps 1 €“ 4 where applicable €“ did not work€¦.
If anyone has any idea of what I have to do to change the date time format in the SQL Server 2005 to use the dd-mm-yyyy format for dates....
Please help me or point me in the direction in which I have to look for an answer.
Thank you very much€¦.
View 9 Replies
View Related
Sep 29, 2006
Hi,
I created an RMO script to generate a republishing replication (A to B then B to C). Because I don't want to start the B to C replication until the A to B replication is complete, I start the Snapshot and the Merge processes synchronously (vs. asynchronously). If I ran them asynchronously, I'd need to put in logic to loop-check the completion status of the Merge agent job before kicking off the B to C process.
In the Snapshot agent, this means running .GenerateSnapshot(); (vs. StartSnapshotGenerationAgentJob)
and in Merge agent this means running .SynchronizationAgent.Synchronize(); (vs. SynchronizeWithJob)
I notice that the Snapshot agent never shows a LastRun or LastRun Outcome even though the snapshots are being generated. I haven't tested the other scenario (asynchronous), but I presume they would register the LastRun columns because, as I read it, they use the SQL Agent jobs to accomplish their work.
Does anyone have RMO code that I could add to plug this information into whatever system table (?) somehow?
I'll put this in the SMO/RMO forum too.
Paul
View 3 Replies
View Related
Apr 19, 2006
For some reason, I recall having read that SQL Server 2005 would supporta datatype that represented date, but not time. (This would be usefulfor storing things like birthday, where you usually don't care about thetime of day). But I've got SQL Server 2005 installed, and there's nosuch datatype to be found.Is this something that might be released in a Service Pack, or is itjust not going to happen?
View 3 Replies
View Related
Feb 17, 2006
Hi,
I am having a problem with the DateTime parameter option in SQL 2005 reporting services.
Note that Server, workstation and database are set to British/English (UK) date/time localisation (dd/MM/yyyy) for example 25th December 2005=25/12/2005.
I create a simple query with a parameter on a datetime column and set the report parameter to Data type: DateTime in the report parameters dialog.
On preview or after deployment the following behaviour is observed.
Selecting 1st February 2006 in the datetime picker control populates the date parameter entry field with
01/02/2006 = 1st February 2006 (the correct UK format as expected)
When View Report is clicked the datetime parameter field magically changes to
02/01/2006
So forcing the datetime values to US format MM/dd/yyyy, the report runs OK but does not return data restricted to the required date.
If we select 25th February 2006 the datetime parameter field populates with
25/02/2006 = 25th February 2006 (the correct UK format as expected)
When View Report is clicked the datetime parameter changes to 02/01/2006 and an error is displayed
An error occurred during local report processing.
The value provided for the report parameter X is not valid for its type.
Is there a solution to this?
Thanks,
Neil
View 42 Replies
View Related
Feb 14, 2006
I user Visual Studio 2005 64 bit ,windowxp 64 bit ,sqlserver 2005
The Sql statement : "insert into sickleave (StaffID,sickLeaveReason,DateStart,DateEnd,RegistrationDate) values (20001,'test',28/3/2006,4/5/2006,4/5/2006 ) "
and the result in Datebase (All the time become 1/1/1900 0:00:00 )
Although i change the datetype from datetime to smalldatetime the result is same
and i try input the date 28/3/2006 0:00:00 into server but
it show the error:Incorrect syntax near '0'.
What wrong ? help me please,Thank.
View 5 Replies
View Related
Jan 1, 2008
hi ,
this is my first post on MS Forums .
Q: my question is that , i want to format a date column .. d/m/yyyy .. but there is no any format facility ..
anybody can help me here ??
View 1 Replies
View Related
Dec 6, 2006
I'm getting error:
String was not recognized as a valid DateTime.
my insert parameter:
<asp:Parameter Name="LastModified" Type="DateTime" DefaultValue= "<%=DateTime.Now.ToString() %>"
my insert command:
InsertCommand="INSERT INTO [Product] ([Enabled], [ProductCode], [ProductName], [ProductAlias], [CarrierId], [DfltPlanId], [DoubleRating], [DoubleRateProductId], [ConnCharges], [StartDate], [EndDate], [Contracted], [BaseProductId], [LastModified], [LastUser]) VALUES (@Enabled, @ProductCode, @ProductName, @ProductAlias, @CarrierId, @DfltPlanId, @DoubleRating, @DoubleRateProductId, @ConnCharges, @StartDate, @EndDate, @Contracted, @BaseProductId, @LastModified, @LastUser)"
LastModified is a datetime field.
Running sql2005
View 1 Replies
View Related
Mar 14, 2007
Hi all, having a little problem with saving dates to sql databaseI've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up Error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."I've tried researching it but not been able to find something similar. Heres the code: DateTime createOn = DateTime.Now;string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')"; Any help would be much appreciated
View 4 Replies
View Related
Mar 17, 2007
Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table:
ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard
The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time.
Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark
View 2 Replies
View Related
May 13, 2006
Hi,
I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date
'31/08/2006 23:28:59'
I get the error "...datetime data type resulted in an out-of-range datetime value"
I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be?
cheers,
Ernest
View 2 Replies
View Related
Mar 11, 2014
I am inserting date and time data into a SQL Server 2012 Express table from an application. The application is providing the date and time as a string data type. Is there a TSQL way to convert the date and time string to an SQL datetime date type? I want to do the conversion, because SQL displays an error due to the
My date and time string from the application looks like : 3/11/2014 12:57:57 PM
View 1 Replies
View Related
Sep 7, 2007
Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.
View 5 Replies
View Related
Jan 17, 2008
e.g.
1st March 2005 12:00:00
is showing as
01/03/2005 00:00:00
instead of
01/03/2005
Why does this happen?
View 4 Replies
View Related
Jun 15, 2004
I have the following SQL:
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
where right(term,4) always returns a string which constitutes a 4 digit year eg '1999','2004',etc.
The SQL above returns
2004-04-20 00:00:00.000 Deposit ...
Which makes me think that it is able to successfully construct the datetime object inline. But then when I try and do:
select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) where dt >= a.submit_date
I get the following error:
Syntax error converting datetime from character string.
Given that it executes the innermost SQL just fine and seems to convert the string to a datetime object, I don't see why subsequently trying to USE that datetime object for something (in this case comparison with submit_date which is a datetime in the table a) should screw it up. Help!!! Thanks...
View 6 Replies
View Related
Jul 20, 2005
Hi,I have a text file that contains a date column. The text file will beimported to database in SQL 2000 server. After to be imported, I wantto convert the date column to date type.For ex. the text file look likeName dateSmith 20003112Jennifer 19991506It would be converted date column to ydm database in SQL 2000 server.In the table it should look like thisName DateSmith 2000.31.12Jennifer 1999.15.06Thanks in advance- Loi -
View 1 Replies
View Related
Nov 5, 2007
Hi,
I am trying to access a date column up to millisecond precession. So I cast date to as follows:
Code BlockCONVERT(varchar(23),CREATE_DATE,121)
I get millisecond part as a result of query but it€™s €œ000€?.
When I try to test the format by using getDate instead of DateTime column I get right milliseconds.
CONVERT(varchar(23),GetDate(),121) --Gives right milliseconds in return
View 4 Replies
View Related
Apr 11, 2008
Hi All i have a column in the table its for only Year and the datatype is of Datetime and it is used for some computations so i have not used Varchar or numbers so can any one help me out how only capture year in the field...So whenever i use Year it gives error "String was not recognized as Valid DateTime" please any one help me out
View 2 Replies
View Related
Jan 19, 2006
I'm going slowly insane trying to get a Date AND Time value stored in SQL 2000 from ASP.NET 2.0.
The following works fine in Query Analyzer, but fails when run from ASP.NET:INSERT INTO tbl_WIP_DATA_DateTime (ObjectInstanceID, ObjectPropertyID, PropertyData_DateTime) VALUES (32226,7,'20060120 10:43:44')
Does anyone have any suggestions?
CheersVatic
View 4 Replies
View Related
Jul 20, 2005
Whats the format to send to a datetime field to distinguish it as B.C. vs A.D.-Jim
View 2 Replies
View Related
Sep 11, 2007
hi,
How do i convert a varchar field into the datetime data type? the reason i need this lies in the requirement that in the earlier data base the column that is hlding the date value is having the data type as varchar. and in the new design the column data type is datetime. i am using sql scripts for the data migration from the older design to the newer and got stuck with this datetime convertion issue. do let me know the best possible solution.
following are the sample data that is theer in the older table for the date.
12/12/2003
1/13/2007
01132004
1-1-2004
1.2.2001
there is no uniformity of the data that is stored currently.
thnkx in adv.
rahul jha
View 11 Replies
View Related
May 21, 2015
I have a table that has a unique ID and a datetime of when something changed.
See example:
IDÂ Â Â TimeStamp
16094Â Â Â 2013-11-25 11:46:38.357
16095Â Â Â 2013-11-25 11:46:38.430
16096Â Â Â 2013-11-25 11:46:38.713
16097Â Â Â 2013-11-25 11:46:38.717
16098Â Â Â 2013-11-25 11:46:38.780
[Code] ....
Is there a way I can calculate the difference between row 16106 and 16105 and enter it in line 10601.
View 10 Replies
View Related
Mar 31, 2008
Hello everyone,
In my project, I have came across a field in which, I have to insert only time in my sql server 2005 version database through ASP.NET using C#. I dont want the date to be entered/displayed. I also would like to know, how I can be able to get time from the user(which control you would suggest me to use for getting time) and store it in sql server. I have to count the no. of hrs and minutes in my project, so need this help from anyone who can suggest me. Thank you.
warm regards,
Yakhub.
View 3 Replies
View Related
Jan 13, 2004
Hi,
I have very little C# experience and I am in over my head here. This script will insert all data into the data base after I have removed all refference to the date.
I know I have to change the datatype in code, but I have tried everything I can think of.
Any suggestion would be greatly appreciated.
The .aspx.cs file is below:
using System;
using System.Configuration;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.IO;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace News
{
public class AddNews : System.Web.UI.Page
{
protected System.Web.UI.HtmlControls.HtmlInputText txt_news_title;
protected System.Web.UI.WebControls.TextBox txt_news_body;
protected System.Web.UI.HtmlControls.HtmlInputFile txt_news_image;
protected System.Web.UI.WebControls.Button UploadBtn;
protected System.Web.UI.WebControls.Button ResetBtn;
protected System.Web.UI.WebControls.RequiredFieldValidator rfv_news_image;
protected System.Web.UI.WebControls.Label lbl_news_result;
public AddNews() { }
private void Page_Load(object sender, System.EventArgs e)
{}
public void UploadBtn_Click(object sender, System.EventArgs e)
{
if (Page.IsValid) //save the image
{
//news_title
string newsTitle = txt_news_title.Value;
//news_body
string newsBody = txt_news_body.Text;
//news_date
DateTime dtCurrTime = DateTime.Today;
string d = dtCurrTime.ToString();
//news_img & news_imgcontenttype
Stream imgStream = txt_news_image.PostedFile.InputStream;
int imgLen = txt_news_image.PostedFile.ContentLength;
string imgContentType = txt_news_image.PostedFile.ContentType;
byte[] imgBinaryData = new byte[imgLen];
int n = imgStream.Read(imgBinaryData,0,imgLen);
int RowsAffected = SaveToDB( newsTitle,newsBody,d,imgBinaryData,imgContentType);
if ( RowsAffected>0 )
{
//Response.Write("<BR>The Image was saved");
lbl_news_result.Text = "Record was added successfully!";
txt_news_title.Value = "";
txt_news_body.Text = "";
}
else
{
//Response.Write("<BR>An error occurred uploading the image");
lbl_news_result.Text = "Record was NOT added!";
}
}
}
public void Reset_Click(object sender, System.EventArgs e)
{
txt_news_title.Value = "";
txt_news_body.Text = "";
lbl_news_result.Text = "";
}
private int SaveToDB(string newsTitle, string newsBody, string d, byte[] imgbin, string imgcontenttype)
{
//use the web.config to store the connection string
SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
SqlCommand command = new SqlCommand( "INSERT INTO Cover (cover_title,cover_body,cover_date,cover_image,cover_imgcontenttype) VALUES ( @news_title,@news_body,@news_date,@img_data,@img_contenttype )", connection );
SqlParameter param0 = new SqlParameter( "@news_title", SqlDbType.VarChar,50 );
param0.Value = newsTitle;
command.Parameters.Add( param0 );
SqlParameter param1 = new SqlParameter( "@news_body", SqlDbType.VarChar,5000 );
param1.Value = newsBody;
command.Parameters.Add( param1 );
SqlParameter param2 = new SqlParameter( "@news_date", SqlDbType.VarChar,8 );
param2.Value = d;
command.Parameters.Add( param2 );
SqlParameter param3 = new SqlParameter( "@img_data", SqlDbType.Image );
param3.Value = imgbin;
command.Parameters.Add( param3 );
SqlParameter param4 = new SqlParameter( "@img_contenttype", SqlDbType.VarChar,50 );
param4.Value = imgcontenttype;
command.Parameters.Add( param4 );
connection.Open();
int numRowsAffected = command.ExecuteNonQuery();
connection.Close();
return numRowsAffected;
}
}
}
Thanks!
View 5 Replies
View Related
