I am programming using .net as front end and SQL server as back end and have the following error.

Question

0.00/5 (No votes)

See more:

, +

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 2:08am

shoaib_ferrari

Add a Solution

5 solutions

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[^]

Posted 23-Jan-13 2:26am

Mike Meinz

Updated 23-Jan-13 2:30am

v2

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

C#

DateTime dtlogindatetime = TextBox1.Text;

or

C#

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:

SQL

SqlDataAdapter da = new SqlDataAdapter("select UserName, LoginTime, LogoutTime, Upload UploadFile, Download DownloadFile from logindata where LoginTime = TextBox1.Text + "'", con);

Hope that helps.

Posted 23-Jan-13 2:42am

M.Edmison

Comments

shoaib_ferrari 23-Jan-13 9:01am

I have declared LoginTime and LogoutTime under LoginData as Varchar(30)...

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

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)

Posted 23-Jan-13 7:44am

Mike Meinz

Solution 2

C#

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

Posted 23-Jan-13 2:31am

Subrata.In

Updated 23-Jan-13 4:19am

v2

Comments

shoaib_ferrari 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.In 23-Jan-13 10:21am

Sorry for my mistake there will be and before year. I modified the solution.

shoaib_ferrari 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.In 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 23-Jan-13 10:44am

Varchar(30)

Subrata.In 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 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.In 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 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.In 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 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 23-Jan-13 13:34pm

I cleared the database but then too the same error occurs...

Subrata.In 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 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.In 23-Jan-13 13:58pm

Check system date using:

Select GetDate()

shoaib_ferrari 23-Jan-13 14:03pm

Its giving the current system date acurately

Mike Meinz 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 23-Jan-13 14:02pm

Error: An expression of non-boolean type specified in a context where a condition is expected, near ')'.

Mike Meinz 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 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 23-Jan-13 14:10pm

Sorry, I meant logintime.

shoaib_ferrari 23-Jan-13 14:13pm

Mike after executing the statement it gives all the values that are present on the table

Mike Meinz 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 23-Jan-13 14:17pm

Mike So what can be done to overcome this error now??

Mike Meinz 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 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.In 23-Jan-13 14:20pm

Post the result of Select * from logindata, if possble post your insert query.

shoaib_ferrari 23-Jan-13 14:24pm

I have posted it above

Subrata.In 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 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 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 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 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 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 23-Jan-13 14:49pm

Incorrect syntax near '23-JAN-2013'.

Subrata.In 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 23-Jan-13 14:53pm

Insert statement is working Mr. Subrata

Subrata.In 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 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.In 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 23-Jan-13 15:14pm

Hurray...Its working now....Awesome....Thanks a ton...U r a rockstar Mr.Subrata...Made my day...:)

shoaib_ferrari 23-Jan-13 15:17pm

Dada..U r the best...

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

shoaib_ferrari · Accepted Answer · 2013-01-23T09:16:00

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

Posted 23-Jan-13 9:16am

shoaib_ferrari