[RESOLVED] Time comparing Sql Query in VB6

[amitpanchal]

Dear Team

I am making one application in vb6 using sql 2000 as database and i am stucked at one point where i m comparing two times with entered time what i want is i want to check if the entered time is equal to existing timeframe in DB than it should popup a msgbox ,but i think i am making some problem in format thats why i am not able to retrieve it correctly.
I am pasting my code here it is for preventing duplicate time.


Private Sub CheckavAilability()
Dim cbemp As String

cbemp = CboEmp.Text
Dim tilde As Integer
tilde = InStr(1, cbemp, "-") 'finds the character position containing the "~"
Dim eid As Integer
Dim ename As String
eid = Val(Left(cbemp, tilde - 1))
ename = Right(cbemp, Len(cbemp) - tilde)
Dim timestart As String
Dim timeend As String
timestart = Format(dtpDate.Value, "yyyy/mm/dd") & " " & Format(CboStartTime, "hh:mm:ss")
timeend = Format(dtpDate.Value, "yyyy/mm/dd") & " " & Format(CboEndTime, "hh:mm:ss")
Dim strix As String
MsgBox " hi "
Dim XYZ As New ADODB.Recordset
'EmpName=" & CboEmp.Text & "
If XYZ.State = 1 Then XYZ.Close

XYZ.Open "Select * from Tbl_Appointment_Package where EmpID= " & eid & " and StartDate ='" & Format(dtpDate.Value, "yyyy/mm/dd") & " ' and timefrom between '" & timestart & "' and '" & timeend & " ' and timeto between '" & timestart & "' and '" & timeend & " ' ", Cn, 1, 3 'and TimeTo = ' " & str & " ' or TimeTo = ' " & str1 & " ' 'and TimeTo=' " & str1 & " '
'Stop
If XYZ.RecordCount > 0 Then
MsgBox "Sorry Please choose another time frame because Employee is busy at selected time "
CboEndTime.Clear
Exit Sub

Else
'MsgBox "Appointment successfully created"
End If

End Sub



In this code i tried with taking timestart and timeend as date also but in that case it was showing error in query can you please help me regarding this.
in my database timefrom and timeto having smalldatetime format and they r getting saved in dd/mm/yyyy hh:mm:ss format but while retriving i have to retrieve them in yyyy/mm/dd hh:mm:ss format so i am confused regarding these formats can you help me regarding this query.

Thanks in advance
Amit Panchal

[si_the_geek]

When you post code please put it inside code tags so it is displayed in a more readable way - either using the Code/VBCode buttons in the post editor screen (or at the top of the Quick Reply box), or by putting them in manually, like this: [code] code here [/code]

If XYZ.RecordCount > 0 Then

Don't use that - it is not only wrong at times (see the FAQ article Why does Recordcount sometimes equal -1?), but is also slower than the proper method, which is:

If Not XYZ.EOF Then

smalldatetime format and they r getting saved in dd/mm/yyyy hh:mm:ss format

No they aren't... just like the Date data type in VB, datetime/smalldatetime values do not ever have any format at all.

They just have a value, and only have a format when they are converted to a String, which includes when they are shown to you in the database tools (or in the Watch window in VB, etc).

but while retriving i have to retrieve them in yyyy/mm/dd hh:mm:ss format so i am confused regarding these formats

When you pass values via SQL statements, everything is in a String - so there needs to be some way to be certain what format the values are meant to be, so they can be converted back to Date etc properly.

The way that is done is by the SQL standards forcing it be so that you can only use ISO format (yyyy-mm-dd) and/or US format (mm/dd/yyyy).

[amitpanchal]

Thank you so much
but dear friend my problem is regarding time ,i want to compare time which is associated with timestart and timeend so as u can see i am storing it in a string format and trying to use these with between keyword in my query it is not responding means it is not encountering or executing query in the way i want so for that i was seeking your help.

Thanks in ADvance
Amit Panchal

[si_the_geek]

First of all correct the issues I mentioned above.

