Member Avatar for problematic:)

hi! well, i'm just a beginner in visual basic and i have this problem in coding the calendar control. I want to reserve the dates and store it on a database. so when there will be another reservation, i can easily know if the date is already taken. I don't know how to start the code. please help!

  • 2 Contributors
  • 8 Replies
  • 265 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by PoisonedHeart

Dani AI

Generated

gave a solid, practical start for checking a selected date against the reservations table. To make that approach robust over time, a few improvements are important: store dates with a DATE/DATETIME column (not as plain text), avoid building SQL with string concatenation, and use parameterized commands. Also avoid naming a column date (it’s a reserved word in many engines) — use ReservationDate or ResDate instead.

A compact, safer VB6 pattern using ADODB.Command (parameterized) keeps the database in charge of date comparison and removes locale-format fragility:

Dim selDate As Date
selDate = DateSerial(Calendar1.Year, Calendar1.Month, Calendar1.Day)

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandText = "SELECT COUNT(*) AS Cnt FROM tblReservations WHERE ReservationDate = ?"
cmd.CommandType = adCmdText
cmd.Parameters.Append cmd.CreateParameter("pDate", adDBDate, adParamInput, , selDate)

Dim rs As ADODB.Recordset
Set rs = cmd.Execute
If rs.Fields("Cnt").Value = 0 Then
    ' Execute a parameterized INSERT here (use parameters for all values)
Else
    MsgBox "Date already reserved.", vbExclamation
End If

Add a unique index/constraint on the date column so concurrent attempts are deterministically rejected by the database (then catch the duplicate-key error and inform the caller). Example (MySQL): ALTER TABLE tblReservations ADD UNIQUE INDEX ux_ResDate (ReservationDate); (syntax differs by engine).

Notes: use DATE for whole-day bookings and DATETIME/TIMESTAMP when time matters; parameterized INSERTs avoid SQL injection and locale bugs; prefer server-side uniqueness over a pure pre-check to avoid race conditions. MSCAL.ocx is a legacy control and may be missing on modern machines — replace it with MonthView/DateTimePicker or a simple custom control if deployment portability is required.

Recommended Answers

All 8 Replies

Member Avatar for PoisonedHeart

I don't really get what your problem is:

1. Is it the code for the calendar control(w/c is stated above) or
2. On how to check if a date selected in the calendar control has already a reservation.

Anyway, what control are you using? is it a month control?, datepicker control? or frankly speaking, the calendar control ->Microsoft Calendar Control ->MSCAL.ocx

If it's calendar control, then here is my idea...this code is a click event of a button, it's up to you on how you will run this code...

Private Sub Command1_Click()
    Dim rsCheckReservations As New ADODB.Recordset  'this will our recordset object
    Dim holdSelectedDate    As String               'this will hold the selected date from the calendar control
                           
    'get the values of the calendar control...
    holdSelectedDate = Me.Calendar1.Month & "/" & Me.Calendar1.Day & "/" & Me.Calendar1.Year
                        '.Month -> returns the month number
                        '.Day   -> returns the daynumber
                        '.Year  -> returns the year
    
    With rsCheckReservations
        If .State = adStateOpen Then .Close
        'open the table from the database, this will check if the selected date from the calendar control is free, or reserved.
        .Open "SELECT * FROM tblReservations WHERE date='" & holdSelectedDate & "'", cn, adOpenKeyset, adLockOptimistic, adCmdText
                                                                                    'cn -> it's the connection string
        'check if there are results.
        If .BOF And .EOF = True Then
            'if there are no results, then process the reservation....
        Else
            MsgBox "Date already reserve", vbOKOnly + vbExclamation, Me.Caption
        End If
    End With
End Sub

NOTE: I assume that the field for the date column is a string data type or a varchar(in MySQL),
and the string formating is: MM/DD/YYYY,

if you are using MySQL database, its formating for date data type is: YYYY/MM/DD

Just play around a little bit on how you would represent the dates, since that the month, day and year property of the calendar control are separate values...

Hope this would help...Goodluck :)

Edited by PoisonedHeart because: Just added some notes..
Member Avatar for problematic:)

thanks! it helped a lot! you're a heaven sent!

Member Avatar for PoisonedHeart

You're welcome!! :)

Happy Coding!!

Member Avatar for problematic:)

ahm i have another question.

what does this mean?

'" & holdSelectedDate & "'

Member Avatar for PoisonedHeart

It as variable to hold the full date

holdSelectedDate = Me.Calendar1.Month & "/" & Me.Calendar1.Day & "/" & Me.Calendar1.Year

As the code above, the value of this variable will be the selected month, day and year from the calendar control...

When we open the reservations table (tblReservation), We use the holdSelectedDate as a condition in this SQL statement:

SELECT * FROM tblReservations WHERE date='" & holdSelectedDate & "'"

Here we open the table with a condition, notice the WHERE clause, WHERE clause is used to extract only those records that fulfill a specified condition. This will return only those records that have the same matching date with the holdSelectedDate variable.

This select statement is use to check if the selected date already exists in the table..

Example:

Suppose you have five records in your table:
(toggle the code window into plain text for better viewing)

|  ID  |  Customer ID |    Date     |
|  1   |   CUST-001   |  01/05/2010 |
|  2   |   CUST-003   |  06/23/2010 |
|  3   |   CUST-002   |  12/01/2010 |
|  4   |   CUST-005   |  03/18/2010 |
|  5   |   CUST-004   |  09/12/2010 |

if the user selected date is 09/12/2010

when our select statement executes, it will return only one record, since that the selected date has only one matching record from the table.

Edited by PoisonedHeart because: n/a
Member Avatar for PoisonedHeart

Sorry, if my English grammar is not that good, though I tried my best to explain as clearly as possible...

Member Avatar for problematic:)

so this will mean that the reservation was selected and therefore cannot be reserved again.

I think I got it now. I wish I could play around the code if I started coding.

thanks again. I guess I can understand it now.

and by the way, I think your grammar is just right. I didn't have the trouble understanding it. :)

Member Avatar for PoisonedHeart

-yes, you're definitely right.

-oh really? good to hear that!..thanks! :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.