out-of-range datetime value

[NinaWilliam]

Hi there

I wrote a web application and it was working fine with no errors in my local server and the live server. My system lost its data and the hard drive got corrupted. So my hard drive was replaced with a new one. When I configure the web application on my new hard drive and run it, it was working ok except for the part that is has date selection on it I get the following error

Exception Type: System.Data.SqlClient.SqlException
Exception Message: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Exception Source: .Net SqlClient Data Provider
Exception Target Site: Read

I didn’t change the code or anything; the same version is working on the live server with no issues. I don’t know why it is giving me this error in my machine.

Any ideas??

[HostingASPNet]

Hello,

You should give us an example of you code.
The problem could be your local regional settings if you have changed them after new installation.

Regards

[NinaWilliam]

The problem is that the front end is sending the date to the backend in this format mm/dd/yyyy. and the date format of the backend is dd/mm/yyyy...

my regional date format is dd/mm/yyyy and the live server has the same formatting... why it is working fine in the live server and showing an error in my server??

also, I try to pass the date in the front end as dd/mm/yyyy, but it still pass it as mm/dd/yyyy



Dim ddate As Date
Dim SelDate As Date
Dim date1 As Date

ddate = Format(Now(), "MMM/dd/yyyy")
SelDate = Format(Me.Calendar1.SelectedDate, "MMM/dd/yyyy")

If SelDate = Date.MinValue Then

date1 = ddate

Else

date1 = SelDate

End If

'Declaring sql connection and sql command
Dim SqlComAppClerk As New SqlClient.SqlCommand("select AppointDate from Appointments where Appointments.AppointDate = CONVERT(DATETIME, '" & date1 & "', 102)", SqlConnAppClerk)

Dim SqlDA As New SqlDataAdapter(SqlComAppClerk)
Dim ds As New DataSet


'Open sql connection if it is closed
If SqlConnAppClerk.State <> ConnectionState.Open Then

SqlConnAppClerk.Open()

End If

SqlDA.Fill(ds) ''<<<< here the error Occurs

[scottk]

You should use parameterized queries and you would not have this issue. It will help with security and performance.

public static string BuildSqlNativeConnStr(string server, string database)
{
return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
}
private void simpleButton1_Click(object sender, EventArgs e)
{
const string query = "Insert Into Employees (RepNumber, HireDate) Values (@RepNumber, @HireDate)";

string connStr = BuildSqlNativeConnStr("apex2006sql", "Leather");
DataTable dt;
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.Add(new SqlParameter("@RepNumber", 50));
cmd.Parameters.Add(new SqlParameter("@HireDate", DateTime.Today));
SqlDataReader dr = cmd.ExecuteReader();
dt = new DataTable();
dt.Load(dr);
}
}
System.Diagnostics.Debugger.Break(); //At this point you have the populated datatable
}

[scottk]

One more handy thing to check that I forgot to mention:

public static bool SqlTypeInRange(DateTime value)
{
return ((value <= System.Data.SqlTypes.SqlDateTime.MaxValue.Value) && (value >= System.Data.SqlTypes.SqlDateTime.MinValue.Value));
}

[NinaWilliam]

Thank you scott, but I am sorry to till you that the code didn't solve the problem...
do you have any other ideas?

[NinaWilliam]

Hi guys ^_______^

I was able to fix the problem by replacing 102 to 103 in the following code line...

SELECT Appointments.AppointDate WHERE (Appointments.AppointDate = CONVERT(DATETIME, '" & date1 & "', 103))

and it works fine now ^___^


Happy Programming :) :) :)