Solved

How to get a date and time from a database?

Posted on 2004-03-23
7
218 Views
Last Modified: 2010-04-17
Hi,

This is my code to check if a date and a time both exist in a table:
------------------------------------------------------------------
        Dim rsVal2 As ADODB.Recordset
        Dim sSQL2 As String
       
        Dim strDate As Date
        Dim strRoom As String
        Dim strSubjectName As String
        Dim strSubjectID As String
        Dim strDay As String
        Dim strStartTime As String
        Dim strEndTime As String
        Dim i As Short
        Dim strYear As String

        strDate = txtDayDateRooms.Text
        strRoom = cmbViewRooms.Text
        strSubjectName = cmbSubjectRooms.Text
        strSubjectID = txtSubjectIDRooms.Text
        strDay = cmbDayRooms.Text
        strStartTime = cmbStartTimeRooms.Text
        strEndTime = cmbEndTimeRooms.Text

'**** CHECK IF TIMETABLE TIME ALREADY EXISTS ******

'Query check if new time already exists in time table.
sSQL2 = "SELECT tblSubjectSchedule.Subject_ID, tblSubjectSchedule.Date_Sch, tblSubjectSchedule.Time_Start, tblSubjectSchedule.Location,
tblSubjectSchedule.Day_Sch " & _
"FROM tblSubjectSchedule " & _
"WHERE tblSubjectSchedule.Subject_ID='" & strSubjectID & "' " & _
"AND tblSubjectSchedule.Location='" & strRoom & "' " & _
"AND tblSubjectSchedule.Day_Sch='" & strDay & "' "
'"AND tblSubjectSchedule.Date_Sch=#" & Format(strDate) & "#"
'"AND tblSubjectSchedule.Time_Start= #" & Format(strStartTime) & "#" <----THESE TWO LINES ARE WHAT ARE
CAUSING THE PROBLEM

rsVal2 = m_Conn.Execute(sSQL2) 'Execute query.

if Not rsVal2.EOF Then
            i = 0
            rsVal2.MoveFirst() 'Move to the first record.

            While Not rsVal2.EOF

                rsVal2.MoveNext() 'Move to the next record.
                i = i + 1
            End While
        End If

        If i = 0 Then
            MsgBox("THERE ARE NO RECORDS")
        End If

        If i > 0 Then
            MsgBox("THERE ARE RECORDS")
            Exit Function
        End If

------------------------------------------------------------------

If the date and time exist then i cant add new details in. If there are no records for the date and time, then i can add in.The query is where the problem is, but i think my date and time formatting is incorrect

How do i do this? My code doesnt seem to recognise the date or time when looping through, and always works as if there are no records for date and time, when i know for
a fact in my Access DB that there is!!

Thanks, im in dire straits trying to get this working, hence the 500 points.

Thanks
0
Comment
Question by:DavidMcGarry
  • 4
  • 2
7 Comments
 
LVL 7

Expert Comment

by:ramesh12
Comment Utility
How are you storing date and time in your DB.

if your time format in db is hh:mm:ss then change

Format(strStartTime)  to Format(strStartTime,"hh:mm:ss")

What i mean is make sure the formats of data stored in db and the format of data you entered and chechking against db i.e., strStarttime is in same format
0
 

Author Comment

by:DavidMcGarry
Comment Utility
In my Access database strStartTime is a date/time data type.

below is the code i tried for the time bit you suggested:

"AND tblSubjectSchedule.Time_Start =
#" & Format(strStartTime,"hh:mm:ss") & "#"

It comes up with and error,and instead of displaying a time in the error handler, it displays hh:mm:ss
0
 

Author Comment

by:DavidMcGarry
Comment Utility
I have strStartTime = txtStartTimeRooms.text

could this be the problem?

I also have strStartTime declared as a string

Is this the problem?

And how do i format it correctly?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 7

Accepted Solution

by:
ramesh12 earned 500 total points
Comment Utility
How are you entering time in your txtStartTimeRooms.text

I dont think declaring startrttime to string to be a problem.
0
 

Author Comment

by:DavidMcGarry
Comment Utility
cmbStartTimeRoom.text is a combo box that is populated with times.

When i select a time, this is the time i want to check!!
0
 

Author Comment

by:DavidMcGarry
Comment Utility
Thanks i just sorted it,How do i give ya the points?

Im doing a thesis+my programming skills are very amateurish, if thats a word!!
0
 
LVL 10

Expert Comment

by:Kavar
Comment Utility
on the screen by everyones comments you should see the "Accept answer button" click on the button associated with the answer that you feel helped you.  
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now