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");
        }

you're going to need to define "doesnt work". You get an error? Unexpected behaviour? Something isn't executed as you thought? We need some more detailed information. — ADyson, Jul 10 '17 at 14:54
As far as I can remember, OleDb does not support named parameters, only ordinal parameters. Therefor, you should change your query to use question marks (`?`) instead of parameter names. Also, you should get used to use all your IDisposables inside `using` statements - OleDbConnection and OleDbCommand are IDisposable. — Zohar Peled, Jul 10 '17 at 15:07
@ZoharPeled Not supported, but it won't throw an error. It will just convert the parameter name into a ? behind the scenes. Parameters have to be in index order, which, in this case, they are. — LarsTech, Jul 10 '17 at 15:10
@LarsTech Good to know, in case one day I'll have to work with OleDb again :-) — Zohar Peled, Jul 10 '17 at 15:15
`MessageBox.Show("sql insert fail");` <= If you are getting an Exception then you are hiding all the details of why the Exception occurred with this line. If you can't handle the Exception then do not catch it or re-throw it with `throw;` statement. — Igor, Jul 10 '17 at 15:49

Zohar Peled · Accepted Answer · 2017-07-10T19:03:57.487

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:

use the Using statement for each instance of a class that implements the IDisposable interface.
Using constructors with parameters to make the code shorter (and more readable, IMHO).
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.
Adding parameters with Add and not AddWithValue. Read this blog post to find out why.

sql insert fail: System.Data.OleDb.OleDbException (0x80040E10): Parameter @client has no default value. — James Piper, Jul 10 '17 at 21:12
I did a console.writeline of listbox1.selecteditem.tostring and it prints out the correct value i dont understand how it is then a 'null' value later on — James Piper, Jul 11 '17 at 17:01
I Don't know what it should be, nor do I know what does your code look like right now. It's impossible to debug by telepathy.... — Zohar Peled, Jul 11 '17 at 17:33
as an unrelated question what is the correct syntax to add parameters to a select statement as ive tried to copy the format of the insert and I get this error (sql insert fail: System.NullReferenceException: Object reference not set to an instance of an object. at testing_sql.Form1.button2_Click(Object sender, EventArgs e) in c:\users\james.piper\documents\visual studio 2015\Projects\testing sql\testing sql\Form1.cs:line 236 ) 'code' Thanks in advance — James Piper, Jul 12 '17 at 14:58
https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it — Zohar Peled, Jul 12 '17 at 15:29

adding to database oledb c#

1 Answers1