[RESOLVED] time format

[sunshine123]

Hi,


I have a datetime picker from which I'm allowing the user to select a time. I've formatted it as hh:mm tt - 09:30 AM using properties. My problem is that while trying to save it into the database it is trying to pass the complete datetime instead of the formatted version, i realise its becos I have formatted only how it is displayed. In the table, I have formatted this time as Medium Time. So this is where the problem occurs.

Could someone tell me how i could format the time as 09.30 AM. I tried something like this...it did not work. I dont want to convert it to a string I want to keep it in datetime format itself as I'm passing it as a parameter to another class method using datetime type.

dtpFrom.Value.TimeOfDay

thanks so much in advance

[jmcilhinney]

Just like the DateTimePicker, the "format" that you specify in the database is for display purposes only. It has no effect on the way the value is stored in the database whatsoever. Whether in a database or in a .NET application a binary date object is a binary date object and the format with which you display its value doesn't change that. You should simply save the Value property of your DateTimePicker to the database field as is. When you look at the value in the database you'll only see the time portion in Medium Time format but the date part will still be in there. When you retrieve the data you'll be getting DateTime objects in the fiedls of your DataTable or DataReader. If you don't want or need the date portion then simply ignore it. You'll already be doing that for display purposes using the CustomFormat property of the DateTimePicker. If you need to perfrom any calculations just get the TimeOfDay property and use that.

[sunshine123]

Thankyou...but I'm still facing a problem...

in my form under button click, I'm passing three values. dtpFrom has a time value in format 09.30 AM

newEnt.AddEntry(txtEvent.Text, dtpDate.Value, dtpFrom.Value.TimeOfDay)

my addEntry method look something like this

public void AddEntry(string evt, DateTime dat, DateTime frm)
        {
            string sSQL = "INSERT INTO Events(Event, DateSc, From) VALUES ('" + evt + "', '" + dat + "', '" + frm + "')";//, '" + to + "')";
            ExecuteSQL(sSQL);
        }

I get the following error for the third argurment that is passed. Coud you please help me identify what the problem is. I simly tried with the first two arguements, txtEvent.Text, dtpDate.Value it works fine.

The best overloaded method match for 'tes.NewEntry.AddEntry(string, System.DateTime, System.DateTime)' has some invalid arguments

Argument '3': cannot convert from 'System.TimeSpan' to 'System.DateTime'

[jmcilhinney]

The Value property of a DateTimePicker returns a DateTime value. Your AddEntry method is expecting a DateTime value so you can pass that property.

The TimeOfDay property of a DateTime returns a TimeSpan value. That is NOT the same thing as a DateTime so it cannot be used where a DateTime is expected. As I said in my previous post:

You should simply save the Value property of your DateTimePicker to the database field as is.

Just save the dtpFrom.Value property into the database. I assume that you're using Access, so when you open the database you'll see that date/time value displayed in the Medium Time format, just as you expect. When you later retrieve the data from the database you'll get a DateTime object with the same value as the one you saved. When you assign that to the Value property of the dtpFrom you'll again see only the time in the appropriate format. You simply ignore the date portion. The ONLY situation where you might need to use the TimeOfDay property is if you wanted to compare two times. If you have two DateTime objects you can compare the times and ignore the dates like so:

if (TimeSpan.Equals(dt1.TimeOfDay, dt2.TimeOfDay))

That will return true if the time portions are the same, even if the date portions are different.

[sunshine123]

Ok I tried as you said but I get the following error. Please help me to sort this out.

System.Data.OleDb.OleDbException was unhandled
Message="Syntax error in INSERT INTO statement."
Source="Microsoft JET Database Engine"
ErrorCode=-2147217900

I've shown my insert statement below

public void AddEntry(string evt, DateTime dat, DateTime frm)
        {
            string sSQL = "INSERT INTO Events(Event, DateSc, From) VALUES ('" + evt + "', '" + dat + "', '" + frm + "')";
            
            ExecuteSQL(sSQL);
        }

the following statment is highlighted in my connection class

 cmd.ExecuteNonQuery();

[jmcilhinney]

What exactly did your sSQL string look like after you created it? Was it valid SQL code. I can guarantee you that it wasn't. Don't use string concatenation to build SQL statements. It is insecure and error-prone. I just wish every teacher, book and Web site would show the proper way to do it in the first place then there'd be several million fewer forum posts asking why it didn't work. ALWAYS use parameters to build an SQL statement, e.g.

OleDbCommand cmd = new OleDbCommand("INSERT INTO Events (Event, DateSc, From) VALUES (@Event, @DateSc, @From)", con);
 
cmd.Parameters.AddWithValue("@Event", evt);
cmd.Parameters.AddWithValue("@DateSc", dat);
cmd.Parameters.AddWithValue("@From", frm);

[sunshine123]

Hi,

Thank you for your valuable input. Such forums are honestly life savers. Its good to know that there are so so many good teachers out there who are so willing to share their knowledge with strangers.


I will take note of your comments about the sql statements hereafter. The problem was that I was using a colum name 'From', after trying it in Access only I realised this problem. Thanks again. It was this problem that led me to think that there was a problem in the way I was passing time parameter!! as in the previous posts.

[jmcilhinney]

Bummer! I should have noticed that.