SQL Statement
Hello.
I'm developing a VB.Net app to rent computers which uses an Access database.
My problem is to check if the computer is free. I used a DataView, but I can't find any SQL Statement to use as a filter that fits to my case. My fields are StartTime, EndTime, Date and PC.
I've been testing some statements, but I never got it 100%. The date and PC search it's easy, but I can't find a statements to validate the time.
For example, if I want to rent a computer from 9 am to 11 am, and if the DB has a record for the same day and pc, but the StartTime and the EndTime are 8 am and 12 am, it doesn't work.
Any help would be excellent!
Thanks in advance,
Pedro Querido
Thanks Paul.
Could you explaint better your second post? Converting from what to what?Do you mean convert from an Integer to Time?
Here is the code I'm using:
| |
Dim SD As New DateTime 'the StartDate variable
Dim ED As New DateTime 'the End Date variable
Dim vdate As New Date 'the date variablevdate = date.Value
ED = EndTime.Value
SD = StartTime.Value OledbConnection1.Close()
OledbConnection1.Open()
DataSet11.Clear()
OleDbDataAdapter1.Fill(DataSet11)
OledbConnection1.Close() Dim dv As New DataView(DataSet11.Tables("Rents")) dv.RowFilter = "(Date = '" & (vdate.ToString("dd-MM-yyyy")) & "' AND [StartTime] <= #" & SD.ToString("HH:mm") & "# AND [EndTime] >= #" & ED.ToString("HH:mm") & "# AND [PC]= '" & PC.Text & "') OR (Date = '" & (vdate.ToString("dd-MM-yyyy")) & "' AND [EndTime] >= #" & ED.ToString("HH:mm") & "# AND [StartTime] <= #" & ED.ToString("HH:mm") & "# AND [PC]= '" & PC.Text & "') AND (Date = '" & vdate.ToString("dd-MM-yyyy") & "' AND [StartTime] >= #" & SD.ToString("HH:mm") & "# AND [EndTime] >= #" & ED.ToString("HH:mm") & "# AND [PC]= '" & PC.Text & "')" If dv.Count >= 1 Then
'if found one or more records, alerts the user, The computer is already rented
Else
'The code to save it to Database
End If
|
I now the code is not very good, but its an easy way I found to do what I need.
Thaks for your help,
Pedro Querido
Hi,
What I meant was convert the datetime fields when filling you dataset. Change you Select command in your DataAdapter into something like this.
"SELECT CONVERT(startdate, 108) AS myTime FROM myTable"
BTW, the convert function is from the SQL docs. Try searching access on what function it uses to convert or format types...
cheers,
Paul June A. Domag
Hi.
Well, after all this time, I just can't find a way to do it. Paul, please, can you post a code sample of your idea? I just don't now what to do...
Thanks!
Pedro Querido
