Data not showing up in database table

Question

This simple Winforms program reads the input from a serial connection. I'm trying to send the received data to a local database for later retrieval.

However, the data doesn't seem to be inserted into the database table correctly since it keeps being empty. I receive no errors when running the program, and everything else works just fine. Below is the program code

public partial class Form1 : Form
    {
        private SerialPort myPort;
        private string dataIn;
        private DateTime dt;
        private static string cnString = Properties.Settings.Default.Database1ConnectionString;
        //Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True
        private SqlConnection dbConnection = new SqlConnection(cnString);
        private SqlDataAdapter adapter = new SqlDataAdapter();        

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            myPort = new SerialPort("COM3", 115200, Parity.None, 8, StopBits.One);

            myPort.DataReceived += MyPort_DataReceived;
            try
            {
                myPort.Open();
                textBox1.Text = "";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error");
            }
        }

        private void MyPort_DataReceived(object sender, SerialDataReceivedEventArgs e)
        {

            this.Invoke(new EventHandler(display_dataevent));

            dataIn = myPort.ReadLine();

            dt = DateTime.Now;

            dbConnection.Open();

            using (var insertCmd = new SqlCommand(@"INSERT INTO Measurements (Distance,DateTime) VALUES (@Distance,@DateTime)", dbConnection))
            {
                insertCmd.Parameters.Add("@Distance", SqlDbType.VarChar).Value = dataIn;
                insertCmd.Parameters.Add("@DateTime", SqlDbType.DateTime).Value = dt;

                insertCmd.ExecuteNonQuery();
            }

            dbConnection.Close();
        }

        private void display_dataevent(object sender, EventArgs e)
        {
            dt = DateTime.Now;
            string time = dt.Hour + ":" + dt.Minute + ":" + dt.Second;
            textBox1.AppendText(time + "\t\t\t" + dataIn + "\n");
        }
    }
}

Everything works now. The connection string was to blame. I don't quite understand how since I've only created the one database, so I don't know where from it got the incorrect string.

The commented connectionstring contains _DataDirectory_. Is the connection string read from the config file the same? If yes then read this QA https://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails — Steve, Nov 26 '19 at 19:17
is datetime a reserved word on the database platform you are using? --- it shouldn't matter, but it might — Hogan, Nov 26 '19 at 19:19
Are you actually receiving data? Is whatever on the serial port sending a newline? — Robert McKee, Nov 26 '19 at 19:33
I have to say, if there are no errors, you're connected to one db but looking into another — T.S., Nov 27 '19 at 04:19
it turns out that the connection string was to blame. I don't really understand where it got an incorrect string from since I've only created that one database. But it works now, thanks! — DanielBeck, Nov 27 '19 at 08:17

score 0 · Answer 1 · answered Nov 27 '19 at 09:44

0

Thanks everyone, the connection string was wrong for some reason

answered Nov 27 '19 at 09:44

DanielBeck

81
1
9

amindomeniko · Answer 2 · 2019-11-26T22:37:12.683

-1

Dispose SqlConnection and SqlCommand by putting them in `using'.

using(var dbConnection = new SqlConnection(cnString))
{
      dbConnection.Open();

      using(var insertCmd = new SqlCommand(@"INSERT INTO Measurements (Distance, DateTime) VALUES(@Distance, @DateTime)", dbConnection))
      {
          insertCmd.Parameters.Add("@Distance", SqlDbType.VarChar).Value = dataIn;
          insertCmd.Parameters.Add("@DateTime", SqlDbType.DateTime).Value = dt;
          insertCmd.ExecuteNonQuery();
       }
}

Also, ensure dataIn has acceptable value and your connectionstring is valid. I tried your code with my tweak above and it was successful.

If the issue, still persists, look at the db log file viewer for any possible errors: https://learn.microsoft.com/en-us/sql/relational-databases/logs/open-log-file-viewer?view=sql-server-ver15

edited Nov 26 '19 at 22:37

answered Nov 26 '19 at 22:08

amindomeniko

417
6
23

How is this different from what they already have? How would this fix the data not being inserted? It seems to just be moving `dbConnection` from class-level to method-level. – Lance U. Matthews Nov 26 '19 at 22:19
@BACON one needs to dispose `SqlConnection` and `SqlCommand` classes, hence putting it in `using`. Least benefit of it is applying good coding practices. – amindomeniko Nov 26 '19 at 22:33
The original code calls `dbConnection.Close()` after `insertCmd` is executed, so it _is_ being cleaned up (`using` or `try`/`finally` would be ideal, of course). Good coding practices are...good, but they don't address the fundamental problem of the question here. To your edit, does the original, untweaked code fail for you? – Lance U. Matthews Nov 26 '19 at 22:41

Data not showing up in database table

2 Answers2