HELP! Coldfusion connector with MSSQL (not MySQL) and Grid

[vito_a]

I’m trying to test (before purchasing the Enterprise version) a simple connection to a SQL database with the Coldfusion connector so that I can display and edit a query result in a dhtmlxgrid.

I’ve searched through all the documentation and the forums and all I can find are pieces, but no step by step instructions of how the connector should be set up to connect to MSSQL (not MySQL).

I found this code, for example:

<cfset grid = createObject(“component”,“dhtmlXConnectors.GridConnector”).init(“database_name”,“MySQL”)>

I changed database_name to the one I use (“concerts”), and instead of MySQL I couldn’t find any support documentation, so I put “MSSQL” instead.

Following that line of code I have:
<cfset grid.render_table(“tblSpecialNotes”,“specialNoteID”,“specialNoteStartDate,SpecialNoteExpireDate”)>

where “tblSpecialNnotes” is the table in the database I am querying, and the remaining parameters after that represent a few of the columns in the table I want to display. My test.cfm page is blank, although a “View Source” shows an XML file with lots of CDATA and "row id=‘1’, etc (but no data from my table).

Can someone please provide a very simple, yet complete from to example of how to use the Coldfusion connector to connect to a MS-SQL database and render a grid with a few columns, and insert & delete row capability?

Here’s the code I have:

<cfset grid = createObject(“component”,“dhtmlXConnectors.GridConnector”).init(“concerts”,“MSSQL”)>

<cfset grid.render_table(“tblSpecialNotes”,“specialNoteID”,“specialNoteStartDate,SpecialNoteExpireDate”)>

Any help would be very much appreciated. Thanks!

[Ivan_CF]

Hi
the main idea of connector to create server-side support only. So grid in the client temlpate should be created without any connector usage.

In the attached sample you can find the working grid sample.
It contains two parts: server- and client-files.
Connector is created only in server file. Minimal call contains two lines of code: create component and render_table/render_sql methods.

