Embeded SQL

[GrantMParticipant]

Is it possible to represent the SQL statement contained in the “value” column as a drop down list?

The SQL returns 1 column called “Category” with 6 different value but i would like these to be options on a dropdownlist.

Thank you.

[DimitarParticipant]

Hello GrantM,

The image you provided is not displayed and we are not sure how the SQL statement is contained in your grid column. Note, however, that you can have a dropdownlist editor for that column which is bound to a database (or any other data source type) through jqxDataAdapter.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[GrantMParticipant]

Sorry it loaded for me because i was logged in. Hopefully this shows:

The SQL is:

SELECT  'Customer' AS Category
UNION
SELECT  'GroupCode' AS Category
UNION
SELECT  'Sector' AS Category
UNION
SELECT  'Area' AS Category
UNION
SELECT  'SalesRep' AS Category
 UNION 
SELECT  'Country' AS Category

Basically that is a string inside the text box. I have the cell set up to be edited, its just that I would like the dropdown to be populated dynamically with the above SQL string if this is possible.

[DimitarParticipant]

Hi GrantM,

In the initeditor column callback function, we suggest you get the value of the cell (SQL string), create a data adapter instance and bind it to your database as per the SQL query. Finally, set the data adapter as a source to your dropdownlist cell editor.

Here is an example of binding a jqxDropDownList to a database that might be helpful to you: Bind DropDownList to MySQL Database.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[GrantMParticipant]

Hi Dimitar,

I dont fully understand your explanation but here is my current code that populates the grid.

							var paramsSource = {
								datatype: "json",
								datafields: [
									{ name: 'Page', type: 'string' },
									{ name: 'Parameter', type: 'string' },
									{ name: 'ParameterType', type: 'string' },
									{ name: 'OperatorType', type: 'string' },
									{ name: 'strSQL', type: 'string' }
								],
								id: 'id',
								url: 'includes/getParametersJSON.asp?page='+selectedModule
							};
							var paramsAdapter = new $.jqx.dataAdapter(paramsSource);
							$("#paramsGrid").jqxGrid({
								width: 440,
								height: 220,
								source: paramsAdapter,
								selectionmode: 'singlecell',
								editable: true,
								//autoheight: true,
								columnsresize: true,
								columns: [
									{ text: 'Parameter', datafield: 'Parameter', width: 270	},
									{ text: 'Value', datafield: 'strSQL', width: 170, columntype: 'dropdownlist',
											createeditor: function (row, column, editor) {
												// assign a new data source to the dropdownlist.
												var sqlString = $('#paramsGrid').jqxGrid('getcelltext', 0, "strSQL");
												editor.jqxDropDownList({ autoDropDownHeight: true, source: sqlString });
											},
											// update the editor's value before saving it.
											cellvaluechanging: function (row, column, columntype, oldvalue, newvalue) {
												// return the old value, if the new value is empty.
												if (newvalue == "") return oldvalue;
											}
									}
								]
							});

This doesnt work as intended. The drop down should be populated with the value of “sqlString” which when executed returns the following:

Category (Row Header)
Area
Country
Customer
GroupCode
SalesRep
Sector

So these options should be displayed in the dropdownlist. This way the drop down will be populated dynamically depending on the different sql statements.

Thanks for your help.

[DimitarParticipant]

Hi GrantM,

Please follow the suggestion I gave you. Your functionality has to be done in the initeditor callback function (which is called each time you start editing a cell). In it, you should create a data adapter instance (and probably pass the current cell value, i.e. the SQL statement as a parameter in the data adapter url). Finally, change the source of the editor to the newly created data adapter instance. The following help topic might help you better understand how to bind a jqxDropDownList to a MySQL database: http://www.jqwidgets.com/jquery-widgets-documentation/documentation/phpintegration/bind-jquery-dropdownlist-to-mysql-database-using-php.htm.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[GrantMParticipant]

I dont know what the initeditor function is, I cant find any examples of it being used or any documentation on it so i dont know how to use it or what stage to use it at. What are the parameters for it?

