locked
Two Access Questions using Date in a Data grid and formating in a select Statement RRS feed

  • Question

  • User515264000 posted

    Guys, this is a two part question involving ASP.net 3.5, Access DB, the Date function and a select Statement.

    #1.

    I have a Table that shows Date and Time. I dont need the time. How do I format it in the Select Statement using VB.net? I just want to strip the time off the end because I have it already. I am making a dynamic grid that will always have the same columns, just different variables.

    Code Snippet...

    "

    Me.TextBox1.Text = Me.TextBox1.Text & " 12:00:00 AM"

    connection()

    dbcommand = New OleDbCommand("Select Scheduler.Patient_ID, Patient_Info.Last_Name, Patient_Info.First_Name,Patient_Info.Home_Phone, Scheduler.Sched_Date, Scheduler.TECH FROM Scheduler INNER JOIN Patient_Info ON Patient_Info.Patient_ID = Scheduler.Patient_ID where Scheduler.Sched_Date " & Date.Today & " AND " & "12/31/4000" & " ORDER BY [Scheduler.Sched_Date] ASC", dbconn)

    'Above is the main problem around where Scheduler.Sched Date....

    dbconn.Open()

    Me.GridView1.DataSource = dbcommand.ExecuteReader()

    Me.GridView1.DataBind()

    dbconn.Close()

    "

    #2.

    I want to be able to set the day 8 weeks ahead and print out all dates availible in the DB from that day on. Lets say today is the 29 of December- 2008.  I want to be able to start on February 29 -2009 and print out all records after February 29 2009.

    I tried Months_Between, but I dont think ASP.net likes it too much.

    Code Snippet

    "

    Dim W8 As Date = Date.Today.AddDays(56)

    Me.TextBox1.Text = W8"

    I've tried to force the DB to look at the textbox, but text and Date/Time dont mix. Setting the Date Time Field to text wot work either,because it goes in alphabetical order andskips around.

     

    Any help I can get would be awesome. Once this project is 90%done, we'll be converting to SQL, I think that would make things alot easier, but Im stuck with access right now.

    Appreciate it a lot!!!

    Monday, December 29, 2008 2:47 AM

Answers

  • User-821857111 posted

    All from today:

    Select Scheduler.Patient_ID, Patient_Info.Last_Name, Patient_Info.First_Name,Patient_Info.Home_Phone, Scheduler.Sched_Date, Scheduler.TECH FROM Scheduler INNER JOIN Patient_Info ON Patient_Info.Patient_ID = Scheduler.Patient_ID where Scheduler.Sched_Date >= Date() ORDER BY [Scheduler.Sched_Date] ASC

    All from 8 weeks time:

    Select Scheduler.Patient_ID, Patient_Info.Last_Name, Patient_Info.First_Name,Patient_Info.Home_Phone, Scheduler.Sched_Date, Scheduler.TECH FROM Scheduler INNER JOIN Patient_Info ON Patient_Info.Patient_ID = Scheduler.Patient_ID where Scheduler.Sched_Date >= DateAdd('ww', 8, Date()) ORDER BY [Scheduler.Sched_Date] ASC

    More about Access Date and Time: http://www.mikesdotnetting.com/Article.aspx?ArticleID=92

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 29, 2008 4:43 AM
  • User-821857111 posted

    Try converting the SelectedDate to an Ole Automation Date:

    Dim dbcommandT As New OleDbDataAdapter("Select TECH from Scheduler where Tech =? or Sched_Date = ?", dbconn)
    dbcommandT.Parameters.AddWithValue"", tech)
    dbcommandT.Parameters.AddWithValue("", Calendar1.SelectedDate.ToOADate())

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 30, 2008 2:45 AM

