Accessing Access Database with GenericOLEDB module

Jelmer · Feb 7, 2006

Hello

Ive downloaded this project to try to acces my Access database:

Code:

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 ill 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:

C#:

        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:

C#:

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 ?

PlausiblyDamp · Feb 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 objects .ExecuteReader method.

Jelmer · Feb 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..

C#:

                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 ?

Jelmer · Feb 7, 2006

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

First ill rebuild the old code from the example..
Then ill edited it so i dont need a listbox anymore and replaced it by messageboxes.

Ill copied the codes to my own project.. and it worked :S

The code:

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 · Feb 7, 2006

Ive build very thing into my class.. it works !
But.. ive got some problems to get to the next or previeus record.

Normally youve got a while loop ore something.
Now i use the read function:

Code:

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

fill does this:

Code:

        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:

Code:

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

Doesnt work

Ill get exact the same data...
It doesnt jump to the next record..
What is the correct code for that ?

The NextResult() doesnt work eighter..

.

Accessing Access Database with GenericOLEDB module

Jelmer

Well-known member

PlausiblyDamp

Administrator

Jelmer

Well-known member

Jelmer

Well-known member

Jelmer

Well-known member