If as you say, i create a new instance of the dataadapter, how will it execute the SQL when normally a php file would do that and return the data? What datatype will i use for the source of the adapter?

[DimitarParticipant]

Hi GrantM,

You can find information about initeditor and its parameters in the jqxGrid API Documentation, under columns. This callback function is demonstrated in the example Custom Column Editor.

Your question about PHP and data adapter will be answered if you take a look at the code of the example I specified in my previous post where you can see both the client- and server-side settings.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[GrantMParticipant]

Thanks for the documentation.
I have followed your advice despite my head telling otherwise and tried using the initeditor callback with many different permitations of datatypes, columntypes, async and autobind, and still cant get this to work. 3 Days now and nothing works.

Im still at the same issue, the dropdownlist doesnt appear to populate because it isnt executing my SQL query. I created a simple array and used that as drop down source and that worked perfectly so i know it works.

Logically, I cant see how it would work without a PHP file using its connection object to connect to the database, execute, then return the data. How can setting the URL on the source as an SQL statement possibly work?

My code is now:

				var paramsSource = {
					datatype: "json",
					datafields: [
						{ name: 'Page', type: 'string' },
						{ name: 'Parameter', type: 'string' },
						{ name: 'ParameterType', type: 'string' },
						{ name: 'OperatorType', type: 'string' },
						{ name: 'strSQL', type: 'string' }
					],
					id: 'id',
					url: 'includes/getParametersJSON.asp?page='+selectedModule
				};
				var paramsAdapter = new $.jqx.dataAdapter(paramsSource);
				$("#paramsGrid").jqxGrid({
					width: 440,
					height: 220,
					source: paramsAdapter,
					selectionmode: 'singlecell',
					editable: true,
					columnsresize: true,
					columns: [
						{ text: 'Parameter', datafield: 'Parameter', width: 270	},
						{ text: 'Value', datafield: 'strSQL', width: 170, columntype: 'dropdownlist',
							initeditor: function (row, cellvalue, editor, celltext, pressedChar) {
								var cellComboSource = {
									datatype: "json",
									url: cellvalue,
									async: true,
									autoBind: true
								};
								var cellComboAdapter = new $.jqx.dataAdapter(cellComboSource);
								editor.jqxDropDownList({ autoDropDownHeight: true, source: cellComboAdapter });
							},
							cellvaluechanging: function (row, column, columntype, oldvalue, newvalue) {
								// return the old value, if the new value is empty.
								if (newvalue == "") return oldvalue;
							}
						}
					]
				});

[DimitarParticipant]

Hello GrantM,

I never stated that there should be no PHP file. In fact, I pointed you to an example where a PHP file is used to make the SQL connection. In your case, you should be doing the same, passing the URL to a PHP file (but with parameters based on the cell value) in the cellComboSource url field.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[GrantMParticipant]

Thats the information I needed! Finally making some progress on this, thanks for your help so far.

Lastly, i need to get the displayMember to show instead of the original “strSQL” string but i dont know what that is until i have created the new adapter. Also, each cell in the column will have a unique displayMember, so what syntax do i need to retrieve the returned column header in cellComboAdapter.

So basically i need to return “Category” from this:

[{"Category":"Area"},{"Category":"Country"},{"Category":"Customer"},{"Category":"GroupCode"},{"Category":"SalesRep"},{"Category":"Sector"}]

and “Year” from this:

[{"Year":2014},{"Year":2013},{"Year":2012},{"Year":2011},{"Year":2010},{"Year":2009}]

baring in mind the column header will potentially be different each time.

Apologies if im not explaining this too well.
Thanks

[DimitarParticipant]

Hi GrantM,

Do you mean that sometimes you will have a “Category” field and sometimes another field? If so, you would have to determine what field you will retrieve and set it as a displayMember (the dropdownlist cannot do this automatically). Maybe you will be able to somehow get it from the SQL query string by slicing it?

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[Author]

Posts