1

Not getting correct date from database on the following lines. The actual date in database is 04/30/2016 09:30:00 PM but I'm getting it as 01/01/0001 12:00:00 AM. Not sure what's going on.

 DateTime fromDb =  sqlReader.GetDateTime(1);
 DateTime toDb = sqlReader.GetDateTime(2);

Here is the full method:

 private bool IsRoomAlreadyTaken(String room, DateTime fromUser, DateTime toUser)
    {
        bool roomAlreadyTaken = false;
        using (SqlConnection sqlConnection = new SqlConnection(connectionString))
        {
            SqlCommand sqlCmd = new SqlCommand("SELECT Id, convert(varchar(30), DateFrom, 131), convert(varchar(30), DateTo, 131) FROM Access_Privilege where RoomId = @RoomId", sqlConnection);
            sqlCmd.Parameters.AddWithValue("@RoomId", room);
            sqlConnection.Open();
            SqlDataReader sqlReader = sqlCmd.ExecuteReader();
            while (sqlReader.Read())
            {
                DateTime fromDb =  sqlReader.GetDateTime(1); //On this line
                DateTime toDb = sqlReader.GetDateTime(2); //On this line

                if (DateTime.Compare(fromUser, fromDb) == 0 && TimeSpan.Compare(fromUser.TimeOfDay, fromDb.TimeOfDay) > 0 && TimeSpan.Compare(toUser.TimeOfDay, toDb.TimeOfDay) <0)
                {
                    roomAlreadyTaken = true;
                }
            }
            sqlReader.Close();
        }
        return roomAlreadyTaken;
    }

Edit after trying the following suggestions from @ManOVision:

From the docs: "No conversions are performed; therefore, the data retrieved must already be a DateTime object." I've had trouble in the past with SqlDataReader.Get[type] before. Try switching it to Convert.ToDateTime(sqlReader.GetValue(1).ToString()) or at least check what sqlReader.GetValue(1) returns from the database.

Results, with the following lines altered:

MessageBox.Show("sqlReader.GetValue(1)-->" + sqlReader.GetValue(1));
MessageBox.Show("sqlReader.GetValue(2)-->" + sqlReader.GetValue(2));
DateTime fromDb =  Convert.ToDateTime(sqlReader.GetValue(1).ToString());
DateTime toDb = Convert.ToDateTime(sqlReader.GetDateTime(2).ToString());

sqlReader.GetValue(1) returns 23/07/1437 9:30:12:000 PM and sqlReader.GetValue(2) returns 23/07/1437 10:30:12:483 PM. With GetValue(), time seems to be have returned correctly but somehow date is still messed up. This experiment proves that the record that is being returned from the DB is the correct one. But date value is still being lost in conversion.

On further execution of the program, Convert.ToDateTime(sqlReader.GetValue(1).ToString()) throws the following error:

String was not recognized as a valid DateTime.



at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
   at System.Convert.ToDateTime(String value)
   at SGFinalProjectRoomAllocationSystem.ManageAccessForm.IsRoomAlreadyTaken(String room, DateTime fromUser, DateTime toUser) in c:\Projects\SGFinalProjectRoomAllocationSystem\SGFinalProjectRoomAllocationSystem\ManageAccessForm.cs:line 222
   at SGFinalProjectRoomAllocationSystem.ManageAccessForm.ValidatePrivilegs(String emp, String room, DateTime from, DateTime to) in c:\Projects\SGFinalProjectRoomAllocationSystem\SGFinalProjectRoomAllocationSystem\ManageAccessForm.cs:line 246
   at SGFinalProjectRoomAllocationSystem.ManageAccessForm.grntAccssBtn_Click(Object sender, EventArgs e) in c:\Projects\SGFinalProjectRoomAllocationSystem\SGFinalProjectRoomAllocationSystem\ManageAccessForm.cs:line 283
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.Run(Form mainForm)
   at SGFinalProjectRoomAllocationSystem.Program.Main() in c:\Projects\SGFinalProjectRoomAllocationSystem\SGFinalProjectRoomAllocationSystem\Program.cs:line 19
