Ajax Event Calendar Support Product Page

QUESTIONS

Cant get data from A MSSQL database - Google Fine

Asked 26 Jan 2010 00:39:21

has this question

26 Jan 2010 00:39:21 Seb Adlington posted:

Hi,

I'm trying to just pull from a SQL Database but I can't get it to show in the calendar - Google items appear no probs.

Here's a dead basic one I tried

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<script language="javascript" type="text/javascript" src="ScriptLibrary/jquery-latest.pack.js"></script>
<script language="javascript" type="text/javascript" src="ScriptLibrary/dmx_events_calendar.js"></script>
<script language="javascript" type="text/javascript" src="ScriptLibrary/calendar_translation_en.js"></script>
<link rel="stylesheet" type="text/css" href="Styles/dmxAjaxCalendar/apple_dark/apple_dark.css" />
</head>

<body>
<div class="dmx_ajax_event_calendar apple_dark" id="dmxAjaxCalendar1" style="width:800px;"></div>
<script type="text/javascript">
  // <![CDATA[
 jQuery(document).ready(
   function()
     {
       jQuery("#dmxAjaxCalendar1").dmxAjaxCalendar(
{id:"dmxAjaxCalendar1", allowed_display_types:[true, true, true, true], legend_position:"bottom", events_type:[{number:0, name:"Type 1", color:"#000000", background_color:"#FFD700", border_color:"#FFD700"}, {number:1, name:"Type 2", color:"#000000", background_color:"#98FB98", border_color:"#98FB98"}, {number:2, name:"Type 3", color:"#000000", background_color:"#4169E1", border_color:"#4169E1"}, {number:3, name:"Type 4", color:"#000000", background_color:"#98B6FC", border_color:"#98B6FC"}, {number:4, name:"Type 5", color:"#000000", background_color:"#A78EC0", border_color:"#A78EC0"}, {number:5, name:"Type 6", color:"#000000", background_color:"#FF8C00", border_color:"#FF8C00"}, {number:6, name:"Type 7", color:"#000000", background_color:"#A52A2A", border_color:"#C04545"}, {number:7, name:"Type 8", color:"#000000", background_color:"#FFC0CB", border_color:"#FFC0CB"}, {number:8, name:"Type 9", color:"#000000", background_color:"#008000", border_color:"#008000"}], google_feeds:[{url:"en.uk#holiday@group.v.calendar.google.com", type:0}], ajax_feeds:[{url:"calendar_ajax/calendar_get_events2.asp"}], remote_call_url:"ScriptLibrary/remoteCall.asp"}       );
     }
 );
  // ]]>
</script>
</body>
</html>

and this is the  get events file

<!--#include file="../Connections/locateit2.asp" -->
<!--#include file="../Scriptlibrary/JSON_latest.asp"-->
<!--#include file="../Scriptlibrary/JSON_UTIL_latest.asp"-->
<% 
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
'handler="locateit2"
'table="dbo.SebDiary"
'title="event_title"
'start="[start]"
'stop="[stop]"
'description="description"
'type="type"
'link="link"
'event_id="event_id"
'db_type="mssql"
Dim dmxEventsRs
Dim dmxEventsRs_cmd
Dim dmxEventsRs_numRows
	
Set dmxEventsRs_cmd = Server.CreateObject ("ADODB.Command")
dmxEventsRs_cmd.ActiveConnection = MM_locateit2_STRING
dmxEventsRs_cmd.CommandText = "SELECT [event_title] AS title, DATEDIFF(second,{d '1970-01-01'},[start]) AS start, DATEDIFF(second,{d '1970-01-01'},[stop]) AS stop , [description] AS description, [type] AS type, [link] AS link, [event_id] AS event_id FROM [dbo.SebDiary]  WHERE DATEDIFF(second,{d '1970-01-01'},[start]) <=" & Replace(Request.QueryString("stop"), "'", "''") & " AND DATEDIFF(second,{d '1970-01-01'},[stop]) >=" & Replace(Request.QueryString("start"), "'", "''")
dmxEventsRs_cmd.Prepared = true
QueryToJSON(dmxEventsRs_cmd, "").Flush
%>

Edited by - Seb Adlington on 26 Jan 2010  00:40:08

Replies

Miroslav Zografski

Replied 26 Jan 2010 10:12:27

26 Jan 2010 10:12:27 Miroslav Zografski replied:

Hello Seb,

Can you provide a link to the page in question so I can investigate the issue? There must be some kind of error returning when ajax query is called. I need to see that error.
If you cannot provide such a link, please, open the page with Firefox and in (if you have installed, if not, please, install) FireBug console check the return of the calendar_get_events#.php file. Please, post the return here.

Regards,

Seb Adlington

Replied 26 Jan 2010 11:00:39

26 Jan 2010 11:00:39 Seb Adlington replied:

Thanks Miroslav,

I can't give you a link as this is hosted internally at work - I will get FireBug installed on the machine the site lives on. Did you eant me to open the calendar_get_events page directly?

I wondered if it was because i was using ASp/Javascript

this is the error using a Javascript DSN

Microsoft VBScript runtime error '800a000d' 

Type mismatch: 'var' 

/systems/Connections/locateit2.asp, line 8

this is the error using a VBScript DSN

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AND'. 

/systems/Scriptlibrary/JSON_UTIL_latest.asp, line 4

Thanks

Seb

Miroslav Zografski

Replied 26 Jan 2010 13:29:05