Next, follow the advice in the FAQ article How can I find out why my SQL statement isn't working?

If you can't solve it yourself at that stage, post your corrected code (in Code tags), and the contents of the SQL statement.... along with a clearer explanation than "it is not responding means it is not encountering or executing query in the way i want", because I do not know what you meant by that.

[leinad31]

If you want to check for intersections then criteria is

(param_datetime_start BETWEEN timestart and timeend) OR (param_datetime_end BETWEEN timestart and timeend)

Use of logical operator OR handles all three scenarios, using AND will handle only second scenario:
- started before period in DB but ended within period (endtime intersection)
- started within period in DB and ended within period (starttime and endtime intersection)
- started within period in DB but ended after period (starttime intersection)

[amitpanchal]

Thank you sir for ur support but sir still i have some problem i will explain you my application so you will understand what i exactly want.

i am developing an appointment system for a saloon using VB6 and sql 2000 as database .and i am creating appointment in which i am selecting customer name ,employee assigned and date and start time and end time of employee.
it is getting saved fine.
now for checking the availability of employee in a time frame i am using the earlier posted code of function name checkavailability.but it is not working fine.
it is unable to prevent an appoint to saved for same employee and on same time frame .so for that i am asking any codes or suggestion because whatever i tried that is not preventing this situation.what i want is if i m selecting an employee and start time and end time and if this employee is already assigned an appointment on this selected times it should pop up the message box.

can anybody help me regarding this.

Thanks in advance.
Amit Panchal

[si_the_geek]

What is your current code?

[amitpanchal]

Private Sub CheckavAilability()
Dim cbemp As String

cbemp = CboEmp.Text
Dim tilde As Integer
tilde = InStr(1, cbemp, "-") 'finds the character position containing the "~"
Dim eid As Integer
Dim ename As String
eid = Val(Left(cbemp, tilde - 1))
ename = Right(cbemp, Len(cbemp) - tilde)
Dim timestart As String
Dim timeend As String
timestart = Format(dtpDate.Value, "yyyy/mm/dd") & " " & Format(CboStartTime, "hh:mm:ss")
timeend = Format(dtpDate.Value, "yyyy/mm/dd") & " " & Format(CboEndTime, "hh:mm:ss")
Dim strix As String
MsgBox " hi "
Dim XYZ As New ADODB.Recordset
'EmpName=" & CboEmp.Text & "
If XYZ.State = 1 Then XYZ.Close

XYZ.Open "Select * from Tbl_Appointment_Package where EmpID= " & eid & " and StartDate ='" & Format(dtpDate.Value, "yyyy/mm/dd") & " ' and timefrom between '" & timestart & "' and '" & timeend & " ' and timeto between '" & timestart & "' and '" & timeend & " ' ", Cn, 1, 3 'and TimeTo = ' " & str & " ' or TimeTo = ' " & str1 & " ' 'and TimeTo=' " & str1 & " '
'Stop
If XYZ.RecordCount > 0 Then
MsgBox "Sorry Please choose another time frame because Employee is busy at selected time "
CboEndTime.Clear
Exit Sub

Else
'MsgBox "Appointment successfully created"
End If

End Sub



In this code i tried with taking timestart and timeend as date also but in that case it was showing error in query can you please help me regarding this.
in my database timefrom and timeto having smalldatetime format and they r getting saved in dd/mm/yyyy hh:mm:ss format but while retriving i have to retrieve them in yyyy/mm/dd hh:mm:ss format so i am confused regarding these formats can you help me regarding this query.

Thanks in advance
Amit Panchal

[si_the_geek]

I'm not even going to read your code, because even after an explanation in one of my posts and a reminder in another, you haven't bothered to put it in Code tags.

Regarding the date formats, I explained that back in post #2.

[amitpanchal]

Dear Team i m extremely sorry for my mistake here i am sending my codes in ur desired way please help me out in solving my problem i ll be very thankful to u.

Private Sub CheckavAilability()
Dim cbemp As String

