?
Solved

Date and Time problem in query in VB.NET

Posted on 2004-03-23
10
Medium Priority
?
561 Views
Last Modified: 2012-08-14
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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 15

Expert Comment

by:SRigney
ID: 10659805
If this is SQL Server then date and time need to be surrounded with single quote.

If this is for Access then the pound sign is used.
0
 

Author Comment

by:DavidMcGarry
ID: 10660321
Im using an access database,

I tried what you suggested + its not working

"AND tblSubjectSchedule.Time_Start= £" & Format(strStartTime) & "£"


It displays the time in the error handler,but has the time surrouned by two pound signs+says its incorrect data type?
0
 
LVL 15

Expert Comment

by:SRigney
ID: 10660393
Not the pound sign as in 5 pounds money, the # is called the pound sign.  Which is what you already had

What type of field is it in Access?
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

Author Comment

by:DavidMcGarry
ID: 10660437
It is a date/time field in Access.

I have strStartTime declared as a string at the top of the code and then set it to

strStartTime = txtCmbStartTimeRooms.text

Then im running the query+then i have problems

0
 
LVL 15

Expert Comment

by:SRigney
ID: 10660495
What are the values of strDate and strStartTime when you get to the line?

sSQL2 = "SELECT ...
0
 

Author Comment

by:DavidMcGarry
ID: 10660601
strDate = txtDayDateRooms.text

strStartTime = cmbStartTimeRooms.text

sSQL2 = "SELECT....

How do i check the values?
0
 

Author Comment

by:DavidMcGarry
ID: 10660622
Ok i used a break point.

strDate = "06/02/2004"

strStartTime = "09:00:00"
0
 
LVL 15

Accepted Solution

by:
SRigney earned 150 total points
ID: 10660666
You put a breakpoint on the sSQL2 = "SELECT...." line and then look to see what the values of the 2 fields are.

You could also add MessageBox code that would show them.  Just so we can see the actual value and see what is being passed in after the format statement in your SQL.
0
 

Author Comment

by:DavidMcGarry
ID: 10660755
Ok i just sorted, i dont even know how i did it???

how do i give ya the points?? First time using this thing!
0
 
LVL 15

Expert Comment

by:SRigney
ID: 10660801
I think you have an option to grade the question then you choose the answer that you want.  You can probably find the exact procedures in the community support room.

I've never asked a question so I'm not sure about how to give points.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Suggested Courses

770 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