adding to database oledb c#

Question

After searching for about an hour it appears this is the correct way to use the oledb libary to insert a record to an access database however it doesnt work for me , HELP...

        InitializeComponent();
        System.Data.OleDb.OleDbConnection conn = new
        System.Data.OleDb.OleDbConnection();
        // TODO: Modify the connection string and include any
        // additional required properties for your database.
        conn.ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = \\crd-a555-015.occ.local\c$\Users\james.piper\Documents\Visual Studio 2015\Projects\Project V1\Project Database.accdb";
        try
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO Work_Done (employee,client,project,task,hours)" + " VALUES (@employee,@client,@project,@task,@hours)";
            cmd.Parameters.AddWithValue("@employee", user.employee);
            cmd.Parameters.AddWithValue("@client", listBox1.SelectedItem.ToString());
            cmd.Parameters.AddWithValue("@project", listBox2.SelectedItem.ToString());
            cmd.Parameters.AddWithValue("@task", listBox3.SelectedItem.ToString());
            cmd.Parameters.AddWithValue("@hours", listBox4.SelectedItem.ToString());



            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("sql insert fail");
        }

Answer 1

I would write this code like this:

var connectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = \\crd-a555-015.occ.local\c$\Users\james.piper\Documents\Visual Studio 2015\Projects\Project V1\Project Database.accdb";
var query = "INSERT INTO Work_Done (employee,client,project,task,hours) VALUES (@employee,@client,@project,@task,@hours)";
using (var conn = new OleDbConnection(connectionString))
{             
    using(var cmd = new OleDbCommand(query, conn))
    {
        // No need to specifiy command type, since CommandType.Text is the default

        // I'm assuming, of course, your parameter data types. You should change them if my assumptions are wrong.
        cmd.Parameters.Add("@employee", OleDbType.Integer).Value = user.employee;
        cmd.Parameters.Add("@client", OleDbType.Integer).Value = Convert.ToInt32(listBox1.SelectedItem);
        cmd.Parameters.Add("@project", OleDbType.Integer).Value =  Convert.ToInt32(listBox2.SelectedItem);
        cmd.Parameters.Add("@task", OleDbType.Integer).Value = Convert.ToInt32(listBox3.SelectedItem);
        cmd.Parameters.Add("@hours", OleDbType.Integer).Value = Convert.ToInt32(listBox4.SelectedItem);

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show($"sql insert fail: {ex}");
        }
    }                
}

The major changes are these:

1. use the Using statement for each instance of a class that implements the IDisposable interface.
2. Using constructors with parameters to make the code shorter (and more readable, IMHO).
3. Note that the constructor of the OleDbCommand also has the OleDbConnection object. In your code, you didn't specify the active connection to the command.
4. Adding parameters with Add and not AddWithValue. Read this blog post to find out why.