the html part is a bit another one and you can see in the documentation how grid can be initialised and configured. The only two things you have to do here: connect the grid with the connector. And it is done with two functions (you can see them in the attached file:

mygrid.loadXML(“01_basic_connector.cfm”);
var dp = new dataProcessor(“01_basic_connector.cfm”);

Where 01_basic_connector.cfm - template which contains connector.
The documentation about it can be found here:
docs.dhtmlx.com/doku.php?id=dhtm … xconnector
Client-code and Server-code: these are two different parts, located in two different temlaptes
01_basic.zip (1.28 KB)
DB_MSSQL.zip (1.49 KB)

[vito_a]

Hi Ivan,

Thanks for the quick reply. I thought it might work that way after playing more with it yesterday. I downloaded your files and used them to modify mine.

My test.cfm file is the equivalent of your “01_basic_connector.cfm” and my “test.cfm” is the main file that equivalent to your “basic.html” file.

When I load the test.cfm in the browser I receive an error:

‘dataprocessor’ is undefined
test4.cfm

I’ve attached the files.

My database and DSN are the same name: “concerts”. The table I’m trying to read in the database is called “tblSpecialNotes”.

Thanks again for your help!
test4.zip (844 Bytes)
test.zip (259 Bytes)

[Ivan_CF]

Hi
Everythings is correct here except one thing:

(The file is located in dhtmlxDataprocessor folder or anywhere else (not sure about how your file system looks).

You didn’t add dataprocessor lib.
Basically when you create var dp = new dataProcessor(“test.cfm”); in the code it requires dataprocessor.
the dhtmlxgrid is responsible only for display of the grid. And dataprocessor makes it possible to save/insert/edit. They both use the same connector, that’s why you set it twice here: one for grid (display) and one for dataprocessor (updates).

[vito_a]

Thanks, Ivan! Adding the dhtmlxdataprocessor.js did fix the problem.

I can now see the data in the grid. One of the columns, however, is supposed to display a select (combo box) using 2 columns of the data from another table.

When I updated the test.cfm code to follow your example for combo boxes with server-side code, the grid on the test4.cfm page only fills data from the table used for the combo box, not the data from the original table.

Here is the updated “test.cfm” page which I’ve commented as far I understand what is taking place:

<?xml version="1.0" encoding="ISO-8859-1" ?>

<cfset list = createObject(“component”,“dhtmlXConnectors.GridConnector”).init(“concerts”,“MSSQL”)>

<cfset list.render_table(“tblConcertsWebPages”,“pageID”,“pageID(value),pageHeader(label)”)>

<cfset grid.set_options(“page_name”,list)>

<cfset grid.render_table(“tblSpecialNotes”,“specialNoteID”,“SpecialNoteID,page_name,SpecialNoteStartDate,SpecialNoteExpireDate,SpecialNote,SpecialNoteEnabled”)>

Can you please help with this last section? I think I’ll be able to handle it afterward. Thanks so much!

I’m trying to better understand if I can access and use multiple tables with the Coldfusion -> MS SQL connector. So far it looks like it has a lot of potential, and it seems that I’ll be able to make it work as we need it, so you’ll have our order for the Enterprise version next week!

[vito_a]

Ivan (or anyone else who may be able to help),

Just another update:

I tried to add a combo box to my grid by using the hardcoded example, but it didn’t work either:

<cfset grid = createObject(“component”,“dhtmlXConnectors.GridConnector”).init(“concerts”,“MSSQL”)>

<cfset grid.set_options(“item_nm”,param)>

<cfset grid.render_table(“tblSpecialNotes”,“specialNoteID”,“SpecialNoteID,item_nm,SpecialNoteStartDate,SpecialNoteExpireDate,SpecialNote,SpecialNoteEnabled”)>

Can you please let me know what I’ve overlooked? I’d prefer to add a combo from the table query as suggested by my previous reply if possible, but I’m unable to get either one to work.

Thanks again!

[Ivan_CF]

Regarding the first message:
You have a bit incorrect server side here:

1. At first you don’t need to set header - it will be set automatically.
2. There is special connector for options (such as you want to have in combo in grid and which are set with set_options later. So it would be correct to have in such way:

<cfset list = createObject("component","dhtmlXConnectors.OptionsConnector").init("concerts","MSSQL")> <cfset list.render_table("tblConcertsWebPages", "pageID","pageID(value),pageHeader(label)")>
(Here we create instance of options and show what table is usedm what field is used as value and what one as label)
3) Then we connect options to some column of the grid:

<cfset grid = createObject("component","dhtmlXConnectors.GridConnector").init("concerts","MSSQL")> <cfset grid.set_options("page_name",list)> <cfset grid.render_table("tblSpecialNotes","specialNoteID", "SpecialNoteID,page_name,SpecialNoteStartDate,SpecialNoteExpireDate,SpecialNote,SpecialNoteEnabled")>
Here we set option to the grid

So these 5 lines is enough for server side connector.
4) Also you should not forget about client side and have mygrid.setColTypes("…,co,…"); in the column in question. If you don’t do this - you will not see combo and as a result there will be no use in Options connector that was used.

[Ivan_CF]

Hi
Regarding thr last post: you should have combo (for example “co”) field type in the client side (in the same way as all other options are set for each column.
For example:

mygrid.setInitWidths("100,*") mygrid.setColTypes("co,co");
Here you set combo to both columns (setColTypes).

Also you can look into the server-side configuration of the grid. It is possible to define all the config data on server side with connectors but not on client-side script.

---

Basically here aer two differet parts:

1. dhtmlx library: all the component creations are described in the docs, it can work without connectors, but you have to create valid xml on server side.
2. cfm-connectors: it makes possible just to define some infromation on client-side script and it will generate all the required data.

so these two parts have to be updated simultaneously. If you add column, for example, you have to add column description in connector. If you add combo in connector - it should be set on client side too.

[vito_a]

Hi Ivan,
Thanks again for the fast response. I copied your example directly from your post into my “test.cfm” file, but when I load the test4.cfm file (which is the one where the grid is displayed) I receive a message window pop-up with an error (“Error occurred while processing request”) in it. This is what I’m using per your instructions for the test.cfm which is the “connector” page that talks to Coldfusion:

<cfset list = createObject(“component”,“dhtmlXConnectors.OptionsConnector”).init(“concerts”,“MSSQL”)>
<cfset list.render_table(“tblConcertsWebPages”, “pageID”,“pageID(value),pageHeader(label)”)>

<cfset grid = createObject(“component”,“dhtmlXConnectors.GridConnector”).init(“concerts”,“MSSQL”)>
<cfset grid.set_options(“page_name”,list)>
<cfset grid.render_table(“tblSpecialNotes”,“specialNoteID”,“SpecialNoteID,page_name,SpecialNoteStartDate,SpecialNoteExpireDate,SpecialNote,SpecialNoteEnabled”)>

Here is what I’m using in test4.cfm that gets the data from test.cfm and builds the grid:

</html

Can you please let me know where I’ve missed a step? It seemed to work correctly and the grid would be filled until I added the coding for the combo box.

Thank you so much!

[Ivan_CF]

Hi.
Locally my grid loads correctly. Could you way be send me the whole response with error if it exists? Or would be good to have two tables to see their structure (I’ve created locally, but you can have a bit different structure).
Also it is possible to switch the logging on:
<cfset grid.enable_log(variables,)>
(could you send me log file if you can’t fix the error). Or you can send me link to check the sample online - I will be able to see problem.
At first sight: it looks correct.

Ivan

[vito_a]

Hi Ivan,
Thanks again for the help. I have attached a ZIP file which contains the latest test.cfm and test4.cfm files I’m using. It also includes the SQL create scripts that shows the structure of the tables I am using. I am using MS SQL 2005.

You can see the actual error I am receiving by viewing the test4.cfm page at:

humphreysconcerts.com/test4.cfm

Perhaps it is something in my SQL table structure? With the “cfset list.render_table” I am only trying to use the first two columns from the table being read to generate the combo list (tblConcertsWebPages), although as you’ll see in the .SQL create file, more than two columns exist in that table. Could that cause a problem with the list.render_table code?

Thank you so much again for all your help!

Vito
test&test4.cfm+SQL_create_table_scripts.zip (2.13 KB)

[Ivan_CF]

Hi.
Sorry for beeing too late: I’ve missed your post because it went to another page.
Regarding your sample:

1. Your error appears because “pagename” field is not present in the DB. There is pageID field for this purpose.
2. While I was making your sample working I’ve fonud the next issues (some of them are yours and some of them are mine):
   test4.cfm

• connector.js should be included after the dhtmlxdataprocessor.js. It was done vice versa and because of this some errors occured.

• I’ve noticed that you moved the config to server-side and I’ve found one bug here: the setColTypes method should be present on both sides to make it working now. I think this bug will be fixed in the next release. But as a solution you just have to define this function both on client and server sides for now.

  test.cfm

Now after the initialization of connectors there is the following code:

[code]<cfset config.setHeader(“ID,Page,Start Date,Expire Date,Special Note,Enabled”)>
<cfset config.setColTypes(“ro,co,edtxt,edtxt,txt,ch”)>
<cfset config.setInitWidths(“40,80,150,150,400,70”)>

<cfset list.render_table(“tblConcertsWebPages”,“pageID”,“pageID(value),pageHeader(label)”)>
<cfset grid.set_config(config)>
<cfset grid.set_options(“pageID”,list)>

<cfset grid.event.attach(“beforeUpdate”,myUpdate)>
<cfset grid.enable_log(variables,getCurrentTemplatePath() & “_debug.log”)>
<cfset grid.render_table(“tblSpecialNotes”,“specialNoteID”,“SpecialNoteID,pageID,SpecialNoteStartDate,SpecialNoteExpireDate,SpecialNote,SpecialNoteEnabled”)>[/code]

As you can see “pageID” is used here instead of “pagename”.
Also one more change which is essential for you DB structure: you display the “SpecialNoteID” in the grid. It is primary key that can’t be updated (identity is set to “yes” in the DB). As a result on update of the grid you will see error that this field can’t be updated. There are two solutions:

1. Avoid this field in the output (it is an easy way - you can do this yourselves).
2. Remove the field from the “update” routine. for this - I’ve added event handler: beforeUpdate. It just removes the “specialNoteID” field from the field list (look function myUpdate for details). And it will work fine.

regards
Ivan

[Ivan_CF]

“grid.enable_log” can be removed. I’ve just added it to check the erasons of problems.