All replies

  • User-821857111 posted

    All from today:

    Select Scheduler.Patient_ID, Patient_Info.Last_Name, Patient_Info.First_Name,Patient_Info.Home_Phone, Scheduler.Sched_Date, Scheduler.TECH FROM Scheduler INNER JOIN Patient_Info ON Patient_Info.Patient_ID = Scheduler.Patient_ID where Scheduler.Sched_Date >= Date() ORDER BY [Scheduler.Sched_Date] ASC

    All from 8 weeks time:

    Select Scheduler.Patient_ID, Patient_Info.Last_Name, Patient_Info.First_Name,Patient_Info.Home_Phone, Scheduler.Sched_Date, Scheduler.TECH FROM Scheduler INNER JOIN Patient_Info ON Patient_Info.Patient_ID = Scheduler.Patient_ID where Scheduler.Sched_Date >= DateAdd('ww', 8, Date()) ORDER BY [Scheduler.Sched_Date] ASC

    More about Access Date and Time: http://www.mikesdotnetting.com/Article.aspx?ArticleID=92

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 29, 2008 4:43 AM
  • User515264000 posted

    Mike,

     

    Thanks,

    That  fixed the first Problem. I appreciate it. Now, my second problem is I need to get some vaules based off of the SeletedDate from the calendar. I need to compare it to the date (Access) in the DB.

     I'm trying to do something like this:

    Code snippet

    "

    Dim dbcommandT As New OleDbDataAdapter("Select TECH from Scheduler where Tech = '" & tech & "' or Sched_Date = " & String.Format("{0:d/M/yyyy HH:mm:ss}", Me.Calendar1.SelectedDate) & "", dbconn)

    "

    The String Format compares the selectedDate to the Long date in the access DB (1/01/1999 12:00:00 AM) This is throwing my entire program off. I can get it to run by just using Date() but it negates the values in my DB.. PLEASE HELP!!! this si breaking my entire application!!!!

     

     

    Tuesday, December 30, 2008 2:09 AM
  • User-821857111 posted

    Is it throwing an error or are you just not getting the results you expect? If the former, then I suspect it might be an incorrect syntax error caused by the fact that you have the wrong delimiters for the DateTime.  Use of parameters will solve that (which you should be doing anyway) http://www.mikesdotnetting.com/Article.aspx?ArticleID=76

    If the latter, you probably need to select within a range if you are storing time as well as the date.

     

    Tuesday, December 30, 2008 2:22 AM
  • User515264000 posted

    It's not giving me an error, it's acting like I didnt click on a date, or the date i selected is not there. 

    Code snippet:

    Dim dbcommandT As New OleDbDataAdapter("Select TECH from Scheduler where Tech = '" & tech & "' or Sched_Date = " & Me.Calendar1.SelectedDate.ToShortDateString & "", dbconn)

    "

    If I use this, it's not comparing the Selected date to the date in the DB.

    I ran a test on the Selected date in the calendar against the DB.

    The Selected Date looks like this: 2/25/2009 and the DB looks like this: 2/25/2009 12:00:00 AM.

    I dont think the program is comparing the two. I know there is a value there, it;'s just not looking at it.

    I need a way to make the Selected date look like 2/25/2009 12:00:00 AM in the select statement so it will match whats in the DB. Date() wont work because Im working with dynamic dates.

    BTW if I use single quotes, I get a datatype mismatch error.

     

    Tuesday, December 30, 2008 2:37 AM
  • User-821857111 posted

    Try converting the SelectedDate to an Ole Automation Date:

    Dim dbcommandT As New OleDbDataAdapter("Select TECH from Scheduler where Tech =? or Sched_Date = ?", dbconn)
    dbcommandT.Parameters.AddWithValue"", tech)
    dbcommandT.Parameters.AddWithValue("", Calendar1.SelectedDate.ToOADate())

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 30, 2008 2:45 AM
  • User515264000 posted

    You, My friend are the freaking Man!!!!

     

    Here's the conversion:

    Code Snippet:

    Dim dbcommandT As New OleDbDataAdapter

    dbcommandT.SelectCommand = New OleDbCommand("Select TECH from Scheduler where Tech =? or Sched_Date = ?", dbconn)

    dbcommandT.SelectCommand.Parameters.AddWithValue("", tech)

    dbcommandT.SelectCommand.Parameters.AddWithValue("", Calendar1.SelectedDate.ToOADate())"

     

    Thank you very Kindly!!!!!

     

    RESOLVED!!!

     

     

    Tuesday, December 30, 2008 3:02 AM
  • User515264000 posted

    One more question:

    I need to format a variable to a medium date.

     This is what I have:

     

    dbcommandT.SelectCommand.Parameters.AddWithValue("", tech.ToString("hh:mm tt"))

    How Do I convert it?

     

    Thanks!

    Monday, January 19, 2009 10:44 PM