Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
i have the following error in the code. please help me solve it
 
Server Error in '/Project' Application.
--------------------------------------------------------------------------------
 
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
 
Exception Details: System.Data.SqlClient.SqlException: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
 
Source Error:
 

Line 35:
Line 36: DataSet ds = new DataSet();
Line 37: da.Fill(ds);
Line 38: GridView1.DataSource = ds;
Line 39: GridView1.DataBind();
 
Source File: c:\Users\Shoaib\Desktop\Project\Admin.aspx.cs Line: 37
 
And the code is as given below:
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
 
public partial class Admin : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        GridView1.Visible = true;
    }
    string connStr = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;
 
    protected void Calendar1_SelectionChanged(object sender, EventArgs e)
    {
        TextBox1.Text = Calendar1.SelectedDate.ToShortDateString();
        Calendar1.Visible = false;
    }
    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        Calendar1.Visible = true;
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
 
        SqlConnection con = new SqlConnection(connStr);
        SqlDataAdapter da = new SqlDataAdapter("select UserName, LoginTime, LogoutTime, Upload UploadFile, Download DownloadFile  from logindata where ltrim(rtrim(str(month(logintime))))+ '/' + ltrim(rtrim(str(day(logintime)))) + '/' + ltrim(rtrim(str(year(logintime)))) ='" + TextBox1.Text + "'", con);
        //+   "+ Convert.ToDateTime;.Convert.ToDateTime (TextBox1.Text), con);
        //Where Logintime='"+TextBox1.Text+"'", con);

        DataSet ds = new DataSet();
        da.Fill(ds);
        GridView1.DataSource = ds;
        GridView1.DataBind();
        GridView1.Visible = true;
    }
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
 
        GridView1.PageIndex = e.NewPageIndex;
        GridView1.DataBind();
        GridView1.Visible = true;
 
    }
    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {
 
    }
}
Posted 23-Jan-13 3:08am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I suggest changing your to something like the following to use the DATEDIFF SQL function. If the difference between the two days is zero, they are the same date.
 
