event calendar

trying to resolve duplicate entries for a time period

[aweise]

I'm trying to tweak a great event calendar that I have. I'm trying to figure out how to not accept an event if there is an event in that "location" already reserved for that time, date, and location.

Please help. I'm new to asp and new help with the code I have to create this so users can accurately set up meetings for rooms without worrying about duplicates OR OVERLAPPING SCHEDULES.

Thanks,

[txbakers]

Hi and welcome to the Webmaster World.

Your problem makes perfect sense, and here is the answer:

Someone where on your ASP page is the routine to either Update or Insert the record into the Calendar.

Usually there will be a command that says, If Parameter!= null then...... which starts the update/insert section.

Right after that line you will build a new query using the date requested as the condition of the where clause. If the result set returns EOF, that means the date is open, and you can proceed to insert/update. If the recordset is NOT EOF that means there already is an entry for that date, and you can stop processing here, perhaps send a message back to that effect.

Without seeing your page, it's hard to give you the code for it, but this should help you out.

If you need more help, you can post a snippet of your code and someone will write the syntax. Keep in mind we don't like to see a huge post of code.

Good luck with it!

[aweise]

Thanks...that's along the lines I thought but the code I have is confusing to me the newbie. I'm trying to read alot to solve the problem but its time consuming and I'm running out of time.

Here is a snippet of where I think the answer may go, but there may be another area on this addevent page:

' *** Insert Record: set variables

If (CStr(Request("MM_insert")) <> "") Then

MM_editConnection = MM_dsnCoe_STRING
MM_editTable = "tblEvents"
MM_editRedirectUrl = "add_review.asp"
MM_fieldsStr = "published¦value¦listtype¦value¦title¦value¦category¦value¦Location¦value¦description¦value¦start1¦value¦end1¦value¦start2¦value¦end2¦value¦start3¦value¦end3¦value¦start1_t¦value¦end1_t¦value¦start2_t¦value¦end2_t¦value¦start3_t¦value¦end3_t¦value¦contact¦value¦phone2¦value¦office¦value¦email¦value"
MM_columnsStr = "published¦',none,''¦listtype¦',none,''¦title¦',none,''¦category¦',none,''¦Location¦',none,''¦description¦',none,''¦start1¦#,none,NULL¦end1¦#,none,NULL¦start2¦#,none,NULL¦end2¦#,none,NULL¦start3¦#,none,NULL¦end3¦#,none,NULL¦start1_t¦#,none,NULL¦end1_t¦#,none,NULL¦start2_t¦#,none,NULL¦end2_t¦#,none,NULL¦start3_t¦#,none,NULL¦end3_t¦#,none,NULL¦contact¦',none,''¦phone2¦',none,''¦office¦',none,''¦email¦',none,''"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "¦")
MM_columns = Split(MM_columnsStr, "¦")

' set the form values
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(i+1) = CStr(Request.Form(MM_fields(i)))
Next

' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If
%>

[txbakers]

You're using UltraDev, which should make your learning easier. While you are doing this with UltraDev, do take the time to visit www.w3schools.com and look over their ASP section to get a feel for all the unneccessary code Dreamweaver puts in.

In order to get the syntax for the "dupe check" create a new recordset, a simple one to read from the calendar table. Then, you will modify the "WHERE" portion with:
WHERE tblEvent.Field ='" & Request("field to check") & "'"

That will be the clue to whether it already exists. You would put all that recordset code.....

If (CStr(Request("MM_insert")) <> "") Then
HERE

if above RecordSet NOT EOF then
stop processing here
else continue below

MM_editConnection = MM_dsnCoe_STRING
MM_editTable = "tblEvents"
MM_editRedirectUrl = "add_review.asp"
MM_fieldsStr = "publishedŠvalueŠlisttypeŠvalueŠtitle

be sure to add an "END IF" after the insert code

good luck with it. I'm sure you'll figure it out. Once you get comfortable with SQL and ASP commands you'll be able to do all the coding without needing Dreamweaver.

[aweise]

Thanks TXBAKER! I've been going mad trying to figure out what do. I will look at www.w3schools.com to get a better feel of these concepts.

BTW...I know it doesn't matter, but I only actually have Dreamweaver and not UltraDev. I know the code is in UltraDev but I purchased the source code and the code did not come with development assistance.

Thanks again!

[aweise]

Txbakers,

