Adding records to MS Access database through C#

Question

I am attempting to add items to an Access database in C#. I have code that seems to work (I can open and close a database), but the button click event produces errors. I have been searching on Google for the whole afternoon but no joy. My code is:

private void button26_Click(object sender, EventArgs e)
{  //Setup tab LoadDatabase
try
{
connection.Open();
button26.ForeColor = Color.Lime;
mainDataGridView.Visible = true;
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "INSERT INTO Main('Prop', 'Value',   'Default','Type')    VALUES('one', 'Kelly', 'Jill','one')";
cmd.ExecuteNonQuery();
button26.Text = "Done Insert"; 
connection.Close();
}
catch (Exception ex)
{
richTextBox1.Text=("Error "+ex);
button26.ForeColor = Color.Black;
connection.Close();
}
}

And the error I get is:

Error System.InvalidOperationException: ExecuteNonQuery: Connection property has not been initialized.
at System.Data.OleDb.OleDbCommand.ValidateConnection(String method)
at System.Data.OleDb.OleDbCommand.ValidateConnectionAndTransaction(String method)
? at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at CrewCheifSettingsBeta3.Form1.button26_Click(Object sender, EventArgs e) in C:\Somepath\Form1.cs:line 49

Clearly something wrong with the connection string, and that it's not SQL-injection proof either.

A command to be executed need to know which connection should be used. You don't set any connection to your command — Steve
– Steve, Commented Jun 17, 2017 at 17:34
it wont compile conn.Open(); OleDbCommand cmd = new OleDbCommand(); connection.CreateCommand(); cmd.CommandText = "INSERT INTO Main('Prop', 'Value',Default','Type') VALUES('one', 'Kelly', 'Jill','one')"; cmd.ExecuteNonQuery(); button26.Text = "Done Insert"; conn.Close(); @Steve — Data
– Data, Commented Jun 17, 2017 at 18:36

Steve · Accepted Answer · 2017-06-17 18:51:32Z

The problem is well known. A command need to know the connection to use to execute the command text. However you have other problems in your code.

Connection objects (like commands) should not be global, but created when they are needed and destroyed after. The using statement is very usefull here because you don't have to explicitly close and destroy these objects and you will never have resource leaks when an exception occurs.

Second, when you use field names that are also reserved keywords in your database you should enclose these name in some kind of escape characters. These characters for Access are the open/close brackets not the single quote.

private void button26_Click(object sender, EventArgs e)
{  
    try
    {
        string cmdText = @"INSERT INTO Main
                          ([Prop], [Value], [Default],[Type])    
                          VALUES('one', 'Kelly', 'Jill','one')";
        using(OleDbConnection connection = new OleDbConnection(.....))
        using(OleDbCommand cmd = new OleDbCommand(cmdText, connection))
        {            
            connection.Open();
            cmd.ExecuteNonQuery();
            button26.Text = "Done Insert"; 
            button26.ForeColor = Color.Lime;
            mainDataGridView.Visible = true;
        }
    }
    catch (Exception ex)
    {
        richTextBox1.Text=("Error "+ex);
        button26.ForeColor = Color.Black;
    }
}

Finally I don't know if your fields are of text type. You pass literal texts so they should be of text type and remember to use parameters when you switch this simple code to your actual values.

Ayaz · Accepted Answer · 2017-06-17 18:04:12Z

1

Assign Connection property as below line.

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = connection;

edited Jun 17, 2017 at 18:04

answered Jun 17, 2017 at 17:47

Ayaz

2,1314 gold badges17 silver badges21 bronze badges

5 Comments

Data Over a year ago

hmm I cant get that to compile @Ayax "The 'connection' does not exist in the current context"

Ayaz Over a year ago

It is the same connection object you have used to open it..connection.Open(). how are you creating the connection object?

Ayaz Over a year ago

Have you changed connection.Open() to conn.Open()? If so, change cmd.Connection = conn;

Data Over a year ago

private OleDbConnection conn = new OleDbConnection(); public Form1() { InitializeComponent(); conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Userssomepath\Set.mdb;Persist Security Info=False;";} @Ayax the compile error I gave u above

Ayaz Over a year ago

Just replace cmd.Connection = connection; with cmd.Connection = conn;

blins · Accepted Answer · 2017-06-17 17:45:35Z

0

Per @Steve's comment, there is no connection associated with the command when you just instantiate it like that. You need to either set the Connection property of the command or better yet use connection.CreateCommand() to create the command in the first place in which case it will already be associated with the connection (cleaner).

answered Jun 17, 2017 at 17:45

blins

2,53521 silver badges34 bronze badges

Collectives™ on Stack Overflow

Adding records to MS Access database through C#

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related