add one variable to existing code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

add one variable to existing code

Author

Topic

crtdude
Starting Member

13 Posts

Posted - 2010-06-11 : 05:38:55

Hello experts. I have a problem in an asp application which I need some expert help with.

I have an asp calendar application which displays event data in a calendar style table based on the user input received from a form where they can select a month and year. I had to add a column in our existing database to make this a multi-location calendar app and also added a condition named PROPERTY to the user input so I could now select a PROPERTY, MONTH and YEAR and get the event calendar for a specific month and a specific property.

Below is the code used originally which was fine before the extra column for PROPERTY was added. The first section is the DB call for the event set and the second is for the display of each cell in the calendar table. No matter how I try to get the variable PROPERTY into the DB call, I am getting errors. Any help would be greatly appreciated.

========
'******** get event data ********
If GetRequest("user_admin") = "" Then

objCommand.CommandText = "select * from OMNINEEDTEST where" &_
" NDATE_MONTH in (0," & Month(objDate) & ")" &_
" and NDATE_YEAR in (0," & Year(objDate) & ")" &_
" order by NDATE_DAY"
Set objRS = objCommand.Execute

If GetRequest("view") = "yes" And GetRequest("admin_action") = "" Then
objCommand.CommandText = "select * from OMNINEEDTEST where" &_
" NDATE_MONTH in (0," & Month(GetRequest("date")) & ")" &_
" and NDATE_DAY = " & Day(GetRequest("date")) &_
" and NDATE_YEAR in (0," & Year(GetRequest("date")) & ")" &_
" or event_weekday = " & WeekDay(GetRequest("date")) &_
" or RecordID = " & GetRequest("RecordID")
Set objRS_view = objCommand.Execute
ElseIf GetRequest("view") = "yes" And GetRequest("admin_action") = "edit" Then
objCommand.CommandText = "select * from OMNINEEDTEST where" &_
" RecordID = " & GetRequest("RecordID")
Set objRS_view = objCommand.Execute
End If

End If
========

========
Function GetEvent(srcDate)
'*** Declare the function variables ***
Dim strEvent ' HTML string of event data
Dim intEventDay ' counter check for each day
Dim intEventWeekDay ' counter check for each day
Dim strEventEndDate ' event duration check
Dim strEventStartDate ' date event starts

If objRS.EOF <> True Then
intEventDay = objRS("NDATE_DAY")
intEventWeekDay = objRS("event_weekday")
strEventEndDate = objRS("event_end_date")
strEventStartDate = objRS("date_updated")

Do While Not objRS.EOF = True
If intEventDay = Day(srcDate) Or intEventWeekDay = WeekDay(srcDate) Then
'strEvent = strEvent & objRS("event_data")
strEvent = " MAR: $" & objRS("MINRATE") & " Internal: " & objRS("PUB_INTERNAL") & " OH.COM: " & objRS("PUB_OHCOM") & " Channels: " & objRS("PUB_CHANNELS") & " Comments: " & objRS("COMMENTS")
If GetRequest("action") = "admin" And (Session("ActiveUser") = objRS("user_id") Or Session("ActiveUserLevel") >= "700") Then
strEvent = strEvent & " <a class=event_link href=admin.asp?view=yes&admin_action=edit&RecordID=" & objRS("RecordID") &_
"&date=" & srcDate & "&action=" & GetRequest("action") & ">edit</a>"
End If
strEvent = strEvent & ""
If strEventEndDate <> "" Then
If DateDiff("d",srcDate,strEventStartDate) > 0 Then
strEvent = ""
End If
If DateDiff("d",srcDate,strEventEndDate) < 0 Then
strEvent = ""
End If
End If
End If
objRS.MoveNext
If objRS.EOF <> True Then
intEventDay = objRS("NDATE_DAY")
intEventWeekDay = objRS("event_weekday")
strEventEndDate = objRS("event_end_date")
strEventStartDate = objRS("date_updated")
End If
Loop
objRS.MoveFirst
End If

GetEvent = strEvent
End Function
========

Sachin.Nand

2937 Posts

Posted - 2010-06-11 : 06:16:15

What is the error?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

crtdude
Starting Member

13 Posts

Posted - 2010-06-11 : 08:01:48

Syntax seems to be the issue here. I have no idea how to modify the script above to request PROPERTY.

If is it more convenient, I can email the actual asp files to you if you are willing to assist.

Let me know.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 08:23:11

Idera wanted to know the error messages.

No, you're never too old to Yak'n'Roll if you're too young to die.

crtdude
Starting Member

13 Posts

Posted - 2010-06-11 : 08:37:50

TO CLARIFY:
I need the following code to be modified with the correct syntax to be able to query the DB where it includes the field named PROPERTY as well as the already listed items. That's it. Let's not worry about "error messages" since I already know my SQL syntax was incorrect.

If GetRequest("view") = "yes" And GetRequest("admin_action") = "" Then
objCommand.CommandText = "select * from OMNINEEDTEST where" &_
" NDATE_MONTH in (0," & Month(GetRequest("date")) & ")" &_
" and NDATE_DAY = " & Day(GetRequest("date")) &_
" and NDATE_YEAR in (0," & Year(GetRequest("date")) & ")" &_
" or event_weekday = " & WeekDay(GetRequest("date")) &_
" or RecordID = " & GetRequest("RecordID")

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources