4 Replies - 1897 Views - Last Post: 20 February 2013 - 01:10 PM Rate Topic: -----

#1 user1999  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 19-February 13

c# connection to access database

Posted 19 February 2013 - 08:12 AM

I HAVE THIS ERRORS WHEN I TRY TO RUN MY CODE in visual studio 2012:
When i press save button:
-------------------------
1. An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll
Additional information: ExecuteReader requires an open and available Connection. The connection's current state is closed.

When i press show all button:
--------------------------------

2. An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Additional information: The 'Microsoft.ACE.OLEBD.12.0' provider is not registered on the local machine.

The code i run:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        
        
        OleDbConnection vcon=new OleDbConnection(@"Provider=Microsoft.ACE.OLEBD.12.0; Data source=C:\Users\user\Desktop\DBMS.accdb");
       

        public Form1()
        {
            InitializeComponent();
           
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            vcon.Open();
        
            
        }

        private void button1_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string vsql = string.Format("insert into PartA values ((0),(1),(2),(3),(4))",textBox1.Text,int.Parse(textBox2.Text),textBox3.Text,textBox4.Text,textBox5.Text);
            
OleDbCommand vcom = new OleDbCommand(vsql, vcon);

            vcom.ExecuteReader();
            MessageBox.Show("Data stored succesfully");
            vcom.Dispose();

        
        }

      
       
        private void button4_Click(object sender, EventArgs e)
        {
            string vsql = "Select * from partA ";
            OleDbCommand vcom = new OleDbCommand(vsql, vcon);
            DataSet vds = new DataSet();
            OleDbDataAdapter vda = new OleDbDataAdapter(vcom);
            vda.Fill(vds, "res");
            dataGridView1.DataSource = vds.Tables["res"];
            vda.Dispose();
            vcom.Dispose();


        }

This post has been edited by tlhIn`toq: 20 February 2013 - 12:23 PM


Is This A Good Question/Topic? 0
  • +

Replies To: c# connection to access database

#2 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5416
  • View blog
  • Posts: 11,599
  • Joined: 02-June 10

Re: c# connection to access database

Posted 20 February 2013 - 12:22 PM

Please dont' post questions in the challenges area.
Just because its a challenge for you - doesn't make it a challenge that others are being invited to participate in.

Question has been moved to Questions area.

Also: USE CODE TAGS
:code:

Posted Image
Was This Post Helpful? 0
  • +
  • -

#3 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4404
  • View blog
  • Posts: 7,644
  • Joined: 08-June 10

Re: c# connection to access database

Posted 20 February 2013 - 01:05 PM

This is the important part of error right here:

Quote

Additional information: ExecuteReader requires an open and available Connection. The connection's current state is closed.


I think this might be because you're opening a connection in the Form_Load, but the connection may have timed out and closed by the time you use it.

baavgai correctly identified that the connection was being disposed.

Quote

Additional information: The 'Microsoft.ACE.OLEBD.12.0' provider is not registered on the local machine.


Sounds like Access 2010 isn't installed on that particular machine, so the ACE provider was never installed.

I hate to say it, but your code is a mess. You don't name your buttons, so we don't know which is your "show all" button. Give your controls and method names meaningful names.

You're dumping your database logic all over the place. That's bad practice. Also, you're opening a connection when the form opens. That's worse. Only open your connection when you're about to make a query, then close it as soon as you're done. It's far, far better to open and close your connections than it is to maintain an open one for the life of the application.

Also, you shouldn't be doing your database interaction piecemeal in separate methods. Make a DataManager class, and do it all there. Give it methods that take in the necessary query data, and output the results in the way you want it.

Last, even if you fix all your errors, you're never going to get your code to work, because you're using String.Format incorrectly. Your SQL statement will come out wrong. Check out my tutorial on how to properly deal with databases:

http://www.dreaminco...ery-a-database/
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5746
  • View blog
  • Posts: 12,553
  • Joined: 16-October 07

Re: c# connection to access database

Posted 20 February 2013 - 01:06 PM

Stop calling Dispose on everything. You don't need it and it's a bad idea.

If you're going to have one connection, you need to be sure it's open before you use it. However, both running a DataAdapter fill or a Dispose will close it for you. Instead, create a connection instance as needed.

e.g.
public partial class Form1 : Form {
	// no
	// OleDbConnection vcon=new OleDbConnection(@"Provider=Microsoft.ACE.OLEBD.12.0; Data source=C:\Users\user\Desktop\DBMS.accdb");
	public Form1() {
		InitializeComponent();
	}

	// no
	// private void Form1_Load(object sender, EventArgs e) {
		vcon.Open();
	}
	
	// instead
	OleDbConnection GetNewConnection() { 
		return new OleDbConnection(@"Provider=Microsoft.ACE.OLEBD.12.0; Data source=C:\Users\user\Desktop\DBMS.accdb");
	}


	private void button3_Click(object sender, EventArgs e) {
		// this is a poor way to do this.  Learn about binding parameters
		string vsql = string.Format("insert into PartA values ((0),(1),(2),(3),(4))",textBox1.Text,int.Parse(textBox2.Text),textBox3.Text,textBox4.Text,textBox5.Text);

		OleDbCommand cmd = new OleDbCommand(vsql, GetNewConnection());
		try {
			cmd.Connection.Open();
			cmd.ExecuteReader();
			MessageBox.Show("Data stored succesfully");
		} finally {
			cmd.Connection.Close();
		}
		// no
		// vcom.Dispose();
	}

 
	private void button4_Click(object sender, EventArgs e) {
		string vsql = "Select * from partA ";
		OleDbCommand vcom = new OleDbCommand(vsql, GetNewConnection());
		DataSet vds = new DataSet();
		OleDbDataAdapter vda = new OleDbDataAdapter(vcom);
		vda.Fill(vds, "res"); // note, this opens and closes than connection
		dataGridView1.DataSource = vds.Tables["res"];
		// NO!
		// vda.Dispose();
		// vcom.Dispose();
	}
}



Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3074
  • View blog
  • Posts: 10,270
  • Joined: 12-December 12

Re: c# connection to access database

Posted 20 February 2013 - 01:10 PM

Microsoft.ACE.OLEBD.12.0


it is OLEDB.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1