... from logindata where DATEDIFF(day,logintime,CAST('" + textbox1.text + "' As SmallDateTime)=0;",con);
 
If you haven't done so already, you need to add some code to validate that textbox1.text contains a valid date. I usually use CDATE() within a Try Catch block to see if a date is a valid date.
 
That said, I strongly suggest that you change your SQL Select statement to a parameterized query instead of using a concatenated string. Using a concatenated string, as you did, leaves you program vulnerable to an SQL Injection attack. Also, there is a performance benefit if you use parameterized queries. See this article Use SQL Parameters to Overcome Ad Hoc Performance Issues[^]
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

I think it may be due to how your trying to cut up the logintime as a string when that isn't really needed as long as it is a true DateTime() in the database.
 
Instead get your TextBox1.Text as a DateTime value then compare the too.
 
So you would need to convert the TextBox1.Text into a DateTime
 
DateTime dtlogindatetime = TextBox1.Text;
or
DateTime dtlogindatetime;
if (Datetime.Tryparse(TextBox1.Text, out dtlogindatetime)
    //success able to run sql code
else
    //give message that datetime not entered

Then you can update the sql command as follows:
 
SqlDataAdapter da = new SqlDataAdapter("select UserName, LoginTime, LogoutTime, Upload UploadFile, Download DownloadFile from logindata where LoginTime = TextBox1.Text + "'", con);
 

Hope that helps.
  Permalink  
Comments
shoaib_ferrari at 23-Jan-13 9:01am
   
I have declared LoginTime and LogoutTime under LoginData as Varchar(30)...
wolfcoder75 at 30-Jan-13 9:24am
   
I see you have that corrected, but using a DateTime field type might be something to loog into as you can break your dates and times up a little easier. Just a thought.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

I added ISNULL to check for a NULL value logintime in the database.
 
select [UserName], [LoginTime], [LogoutTime], [Upload] as 'UploadFile', [Download] as 'DownloadFile' from logindata where DateDiff(d, Cast(ISNULL([logintime],'01-JAN-1901') as datetime),'" + YourDate.ToString("dd-MMM-yyyy") + "')=0
'
'
' Use this query to find any rows with an invalid "logindate" date. If any rows with invalid "logindate" exist, that is the cause of the error.
select [UserName], [LoginTime], [LogoutTime], [Upload] as 'UploadFile', [Download] as 'DownloadFile',logindate from logindata where Not IsDate(logintime)
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 7

Thanks to Mr. Subrata and Mr. Mike for their precious time and helping me out..I was finally able to solve the error ater 8Hrs with the help of Mr. Subrata guiding me to change the date format in the database...
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

//befor convert you must verify the input or use try catch block
datetime YourDate=Convert.ToDate(TextBox1.Text)
 
System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter("select UserName, LoginTime, LogoutTime, Upload UploadFile, Download DownloadFile  from logindata where month(logintime)=" + YourDate.Month + " And day(logintime)=" + YourDate.Day + " and year(logintime)=" + YourDate.Year, con);
 
  Permalink  
v2
Comments
shoaib_ferrari at 23-Jan-13 8:59am
   
It says this error now after implementing the changes as suggested by you:
Incorrect syntax near 'year'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
 
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'year'.
 
Source Error:
 

Line 43:
Line 44: DataSet ds = new DataSet();
Line 45: da.Fill(ds);
Line 46: GridView1.DataSource = ds;
Line 47: GridView1.DataBind();
_Subrata_ at 23-Jan-13 10:21am
   
Sorry for my mistake there will be and before year. I modified the solution.
shoaib_ferrari at 23-Jan-13 10:34am
   
Thank you for your help. But even after modifying it gives the same error as before. i.e.
Exception Details: System.Data.SqlClient.SqlException: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
 
_Subrata_ at 23-Jan-13 10:43am
   
What is the data type of logintime? Try to change datatype as datetime. If not possible have to convert data type as datetime using cast(logintime as datetime).
 
Code will be:
SqlDataAdapter da = new SqlDataAdapter("select UserName, LoginTime, LogoutTime, Upload UploadFile, Download DownloadFile from logindata where month(cast(logintime as datetime))=" + YourDate.Month + " And day(cast(logintime as datetime))=" + YourDate.Day + " and year(cast(logintime as datetime))=" + YourDate.Year, con);
shoaib_ferrari at 23-Jan-13 10:44am
   
Varchar(30)
_Subrata_ at 23-Jan-13 11:01am
   
You can try previous query or this:
SqlDataAdapter da = new SqlDataAdapter("select UserName, LoginTime, LogoutTime, Upload UploadFile, Download DownloadFile from logindata where DateDiff(d, Cast(logintime as datetime)," + YourDate.ToString() + ")=0", con);
shoaib_ferrari at 23-Jan-13 11:36am
   
Tried to convert the datatype of database but couldnt do it as It wud drop the entire table if i did that change..
Also the above code results in the same error ...no change..:(
 
I appreciate your help Mr. Subrata but its not working yet...Have been trying to solve this from past 8 hrs..
_Subrata_ at 23-Jan-13 12:32pm
   
Still this problem not solved? It is very simple query. However please try this if not solved, try to execute this query to query editor (SQL Server) and check what message is showing.
 
If you change the field type to datetime:
select [UserName], [LoginTime], [LogoutTime], [Upload] as 'UploadFile', [Download] as 'DownloadFile' from logindata where DateDiff(d, [logintime],'" + YourDate.ToString("dd-MMM-yyyy") + "')=0
 

If you unable to change the field type to datetime:
select [UserName], [LoginTime], [LogoutTime], [Upload] as 'UploadFile', [Download] as 'DownloadFile' from logindata where DateDiff(d, Cast([logintime] as datetime),'" + YourDate.ToString("dd-MMM-yyyy") + "')=0
shoaib_ferrari at 23-Jan-13 13:03pm
   
Thanks Mr. Subrata. But still unable to solve the error...
 
As u said I executed the above query and got this error saying "Conversion failed when converting date and/or time from character string."
 
Also not able to change the field type to datetime as SQL server is not allowing to change the data type.
_Subrata_ at 23-Jan-13 13:13pm
   
It will be better to change datatype in database (in datetime by dropping and re-creating table). However, I am sure there is some wrong/junk value in logintime. Use: Select * from table name and check care fully the fields. You can post your insert query, problem may be there (inserting data in wrong format).
Mike Meinz at 23-Jan-13 13:19pm
   
Maybe there is some data in your database in the "logintime" column that is not a valid date. A NULL, spaces, a random word, an invalid date, etc. in "loginintime" in any row of the table will cause this error.
shoaib_ferrari at 23-Jan-13 13:34pm
   
I cleared the database but then too the same error occurs...
_Subrata_ at 23-Jan-13 13:44pm
   
Execute this query on query eitor:
select [UserName], [LoginTime], [LogoutTime], [Upload] as 'UploadFile', [Download] as 'DownloadFile' from logindata where DateDiff(d, Cast([logintime] as datetime),'23-Jan-2013')=0
 
And check what message showing. If possible show the script of that table.
shoaib_ferrari at 23-Jan-13 13:49pm
   
After executing the above query. It gives an error as "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."
_Subrata_ at 23-Jan-13 13:58pm
   
Check system date using:
 
Select GetDate()
shoaib_ferrari at 23-Jan-13 14:03pm
   
Its giving the current system date acurately
Mike Meinz at 23-Jan-13 13:58pm
   
Execute this query in a query window to find any rows with invalid date in logintime column:
select [UserName], [LoginTime], [LogoutTime], [Upload] as UploadFile, [Download] as DownloadFile,logindate from logindata where Not IsDate(logintime)
shoaib_ferrari at 23-Jan-13 14:02pm
   
Error: An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Mike Meinz at 23-Jan-13 14:05pm
   
Sorry, use this
 
select [UserName], [LoginTime], [LogoutTime], [Upload] as 'UploadFile', [Download] as 'DownloadFile',logindate from logindata where ISDATE(logindate)=0
shoaib_ferrari at 23-Jan-13 14:09pm
   
Mike there is no column as logindate
The columns are:
UserName : varchar(30)
LoginTime:varchar(30)
LogoutTime:varchar(30)
LogSession:varchar(30)
Upload:varchar(30)
download:varchar(30)
Mike Meinz at 23-Jan-13 14:10pm
   
Sorry, I meant logintime.
shoaib_ferrari at 23-Jan-13 14:13pm
   
Mike after executing the statement it gives all the values that are present on the table
Mike Meinz at 23-Jan-13 14:15pm
   
Then the values in "logintime" are invalid date formats.
 
ISDATE expression Formats
--------------------------------------------------------------------------------
 
For examples of valid formats for which ISDATE will return 1, see the section "Supported String Literal Formats for datetime" in the datetime and smalldatetime topics. For additional examples, also see the Input/Output column of the "Arguments" section of CAST and CONVERT.
 
The following table summarizes input expression formats that are not valid and that return 0 or an error. Show us some sample values.
 
Following is from the Documentation of IsDate. It shows the cases where IsDate returns a zero. These are cases where it is an invalid date.
 
ISDATE expression
ISDATE return value

NULL
0

Values of data types listed in Data Types in any data type category other than character strings, Unicode character strings, or date and time.
0

Values of text, ntext, or image data types.
0

Any value that has a seconds precision scale greater than 3, (.0000 through .0000000...n)
0

Any value that mixes a valid date with an invalid value, for example 1995-10-1a.
0
shoaib_ferrari at 23-Jan-13 14:17pm
   
Mike So what can be done to overcome this error now??
 
Mike Meinz at 23-Jan-13 14:18pm
   
Without knowing what the values of logintime look like that is impossible to answer. It is clear that the values in logintime are not valid dates. I need to see what they look like to give you further advice.
shoaib_ferrari at 23-Jan-13 14:23pm
   
The table Logindata looks like:
Username LoginTime LogoutTime Logsession upload download
randy 23/01/2013 18:51:41 23/01/2013 325376 s.txt s.txt
femi 23/01/2013 18:56:27 23/01/2013 4287 d.txt d.txt
_Subrata_ at 23-Jan-13 14:20pm
   
Post the result of Select * from logindata, if possble post your insert query.
shoaib_ferrari at 23-Jan-13 14:24pm
   
I have posted it above
_Subrata_ at 23-Jan-13 14:28pm
   
Try to insert data "dd-MMM-yyyy HH:m:s" format. Or post your insert query for the table Logindata.
shoaib_ferrari at 23-Jan-13 14:36pm
   
It works...1 row added
insert into LoginData VALUES ('rock', '23/01/2013 18:51:41','23/01/2013','325376','j.txt','j.txt')
shoaib_ferrari at 23-Jan-13 14:44pm
   
It works when i insert data as "insert into LoginData VALUES ('rocky', '23-JAN-2013 18:51:41','23/01/2013','325376','j.txt','j.txt')"
 
Mike Meinz at 23-Jan-13 14:32pm
   
Your problem is caused by day (23) first date. Using CONVERT with 103 parameter solves this. Try this query.
 
select [UserName], [LoginTime], [LogoutTime], [Upload] as 'UploadFile', [Download] as 'DownloadFile' from logindata where DateDiff(d, CONVERT(DateTime,ISNULL(logintime,'01/01/1901'),103),'" + YourDate.ToString("dd-MMM-yyyy") + "')=0
shoaib_ferrari at 23-Jan-13 14:39pm
   
Mike thanks for your time and help...i really appreciate it
but it still gives an error
"Conversion failed when converting date and/or time from character string."
Mike Meinz at 23-Jan-13 14:42pm
   
Maybe your date that you input is incorrect.
Try This
 

select [UserName], [LoginTime], [LogoutTime], [Upload] as 'UploadFile', [Download] as 'DownloadFile' from logindata where DateDiff(d, CONVERT(DateTime,ISNULL(logintime,'01/01/1901'),103)," + "'23-JAN-2013'" + ")=0
shoaib_ferrari at 23-Jan-13 14:49pm
   
Incorrect syntax near '23-JAN-2013'.
_Subrata_ at 23-Jan-13 14:49pm
   
Insert data "dd-MMM-yyyy HH:m:s" format.
I think you are using: DateTime.Now.ToString()
Use DateTime.Now.ToString("dd-MMM-yyyy HH:m:s")
shoaib_ferrari at 23-Jan-13 14:53pm
   
Insert statement is working Mr. Subrata
_Subrata_ at 23-Jan-13 15:00pm
   
You are inserting "dd-MMM-yyyy HH:m:s" fromat means "23-Jan-2013 1:27:30", so why it is showing 23/01/2013 18:56:27?
In normal in your data is showing correct format, but it is sure there is some wrong may there some unnecessary space or anything.
It is better you change your data type or check there is some mistake on insertions.
shoaib_ferrari at 23-Jan-13 15:04pm
   
As it is of data type varchar..it accepts any input in any format...either 23-Jan-2013 1:27:30 or 23/01/2013 18:56:27 and it also displays in the same format in the table too...
_Subrata_ at 23-Jan-13 15:10pm
   
Yes I know, for this reason I am telling to store "23-Jan-2013" format not "23/01/2013" this format. May sql server treat first two digit as month. I think if you store '01/01/2013' it will not raise any error.
So either store that format or recreate table (and drop previous table: drop table <tablename>) with datetime field.
shoaib_ferrari at 23-Jan-13 15:14pm
   
Hurray...Its working now....Awesome....Thanks a ton...U r a rockstar Mr.Subrata...Made my day...:)
shoaib_ferrari at 23-Jan-13 15:17pm
   
Dada..U r the best...
_Subrata_ at 23-Jan-13 15:25pm
   
You are Welcome.
It's a very simple and also common problem, but it take long time to identify. Always use proper datatype else especially datetime data, store "dd-MMM-yyyy" format to avoid conversion error.
Good night.
Mike Meinz at 23-Jan-13 14:53pm
   
Bad date format in your input.
 
What have you learned?
1. You would not have had this problem if you had declared the LoginTime and LogoutTime columns in your table as follows:
[LoginTime] [datetime] NOT NULL,
[LogoutTime] [datetime] NOT NULL,
 
Any date, time or datetime column should be declared as a "DateTime" data type.
NULL or NOT NULL depends on whether a date is required or optional.
If a date is optional, all SQL statements must take that into consideration.
 
Let the database server help you!
 
2. A good programmer always checks data for validity. Whether the value is in the database or is new input data, there should be validation to check that the data is as expected and if not to alert the user.
shoaib_ferrari at 23-Jan-13 14:59pm
   
Mr. Mike I know that I made an error when i created the database by not selecting the data type of LoginTime and LogoutTime as datetime...But now the sql server is not letting me to change the datatype..So what can i do in this situation?
Mike Meinz at 23-Jan-13 15:06pm
   
Do both of these:
1. Use the CONVERT statement in your Select statement.
2. Use VB FORMAT$ to format your input value to dd-MMM-yyyy format
Mike Meinz at 23-Jan-13 16:27pm
   
If you want to do this right, correct the design of your table.
Create a new table. use INSERT into newtable with SELECT from oldtable to copy the data from the oldtable to the newtable. Rename the oldtable to oldtable_save. Rename the newtable to the oldtable name.
 
I recommend that you get some help from a senior programmer at your company.
shoaib_ferrari at 23-Jan-13 18:13pm
   
Mr. Mike I was able to solve the error by converting the input to dd-MMM-yyyy format..the error has been solve..Thanks for you help and time...By the way I am a Student and this was for my project..Thanks once again
Mike Meinz at 23-Jan-13 18:32pm
   
If your teacher(professor) has appropriate programming skills, you will get a lower grade if you do not have the logintime and logouttime columns declared as DateTime Not Null.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 475
1 Maciej Los 349
2 Richard MacCutchan 220
3 BillWoodruff 219
4 Mathew Soji 160
0 OriginalGriff 8,759
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,639
3 Maciej Los 5,269
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web01 | 2.8.1411028.1 | Last Updated 23 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100