Active Query Builder support area

Prepopulate Expression Grid with partially populated rows

Avatar
  • updated
  • Completed

Ideally I need to be able to do this:

  1. Load metadata with connection string
  2. Automatically add specific table to query builder
  3. Populate Expression Grid with rows that have no expression, but with Alias (Column name) populated. This list is unrelated to the table selected.

The idea is to help the user by supplying all aliases that will be needed instead of having them type them manually.

I looked in examples such as AlternateNames, but the rows added are related to the table selected.

Is there a way to do this?

Thank you,

Simon

Avatar
Andrey Zavyalov, PM

Hello,

Unfortunately, it is not possible to do what you want. Of course, you can use JavaScript API of Active Query Builder ASP.NET edition 2.0 to create your own control, but it's not an easy task.

What we can suggest is to change the Alias column from a simple text edit box to combobox and prepopulate it with the list of values you need. The weak point is that users can select duplicate values or not select them at all. Do you need such customization?

Avatar
Simon

That was exactly my question in my other thread and I was told this was not possible:

Yes, if possible that would be a great solution actually.

Avatar
Denis Shundeev

The new version 2.10.14 implements new event QB.Web.Application.GridComponent.CustomEditControl.

Usage sample:

    <script>
        OnApplicationReady(function () {
            QB.Web.Application.GridComponent.CustomEditControl = function(key, cell, row) {
                var control = null;
                if (key == MetaData.FieldParamType.alias) {
                    var values = ["alias1", "alias2", "alias3", "alias4"];
                    control = $('<select>');
                    values.forEach(function(val, i){
                        $('<option value="'+val+'">'+val+'</option>').appendTo(control);
                    })
                }
                return control;
            }
        });
    </script>
Avatar
Simon

I am not able to get this to work, could you please give me more context?

  1. Which sample can I dump this in ?
  2. In which file exactly?
  3. Do I paste it as is or do I need to rename something?

Also: will it work in the trial version?

Thank you,

Simon

Avatar
Simon

I couldn't edit, so I am updating question here (ignore previous one).

Question 1:

I am not able to get this to work, could you please give me more context?

  1. Which sample can I dump this in ?
  2. In which file exactly?
  3. Do I paste it as is or do I need to rename something?

Question 2:

The sample code you provided on 03/04 adds option elements to en existing select element. Does this mean that the code only works where a select element already exists ? But the alias column does not have a combo. I am confused.

Question 3:

Will it work in the trial version since aliases are disabled?

Thank you,

Simon

Avatar
Andrey Zavyalov, PM

Hello, Simon.

Sorry for the late response. These days are holidays in Russia, so we couldn't answer promptly. We'll get back to work on 03/09.

Q1:

As far as I can see this code is intended to be pasted to the source code of your web page containing Active Query Builder. You don't need to change anything in this code except the list of values for alias.

Q2:

The sample code you provided on 03/04 adds option elements to en existing select element.

No, it is not correct. The options are added to the newly created <select> element:

Creating select:

control = $('<select>');

Adding elements to it:

$('<option value="'+val+'">'+val+'</option>').appendTo(control);

Returning it for use in a cell:

return control;

This event will be fired each time a new row is added to the Query Column List.

Q3:

Yes, it will work: you will see a combo box in the Alias cell. But after selecting a value, it won't be applied to the query, but replaced with random alias. This is the limitation of the trial version.

PS: The most probable reason why this code doesn't work is that you didn't updated Active Query Builder in your project to the latest version. Please install the latest version 2.10.14, open your project, in the References section make sure that versions of ActiveDatabaseSoftware.ActiveQueryBuilder.Web.* assemblies are of the latest version, then replace the necessary .js and .css files with the latest ones, clean and rebuild your project.

Avatar
Simon

Thank you for all the answers but unfortunately it's still not working.

  1. I had completely uninstalled the old version and installed 2.10.14 before trying
  2. The ActiveDatabaseSoftware.ActiveQueryBuilder.Web.* assemblies correctly point to 2.10.14
  3. I can step into the JS code and I don't see any errors

The alias column (I assume that is "Column Name") is not a combo box.

This is the code I am using. I modified the Default.aspx file of the "Client event handle" sample:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Samples.Default" %>
<%@ Register TagPrefix="uc" TagName="QueryBuilderUC" Src="ClientEventHandle.ascx" %>

<!DOCTYPE html>
<html>
<head id="Head1" runat="server">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>ActiveQueryBilder ASP.NET - Simple Offline Demo</title>
    <link href="jquery.jgrowl.css" type="text/css" rel="stylesheet" />
    <style type="text/css">

	</style>
</head>
<body>
    <script type="text/javascript" language="javascript" src="jquery.jgrowl.js"></script>
    <script type="text/javascript" language="javascript">
        //OnApplicationReady(function () {
        //	QB.Web.Application.Canvas.bind(QB.Web.Canvas.Events.CanvasOnAddTable, onAddTableToCanvas);
        //	QB.Web.Core.bind(QB.Web.Core.Events.DataSending, beforeDataExchange);
        //	QB.Web.Core.bind(QB.Web.Core.Events.DataReceived, afterDataExchange);
        //});

        OnApplicationReady(function () {
            QB.Web.Application.GridComponent.CustomEditControl = function (key, cell, row) {
                var control = null;
                if (key == MetaData.FieldParamType.alias) {
                    var values = ["alias1", "alias2", "alias3", "alias4"];
                    control = $('<select>');
                    values.forEach(function (val, i) {
                        $('<option value="' + val + '">' + val + '</option>').appendTo(control);
                    })
                }
                return control;
            }
        });

        function beforeDataExchange(e, data) {
            $.jGrowl("Before data exchange", { header: 'Core event' });
        }

        function afterDataExchange(e, data) {
            $.jGrowl("After data exchange", { header: 'Core event' });
        }

        function onAddTableToCanvas(sender, e) {
            $.jGrowl("Add table to canvas", { header: 'Canvas event' });
        }
    </script>
    <form id="Form1" runat="server">
        <uc:QueryBuilderUC ID="QueryBuilderUC1" runat="server" />
    </form>
</body>
</html>


Avatar
Andrey Zavyalov, PM

Thank you for detailed report.

We will investigate the problem and let you know about result tomorrow.

Avatar
Denis Shundeev

Hello, Simon.

Indeed, we've found that this code doesn't work in the current version, even it's technically possible make it work. Please accept my apologies for this mistake.

 

Avatar
Simon

Still not working...

I uninstalled 2.10.14 Trial and installed 2.10.14 Free.

I am trying to work with the "Client event handle" sample, but without adding any of the new code it doesn't run.

I want to load the XML just like the Trial version does. So I copied:

queryBuilder.MetadataContainer.ImportFromXML(Page.Server.MapPath(ConfigurationManager.AppSettings["XmlMetaData"]));

But this causes a compile error:

'QueryBuilder' does not contain a definition for 'MetadataContainer' and no extension method 'MetadataContainer' accepting a first argument of type 'QueryBuilder' could be found

So I tried to load a connection instead using:

System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection{
	ConnectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString
};

But then I get

An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.

I tried adding my Provider to the connection string (which should not be needed anyway) and it still doesn't work.

Can you please send me code that works? I have installed 5 different releases and spend a month on this very simple project.

Thank you