[C#] Problem sending through a datastructure

Dear readers,

I got a problem with my code
I'll first explain the problem my code is getting a datastructure from a twincat system through the Twincat.ADS libary getting the datastructure works perfectly fine without any problems only now i want to send it to a mysql database for as i see i could just call the datastructure in the query? but well it doesn't work as it should be maybe some of you could take a look into this and help me out it would be great.
Regards,
Touchwise

The Code:

PHP Code:

---

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Runtime.InteropServices;
using TwinCAT.Ads;
using MySql.Data.MySqlClient;
using System.Timers;


namespace Sample07
{
    /// <summary>
    /// Summary description for Form1.
    /// </summary>
    public class Form1 : System.Windows.Forms.Form
    {
        internal System.Windows.Forms.Button btnDeleteNotifications;
        internal System.Windows.Forms.Button btnAddNotifications;

        internal System.Windows.Forms.Button btnRead;
        internal System.Windows.Forms.GroupBox GroupBox3;
        internal System.Windows.Forms.TextBox tbComplexStruct_Datavalues;
        internal System.Windows.Forms.Label Label14;

        internal System.Windows.Forms.TextBox tbComplexStruct_stringVal;
        internal System.Windows.Forms.Label Label13;


        internal System.Windows.Forms.TextBox tbComplexStruct_Countervalues;
        internal System.Windows.Forms.Label Label12;
        private IContainer components;

        //PLC variable handles

        private int hcomplexStruct;
        private ArrayList notificationHandles;
        private System.Windows.Forms.Timer timer1;

        private TcAdsClient adsClient;

        public Form1()
        {
            //
            // Required for Windows Form Designer support
            //
            InitializeComponent();

            //
            // TODO: Add any constructor code after InitializeComponent call
            //
        }

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (components != null)
                {
                    components.Dispose();
                }
            }
            base.Dispose(disposing);
        }

        #region Windows Form Designer generated code
        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.components = new System.ComponentModel.Container();
            this.btnDeleteNotifications = new System.Windows.Forms.Button();
            this.btnAddNotifications = new System.Windows.Forms.Button();
            this.btnRead = new System.Windows.Forms.Button();
            this.GroupBox3 = new System.Windows.Forms.GroupBox();
            this.tbComplexStruct_Datavalues = new System.Windows.Forms.TextBox();
            this.Label14 = new System.Windows.Forms.Label();
            this.tbComplexStruct_stringVal = new System.Windows.Forms.TextBox();
            this.Label13 = new System.Windows.Forms.Label();
            this.tbComplexStruct_Countervalues = new System.Windows.Forms.TextBox();
            this.Label12 = new System.Windows.Forms.Label();
            this.timer1 = new System.Windows.Forms.Timer(this.components);
            this.GroupBox3.SuspendLayout();
            this.SuspendLayout();
            // 
            // btnDeleteNotifications
            // 
            this.btnDeleteNotifications.Location = new System.Drawing.Point(975, 96);
            this.btnDeleteNotifications.Name = "btnDeleteNotifications";
            this.btnDeleteNotifications.Size = new System.Drawing.Size(112, 23);
            this.btnDeleteNotifications.TabIndex = 13;
            this.btnDeleteNotifications.Text = "Delete Notifications";
            this.btnDeleteNotifications.Click += new System.EventHandler(this.btnDeleteNotifications_Click);
            // 
            // btnAddNotifications
            // 
            this.btnAddNotifications.Location = new System.Drawing.Point(975, 61);
            this.btnAddNotifications.Name = "btnAddNotifications";
            this.btnAddNotifications.Size = new System.Drawing.Size(112, 23);
            this.btnAddNotifications.TabIndex = 12;
            this.btnAddNotifications.Text = "Add Notifications";
            this.btnAddNotifications.Click += new System.EventHandler(this.btnAddNotifications_Click);
            // 
            // btnRead
            // 
            this.btnRead.Location = new System.Drawing.Point(975, 28);
            this.btnRead.Name = "btnRead";
            this.btnRead.Size = new System.Drawing.Size(112, 23);
            this.btnRead.TabIndex = 10;
            this.btnRead.Text = "Read";
            this.btnRead.Click += new System.EventHandler(this.btnRead_Click);
            // 
            // GroupBox3
            // 
            this.GroupBox3.Controls.Add(this.tbComplexStruct_Datavalues);
            this.GroupBox3.Controls.Add(this.Label14);
            this.GroupBox3.Controls.Add(this.tbComplexStruct_stringVal);
            this.GroupBox3.Controls.Add(this.Label13);
            this.GroupBox3.Controls.Add(this.tbComplexStruct_Countervalues);
            this.GroupBox3.Controls.Add(this.Label12);
            this.GroupBox3.Location = new System.Drawing.Point(12, 8);
            this.GroupBox3.Name = "GroupBox3";
            this.GroupBox3.Size = new System.Drawing.Size(957, 280);
            this.GroupBox3.TabIndex = 9;
            this.GroupBox3.TabStop = false;
            this.GroupBox3.Text = "Data Structure";
            this.GroupBox3.Enter += new System.EventHandler(this.GroupBox3_Enter);
            // 
            // tbComplexStruct_Datavalues
            // 
            this.tbComplexStruct_Datavalues.Location = new System.Drawing.Point(105, 53);
            this.tbComplexStruct_Datavalues.Name = "tbComplexStruct_Datavalues";
            this.tbComplexStruct_Datavalues.Size = new System.Drawing.Size(835, 20);
            this.tbComplexStruct_Datavalues.TabIndex = 20;
            this.tbComplexStruct_Datavalues.TextChanged += new System.EventHandler(this.tbComplexStruct_Datavalues_TextChanged);
            // 
            // Label14
            // 
            this.Label14.Location = new System.Drawing.Point(6, 60);
            this.Label14.Name = "Label14";
            this.Label14.Size = new System.Drawing.Size(99, 16);
            this.Label14.TabIndex = 19;
            this.Label14.Text = "Data array:";
            this.Label14.Click += new System.EventHandler(this.Label14_Click);
            // 
            // tbComplexStruct_stringVal
            // 
            this.tbComplexStruct_stringVal.Location = new System.Drawing.Point(105, 105);
            this.tbComplexStruct_stringVal.Name = "tbComplexStruct_stringVal";
            this.tbComplexStruct_stringVal.Size = new System.Drawing.Size(835, 20);
            this.tbComplexStruct_stringVal.TabIndex = 11;
            this.tbComplexStruct_stringVal.TextChanged += new System.EventHandler(this.tbComplexStruct_stringVal_TextChanged);
            // 
            // Label13
            // 
            this.Label13.Location = new System.Drawing.Point(6, 105);
            this.Label13.Name = "Label13";
            this.Label13.Size = new System.Drawing.Size(109, 16);
            this.Label13.TabIndex = 10;
            this.Label13.Text = "stringValues:";
            this.Label13.Click += new System.EventHandler(this.Label13_Click);
            // 
            // tbComplexStruct_Countervalues
            // 
            this.tbComplexStruct_Countervalues.Location = new System.Drawing.Point(105, 160);
            this.tbComplexStruct_Countervalues.Name = "tbComplexStruct_Countervalues";
            this.tbComplexStruct_Countervalues.Size = new System.Drawing.Size(835, 20);
            this.tbComplexStruct_Countervalues.TabIndex = 18;
            this.tbComplexStruct_Countervalues.TextChanged += new System.EventHandler(this.tbComplexStruct_Countervalues_TextChanged);
            // 
            // Label12
            // 
            this.Label12.Location = new System.Drawing.Point(6, 157);
            this.Label12.Name = "Label12";
            this.Label12.Size = new System.Drawing.Size(100, 23);
            this.Label12.TabIndex = 17;
            this.Label12.Text = "Counter array:";
            this.Label12.Click += new System.EventHandler(this.Label12_Click);
            // 
            // timer1
            // 
            this.timer1.Enabled = true;
            this.timer1.Interval = 10000;
            this.timer1.Tick += new System.EventHandler(this.btnRead_Click);
            // 
            // Form1
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(1181, 302);
            this.Controls.Add(this.btnDeleteNotifications);
            this.Controls.Add(this.btnAddNotifications);
            this.Controls.Add(this.btnRead);
            this.Controls.Add(this.GroupBox3);
            this.Name = "Form1";
            this.Text = "Readout PLC";
            this.Closing += new System.ComponentModel.CancelEventHandler(this.Form1_Closing);
            this.Load += new System.EventHandler(this.Form1_Load);
            this.GroupBox3.ResumeLayout(false);
            this.GroupBox3.PerformLayout();
            this.ResumeLayout(false);

        }
        #endregion

        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.Run(new Form1());
        }

        private void Form1_Load(object sender, System.EventArgs e)
        {
            adsClient = new TcAdsClient();
            notificationHandles = new ArrayList();
            try
            {
                adsClient.Connect(801);
                adsClient.AdsNotificationEx += new AdsNotificationExEventHandler(adsClient_AdsNotificationEx);
                btnDeleteNotifications.Enabled = false;
                //create handles for the PLC variables;

                hcomplexStruct = adsClient.CreateVariableHandle("MAIN.ComplexStruct1");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void Form1_Closing(object sender, System.ComponentModel.CancelEventArgs e)
        {
            adsClient.Dispose();
        }


        private void btnRead_Click(object sender, System.EventArgs e)
        {
            Main2();
            try
               
            {
                //read by handle
                //the second parameter specifies the type of the variable

                FillStructControls((ComplexStruct)adsClient.ReadAny(hcomplexStruct, typeof(ComplexStruct)));
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void Main2()
        {
    {
        string cs = @"server=localhost;userid=root;
            password=flex;database=mydb";

        MySqlConnection conn = null;
        MySqlDataReader rdr = null;

        try
        {
            conn = new MySqlConnection(cs);
            conn.Open();

            using (MySqlCommand command = (new MySqlCommand("UPDATE mycontacts(tbComplexStruct_Datavalues.Text)", conn)))
                 //(MySqlCommand ExecuteNonQuery()("INSERT INTO  mycontacts  VALUES(@P, '')");
                 //(MySqlParameter= command.Parameters.Add("@P", MySqlDbType.Int16));
              //  command.Prepare();

    // for (int i = 0; i < jointArray.Length; i++) {
            {
              //  MySqlCommand Value = tbComplexStruct_Datavalues.Text;

          
   //   command.ExecuteNonQuery();("INSERT INTO mycontacts (a, b, c) VALUES (?a, ?b,?c)", conn)
    //       {
 //               var nameParameter = new MySqlParameter("a", conn);
 //               var idParameter = new MySqlParameter("b", conn);
 //               var Parameter = new MySqlParameter("c", conn);
 //
//               //tbComplexStruct_Datavalues
//
 //               command.Parameters.Add(nameParameter);
  //              command.Parameters.Add(idParameter);
  //              command.Parameters.Add(Parameter);

//                for (int i = 0; i < "a".Length; i++)
 //               {
  //                  nameParameter.Value = "a"[i];
   //                 idParameter.Value = "b"[i];
    //                Parameter.Value = "c"[i];
     //               command.ExecuteNonQuery();
     //           }




 //   using (SqlCommand command = new SqlCommand("insert into [" + folderName + "] values(@P, '')", sql) {

 //     SqlParameter param = command.Parameters.Add("@P", SqlDbType.VarChar, 50);
 //     command.Prepare();

  //    for (int i = 0; i < jointArray.Length; i++) {
   //     param.Value = jointArray[i].ToString();
   //     command.ExecuteNonQuery();
 //     }

 //   }
 // }
//}/




















            }
        }
        catch (MySqlException ex)
        {
            Console.WriteLine("Error: {0}", ex.ToString());

        }
        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }

            if (conn != null)
            {
                conn.Close();
            }

        }
    }

      }

        private void btnAddNotifications_Click(object sender, System.EventArgs e)
        {
            notificationHandles.Clear();
            try
            {
                //register notification            

                notificationHandles.Add(adsClient.AddDeviceNotificationEx("MAIN.complexStruct1", AdsTransMode.OnChange, 100, 0, Datavalues, typeof(ComplexStruct)));
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            btnDeleteNotifications.Enabled = true;
            btnAddNotifications.Enabled = false;
        }

        private void btnDeleteNotifications_Click(object sender, System.EventArgs e)
        {
            //delete registered notifications.
            try
            {
                foreach (int handle in notificationHandles)
                    adsClient.DeleteDeviceNotification(handle);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            notificationHandles.Clear();
            btnAddNotifications.Enabled = true;
            btnDeleteNotifications.Enabled = false;
        }

        private void adsClient_AdsNotificationEx(object sender, AdsNotificationExEventArgs e)
        {
            TextBox textBox = (TextBox)e.UserData;
            Type type = e.Value.GetType();
            if (type == typeof(string) || type.IsPrimitive)
                textBox.Text = e.Value.ToString();
            else if (type == typeof(ComplexStruct))
                FillStructControls((ComplexStruct)e.Value);
        }

        private void FillStructControls(ComplexStruct structure)
        {

            tbComplexStruct_Datavalues.Text = String.Format(
                "{0:d}, {1:d}, {2:d}, {3:d}, {4:d}, {5:d}, {6:d}, {7:d}, {8:d}, {9:d}, {10:d}, {11:d}, {12:d}, {13:d}, {14:d}, {15:d}, {16:d}, {17:d}, {18:d}, {19:d}, {20:d}, {21:d}, {22:d}, {23:d}, {24:d},{25:d}, {26:d}, {27:d}, {28:d},{29:d},{30:d},{31:d}, {32:d}, {33:d}, {34:d},{35:d}, {36:d}, {37:d}, {38:d},{39:d}, {40:d}, {41:d}, {42:d}, {43:d}, {44:d},{45:d}, {46:d}, {47:d}, {48:d},{49:d}, {50:d}, {51:d}, {52:d}, {53:d}, {54:d},{55:d}, {56:d}, {57:d}, {58:d},{59:d}, {60:d}, {51:d}, {52:d}, {53:d}, {54:d},{55:d}, {56:d}, {57:d}, {58:d},{59:d}, {60:d}, {61:d}, {62:d}, {63:d}, {64:d},{65:d}, {66:d}, {67:d}, {68:d},{69:d}, {70:d}, {71:d}, {72:d}, {73:d}, {74:d},{75:d}, {76:d}, {77:d}, {78:d},{79:d}, {80:d}, {81:d}, {82:d}, {83:d}, {84:d},{85:d}, {86:d}, {87:d}, {88:d},{89:d}, {90:d}, {91:d}, {92:d}, {93:d}, {94:d},{95:d}, {96:d}, {97:d}, {98:d},{99:d}, {100:d}"

                , structure.Datavalues[0], structure.Datavalues[1], structure.Datavalues[2], structure.Datavalues[3],
                structure.Datavalues[4], structure.Datavalues[5], structure.Datavalues[6], structure.Datavalues[7],
                structure.Datavalues[8], structure.Datavalues[9], structure.Datavalues[10], structure.Datavalues[11],
                structure.Datavalues[12], structure.Datavalues[13], structure.Datavalues[14], structure.Datavalues[15],
                structure.Datavalues[16], structure.Datavalues[17], structure.Datavalues[18], structure.Datavalues[19],
                structure.Datavalues[20], structure.Datavalues[21], structure.Datavalues[22], structure.Datavalues[23],
                structure.Datavalues[24], structure.Datavalues[25], structure.Datavalues[26], structure.Datavalues[27],
                structure.Datavalues[28], structure.Datavalues[29], structure.Datavalues[30], structure.Datavalues[31],
                structure.Datavalues[32], structure.Datavalues[33], structure.Datavalues[34], structure.Datavalues[35],
                structure.Datavalues[36], structure.Datavalues[37], structure.Datavalues[38], structure.Datavalues[39],
                structure.Datavalues[40], structure.Datavalues[11], structure.Datavalues[42], structure.Datavalues[43],
                structure.Datavalues[44], structure.Datavalues[45], structure.Datavalues[46], structure.Datavalues[47],
                structure.Datavalues[48], structure.Datavalues[49], structure.Datavalues[50], structure.Datavalues[51],
                structure.Datavalues[52], structure.Datavalues[53], structure.Datavalues[54], structure.Datavalues[55],
                structure.Datavalues[56], structure.Datavalues[57], structure.Datavalues[58], structure.Datavalues[59],
                structure.Datavalues[60], structure.Datavalues[61], structure.Datavalues[62], structure.Datavalues[63],
                structure.Datavalues[64], structure.Datavalues[65], structure.Datavalues[66], structure.Datavalues[67],
                structure.Datavalues[68], structure.Datavalues[69], structure.Datavalues[70], structure.Datavalues[71],
                structure.Datavalues[72], structure.Datavalues[73], structure.Datavalues[74], structure.Datavalues[75],
                structure.Datavalues[76], structure.Datavalues[77], structure.Datavalues[78], structure.Datavalues[79],
                structure.Datavalues[80], structure.Datavalues[81], structure.Datavalues[82], structure.Datavalues[83],
                 structure.Datavalues[84], structure.Datavalues[85], structure.Datavalues[86], structure.Datavalues[87],
                structure.Datavalues[88], structure.Datavalues[89], structure.Datavalues[90], structure.Datavalues[91],
                structure.Datavalues[92], structure.Datavalues[93], structure.Datavalues[94], structure.Datavalues[95],
                structure.Datavalues[96], structure.Datavalues[97], structure.Datavalues[98], structure.Datavalues[99],
                structure.Datavalues[100]
                );

            tbComplexStruct_stringVal.Text = structure.stringVal;


            tbComplexStruct_Countervalues.Text = String.Format(
                "{0:d}, {1:d}, {2:d}, {3:d}, {4:d}, {5:d}, {6:d}, {7:d}, {8:d}, {9:d}, {10:d}"

             , structure.Countervalues[0], structure.Countervalues[1], structure.Countervalues[2], structure.Countervalues[3],
             structure.Countervalues[4], structure.Countervalues[5], structure.Countervalues[6], structure.Countervalues[7],
             structure.Countervalues[8], structure.Countervalues[9], structure.Countervalues[10]);

        }

        private ComplexStruct GetStructFromControls()
        {
            ComplexStruct structure = new ComplexStruct();
            String[] stringArr = tbComplexStruct_Datavalues.Text.Split(new char[] { ',' });

            for (short i = 0; i < stringArr.Length; i++)
                structure.Datavalues[i] = short.Parse(stringArr[i]);


            structure.stringVal = tbComplexStruct_stringVal.Text;

            String[] stringArra = tbComplexStruct_Countervalues.Text.Split(new char[] { ',' });
            for (int i = 0; i < stringArra.Length; i++)
                structure.Countervalues[i] = int.Parse(stringArra[i]);

            return structure;
        }

        public object Datavalues { get; set; }

        private void tbComplexStruct_Datavalues_TextChanged(object sender, EventArgs e)
        {
        }

        private void Label14_Click(object sender, EventArgs e)
        {
        }

        private void tbComplexStruct_stringVal_TextChanged(object sender, EventArgs e)
        {
        }

        private void tbComplexStruct_Countervalues_TextChanged(object sender, EventArgs e)
        {
        }

        private void Label12_Click(object sender, EventArgs e)
        {
        }

        private void Label13_Click(object sender, EventArgs e)
        {

        }

        private void GroupBox3_Enter(object sender, EventArgs e)
        {

        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }

        private void timer1_Tick(object sender, EventArgs e)
        {

        }
    }

    [StructLayout(LayoutKind.Sequential, Pack = 1)]
    public class ComplexStruct
    {
        //specifies how .NET should marshal the array
        //SizeConst specifies the number of elements the array has.
        [MarshalAs(UnmanagedType.ByValArray, SizeConst = 101)]
        public short[] Datavalues = new short[101];

        //specifies how .NET should marshal the string
        //SizeConst specifies the number of characters the string has.
        //'(inclusive the terminating null ). 
        [MarshalAs(UnmanagedType.ByValTStr, SizeConst = 81)]
        public string stringVal = "";

        //SizeConst specifies the number of elements the array has.
        [MarshalAs(UnmanagedType.ByValArray, SizeConst = 11)]
        public int[] Countervalues = new int[11];
    }
} 


---

Looking at your code right now, it won't run because you haven't executed your query.

Why don't you put the code back to the state it was in when it was running, but not giving you the result you expected. Then, tell us what errors you're getting (if any) and what the result of the program is.

The whole program runs perfect everything works expect executing the query it should get all the information to fill the database form the datastruct

Code:

---

using (MySqlCommand command = (new MySqlCommand("UPDATE mycontacts(tbComplexStruct_Datavalues.Text)", conn)))
                //(MySqlCommand ExecuteNonQuery()("INSERT INTO  mycontacts  VALUES(@P, '')");
                //(MySqlParameter= command.Parameters.Add("@P", MySqlDbType.Int16));
              //  command.Prepare();

    // for (int i = 0; i < jointArray.Length; i++) {
            {
              //  MySqlCommand Value = tbComplexStruct_Datavalues.Text;

         
  //  command.ExecuteNonQuery();("INSERT INTO mycontacts (a, b, c) VALUES (?a, ?b,?c)", conn)
    //      {
 //              var nameParameter = new MySqlParameter("a", conn);
 //              var idParameter = new MySqlParameter("b", conn);
 //              var Parameter = new MySqlParameter("c", conn);
 //
//              //tbComplexStruct_Datavalues
//
 //              command.Parameters.Add(nameParameter);
  //              command.Parameters.Add(idParameter);
  //              command.Parameters.Add(Parameter);

//                for (int i = 0; i < "a".Length; i++)
 //              {
  //                  nameParameter.Value = "a"[i];
  //                idParameter.Value = "b"[i];
    //                Parameter.Value = "c"[i];
    //              command.ExecuteNonQuery();
    //          }




 //  using (SqlCommand command = new SqlCommand("insert into [" + folderName + "] values(@P, '')", sql) {

 //    SqlParameter param = command.Parameters.Add("@P", SqlDbType.VarChar, 50);
 //    command.Prepare();

  //    for (int i = 0; i < jointArray.Length; i++) {
  //    param.Value = jointArray[i].ToString();
  //    command.ExecuteNonQuery();
 //    }

 //  }
 // }
//}/

---

That part there is all commented out. You're not doing anything with the query except specifying a command string, which won't do what you think it will do.


This line

Code:

---

"UPDATE mycontacts(tbComplexStruct_Datavalues.Text)"

---

Is an invalid update. An example of an update statement would be like

"UPDATE mycontacts set x='test' where id='1'"

It seems to me, you're trying to update your table based upon whatever is in tbComplexStruct_Datavalues.Text. Can you paste example data of what would be in there?

Well it's a structure from a twincat system it are just int's wich it gets from the twincat system. Wich i want to send to my db the db column names are set up like : A - AB - AC - AD till CZ or something it gets the values perfectly but now i need to fill the columns in my db how to do this?

As he already said, your MySQL statement is invalid.

Read his post and you'll get your answer...