cbemp = CboEmp.Text
Dim tilde As Integer
tilde = InStr(1, cbemp, "-")      'finds the character position containing the "~"
    Dim eid As Integer
   Dim ename As String
    eid = Val(Left(cbemp, tilde - 1))
    ename = Right(cbemp, Len(cbemp) - tilde)
Dim timestart As Date
Dim timeend As Date
timestart = Format(dtpDate.Value, "yyyy/mm/dd") & " " & Format(CboStartTime, "hh:mm:ss")
timeend = Format(dtpDate.Value, "yyyy/mm/dd") & " " & Format(CboEndTime, "hh:mm:ss")
Dim strix As String
MsgBox " hi "
Dim XYZ As New ADODB.Recordset
'EmpName=" & CboEmp.Text & "
If XYZ.State = 1 Then XYZ.Close

XYZ.Open "Select * from Tbl_Appointment_Package where EmpID=  " & eid & "   and  StartDate ='" & Format(dtpDate.Value, "yyyy/mm/dd") & "'  and  timefrom between  '" & Format(timestart, "yyyy/mm/dd hh:mm:ss") & "'  and '" & Format(timeend, "yyyy/mm/dd hh:mm:ss") & "  ' and timeto between  '" & Format(timestart, "yyyy/mm/dd hh:mm:ss") & "'  and '" & Format(timeend, "yyyy/mm/dd hh:mm:ss") & "  ' ", Cn, 1, 3            'and TimeTo = '  " & str & " ' or TimeTo =  '  " & str1 & " '           'and TimeTo='  " & str1 & " '
'Stop
If Not XYZ.EOF Then
MsgBox "Sorry Please choose another time frame because Employee is busy at selected time "
'Stop
CboEndTime.Clear

Exit Sub

Else
'MsgBox "Appointment successfully created"
End If
Set XYZ = Nothing
'XYZ.Close
Call CheckavAilabilityforresource
End Sub

hope u ll help me and seeking response from ur side .

Thanks in advance
Amit Panchal

[leinad31]

If you want to check for intersections then criteria is

(param_datetime_start BETWEEN timestart and timeend) OR (param_datetime_end BETWEEN timestart and timeend)

Use of logical operator OR handles all three scenarios, using AND will handle only second scenario:
- started before period in DB but ended within period (endtime intersection)
- started within period in DB and ended within period (starttime and endtime intersection)
- started within period in DB but ended after period (starttime intersection)

Forgot one more scenario
- started before period in DB and ended after period (existing record is nested between encoded time start and end)

Create additional OR conditions accordingly.. as to why OR conditions were used consider x < 3 AND x > 5... that will ALWAYS return false as there is no value for x that can satisfy the condition.

[si_the_geek]

Your SQL statement and .Open line are incredibly hard to read... you should really spread it out a bit more, and preferably use a String variable to build the SQL statement (which will help to work out the source of problems, as explained in the FAQ article I linked to), eg:

Dim strSQL as String
  strSQL = "Select * " _
          & "from Tbl_Appointment_Package " _
          & "where EmpID=  " & eid _
          & "   and  StartDate ='" & Format(dtpDate.Value, "yyyy/mm/dd")  _
          & "'  and  timefrom between  '" & Format(timestart, "yyyy/mm/dd hh:mm:ss")  _
                 & "'  and '" & Format(timeend, "yyyy/mm/dd hh:mm:ss")  _
          & "  ' and timeto between  '" & Format(timestart, "yyyy/mm/dd hh:mm:ss")  _
                 & "'  and '" & Format(timeend, "yyyy/mm/dd hh:mm:ss") & "  ' "
  'and TimeTo = '  " & str & " ' or TimeTo =  '  " & str1 & " '           'and TimeTo='  " & str1 & " '
  XYZ.Open strSQL, Cn, 1, 3

The parts I highlighted contain obvious mistakes which could cause problems - there should not be spaces in them.


As to the reason it is not doing what you want, read leinad31's posts.

[amitpanchal]

Thanks a lot dear team with ur help i solved this issue .

Thanks again
Amit Panchal