Accessing Access Database with GenericOLEDB module

Jelmer · February 7, 2006

Hello

I've downloaded this project to try to acces my Access database:

http://www.codeproject.com/cs/database/csharpaccessdb.asp

I can fill all fields and i can access my database.. and read everything.

Nice!

Next step.. implement this into my own program.

This things will work:

- access database

- read column headers

But if i try to read the data i'll get this error:

Object reference not set to an instance of an object.

On this line:

MessageBox.Show(reader.GetValue(0).ToString());

My code is:

        public void read(){
           IniFile ini = new IniFile("test.ini");
           database = ini.IniReadValue("Info", "Database");

           if (dbAccess != null)
           {
               if (dbAccess.IsOpen == true)
               {
                   dbAccess.Close();
               }
           }            
           
           dbAccess = new GenericOLEDBClass();
           dbAccess.SelectCommand = "select * from Bedrijven";
           dbAccess.Open(Provider, UserID, Password, database, Mode);

           if (dbAccess.ExecuteCommand() == true)
           {
               int nCount = dbAccess.GetReader.FieldCount;
               //ListViewItem lvItem = new ListViewItem();
               for (int i = 0; i < nCount; i++)
               {
                   ColumnHeader header = new ColumnHeader();
                   header.Width = 100;
                   header.Text = dbAccess.GetReader.GetName(i);
                   //listView1.Columns.Add(header);
                   /// Store the header names in a collection
                   //string temp = dbAccess.GetReader.GetName(i);
               }
               while (dbAccess.GetReader.Read() == true)
               {
                   //lvItem = listView1.Items.Add(reader.GetValue(0).ToString());
                   MessageBox.Show(reader.GetValue(0).ToString());
                   for (int i = 1; i < nCount; i++)
                   {
                       int IID;
                       MessageBox.Show(dbAccess.GetReader.GetValue(1).ToString());
                       Int32.TryParse(dbAccess.GetReader.GetValue(0).ToString(), out IID);
                       cust[cust.Length + 1] = new Class_customers(IID, dbAccess.GetReader.GetValue(1).ToString(), dbAccess.GetReader.GetValue(2).ToString(), dbAccess.GetReader.GetValue(3).ToString(), dbAccess.GetReader.GetValue(4).ToString(), dbAccess.GetReader.GetValue(5).ToString(), dbAccess.GetReader.GetValue(6).ToString(), dbAccess.GetReader.GetValue(8).ToString(), dbAccess.GetReader.GetValue(8).ToString(), dbAccess.GetReader.GetValue(9).ToString(), dbAccess.GetReader.GetValue(10).ToString(), dbAccess.GetReader.GetValue(11).ToString(), dbAccess.GetReader.GetValue(12).ToString(), dbAccess.GetReader.GetValue(13).ToString(), dbAccess.GetReader.GetValue(14).ToString());
                   }
               }
           }
           else
           {
               MessageBox.Show(dbAccess.ErrorMessage);                
           }
}

This is the original code from the original project:

public void DisplayList( OleDbDataReader reader )
	{
		listView1.Clear();

		int nCount = reader.FieldCount;

		/// build the headers first
		
		for( int i=0; i<nCount; i++ )
		{
			ColumnHeader header = new ColumnHeader();
			header.Width = 100;
			header.Text = reader.GetName( i );
			listView1.Columns.Add( header );
			/// Store the header names in a collection
			stringCol.Add( reader.GetName( i ) );
		}

		// now add the data
		ListViewItem lvItem = new ListViewItem();

		while( reader.Read() == true )
		{
			lvItem = listView1.Items.Add( reader.GetValue( 0 ).ToString() );
			for( int i=1; i<nCount; i++ )
			{
                   MessageBox.Show(reader.GetValue(i).ToString());
				lvItem.SubItems.Add( reader.GetValue( i ).ToString() );
			}
		}
	}

private void OnOpenDatabase(object sender, System.EventArgs e)
	{
		if( textBox1.Text == null  )
		{
			MessageBox.Show( "You need to enter a database driver" );
			return;
		}
		else
			Provider = textBox1.Text;

		if( textBox2.Text == null )
		{
			MessageBox.Show( "You need to enter a user id" );
			return;
		}
		else
			UserID = textBox2.Text;

		Password = textBox3.Text;

		/// the password can be blank so skip it
		if( textBox4.Text == null )
		{
			MessageBox.Show( "You need to enter a database file" ); 
			return;
		}
		else
			DatabaseName = textBox4.Text;


		if( dbAccess != null )
		{
			if( dbAccess.IsOpen == true )
			{
				dbAccess.Close();
			}
		}

		dbAccess = new GenericOLEDBClass();
		dbAccess.Open( Provider, UserID, Password, DatabaseName, Mode );

		/// set the insert command and run it
		if( SelectCommand == null )
		{
			MessageBox.Show( "you need to generate a select command first" );
			return;
		}

		dbAccess.SelectCommand = SelectCommand;

		if( dbAccess.ExecuteCommand() == true )
		{
			DisplayList( dbAccess.GetReader );
		}
		else
		{
			MessageBox.Show( dbAccess.ErrorMessage );
			return;
		}

	}