Since I don't have UltraDev and just Dreamweaver 4, I tried looking up the appropriate way to code how to create the recordset (see I told you I was green). I believe I found how to do this manually, but I'm getting errors (HTTP 500 error page). I know there is a way to turn off this friendly message and get to the root of the problem, but my company's IMS department has limited everyone's internet option abilities. I could look into changing this for our department but it would be alot of red tape. Anyways, I can't see why I'm getting the error so if you or anyone else for that matter can see what I'm doing it would be great. Here's the code:

<%
' *** Insert Record: set variables

If (CStr(Request("MM_insert")) <> "") Then
Dim rsstart1
Set rsstart1 = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT start1 from tblEvents WHERE tblEvents.field = '" & Request("start1") & "';"
rsstart1.Open strSQL, db

If Not rsstart1.EOF Then response.write "This room has been already reserved for this time, please select another time, room, or date."
Else

MM_editConnection = MM_dsnCoe_STRING
MM_editTable = "tblEvents"
MM_editRedirectUrl = "add_review.asp"
MM_fieldsStr = "published¦value¦listtype¦value¦title¦value¦category¦value¦Location¦value¦description¦value¦start1¦value¦end1¦value¦start2¦value¦end2¦value¦start3¦value¦end3¦value¦start1_t¦value¦end1_t¦value¦start2_t¦value¦end2_t¦value¦start3_t¦value¦end3_t¦value¦contact¦value¦phone2¦value¦office¦value¦email¦value"
MM_columnsStr = "published¦',none,''¦listtype¦',none,''¦title¦',none,''¦category¦',none,''¦Location¦',none,''¦description¦',none,''¦start1¦#,none,NULL¦end1¦#,none,NULL¦start2¦#,none,NULL¦end2¦#,none,NULL¦start3¦#,none,NULL¦end3¦#,none,NULL¦start1_t¦#,none,NULL¦end1_t¦#,none,NULL¦start2_t¦#,none,NULL¦end2_t¦#,none,NULL¦start3_t¦#,none,NULL¦end3_t¦#,none,NULL¦contact¦',none,''¦phone2¦',none,''¦office¦',none,''¦email¦',none,''"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "¦")
MM_columns = Split(MM_columnsStr, "¦")

' set the form values
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(i+1) = CStr(Request.Form(MM_fields(i)))
Next

' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If
End If
%>

THANKS SO MUCH!

[txbakers]

WHERE tblEvents.field = '" & Request("start1") & "';"

This should be your field name, not the word "field"

[aweise]

okay this is what I did and I still get an error message

If (CStr(Request("MM_insert")) <> "") Then
Dim rsstart1
Set rsstart1 = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT start1 from tblEvents WHERE tblEvents.start1 = '" & Request("start1") & "';"
rsstart1.Open strSQL, db

If Not rsstart1.EOF Then response.write "This room has been already reserved, please select another time, room, or date."
Else

MM_editConnection = MM_dsnCoe_STRING

[txbakers]

I'm not totally up on the VB version, I use mostly javascript for my ASP coding.

What is the error message?

[aweise]

I get a http 500 internal error message. I'm unable to see what's wrong because of the whole story I went into earlier.
If there's a javascript way to do it...bring it on.

[aweise]

Alright, I'm back... I was able to get true error message from Netscape. I have tried a few different things to solve this error, but I DON'T KNOW CLEARLY WHAT I'M DOING...PLEASE HELP!

Here's the error message:

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/keenanassoc/res2/addevent.asp, line 26

The Code:

17 <%
18' *** Insert Record: set variables
19
20 If (CStr(Request("MM_insert")) <> "") Then
21 Dim rsstart1
22 Set rsstart1 = Server.CreateObject("ADODB.Recordset")
23 strSQL = "SELECT start1 from tblEvents WHERE tblEvents.start1= '" & Request("start1") & "';"
24 rsstart1.Open strSQL, db
25 If Not rsstart1.EOF Then response.write "This room has been already reserved, please select another time, room, or date."
26 Else

