Thank you DJ, I also think I cannot use the cmd to return my SQL response. And I think I need to use a loop to fill an array if I get more then one hit with the query. i need to slow down and think the process through.

Yeah - wait for ptp's response, he'll know. I'm not in front of visual studio to assist. He'll also need to check for a null if no records match and return a blank string

@ptp, Ok my friend where did I go wrong. (besides trying this in the first place) Here is my code. When I debug from VisualStudio, I get unknown function. I did add my plugin to my project and see the blank form. I get no errors when I build the project.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Data.Odbc;

namespace DSNConnectQuery
{
    public partial class FormMain :EZ_Builder.UCForms.FormPluginMaster {

        public FormMain() {

            InitializeComponent();
        }
        private void FormMain_Load(object sender, EventArgs e)
        {

            // Intercept all unknown functions called from any  EZ-Script globally.
            // If a function is called that doesn't exist in the  EZ-Script library, this event will execute
            ExpressionEvaluation.FunctionEval.AdditionalFunctionEvent += FunctionEval_AdditionalFunctionEvent;
        }

        private void FormMain_FormClosing(object sender, FormClosingEventArgs e)
        {

            // Disconnect from the function event
            ExpressionEvaluation.FunctionEval.AdditionalFunctionEvent -= FunctionEval_AdditionalFunctionEvent;
        }

        /// 
        /// This is executed when a function is specified in any ez-scripting that isn't a native function.
        /// You can check to see if the function that was called is your function.
        /// If it is, do something and return something.
        /// If you don't return something, a default value of TRUE is returned.
        /// If you throw an exception, the  EZ-Script control will receive the exception and present the error to the user.
        /// 
        private void FunctionEval_AdditionalFunctionEvent(object sender, ExpressionEvaluation.AdditionalFunctionEventArgs e)
        {

            // Check if the function is our function (DSNQuery)
            if (e.Name.Equals("DSNQuery", StringComparison.InvariantCultureIgnoreCase))
            {

                // Check if the correct number of parameters were passed to this function
                if (e.Parameters.Length != 2)
                    throw new Exception("Expects 2 parameters. Usage: DSNQuery(''QueryString'',''DSNname''");

                // Do something
                string myDSN = Convert.ToString(e.Parameters[1]);
                string myQuery = Convert.ToString(e.Parameters[0]);
               
                using (var cn = new OdbcConnection("dsn=" + myDSN))
                {
                    Array[] meta = new Array[10];
                    Array[] myArray = new Array[10];
                    bool read;
                    OdbcCommand command = new OdbcCommand(myQuery, cn);
                    cn.Open();
                    OdbcDataReader reader = command.ExecuteReader();
                    if (reader.Read() == true)
                    {
                        do
                        {
                            int NumberOfCollumns = reader.GetValues(meta);
                            for (int i = 0; i < NumberOfCollumns; i++)
                                myArray[i] = meta[i];
                                read = reader.Read();
                        }
                        while (read == true);
                    }
                    reader.Close();
                    cn.Close();
                    e.ReturnValue = myArray;
                    
                }
                    }
            return;

        }
            }
        }

Didn’t add the event to the ezscript engine. Most likely because form main load and form main closing aren’t actually assigned to their events.

Select the form main. In the event Browser select those events to assign to those methods.

You are going to have to give me more then that. I see FormMain_Load and FormMain_FormClosing but do not understand what you want me to do with them. Sorry this C# is all new to me yet.

@rz90208: you are progressing in the right direction, almost there.

Opening Visual Studio, I'll code the missing logic, give me a couple minutes.

@rz90208: I added a new function QueryData to the Miscellaneous Utility Plugin: https://synthiam.com/redirect/legacy?table=plugin&id=293

Source Code: https://github.com/ppedro74/ezrobot-playground/blob/master/plugins/MiscUtilityPlugin/MiscUtilityPlugin/MainForm.cs

I'm running out time, so minimal documentation, and basic tests no bullet proof code.

Copied from the plugin page*

version 4 : Added: DataQuery function Syntax: DataQuery (connection, query, [limit], [output variable], [query argument 0], [query argument 1], [query argument n]) => Int32 Connection: Provider Name and connection string. Query: SQL Query Limit: Max Number of records to return. Default 100 Output Variable: Name of Array Variable. Default is $data_ . $data_0 array for column 0 values, $data_1 array for column 1 values, etc. Query Argument 0: Value for query Parameter @0, @1, etc. Returns: Number Of Rows Some Examples:

Excel File (Note Excel 97/2003 format) Excel1.zip

SQL 2012 server Query:

$connection="System.Data.SqlClient|Integrated Security=SSPI;Data Source=.\SQL2012;Initial Catalog=Test1;"
$sql="SELECT * FROM table1;"
$totalRows=DataQuery($connection, $sql)
print($totalRows)

ODBC User DSN MyDSN1 Query :

$connection="System.Data.Odbc|DSN=MyDSN1;"
$sql="SELECT * FROM [Sheet1$];"
$totalRows=DataQuery($connection, $sql, 15)
print($totalRows)

ODBC excel 97/2003 Query:

$connection="System.Data.Odbc|Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\Users\ptp\Desktop\Excel1.xls;"
$sql="SELECT * FROM [Sheet1$];"
$totalRows=DataQuery($connection, $sql, 15)
print($totalRows)

OLEDB excel 97/2003 Query: Paging the results, using a query parameter @0 for $LastId

$connection="System.Data.OleDb|Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Users\ptp\Desktop\Excel1.xls;Extended Properties='Excel 8.0;HDR=YES;';"
$sql="SELECT [Id],[Int1],[Date1],[Text1],[DateTime1] FROM [Sheet1$] WHERE [Id]>@0;"
$lastId=0
:loop

Print("Start Id=" + $LastId)
$totalRows=DataQuery($connection, $sql, 10, "$myData", $lastId)
Print("TotalRows=" + $TotalRows)

IF ($totalRows>0)
  Print("Rx : Id | Int1 | Date1 | Text1 | DateTime1")
  REPEAT ($rx, 0, $totalRows-1, 1)
    Print($rx + " : " + $myData0[$rx] + " | " + $myData1[$rx] + " | " + $myData2[$rx] + " | " + $myData3[$rx] + " | " + $myData4[$rx])
  ENDREPEAT
  $lastId=$myData0[$totalRows-1]
  
  print("LastId=" + $LastId)
  Goto(loop)
ENDIF

Try it and let me know the feedback.

Ptp’s got you covered.

As for learning events, google tutorials are great. If you look at the code which you’ve copy and pasted from my tutorial, there’s a formmain_load and formmain_closing

Those aren’t being executed because they’re not connected to anything. Events in object oriented programming are executed when something else executed them. In those case, by their names, loading the form and closing the form.

You need to tell the form to execute those functions when the form loads and when the form is closing.

Do that in the events list of the properties of the form. Locate the form loading and form closing options and select the appropriate methods from the drop down.

Look at this video. It’s terriblr quality but he shows exactly how to find the events for a form: https://youtu.be/LbgO1lZ6WRI

All graphic objects have events. You can explore what events each object has. For example a button has an OnClick event for when it’s pressed