10 Replies - 13703 Views - Last Post: 19 December 2009 - 03:09 AM Rate Topic: -----

#1 -tr  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 16-December 09

OLEDB Help

Posted 16 December 2009 - 03:21 PM

Hi there. I'm working on a program to display the database in a datagridview, and allow the user to enter new records. Then, ideally there will be another form to enter expenses which will accumulate to the customers bill. Here's what I have so far:

private void frmMain_Load(object sender, EventArgs e)
		{
			String connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\bin\\Debug\\accounts.accdb";
			String query = "SELECT * FROM customers";

			OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connString);
			OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);
			DataTable dataTable = new DataTable();
			dataAdapter.Fill(dataTable);

			BindingSource bindingSource = new BindingSource();
			bindingSource.DataSource = dataTable;
			dgView.DataSource = bindingSource;
		}


Doesn't seem like much but from what I understand that's the foundation and the rest doesn't take too much work (also note that my home computer is 64-bit and I cannot get past the 'microsoft.jet.oledb.12.0.etc is not registered on this machine' error so I'm not able to constantly check on the code and thus, I'm not entirely certain the code I have now is actually displaying the records in the gridview).

Now what I need help with is adding records. I found somewhere that
dataAdapter.Update(dataTable);
is what you use in the submit button to save the record but it tells me that the dataAdapter and dataTable references do not exist in the current context. I feel liking I'm missing something obvious with this but can't think of what it is..

Any tips/links to tutorials that actually work are greatly appreciated.

Is This A Good Question/Topic? 0
  • +

Replies To: OLEDB Help

#2 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: OLEDB Help

Posted 17 December 2009 - 05:19 AM

The exception that 'microsoft.jet.oledb.12.0.etc is not registered on this machine' is very common and to solve this problem do the following:

1. In solution explorer select your project (the one that uses this oledb driver to connect to database if you have more than one project in you solution).

2. Right click on the project and select "Properties".

3. In the properties dialog, select "Build" from the menu options on left.

4. Under "Platform target:" choose "x86" instead of "Any CPU".

5. Save your project.


Now you will be able to connect to database, retrieve records, update records, etc.

Hope this resolves your issue.

This post has been edited by FlashM: 17 December 2009 - 05:20 AM

Was This Post Helpful? 0
  • +
  • -

#3 zombie_chan51  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 327
  • Joined: 16-March 08

Re: OLEDB Help

Posted 17 December 2009 - 07:39 AM

Are you using Access 2007?
Was This Post Helpful? 0
  • +
  • -

#4 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: OLEDB Help

Posted 17 December 2009 - 07:46 AM

Both Access 2003 and Access 2007 throw this same exception if project is compiled for "x64" or "Any CPU" platform.
Was This Post Helpful? 0
  • +
  • -

#5 -tr  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 16-December 09

Re: OLEDB Help

Posted 17 December 2009 - 09:13 AM

I've actually come across this solution a number of times in a number of different ways but was never able to get it to work. On the build page (I have the 2008 express edition, btw) there is not an option for platform target, even after selecting 'show advanced build configurations' through the options menu.

I then tried to edit the .csproj file to include it but all that was there was a project xlmns link and it wouldn't work if I entered anything.

Also, I have used both an Access 2007, and 2010 database, both giving the same result. EDIT: Just checked and Access 2010 actually saves in 2007 format.


EDIT Again: There is a platform drop down list on the build page which I apparently missed but the only option is Active (Any CPU).

This post has been edited by -tr: 17 December 2009 - 09:19 AM

Was This Post Helpful? 0
  • +
  • -

#6 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: OLEDB Help

Posted 17 December 2009 - 09:45 AM

OK, just give me a sec and I'll provide you with another solution.
Was This Post Helpful? 0
  • +
  • -

#7 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: OLEDB Help

Posted 17 December 2009 - 09:51 AM

From MSDN help I fount that platform is not available in the development environment in Visual C# Express.

Another solution would be to compile the application using csc.exe in command prompt. Here is the link on how to do this:
http://msdn.microsof...28VS.80%29.aspx

If you need any further help, feel free to ask.
Was This Post Helpful? 0
  • +
  • -

#8 -tr  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 16-December 09