MM_editConnection = MM_dsnCoe_STRING
MM_editTable = "tblEvents"
MM_editRedirectUrl = "add_review.asp"
MM_fieldsStr = "published¦value¦listtype¦value¦title¦value¦category¦value¦Location¦value¦description¦value¦start1¦value¦end1¦value¦start2¦value¦end2¦value¦start3¦value¦end3¦value¦start1_t¦value¦end1_t¦value¦start2_t¦value¦end2_t¦value¦start3_t¦value¦end3_t¦value¦contact¦value¦phone2¦value¦office¦value¦email¦value"
MM_columnsStr = "published¦',none,''¦listtype¦',none,''¦title¦',none,''¦category¦',none,''¦Location¦',none,''¦description¦',none,''¦start1¦#,none,NULL¦end1¦#,none,NULL¦start2¦#,none,NULL¦end2¦#,none,NULL¦start3¦#,none,NULL¦end3¦#,none,NULL¦start1_t¦#,none,NULL¦end1_t¦#,none,NULL¦start2_t¦#,none,NULL¦end2_t¦#,none,NULL¦start3_t¦#,none,NULL¦end3_t¦#,none,NULL¦contact¦',none,''¦phone2¦',none,''¦office¦',none,''¦email¦',none,''"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "¦")
MM_columns = Split(MM_columnsStr, "¦")

' set the form values
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(i+1) = CStr(Request.Form(MM_fields(i)))
Next

' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If

%>

[aspdaddy]

WHERE tblEvents.start1= '" & Request("start1")& "';"

Looks like you are comparing a string with a date/time.

Its funny seeing the Intranet room booking system come up here, I had the exact same app but chose to do it in Outlook/VBA - much easier :)

[aweise]

Thanks that helps to clarify the problem, but I'm really dense. How would I go about changing this? I'm not entirely understanding the statement. Yet I do understand you can't compare a string to a number you would need to convert one to the other for comparison.

Request("start1") should be in the access database as e.g. (03/28/03)--date/time and tblEvents.start1 should be...?

What would be your suggestion on how to solve this conflict? Do I need to use # in my database...a random thought.

[aspdaddy]

You actually surround the date with # for access, for sql its fine.

That error may also mean the date is invalid. If you use two digits for the year or month part it allows it to be interpreted incorrectly by the database.

Better to use dd-mmm-yyyy

Some info on dates and ASP here
[aspfaq.com...]

[aweise]

okay the basic premise I think of what the sql statement is suppose to do is to check to see if there is a value in that field (start1 which is a date/time field with no special formatting in access) if there is than it needs to display a message stating choose another time, date or location, otherwise proceed to adding the event. THE DATES ARE ACTUALLY 03/28/2003.

I'M GOING BONKERS. THE CODE I HAVE IS GREAT, BUT I NEED TO PREVENT USERS FROM CREATING MULTIPLE RESERVATIONS FOR THE SAME ROOM, AT THE SAME TIME AND DATE. I'M ALSO TRYING TO PREVENT OVERLAPPING. IS THERE AN EASIER WAY TO DO THIS. SHOULDN'T THIS BE PRETTY SIMPLE?

YOUR HELP WOULD BE MUCH APPRECIATED!

[txbakers]

There's not an easier way to do it, the way we presented is very easy. You just need to work out the syntax of dates in Access.

You have all the pieces you need already, just keep tinkering with it until it works.

(I feel like Ellery Queen!)

You don't put the hash marks in the DB, you put them in your SQL statement

WHERE table.myDateField = #2/3/2003#

or, in your case since you are using variables:
myDate = 2/3/2003
mydate = "#" & mydate & "#"

WHERE table.myDateField = mydate

[aweise]

I know your patience is running thin, but please walk me through this a bit more. I am trying to use my noodle. I have tried substituting = '" & Request("start1") & "';" for
strSQL = "SELECT start1 from tblEvents WHERE tblEvents.start1 = "#" & start1 & "#;"
and I get Expected end of statement error.

It look like you wanted to declare it. I have even do this statement: "SELECT start1 from tblEvents
WHERE start1 = "#" & 05/18/2003 & "#";"

Anything else you can suggest or point me to an example?

[txbakers]

It look like you wanted to declare it. I have even do this statement: "SELECT start1 from tblEvents

Anything else you can suggest or point me to an example?

WHERE start1 = "#05/18/2003#";"

The Hash marks are part of the date. Just try it with a fixed date rather than a variable to see that it works.

Then substitue the parameter.

It's a process.

A great tutorial for all things ASP can be found at www.w3schools.com. I've learned a great deal from them.

The problem you're running into is two fold: Access has a funny syntax sometimes (using the # for dates for example) and your UltraDev code has a lot of extraneous spaghetti.

I think you'll do fine.

[duckhunter]

Try wrapping the request("start1") in the FormatDateTime function like this:

formatdatetime(request("start1"),vbShortDate)