Problem updating DB

[ChrisHunter]

Hi i keep getting an exception when i'm trying to update table values within a database. the exception is below but i dont change from datetime to varchar it always stay as datetime.

is there any other reasons why this is happening ?

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

objtyp.dtForm  = Convert.ToDateTime(reader["dtFrom"]);

public DateTime dtForm
{
   get;
   set;
}

string SQL = "UPDATE TABLE_NAME SET dtFrom = '" + objtyp.dtForm + "' WHERE ObjectTypeID = 1";

Thanks

Chris

[Mitja Bonca]

You can see from the exception, that your conversation is not ok.
Try to do:

DateTime dateDate = Convert.ToDateTime(reader["dtFrom"]); //column "dtFrom" has to be type of DateTime!

but as it seems from your excetion, your column "dtFrom" is a type of varchar (so string, and no DateTime).
So you can do:

string strDate = (string)reader["dtFrom"];
//if you want some further "date" changing (from whole date-time value to only date for example) you can do:
DateTime dateDate = Convert.ToDateTime(strDate);
strDate = String.Format("{0:MM.dd.yyyy}", dateDate);
//or:
strDate = dateDate.ToShortDateString();

The point is that you have wrong type of columns in the dataBase. If you have dates, times, the type has to be a "DateTime", and notihng else (its very desired). And if we skip this issue, and go back to yours here, conversations arent good enought, and mostly becuase of wrong column types.

Hope this explains your problem - but my code above shoud salve them all anyway - regarldess on wring column types.

Mitja

[ddanbe]

Or you could try

DateTime dt = DateTime.Parse((reader["dtFrom"]).ToString());

[ChrisHunter]

The Value of dtForm of the column is definatly DateTime. For some reason the column values are formatted like so '000-00-00 00:00:00.000', but will only accept '000-00-00' when updating the column values.

I don't know what the issue is, if it was a format issue it wouldn't allow such a value to be stored in the first place would it ?

I'm okay just storing '000-00-00' for now but would like to know the solution if possible.

Thank you for your help so far.