Re: OLEDB Help

Posted 17 December 2009 - 11:36 AM

Excellent, I'll give that a shot when I get home. :^:

In the mean time, I confirmed that the datagridview is connected and displaying properly. However I'm still stuck on the update part to add the record. Is the dataAdapter.Update command all I need to add the records (and if so, where does my other code need to go so that I can reference the dataTable and dataAdapter) or do I need to code an INSERT INTO command? Also, I would like to use the textboxes on my form (one being a masked textbox, if that makes any difference) to add a record rather than the datagridview.

Thanks again. :)
Was This Post Helpful? 0
  • +
  • -

#9 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: OLEDB Help

Posted 17 December 2009 - 01:52 PM

Well, I remember I have used the following code, that automatically updated, deleted and inserted records to database table whenever any changes have been made in datagridview. I just tested this code and unfortunately it doesn't work just the way I expected.

using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace DatabaseApp
{
	public partial class Form1 : Form
	{
		private OleDbConnection connection = null;
		private OleDbCommand command = null;
		private OleDbCommandBuilder cmdBuilder = null;
		private OleDbDataAdapter dataAdapter = null;
		private DataTable dataTable = null;

		public Form1()
		{
			InitializeComponent();
			InitCustomComponents();
		}

		private void InitCustomComponents()
		{
			connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\testdb.mdb;");
			command = new OleDbCommand();
			command.CommandText = "SELECT id, name, surname FROM person";
			command.Connection = connection;
			dataAdapter = new OleDbDataAdapter(command);
			cmdBuilder = new OleDbCommandBuilder(dataAdapter);
			dataTable = new DataTable();
			dataTable.TableNewRow += new DataTableNewRowEventHandler(dataTable_TableNewRow);
			dataTable.RowDeleted += new DataRowChangeEventHandler(dataTable_RowDeleted);
			dataTable.RowChanged += new DataRowChangeEventHandler(dataTable_RowChanged);
		}

		void dataTable_RowChanged(object sender, DataRowChangeEventArgs e)
		{
			if (e.Action != DataRowAction.Add)
			{
				if (e.Row.RowState != DataRowState.Unchanged)
				{
					dataAdapter.Update(dataTable);
				}
			}
		}

		void dataTable_RowDeleted(object sender, DataRowChangeEventArgs e)
		{
			dataAdapter.Update(dataTable);
		}

		void dataTable_TableNewRow(object sender, DataTableNewRowEventArgs e)
		{
			dataAdapter.Update(dataTable);
		}

		private void btnGetData_Click(object sender, EventArgs e)
		{
			dataAdapter.Fill(dataTable);
			BindToGridView(dataTable);
		}

		private void BindToGridView(DataTable table)
		{
			dgvEmployees.DataSource = table;
		}
	}
}



Here's another link from Microsoft you should read: http://msdn.microsof...28VS.71%29.aspx

I hope it works for you...

This post has been edited by FlashM: 17 December 2009 - 02:10 PM

Was This Post Helpful? 0
  • +
  • -

#10 -tr  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 16-December 09

Re: OLEDB Help

Posted 18 December 2009 - 01:26 PM

View PostFlashM, on 17 Dec, 2009 - 08:51 AM, said:

From MSDN help I fount that platform is not available in the development environment in Visual C# Express.

Another solution would be to compile the application using csc.exe in command prompt. Here is the link on how to do this:
http://msdn.microsof...28VS.80%29.aspx

If you need any further help, feel free to ask.


Turns out this isn't working as planned either, the VC# Express Edition doesn't seem to install the visual studio tools or command prompt. I tried manually editing the Path environment variable, but updating for the 3.5 framework, but csc is still not being recognized.

EDIT: I'm guessing I just entered the paths wrong or something as I can see the csc application in C:\Windows\Microsoft.Net\Framework\3.5 and C:\Windows\Microsoft.Net\Framework64\3.5. Any ideas on how I need to set this up?

This post has been edited by -tr: 18 December 2009 - 01:29 PM

Was This Post Helpful? 0
  • +
  • -

#11 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: OLEDB Help

Posted 19 December 2009 - 03:09 AM

There are numerous articles on internet how to set environmental path in windows. Just google it.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1