Documentation Details :: Problem with blog and calendar filtering on recordset

Hello,

I am using asp and SQL server 2005 to set up the Blog application from your toturials. Everything is working fine except when selecting a day on the calendar nugget I get no results for that day. I saw a similar post under

http://www.interaktonline.com/Products/Dreamweaver-Extensions/MXCalendar/Product-Forum/Details/50955/Calendar+for+Blog.html

Can you please help me fix this so it will work on asp/SQL server 2005. If I go into the dba and set the time to it's default then it will put the article fine, but if it is a user created one it will not show up.

Please let me know how I can fix this.
Many thanks

Jason

Hello,

I think that the problem is with the mode the date is passed to the page, or with the way the recordset is filtered. Can you provide in a private post FTP connection details and the URL of the page where we can test this?

Regards,
Razvan RACASANU

Thanks for the quick reply Razvan,

Currently this is set up on a local testing machine and it should be online within the next few business days. If this is not helpful then I can get you access to debug.

The recorset, from the advanced recorset panel looks like this:

SELECT blg_article_art.date_art, blg_article_art.visible_art, count(blg_comment_com.id_com)  AS count_id_com_1, blg_topic_top.title_top, blg_topic_top.id_top, blg_article_art.description_art, blg_article_art.title_art, blg_article_art.id_art
FROM ((blg_topic_top LEFT JOIN blg_article_art ON blg_article_art.idtop_art=blg_topic_top.id_top) LEFT JOIN blg_comment_com ON blg_comment_com.idart_com=blg_article_art.id_art)
WHERE blg_article_art.date_art=KTColParam1  AND blg_article_art.visible_art=1  GROUP BY blg_article_art.date_art, blg_article_art.visible_art, blg_topic_top.title_top, blg_topic_top.id_top, blg_article_art.description_art, blg_article_art.title_art, blg_article_art.id_art

KTColParam1 is set to a date data type and the value (copy pasted from the box) is Request.QueryString("date") 

The string passed in the webpage is :
http://localhost/default.asp?mod=archived_articles&view=day&date=2006%2D07%2D10

I am using DW 8.02 and all updated Interakt extensions. I've attacked a dump of the table and a screenshot of the table properties in the excel file.

Thanks and let me know if I can give you any more info

Hello,

The query you have written here is taken from the code, or from the Dreamweaver recordset interface?. If it is taken from the code, the KTColParam1 should have been replaced by ? in the query. If it is not, opening the query and saving it again should replace it.

If it is OK, then try to put quotes around the KTColParam1 so that the condition will look like this:

WHERE blg_article_art.date_art="KTColParam1"

If non of this works, we will need FTP in order to debug this.

Regards,
Razvan RACASANU

Yes that was the code from the DW interface. It does have the ? in the actual code on the page. 

I tried to put " marks around the parameter and also tried ' for the fun of it. No luck, get syntax errors.

Though I think the syntax is correct the way it is, because when I set the date on the one event to the default it will pull that article up on the older_articles.asp page. It is when there is a time other than 12:00:00 am that it will not display the record.

If this doesn't provide anymore clues I will let you know when I have this online next week and can give you access. Unless you can tell me how to do that for a local machine.

Thanks

Hello,

Unfortunately, I cannot think of anything right now. Please let me know when the site is published so we can access it by FTP, and also post the steps to reproduce this.

Regards,
Razvan RACASANU

Razvan,

I tried your suggestion but just kept getting errors. While trying to google comparing dates to make sure my syntax was correct I ran across a post that said I should convert the Datetime field to just date in the SQL. This worked so wanted to let anyone else know how to do this with SQL server.

WHERE CONVERT(CHAR(8), blg_article_art.date_art , 112) = 'KTColParam1'

Thanks for your help and very fast responses!