26 Jan 2010 13:29:05 Miroslav Zografski replied:

Hello Seb,

Is your Dreamweaver site definition set as ASP JS?
If so probably that is the problem. Because the connection file is set on ASP JS, but calendar_get_events#.asp is ASP VB.

I'll see about ASP JS support with the developer.

Regards,

Seb Adlington

Replied 26 Jan 2010 15:16:37

26 Jan 2010 15:16:37 Seb Adlington replied:

I've changed site definitions and made sure it's all in VB but still getting the problem - I'm just wondering if it doesn't like my data??

this is the data in my table - I'm not using the time column - just the TransportDate as a date time - I'm using this for start and stop

Another column I haven't used is type - do I need to include this?

Thanks

ID	 PickID	 PickedBy	 TransportDate	 TransportTime	 TranportTo	 CollectFrom	 CollectTime	 CollectDate	 DirectDelivery	 Notes	 OrderNo	 BookingRef	 Status	 DateCreated	 CustCode	 Email
1	10001	Fred Smith	25/01/2010 15:00:00	b4noon	Chromo - Notts						1234	4321	Pending		POL02	Fred@email.com
2	10002	Simon Fawley	25/01/2010 00:30:00	b4 noon	Chromo - Notts						seb	sfdsfd45/343	In Progress		POL02	Fred@email.com
3	10003	Fred Smith	25/01/2010 15:00:00		xxx								Pending		POL02	
4	10004	Fred Smith	23/01/2010 15:00:00	13:00	Lockwoods	Polestar Leeds	after 8	23/01/2010	No		Simon123	FQ12 / 2334	In Progress		POL02	Fred@email.com
5	10005	Simon Fawley	25/01/2010 17:00:00		xxxx								Pending		POL02	
6	10006	Fred Smith	25/01/2010 13:00:00		xxxx								Pending		POL02	
7	10007	Fred Smith	26/01/2010		xxxx								Pending		POL02	
8	10008	SFred Smith											Pending		POL02	
9	10009	Fred Smith											Pending		POL02

Miroslav Zografski

Replied 26 Jan 2010 15:38:48

26 Jan 2010 15:38:48 Miroslav Zografski replied:

Hi Seb,

You can have your Status set as a different types of events. and instead of placing there a string you can place the number of the event that is corresponding to that Status. This way you will have your calendar more intuitive. By the way Event type should be required.

Regards,

Seb Adlington

Replied 27 Jan 2010 13:35:17

27 Jan 2010 13:35:17 Seb Adlington replied:

Hi Miroslav,

Still having troubles with the data - could you let me know what fields are optional or required and ideally a sample of some data that works would be great.

For the Calendar_get_events#.asp should I be able to open this directly in the browser as I don't seem to be able to? - I'm just wondering whether our IIS server (Windows 2008 + IIS 7) is causing problems as it has a bad habit of doing!

Thanks

Seb

Seb Adlington

Replied 27 Jan 2010 17:25:48

27 Jan 2010 17:25:48 Seb Adlington replied:

OK - it gets stranger

If I edit the select command in calendar_get_events and take the dbo. part off the front of the table name - it sort of works. From a year view I can see entries (the highlighted days) but if I try and click on it it won't display - if I flick to week view and that day is included on the page, the whole calendar is blank - if I look at the following or previous week it displays fine ..[?]

Seb Adlington

Replied 27 Jan 2010 17:29:43

27 Jan 2010 17:29:43 Seb Adlington replied:

OK - I took the whole DatDiff parts off and just bought back the whole table (Only 2 records)

this was the result

[{"title":10001,"start":1264413600,"stop":1264413600,"description":"Hull","type":"Deliveries","event_id":1},{"title":10002,"start":1264428000,"stop":1264428000,"description":"Notts","type":"Deliveries","event_id":2}]

so the final code in the get event page is

dmxEventsRs_cmd.CommandText = "SELECT [PickID] AS title, DATEDIFF(second,{d '1970-01-01'},[TransportDate]) AS start, DATEDIFF(second,{d '1970-01-01'},[TransportDate]) AS stop , [TranportTo] AS description, [Status] AS type, [ID] AS event_id FROM [SebGINHeader2]  "

I hope that helps, I could really do with getting this working for a project tomorrow

Thanks

Seb Adlington

Replied 29 Jan 2010 12:01:12

29 Jan 2010 12:01:12 Seb Adlington replied:

OK - I found the bug.

For some reason if I use a numeric field as a title it kept falling over, as soon as I changed that to a string it worked fine....[^]

Miroslav Zografski

Replied 29 Jan 2010 12:30:01

29 Jan 2010 12:30:01 Miroslav Zografski replied:

Hello Seb,

Humz.. that is weird. I was trying to recreate your issue but never thought of that. I'll address this to developers so they can check out that, 'cose it seems as bug.

Regards,

Seb Adlington

Replied 29 Jan 2010 15:59:02

29 Jan 2010 15:59:02 Seb Adlington replied:

Hi Miroslav,

Just to let you know that I used CAST([xxx] as varchar) on the title field, and it displays just fine[

]

Cheers
Seb

Miroslav Zografski

Replied 29 Jan 2010 16:19:50

29 Jan 2010 16:19:50 Miroslav Zografski replied:

Hi Seb,

Nice workaround! Thank you for your contribution.

Regards,

Ajax Event Calendar Support Product Page

QUESTIONS

Cant get data from A MSSQL database - Google Fine

Replies

Reply to this topic