Does anybody see what i am doing wrong ?

Edited February 7, 2006 by PlausiblyDamp

PlausiblyDamp · February 7, 2006

Only had a quick glance but you do not appear to be setting the reader varibale to anything, normally you would make it equal to the result of executing a command object's .ExecuteReader method.

Jelmer · February 7, 2006

Myfoult.. reader. should not be used..

MessageBox.Show(dbAccess.GetReader.GetValue(1).ToString());

Its like this.. that other messagebox was for a test..

                while (dbAccess.GetReader.Read() == true)
               {
                   //lvItem = listView1.Items.Add(reader.GetValue(0).ToString());
                   MessageBox.Show(dbAccess.GetReader.GetValue(1).ToString());
                   for (int i = 1; i < nCount; i++)
                   {
                       int IID;
                       MessageBox.Show(dbAccess.GetReader.GetValue(1).ToString());

i will search for de execute command.. but its also not used in that other program that works well !!

I thought that dbAccess.ExecuteCommand() would do that ?

Edited February 7, 2006 by PlausiblyDamp

Jelmer · February 7, 2006

No idee what went wrong.. but it works.. i'll get every information from the databse with this code.

First i'll rebuild the old code from the example..

Then i'll edited it so i don't need a listbox anymore and replaced it by messageboxes.

I'll copied the codes to my own project.. and it worked :S

The code:

        public void DisplayList(OleDbDataReader reader)
       {
           //listView1.Clear();

           int nCount = reader.FieldCount;

           /// build the headers first

           for (int i = 0; i < nCount; i++)
           {
               ColumnHeader header = new ColumnHeader();
               header.Width = 100;
               header.Text = reader.GetName(i);
               //listView1.Columns.Add(header);
               /// Store the header names in a collection
               //stringCol.Add(reader.GetName(i));
           }

           // now add the data
           ListViewItem lvItem = new ListViewItem();

           while (reader.Read() == true)
           {
               //lvItem = listView1.Items.Add(reader.GetValue(0).ToString());
               for (int i = 1; i < nCount; i++)
               {
                   MessageBox.Show(reader.GetValue(i).ToString());
                   //lvItem.SubItems.Add(reader.GetValue(i).ToString());
               }
           }
       }

       public void read(){
           IniFile ini = new IniFile("test.ini");
           database = ini.IniReadValue("Info", "Database");


		if( dbAccess != null )
		{
			if( dbAccess.IsOpen == true )
			{
				dbAccess.Close();
			}
		}

		dbAccess = new GenericOLEDBClass();
           UserID = "";
           Password = "";
           Provider = "Microsoft.Jet.OLEDB.4.0";
           Mode = "ReadWrite";

		dbAccess.Open( Provider, UserID, Password, database, Mode );

		dbAccess.SelectCommand = "SELECT * FROM Bedrijven";

           MessageBox.Show(dbAccess.SelectCommand);

		if( dbAccess.ExecuteCommand() == true )
		{
			DisplayList( dbAccess.GetReader );
		}
		else
		{
			MessageBox.Show( dbAccess.ErrorMessage );
			return;
		}

Jelmer · February 7, 2006

I've build very thing into my class.. it works !

But.. i've got some problems to get to the next or previeus record.

Normally you've got a while loop ore something.

Now i use the read function:

            if (reader.Read() == true)
           {
               fill(reader);                     
           }

fill does this:

        public void fill(OleDbDataReader reader)
       {
           int IID;
           Int32.TryParse(reader.GetValue(1).ToString(), out IID);
           //Class vullen
           ID = IID;
           Bedrijf = reader.GetValue(2).ToString();
           Contactpersoon = reader.GetValue(3).ToString();
           Adres = reader.GetValue(4).ToString();
           Postcode = reader.GetValue(5).ToString();
           Plaats = reader.GetValue(6).ToString();
           Tel = reader.GetValue(7).ToString();
           Mobiel = reader.GetValue(8).ToString();
           Fax = reader.GetValue(9).ToString();
           Email = reader.GetValue(10).ToString();
           Website = reader.GetValue(11).ToString();
           KVK = reader.GetValue(12).ToString();
           BTW = reader.GetValue(13).ToString();
           Debiteurnr = reader.GetValue(14).ToString();
       }

But the problem is my next void:

        public void next()
       {
           nummer = nummer + 1;
           if (dbAccess.GetReader.Read() == true)
           {
               fill(dbAccess.oleDbDataReader);              
           }
           else
           {
               MessageBox.Show("Dit is de laatste klant.");
           }
       }

Doesn't work :(

I'll get exact the same data...

It doesn't jump to the next record..

What is the correct code for that ?

The NextResult() doesnt work eighter.. :(.

Edited February 7, 2006 by Jelmer

Sign In

Accessing Access Database with GenericOLEDB module

Recommended Posts

Jelmer

PlausiblyDamp

Jelmer

Jelmer

Jelmer

Join the conversation

Browse

Activity