ExecuteNonQuery: Connection property has not been initialized

Question

I've searched for my question here, but I couldn't find it. I am using Microsoft VS 2010 C#.

Here is my code:

private OleDbConnection myCon;
public Form5()
{
   myCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|ForeignWorkerinfo.accdb");
   InitializeComponent();
}

private void Form5_Load(object sender, EventArgs e)
{
   // TODO: This line of code loads data into the 'foreignWorkerinfoDataSet.FWinFO' table. You can move, or remove it, as needed.
   this.fWinFOTableAdapter.Fill(this.foreignWorkerinfoDataSet.FWinFO);
}

private void button1_Click(object sender, EventArgs e)
{                
   OleDbCommand cmd = new OleDbCommand();
   cmd.Connection = myCon;
   cmd = new OleDbCommand("INSERT INTO [FWinFO] ([ID], [Name], [Gender], [Date of Birth], [Country], [Date of Expire], [Passport No], [Working Place]) VALUES (?, ?, ?, ?, ?, ?, ?, ?);");

   cmd.Parameters.AddWithValue("@id", textBox8.Text);
   cmd.Parameters.AddWithValue("@name", textBox1.Text);
   cmd.Parameters.AddWithValue("@gender", textBox2.Text);
   cmd.Parameters.AddWithValue("@dob", dateTimePicker1.Value);
   cmd.Parameters.AddWithValue("@country", textBox4.Text);
   cmd.Parameters.AddWithValue("@doe", dateTimePicker2.Value);
   cmd.Parameters.AddWithValue("@passport", textBox6.Text);
   cmd.Parameters.AddWithValue("@workplace", textBox7.Text);

   cmd.ExecuteNonQuery();
   myCon.Close();
}

Can anyone tell me why the connection property has not been initialized?

What is the specific error you are seeing? A `try/catch` block would be helpful... — Brian, Mar 06 '13 at 16:53

Justin Pihony · Answer 1 · 2013-03-07T05:38:31.567

3

You have two errors, the one you state, and one that will occur after you fix the first one:

The first one is that you are overwriting your setting of the Connection property on the cmd by calling new again. Either do one new, or set the query text property.

The next error is:

You have to call myCon.Open to make the ExecuteNonQuery() call. You cannot execute a query against an unopened connection.

You can always check if the connection is open by checking its State to see if it is Open.

However, I would actually suggest to create the connection on demand, but it depends on the code. Using a global, shared connection object leaves itself open to problems. You can cache the connection string, though.

edited Mar 07 '13 at 05:38

answered Mar 06 '13 at 16:53

Justin Pihony

66,056
18
147
180

@ThamJunKai As another suggested, try moving `myCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|ForeignWorkerinfo.accdb");` after the `InitializeComponent` Maybe that is overwriting your initialization – Justin Pihony Mar 07 '13 at 03:30
Duh....it was too obvious that I missed it: you are double initializing your command. Move the `cmd = new OleDbCommand("INSERT INTO [FWinFO] ([ID], [Name], [Gender], [Date of Birth], [Country], [Date of Expire], [Passport No], [Working Place]) VALUES (?, ?, ?, ?, ?, ?, ?, ?);");` above the `cmd.Connection = myCon;` By calling `new` again, you are overwriting the original connection. But, you still need the `MyCon.Open` – Justin Pihony Mar 07 '13 at 05:35
@JustinPihony - +1 for suggesting that the OP create the connection 'on-demand'. This is almost always the best way to go. – Brian Mar 07 '13 at 07:07

score 1 · Accepted Answer · answered Mar 06 '13 at 16:54

1

Try opening the connection first, with myCon.Open()

answered Mar 06 '13 at 16:54

Dean Becker

66
1
5

@ThamJunKai Just out of curiosity, I thought that this did not work? Not trying to take away from another if it is correct, but did you mean to give me the checkmark. It is just per the error and our discussion, I believe that mine is what solved the problem? If not, just ignore this – Justin Pihony Mar 07 '13 at 14:55

score 1 · Answer 3 · answered Mar 06 '13 at 16:55

1

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = myCon;
cmd = new OleDbCommand("INSERT INTO [FWinFO] ([ID], [Name], [Gender], [Date of Birth], [Country], [Date of Expire], [Passport No], [Working Place]) VALUES (?, ?, ?, ?, ?, ?, ?, ?);");

The above code is correct. The problem is, you aren't opening the connection. Please use:

 OleDbCommand cmd = new OleDbCommand();
 cmd.Connection = myCon;
 myCon.Open();
 cmd = new OleDbCommand("INSERT INTO [FWinFO] ([ID], [Name], [Gender], [Date of Birth], [Country], [Date of Expire], [Passport No], [Working Place]) VALUES (?, ?, ?, ?, ?, ?, ?, ?);");

answered Mar 06 '13 at 16:55

Brian

5,069
7
37
47

Hi, I had follow your code, but still doesn't work. ExecuteNonQuery: Connection property has not been initialized. – Tham JunKai Mar 07 '13 at 02:59
@ThamJunKai - There are several issues with your code as I mentioned earlier. First, you don't have a `ConnectionObject` to work with, and second, your `ParameterObjects` won't work with how you have declared them. Oh, and where are you instantiating your `SqlDataAdapter`? Let me edit my answer and see if that helps you... – Brian Mar 07 '13 at 07:01

ExecuteNonQuery: Connection property has not been initialized

3 Answers3

Linked