The conversion of a char data type to a datetime data type resulted in an out-of-rang

[etti]

I really need help with the below error.
I dont get this when i run my asp.net c# application in VS@005,
but when i make virtual directory on my office Windows server I
get this error.
Tell me what changes need to be done in C# code or crystal report or Sql queries or
server settings !!below is the error :

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Description: An unhandled exception occurred during the execution of the current web request.
Exception Details: System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The scenario is there is a VB 6.0 exe which runs on the startup of machine and using a simple hard coded sql query collects date time of the instance and puts in database.
Later for checking out the entries asp.net c# application is being made where using crystal report
we are displaying the entries.
Specifically there is a report which calculates user's Leaves:
Below is the simple function written in c# for calculating leave to show in report :

public void totalleavetaken()
    { 
//        select count(status) from user_info 
//where status = 'leave' and user_name = 'vishal'
//and user_date < '04/30/2008'

        string username;
        DateTime enddate;
        DateTime startdate;

        username = ddluser.SelectedValue;
        enddate = CalendarPopup2.SelectedDate;
        startdate = CalendarPopup1.SelectedDate;

        sqlconn = new SqlConnection(creativeconfiguration.DbConnectionString);
        string str = "select count(status) from user_info where status = 'Leave' and user_name ='" + username + "' and user_date between '" + startdate + "' and '" + enddate + "'";
        cmd = new SqlCommand(str, sqlconn);
        sqlconn.Open();
        dr =  cmd.ExecuteReader();
        if (dr.Read()) -----------------------> The error coming on this line
         {
            lbltotalleave.Text = Convert.ToString(dr[0]);
        }
        sqlconn.Close();
    
    }

[greeny_1984]

hi,

string str = "select count(status) from user_info where status = 'Leave' and user_name ='" + username + "' and user_date between '" + startdate + "' and '" + enddate + "'";
change to

string str = "select count(status) from user_info where status = 'Leave' and user_name ='" + username + "' and user_date between '" + startdate.ToShortDateString() + "' and '" + enddate.ToShortDateString() + "'";

[etti]

string str = "select count(status) from user_info where status = 'Leave' and user_name ='" + username + "' and user_date between '" + startdate.ToShortDateString() + "' and '" + enddate.ToShortDateString() + "'";

The above solution gets the injection attack.
Finally i found the answer which is explained below:
The previous query was getting injection ::

string str = "select count(status) from user_info where status = 'Leave' and user_name ='" 
  + username + "' and user_date between '" + startdate + "' and '" + enddate + "'";

I suggest that you change it to use stored procedures or parameterised TSQL

string str = "select count(status) from user_info where status = 'Leave' and user_name = @USER and user_date between @startdate and @enddate'"; 

cmd.Parameters.Add("@USER", SqlDbType.NVarChar, 100);  -- Assuming type and size
cmd.Parameters["@USER"].Value = username
cmd.Parameters.Add("@startdate ", SqlDbType.DateTime);
cmd.Parameters["@startdate "].Value = startdate ;
cmd.Parameters.Add("@enddate", SqlDbType.DateTime);
cmd.Parameters["@enddate"].Value = enddate;

This works perfectly :)