Windows Form connected to MS Access Database not saving records to database

Question

I have a Windows Form in Visual Studio that is connected to a MS Access Database. The application is supposed to allow the user to type a report into a text box and save that data along with a time stamp as a record in the database. Everything seems to be working on the application end but the data isn't being saved or isn't showing up in the database.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml.Linq;

namespace CapstoneProjectDataEntryApp
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void SubmitBtn_Click(object sender, EventArgs e)
        {

            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\Users\Austin\Documents\School\CapstoneProjectDataEntryApp\ShabelReports.accdb";
            try
            {
                conn.Open();
                string myEntry = EntryTB.Text;
                string myDateTime = DateTime.Now.ToString();
                string myQuery = "INSERT INTO ProgressReports (Report, DateTime) VALUES ('" + myEntry + "','" + myDateTime + "')";
                OleDbCommand cmd = new OleDbCommand(myQuery, conn);
                cmd.ExecuteNonQueryAsync();

                MessageBox.Show("Data saved successfully!");
            }
            catch(Exception ex)
            {
                MessageBox.Show("Failed due to" + ex.Message);
            }
            finally
            {
                conn.Close();
                EntryTB.Text = "";
            }
        }
    }
}

I have tried just using the database as a data source, and just as a data connection, and both, and all have the same result. I'm new to what I'm doing so I don't know much about working with databases in Windows Forms

At a glance, you probably need to make the button click handler `async` and then `await` the call to `cmd.ExecuteNonQueryAsync` or use a non-async execute method instead. Note your code also looks vulnerable to *SQL injection*, which is a security concern. Something to look into. — , Oct 31 '22 at 03:36
Firstly, either make your method `async` and `await` that call to `ExecuteNonQueryAsync` or else call `ExecuteNonQuery` instead. Next, check what value is returned by that call. — jmcilhinney, Oct 31 '22 at 03:37
The following may be helpful: https://stackoverflow.com/a/70484316/10024425, https://stackoverflow.com/a/72759270/10024425, and https://stackoverflow.com/a/69638011/10024425. — Tu deschizi eu inchid, Oct 31 '22 at 03:47
I tried ExecuteNonQuery and the error message box would say there was a syntax error in the INSERT INTO statement. — austinreburks, Nov 01 '22 at 05:54
Something I noticed is that everything I run the application it closes the connection to the database — austinreburks, Nov 01 '22 at 06:47

score 0 · Answer 1 · answered Oct 31 '22 at 10:10

The following was written with .NET Core. Note there is no string concatenation, always best to use typed parameters e.g. cmd.Parameters.Add("?", OleDbType.Date).Value = dateTime

The method returns a bool which if ExecuteNonQueryAsync returns 1 meaning one record was inserted than true else false. If return true and the database does not reflect a new record than ensure you are looking at the right database.

public static async Task<(bool success, Exception exception)> Insert(string entry, DateTime dateTime)
{
    using (var cn = new OleDbConnection { ConnectionString = "Your connection string" })
    {
        using (var cmd = new OleDbCommand() { Connection = cn })
        {
            cmd.CommandText = "INSERT INTO ProgressReports(Report,DateTime) VALUES(?,?)";
            cmd.Parameters.Add("?", OleDbType.LongVarChar).Value = entry;
            cmd.Parameters.Add("?", OleDbType.Date).Value = dateTime;

            try
            {
                await cn.OpenAsync();
                return (await cmd.ExecuteNonQueryAsync() == 1, null);
            }
            catch (Exception localException)
            {
                return (false, localException);
            }
        }
    }
}

Invoking the above method, in this case the method above is in a separate class but can also be in the form.

private async void InsertButton_Click(object sender, EventArgs e)
{
    var (success, exception) = await Operations.Insert("My entry", DateTime.Now);
    if (success)
    {
        MessageBox.Show("Data Saved Successfuly!");
    }
    else if (exception is not null)
    {
        MessageBox.Show($"Failed: {exception.Message}");
    }
}

Note exception is not null is .NET Core

score 0 · Answer 2 · answered Nov 01 '22 at 08:52

SOLVED! I used parameters as recommended.I also had to remove the DateTime from the code and do that on the database end. Here's the code that worked.

        private void SubmitBtn_Click(object sender, EventArgs e)
    {
        OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=*path to file*");
        string query = "INSERT INTO ProgressReports (Report) VALUES(@Report)";
        OleDbCommand cmd = new OleDbCommand(query, conn);
        try
        {
            cmd.Parameters.AddWithValue("@Report",EntryTB.Text);
            conn.Open();
            cmd.ExecuteNonQuery();

            MessageBox.Show("Data Saved Successfuly!");
        }
        catch (Exception ex)
        {
            MessageBox.Show("Failed due to" + ex.Message);
        }
        finally
        {
            conn.Close();
            EntryTB.Text = "";
        }
    }

Windows Form connected to MS Access Database not saving records to database

2 Answers2