title: SQL update using data collected by C# from Arduino Serial
body: <p>I am trying to write a simple program in C# that will read data output from an Arduino connected via serial, lift the relevant data out of the text strings which are formatted for ease-of-understanding in the Serial Monitor, display the two pieces of information in two read-only text boxes that act as counters (overwrite every time the information is updated), and UPDATE an SQL table with these figures.</p>
  
  <hr>
  
  <p>Example:</p>
  
  <p>Arduino outputs: </p>
  
  <pre><code>'Day Users: 234231'
  'All Users: 433241'
  </code></pre>
  
  <p>C# displays full message in textbox1</p>
  
  <p>C# displays 234231 in textbox2</p>
  
  <p>C# displays 433241 in textbox3</p>
  
  <p>C# UPDATES SQL table with something like:</p>
  
  <pre><code>SqlCommand myCommand = new SqlCommand("UPDATE USER_NUMBERS
  SET NUMBERZ= 234231 
  WHERE UPTIME = DAYHK");
  </code></pre>
  
  <p>But obviously, '234231' is a variable, specified by the input from the Arduino, which then informs the content of both textbox2 and the value needed to update the SQL table.</p>
  
  <hr>
  
  <p>I'm very new to C#, but so far I have a program which can read the serial data from the Arduino and put the entire output into textbox1, but no more.</p>
  
  <p>How can I specify a part of the incoming data that I want assigned to a value to be the text output in both textbox2 (or textbox3), and then use it to do both that and an update of the value held in my SQL table?</p>
  
  <p>My code so far is thus:</p>
  
  <pre><code>    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;
  
  namespace Serial_Reader
  {
      public partial class Form1 : Form
      {
  
          string RxString;
  
          public Form1()
          {
              InitializeComponent();
          }
  
          private void buttonStart_Click(object sender, EventArgs e)
          {
              serialPort1.PortName = "COM3";
              serialPort1.BaudRate = 9600;
  
              serialPort1.Open();
              if (serialPort1.IsOpen)
              {
                  buttonStart.Enabled = false;
                  buttonStop.Enabled = true;
                  buttonSQL.Enabled = true;
                  textBox1.ReadOnly = false;
              }
          }
  
          private void buttonStop_Click(object sender, EventArgs e)
          {
              if (serialPort1.IsOpen)
              {
                  serialPort1.Close();
                  buttonStart.Enabled = true;
                  buttonStop.Enabled = false;
                  buttonSQL.Enabled = false;
                  textBox1.ReadOnly = true;
              }
          }
  
          private void textBox1_KeyPress(object sender, KeyPressEventArgs e)
          {
              // If the port is closed, don't try to send a character.
  
              if (!serialPort1.IsOpen) return;
  
              // If the port is Open, declare a char[] array with one element.
              char[] buff = new char[1];
  
              // Load element 0 with the key character.
  
              buff[0] = e.KeyChar;
  
              // Send the one character buffer.
              serialPort1.Write(buff, 0, 1);
  
              // Set the KeyPress event as handled so the character won't
              // display locally. If you want it to display, omit the next line.
              e.Handled = true;
          }
  
          private void serialPort1_DataReceived(object sender, System.IO.Ports.SerialDataReceivedEventArgs e)
          {
              RxString = serialPort1.ReadExisting();
              this.Invoke(new EventHandler(DisplayText));
          }
  
          private void Form1_FormClosing(object sender, FormClosingEventArgs e)
          {
              if (serialPort1.IsOpen) serialPort1.Close();
          }
  
          private void DisplayText(object sender, EventArgs e)
          {
              textBox1.AppendText(RxString);
          }
  
          private void buttonSQL_Click(object sender, EventArgs e)
          {
  
          }
  
      }
   }
  </code></pre>
  
  <p>As you can see from 'my' adept commenting, but inept programming, I am using code from the brilliant tutorial <a href="http://csharp.simpleserial.com" rel="nofollow">here</a>: and all that my program will have extra is a button to begin the SQL communication, stopped when the Serial comms are stopped, and two extra textboxes.</p>
  
  <hr>
  
  <p><strong>EDIT</strong>: </p>
  
  <p>The input is in the format:</p>
  
  <pre><code>Someone got up from seat number 9
  All-time users on seat number 9 is: 5
  Today's total users: 17
  All-time total users: 17
  </code></pre>
  
  <p>So that non-programmers checking the serial monitor can easily understand the output. The first line is always either 'Someone got up from' or 'Someone sat down on', on the second line, only the numbers change, and again on the third and fourth lines, but the numbers on the third and fourth lines (in this case the same because the system has been running less than a day) are the ones that need to be parsed.</p>
  
  <hr>
  
  <p><strong>EDIT 2</strong> (New Code):</p>
  
  <pre><code>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 System.Data.SqlClient;
  
  namespace Serial_Reader
  {
      public partial class Form1 : Form
      {
  
          string RxString;
  
          public Form1()
          {
                InitializeComponent();
          }
  
          private void buttonStart_Click(object sender, EventArgs e)
          {
              serialPort1.PortName = "COM3";
              serialPort1.BaudRate = 9600;
  
              serialPort1.Open();
              if (serialPort1.IsOpen)
              {
                  buttonStart.Enabled = false;
                  buttonStop.Enabled = true;
                  buttonSQL.Enabled = true;
                  textBox1.ReadOnly = true;
              }
          }
  
          private void buttonStop_Click(object sender, EventArgs e)
          {
              if (serialPort1.IsOpen)
              {
                  serialPort1.Close();
                  buttonStart.Enabled = true;
                  buttonStop.Enabled = false;
                  buttonSQL.Enabled = false;
                  textBox1.ReadOnly = true;
              }
          }
  
          private void textBox1_KeyPress(object sender, KeyPressEventArgs e)
          {
              // If the port is closed, don't try to send a character.
  
              if (!serialPort1.IsOpen) return;
  
              // If the port is Open, declare a char[] array with one element.
              char[] buff = new char[1];
  
              // Load element 0 with the key character.
  
              buff[0] = e.KeyChar;
  
              // Send the one character buffer.
              serialPort1.Write(buff, 0, 1);
  
              // Set the KeyPress event as handled so the character won't
              // display locally. If you want it to display, omit the next line.
              e.Handled = true;
          }
  
          private void serialPort1_DataReceived(object sender, System.IO.Ports.SerialDataReceivedEventArgs e)
          {
              RxString = serialPort1.ReadExisting();
              this.Invoke(new EventHandler(DisplayText));
  
  
          }
  
          private void Form1_FormClosing(object sender, FormClosingEventArgs e)
          {
              if (serialPort1.IsOpen) serialPort1.Close();
          }
  
          private void DisplayText(object sender, EventArgs e)
          {
              textBox1.AppendText(RxString);
  
          }
  
          private void buttonSQL_Click(object sender, EventArgs e)
          {
              //   SqlConnection myConnection = new SqlConnection("user id=username;" +
              //                            "password=password;server=serverurl;" +
              //                            "Trusted_Connection=yes;" +
              //                            "database=database; " +
              //                            "connection timeout=30");
  
              do
              {
                  string dayUsers, allUsers;
                  string s1 = "Today's total users: ";
                  string s2 = "All-time total users: ";
  
                  if (RxString.Contains(s1))
                  {
                  dayUsers = RxString.Replace(s1, "");
                  textDU.Text = dayUsers;
             //     string queryDay = "UPDATE USER_NUMBERS SET NUMBERZ=" + dayUsers + "WHERE UPTIME=DAYHK";
                  }
  
                  if (RxString.Contains(s2))
                  {
                  allUsers = RxString.Replace(s2, "");
                  textAU.Text = allUsers;
             //     string queryAll = "UPDATE USER_NUMBERS SET NUMBERZ=" + allUsers + "WHERE UPTIME=ALLHK";
                  }
              }
              while (serialPort1.IsOpen);
          }
  
  
  
          private void textDU_TextChanged(object sender, EventArgs e)
          {
  
          }
  
          private void textAU_TextChanged(object sender, EventArgs e)
          {
  
          }
  
      }
  }
  </code></pre>
  
  <hr>
  
  <p><strong>EDIT 3</strong> (Incoming Data):</p>
  
  <pre><code>if (abs (then [i] - now[i]) &gt; THRESH)
  {
    Serial.print ("Someone ");
    if (now [i] &gt; then [i])
    {
      Serial.print ("sat on");
      allusers++;
      dayusers++;
      digitalWrite(counterPin, HIGH);  // hit counter
      delay(75);
      digitalWrite(counterPin, LOW);
      val[i]++;
    }  
    else
    {
      Serial.print ("got up from");
    }  
    Serial.print (" seat number ");
    Serial.println (i);
    Serial.print ("All-time users on seat number ");
    Serial.print (i);
    Serial.print (" is: ");
    Serial.println (val[i]); 
    Serial.print ("Today's total users: ");
    Serial.println (dayusers);
    Serial.print ("All-time total users: ");
    Serial.println (allusers);
  }
  </code></pre>