Saurabh Gupta
  • 81
  • 10
  • Are there multiple results in the table that would match your query? You are using a while loop so what ever row is last will set your roomAlreadyTaken variable. – ManOVision May 01 '16 at 04:20
  • 1
    The 1/1/0001 date will occur if the database value is an empty string since you are converting to a varchar and then letting C# convert that back to a DateTime. My guess is the entry it is reading is null or an empty string. – ManOVision May 01 '16 at 04:23
  • 4
    Remove the `convert` from the Sql. no point of converting to string and then back to datetime. Sql server's Datetime maps directly to .Net datetime. – Zohar Peled May 01 '16 at 04:47
  • @ManOVision Yes there are multiple results that will match my query. Typically there will be only one row that will match the criteria if the room is already taken in that time. – Saurabh Gupta May 01 '16 at 13:25
  • @ManOVision, no I checked it. The value is not null or empty. There are rows present in the database with the correct date. None of the rows have null or empty date, so not sure why it's happening. Yes you are right though. The problem seems to be of null or empty date, but either some of the APIs/converters that I have used are making it null or it's not able to read the correct date. – Saurabh Gupta May 01 '16 at 13:28
  • @ZoharPeled, I had it without convert and was getting the same problem, so thought may be that will fix it. But it did not. I'll take it out. – Saurabh Gupta May 01 '16 at 13:29
  • @ManOVision Please check the updates on the main post. I have updated results from your suggestions. Still no luck. – Saurabh Gupta May 01 '16 at 14:31
  • @ManOVision I can't tag you on the other comment on this post. So posting here as well so you get the notification. Educate me if there is a better way of doing this. – Saurabh Gupta May 01 '16 at 14:42
  • 1
    It looks like your date range check is incomplete - you don't check against the end date. Besides which, it'll return wrong results anyways - `Compare` is going to include the time portion, with obvious results. Thankfully, [the proper check is very simple](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap). Also, no need to iterate through all rows (if multiple exist) - `break` out early (you unfortunately can't just `return`, given the need to close the reader). – Clockwork-Muse May 01 '16 at 16:24
  • @Clockwork-Muse not sure if I really understand that post completely. Also, not sure why you would not check against enddate. I definitely have problems in not including "=" in the datecheck. Yes I'm trying to figure out how to exclude "second" portion of the time. Need to exclude that. Yes breaking is a good idea. Generally there can never be two records but I should still break it, just in case. – Saurabh Gupta May 01 '16 at 16:38
  • What I'm getting at is, you only check the _time_ portion of the end date - if the actual 'date' portion is different you'll get (probably) wrong results. The check you should be doing is `fromUser < toDb && toUser > fromDb` (you want the exclusive version when dealing with full-precision timestamps, because then it allows you to have reservations starting at the same time one ends, rather than having to mess with bumping it by a second or whatever). I'm surprised you only have one row in that table, since it would mean you could only have one reservation at a time. – Clockwork-Muse May 02 '16 at 00:00

3 Answers3

1

Try DateTime.Parse method. MSDN

DateTime fromDb = DateTime.Parse(sqlReader[1].ToString()); DateTime toDb = DateTime.Parse(sqlReader[2].ToString());

Or Try DateTime.TryParse method. MSDN

DateTime fromDb;

if(DateTime.TryParse(sqlReader[1].ToString(), out fromDb)) //Conversion Successful. fromDb is set else //Conversion Unsuccessful

katu
  • 367
  • 1
  • 4
  • 14
  • This change also gives the exact same results as MapOVision's suggestions from the top. Date still messed up. Correct time. And Format Exception. Please refer at the post above to know about these results. Appreciate your help! – Saurabh Gupta May 01 '16 at 14:48
  • 1
    I edited my answer and added a second method. Try it. It won't give an error. Can you post the output of `sqlReader[1].ToString()`. Even if your DB column is not of type `datetime`, a string of format 04/30/2016 09:30:00 PM should be parsed to `DateTime`. – katu May 01 '16 at 16:32
  • Thanks for your help. I already found a solution and posted as answer. Please check. – Saurabh Gupta May 01 '16 at 16:33
  • 1
    Didn't see you were using conversion on DB side. My bad :). Always try your queries on Management Studio before adding to code. It saves lot of problems. Happy you solved it. – katu May 01 '16 at 16:38
  • Yep I agree. Thanks! :) – Saurabh Gupta May 01 '16 at 16:52
1

Convert methods were causing the problem. Removing them from the query solved it.

Code with the problem:

SqlCommand sqlCmd = new SqlCommand("SELECT Id, convert(varchar(30), DateFrom, 131), convert(varchar(30), DateTo, 131) FROM Access_Privilege where RoomId = @RoomId", sqlConnection);

Working code.

SqlCommand sqlCmd = new SqlCommand("SELECT Id, DateFrom, DateTo FROM Access_Privilege where RoomId = @RoomId", sqlConnection);
Saurabh Gupta
  • 81
  • 10
0

From the docs: "No conversions are performed; therefore, the data retrieved must already be a DateTime object."

I've had trouble in the past with SqlDataReader.Get[type] before. Try switching it to Convert.ToDateTime(sqlReader.GetValue(1).ToString()) or at least check what sqlReader.GetValue(1) returns from the database.

ManOVision
  • 1,853
  • 1
  • 12
  • 14
  • I have updated the main post with the results from your suggestions. Please check. @ManOVision – Saurabh Gupta May 01 '16 at 14:29
  • 1
    Now that you are sure how the data is returned from the database you can use the correct convert method. The culture info will need to be passed in. In a MM/dd/yyyy format (as it appears from your updated question) the conversion would fail. – ManOVision May 01 '16 at 14:36
  • Not sure how you pass the culture info. Could you advice? – Saurabh Gupta May 01 '16 at 14:40