I am having a problem trying to figure out why a querystring value isn't getting passed into the URL string. I have an ASP calendar where the date links should pass the event date as a querystring value into the URL string.

I could get it to work with an Access DB, but not with a SQL Server DB. I'm at a loss? I had to use convert to change the date format to mm/dd/yyyy from the default date format created using the smalldatetime data type within SQL Server. When I tested the recorset in DMX, it displayed the correct format. So, I don't think the SQL is incorrect?

Here is a snippet of the relevant code:
(As a note: I tried removing the restrict access portion of the code just to see if that was causing the problem...it still didn't work)

<%
' *** Restrict Access To Page: Grant or deny access to this page
MM_authorizedUsers="1,2"
MM_authFailedURL="fail.htm"
MM_grantAccess=false
If Session("MM_Username") <> "" Then
If (false Or CStr(Session("MM_UserAuthorization"))="") Or _
(InStr(1,MM_authorizedUsers,Session("MM_UserAuthor ization"))>=1)
Then
MM_grantAccess = true
End If
End If
If Not MM_grantAccess Then
MM_qsChar = "?"
If (InStr(1,MM_authFailedURL,"?") >= 1) Then MM_qsChar = "&"
MM_referrer = Request.ServerVariables("URL")
if (Len(Request.QueryString()) > 0) Then MM_referrer = MM_referrer & "?" &
Request.QueryString()
MM_authFailedURL = MM_authFailedURL & MM_qsChar & "accessdenied=" &
Server.URLEncode(MM_referrer)
Response.Redirect(MM_authFailedURL)
End If
%>

<%
Dim rsCalendar
Dim rsCalendar_numRows

Set rsCalendar = Server.CreateObject("ADODB.Recordset")
rsCalendar.ActiveConnection = MM_DBConn_STRING
rsCalendar.Source = "SELECT blogID, convert(char(10), blogDate, 101) AS
blogDate FROM dbo.tblWeblog"
rsCalendar.CursorType = 0
rsCalendar.CursorLocation = 2
rsCalendar.LockType = 1
rsCalendar.Open()

rsCalendar_numRows = 0
%>
<%
Dim rsComment__MMColParam
rsComment__MMColParam = "12/15/1984"
If (CDate(Request.QueryString("blogDate")) <> "") Then
rsComment__MMColParam = CDate(Request.QueryString("blogDate"))
End If
%>
<%
Dim rsComment
Dim rsComment_numRows

Set rsComment = Server.CreateObject("ADODB.Recordset")
rsComment.ActiveConnection = MM_DBConn_STRING
rsComment.Source = "SELECT blogID, convert(char(10), blogDate, 101) AS
blogDate, blogHeader, blogComment FROM dbo.tblWeblog WHERE blogDate = ' "
+ Replace(rsComment__MMColParam, "'", "''") + " ' ORDER BY blogID DESC"
rsComment.CursorType = 0
rsComment.CursorLocation = 2
rsComment.LockType = 1
rsComment.Open()

rsComment_numRows = 0
%>
<%
Function ASPCalendar
If Request("EventDate") <> "" Then
EventDate = DateValue(Request("EventDate"))
Else
EventDate = date()
End if
CurMonth = Month(EventDate)
CurMonthName = MonthName(CurMonth)
CurYear = Year(EventDate)
FirstDayDate = DateSerial(CurYear, CurMonth, 1)
FirstDay = WeekDay(FirstDayDate, 0)
CurDay = FirstDayDate
Dim tmpHTML
tmpHTML=""
tmpHTML = tmpHTML & "<table summary="""" id=""calendar"" cellspacing=""0"">"
& Chr(10)
tmpHTML = tmpHTML & "<caption></caption>" & Chr(10)
tmpHTML = tmpHTML & "<tr id=""title"">" & Chr(10)
tmpHTML = tmpHTML & "<th colspan=""7"">" & Chr(10)
tmpHTML = tmpHTML & "<a href=""?EventDate=" &
Server.URLEncode(DateAdd("m",-1, EventDate)) & """>&lt;</a>" & CurMonthName
& "<a href=""?EventDate=" & Server.URLEncode(DateAdd("m",1,EventDate)) &
""">&gt;</a>"
tmpHTML = tmpHTML & "<a href=""?EventDate=" &
Server.URLEncode(DateAdd("yyyy",-1, EventDate)) & """>&lt;</a>" & CurYear &
"<a href=""?EventDate=" & Server.URLEncode(DateAdd("yyyy",1,EventDate)) &
""">&gt;</a>"
tmpHTML = tmpHTML & "</th>" & Chr(10) & "</tr>" & Chr(10) & "<tr
id=""days"">"
Response.Write(tmpHTML)
For DayLoop = 1 to 7
Response.Write("<th>" & WeekDayName(Dayloop, True, 0) & "</th>" & Chr(10))
Next
Response.Write("</tr>" & Chr(10) & "<tr class=""firstweek"">")
If FirstDay <> 1 Then
Response.Write("<td colspan=""" & (FirstDay -1) & """
class=""blank"">&nbsp;</td>" & Chr(10))
End if
DayCounter = FirstDay
CorrectMonth = True
Do While CorrectMonth = True
isEvent = FALSE
rsCalendar.filter = 0
Dim iCheck
Dim chkStr
chkStr = (rsCalendar.Fields.Item("blogDate").Name)
iCheck = CurDay
rsCalendar.filter = chkStr & "=" & (iCheck)
If not(rsCalendar.EOF) Then isEvent = TRUE
If CurDay = EventDate Then
Response.Write("<td class=""today"">")
Else
Response.Write("<td class=""day" & DayCounter & """>")
End if
If isEvent = TRUE Then
Response.Write("<a href=""members.asp?EventDate=" & Server.URLEncode(CurDay)
& """>" & Day(CurDay)& "</a>")
Response.Write("</td>" & Chr(10))
Else
Response.Write(Day(CurDay) & "</td>" & Chr(10))
End If
DayCounter = DayCounter + 1
If DayCounter > 7 Then
DayCounter = 1
Response.Write("</tr>" & Chr(10))
Response.Write("<tr")
If Month(CurDay+8) <> CurMonth Then
Response.Write(" class=""lastweek""")
End If
Response.Write(">" & Chr(10))
End if
CurDay = DateAdd("d", 1, CurDay)
If Month(CurDay) <> CurMonth then
CorrectMonth = False
End if
Loop
IF DayCounter <> 1 Then
Response.Write("<td colspan=""" & (8-DayCounter) & """
class=""blank"">&nbsp;</td>")
Else
Response.Write("<td colspan=""7"" class=""blank"">&nbsp;</td>")
End if
Response.Write("</tr>" & Chr(10) & "</table>" & Chr(10))
End Function
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rsComment_numRows = rsComment_numRows + Repeat1__numRows
%>

Does anyone see what might be causing the problem? Thanks for any help!
-Dman100-

Hi Dman,

Is that you are facing problem with Querystring or with SQL statement where you compare the blogDate with the querystring value? More information on that with some sample data would help us understand better, than looking at the entire code/guessing.

What is the value & date format you pass here as CurDay for each link? Also I would suggest you always compare the date values in "yyyy-mm-dd" format with SQL data. Use 121 format code in place of 101 in convert function, when comparing against sql date values. So I assume that you would pass your querystring in that format, so that it requires no effort to convert that to the above mentioned format to be used in the sql statement later.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Hi Vijay,

Here are the sql statements for the two recordsets I'm using:

SELECT blogID, convert(char(10), blogDate, 121) AS blogDate
FROM dbo.tblWeblog

SELECT blogID, convert(char(10), blogDate, 121) AS blogDate, blogHeader, blogComment
FROM dbo.tblWeblog
WHERE blogDate = ' MMColParam '
ORDER BY blogID DESC

MMColParam - variable name
12/15/1985 - default value
CDate(Request.QueryString("blogDate")) - Run-time value

Are the SQL statements incorrect? I switched the date format code to '121' as you suggested, but the date links still don't work.

<a href=""members.asp?EventDate=" & Server.URLEncode(CurDay)

This line should create a link in the calendar for any date that has an entry within the database, but it doesn't. It works fine in Access, but not SQL Server. So, I'm guessing there is something wrong in the value that is passed for CurDay.

CurDay is defined as follows:
FirstDayDate = DateSerial(CurYear, CurMonth, 1)
CurDay = FirstDayDate

I'm really struggling trying to determine what is wrong? I'm trying everything I can think of. What is most frustrating, is that it works fine when I use an Access DB. I can't figure out where I'm going wrong with SQL Server.

Any ideas?
Thanks.
-Dman100-

Hi,

It doesn't matter how you format date values in the select list, but the WHERE clause should have that in 121 format.

Try this query...
So, it should look like this in you ASP code. Where MMColParam would have 'yyyy-mm-dd' formatted date. If MMColParam doesn't already come with that formatted date, you will have to convert, else you can pass the variable as it is.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Hi Vijay,

Hmmm, I tried the sql you suggested, but the date links still don't work. I'm really at a loss?

I'm using following Dreamweaver extension to create the calendar:
Here is a link to the extension:
http://www.dreamlettes.net/extensions/#CalPack

I read a thread that says the extension requires you to have the date in the short date format. Here is the link to the thread:
http://ranjan.ws/40

Whatever I'm missing, I don't know?

If you take a look at this extension, can you let me know if I'm missing anything simple?

Thanks for all your help! I really appreciate it.
-Dman100-

I am not sure what you are missing, unless I get the exact picture of what is shown on the browser calendar and what is passed as querystring and in what format and how the sql statement is contructed to compare date values against the db data and what output is expected from it. By any chance can we take a look at this web page, so that to know what exactly is happening or being missed out. If that is too much of asking, probably can you explain a bit more on that.

Cheers!

_________________________
- Vijay G
Strive for Perfection

Hi Vijay,

Here is a link where you can download the page and see all the code:

www.dwayneepps.com/download.asp

Will that help?
-dman100-

That has got connectivity with DB, I can view that in my browser for which I got to modify a lot. Is that possible to see that page hosted somewhere online?

_________________________
- Vijay G
Strive for Perfection

Hi Vijay,

I am working on getting it uploaded on the hosting server so you can view the page online. I'm just waiting on the hosting company to load the backup I sent them. I'll post the URL when it's completed. Should be sometime today.

-Dman100-

Hi Vijay,

I finally figured out the problem after several days of pulling my hair out. It was how I had the default set for the date field in SQL Server. I had the default set as getdate(). I needed to set the default as:
Convert(char(10), getdate(), 101)

Now the date links are working fine.

Thanks again for all of your help!
-D-