.net scheduler and ms access db

Scheduler Javascript
#1

Hi I’m relatively new to working with this, I have been able to get the .net scheduler working with the same MDF file andit saves etc, but when I try to link to a ms access db via odbc it gives the following error:

ERROR [22018] [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression

my code for the connector is:

Dim Connectr
Connectr = New dhtmlxSchedulerConnector(“Events”, “EventID”, DhtmlxDatabaseAdapterType.Odbc, ConfigurationManager.ConnectionStrings(“sample”).ToString, “FromDate”, “ToDate”, “text , details , Tags”)
Return Connectr

My Table lay is as follows:
EventID - Number(integer)
text - text(255)
details - text(255)
FromDate - datetime
ToDate - datetime
Tags - Text(255)

Does any know how to connect to a ms access db with scheduler connector or is there another way ?

#2

hi,
unfortunately, because of the lack of automatic type conversion in ms access database, connectors won’t work there

you can define all server-side actions manually, without connectors
here is some details about data loading and dataprocessor protocols
docs.dhtmlx.com/doku.php?id=dhtm … ntegration
docs.dhtmlx.com/doku.php?id=dhtm … gprinciple

#3

Thanks I did a type conversion to the date fields and the error disappeared but not data shows up in calendar.

the code is:
Connectr = New dhtmlxSchedulerConnector(“Events”, “EventID”, dhtmlxDatabaseAdapterType.Odbc, ConfigurationManager.ConnectionStrings(“sample”).ToString, “CDate(FromDate) as FDate”, “CDate(ToDate) as TDate”, “text , details , Tags”)

and the output from the schedulerconnector.ashx is:

<?xml version="1.0" encoding="utf-8" ?>
  • <start_date>
  • </start_date>
  • <end_date>
  • </end_date>
#4

I copied the output to a blank xml file and found that it would load once I got rid of the ‘-’ from the page. Is there any way to stop these from being generated ?

#5

do you mean that the connector generates not well-formed xml?
can you attach sample project where i can test it,
and file with output from schedulerconnector.ashx(not modified), please.

#6

I got rid of the ‘-’ from the page
Those do not generated by script - they are just formatting, which browser adds

#7

Hi,
I’ve attached the project files (the access db is linked via a system dsn named db1) and the output is as below:

2011-07-20 12:00:00 2011-07-20 15:00:00 Make release
Prepare dhtmlxScheduler and dhtmlxSchedulerConnector for release
DEV,Tests 2011-08-20 12:00:00 2011-08-20 15:00:00 Make release
Prepare dhtmlxScheduler and dhtmlxSchedulerConnector for release
DEV,Tests 2011-09-20 12:00:00 2011-09-20 15:00:00 Make release
Prepare dhtmlxScheduler and dhtmlxSchedulerConnector for release
DEV,Tests 2011-07-21 00:00:00 2011-07-22 00:00:00 Event description
Event location
Tests 2011-07-22 00:00:00 2011-07-23 00:00:00 Event description
Event location 2
Tests 2011-10-07 00:00:00 2011-10-08 00:00:00 New event
2011-10-16 00:00:00 2011-10-17 00:00:00 New event
2011-11-04 00:00:00 2011-11-12 00:00:00 Meeting

I’ve compared it to the output when its to the sample mdf source and it appears the same. There are no error messages but nothing displays in the calendar when its set to the access db.
WebApplication1.rar (689 KB)

#8

Does anyone have an idea why this is happening ? The xml output from schedulerconnector.ashx seems fine but no events show in the calendar, I’ve attached the project files if anyone is curious.

#9

hi,
seems like dynamic loading doesn’t work with odbc connector for access,
at least after i’ve commented
scheduler.setLoadMode(“month”);
events was shown in calendar, also i think you will need to define custom sql for insert/update operations, something like
Connectr.Request.CustomSQLs.Add(CustomSQLType.Update, "UPDATE Events SET text = ‘{text}’ WHERE EventID={EventID} ")
for now access returns syntax error in generated queries.
after all, i think define server-side CRUD logic may take less effort than make odbcConnector work with access

#10

Thank you Aliaksandr

How would you do an custom sql insert as I noticed new events don’t get saved when page is refreshed ?

#11

I’ve tried to add some custom sql as follows

Connectr.Request.CustomSQLs.Add(CustomSQLType.Insert, “INSERT INTO Events (EventID, FromDate, ToDate, text, details, Tags) VALUES ({EventID},{start_date},{end_date},’{text}’,’{details}’,’{Tags}’ WHERE EventID={EventID}”)
Connectr.Request.CustomSQLs.Add(CustomSQLType.Update, “UPDATE Events SET text=’{text}’ WHERE EventID={EventID}”)
Connectr.Request.CustomSQLs.Add(CustomSQLType.Delete, "DELETE FROM Events WHERE EventID={EventID} ")

The delete command works but the update and insert ones don’t. When I try to add or update an event and click save the text goes red, but if I refresh the change disappears.

#12

Ok I’ve kind of got the update field working for the text and details fields (I had to have text inside brackets due the reserved name in ms access) but I’m not sure where to get the information from for the start and end date. Does anyone know what the variables are called where I get this data from ? I’ve tried {start_date} or [end_date}.

The new code is

Connectr.Request.CustomSQLs.Add(CustomSQLType.Update, “UPDATE Events SET Events.[text]=’{text}’, Events.details=’{details}’, FromDate=#{start_date}#, ToDate=#{end_date}# WHERE Events.EventID={EventID}”)

#13

you should use the same name(or alias) as in connector’s definition, so for

Connectr = New dhtmlxSchedulerConnector("Events", "EventID", dhtmlxDatabaseAdapterType.Odbc, ConfigurationManager.ConnectionStrings("sample").ToString, "CDate(FromDate) as fdate", "CDate(ToDate) as tdate", "text , details , Tags")

it will be {fdate} and {tdate},
but i see a problem here, connector quotes field values, and
from #{fdate}# you will get #‘some_date’#, there is no way to avoid it, so here may be two solutions - create your own ashx handlers for create/update operations, or modify connector sources(DataRequest.cs->ParseSQLTemplate)

#14

I should be able to use CDate to convert any text output from the the scheduler to date value like I did with the select. But I tried that with the variable names you suggested and it still won’t update. I know CDate work within an update query as I tried it in the the database, I just got to find the right variable names.

#15

Hi I got it working for update, you were right about the variable names I just had an error in my xml date formatting that access couldn’t convert. but for new events that are added I still can’t add them, I can only assume that the date variables are different as they are new events instead of old ones being updated that would have the FDate and TDate variables.

#16

Got it all to work, I was trying to insert the eventid when that should have been an auto number within the access database.

working code looks like this if anyone is interested:
Dim Connectr
Connectr = New dhtmlxSchedulerConnector(“Events”, “EventID”, dhtmlxDatabaseAdapterType.Odbc, ConfigurationManager.ConnectionStrings(“sample”).ToString, “CDate(FromDate) as FDate”, “CDate(ToDate) as TDate”, “text , details , Tags”)
Connectr.Request.CustomSQLs.Add(CustomSQLType.Insert, “INSERT INTO Events (FromDate, ToDate, [text], details, Tags) VALUES ( CDate({FDate}), CDate({TDate}), ‘{text}’, ‘{details}’, ‘{Tags}’)”)
Connectr.Request.CustomSQLs.Add(CustomSQLType.Update, “UPDATE Events SET Events.[text]=’{text}’, Events.details=’{details}’, Events.Tags={Tags}, Events.FromDate=CDate({FDate}), Events.Todate=CDate({TDate}) WHERE Events.EventID={EventID}”)
Connectr.Request.CustomSQLs.Add(CustomSQLType.Delete, "DELETE FROM Events WHERE EventID={EventID} ")
Return (Connectr)

This will add, update and delete to a MS Access Database

#17

Everything was working fine in visual studio but when I publish it to my site, it won’t update the db. When I check the folder where the databases are the one for the calendar has a record locking file. Why would this be happening when the page updates fine when run from visual studio development server ?

#18

Hi,
not sure what is causing it, have found number of topics with similar issue(locked msaccess db on iis), but there was no any good solution
may be this will give you a hint
stackoverflow.com/questions/1015 … y-ldb-file
forums.asp.net/t/913427.aspx/1/10
this may be happening due some error in scripts, then then connection wouldn’t be closed and ldb file will not be deleted automatically, or may be you are missing some permissions settings for databases folder(but since your other databases work well, it’s unlikely…)

#19

Hi Aliaksandr,
Solved it the issue was with the permissions to the database I simply added it to the app data folder of the project and changed the DSN to point to that and everything works fine now. Thanks for the help.