I have no idea why the program isnt working, its practically the same code I used in the last project so why wont the database update? The code for the program is as follows:

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.IO;
using System.Data.Common;
using System.Data.OleDb;  


namespace WindowsFormsApplication1
{
    // This class will contain methods and fields needed to handle the data. 
    class Data
    {
        // Fields -----------------------------------------------------------------------------------------------------------------

        private static System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection();
        private static System.Data.OleDb.OleDbDataAdapter da;
        private static System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(da);

        public static DataSet ds1; // tblCars
        public static DataSet ds2; //tblStaff

        private static string sql;
        private static string dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;";
        private static string dbSource = "Data Source = E:\\cars.accdb";

        private static string userName;
        private static string userPin;

        private static string reg;

        private static bool valid;

        public static int numOfRowsCars;
        public static int numOfRowsStaff;

        public static int Index =0;

        public static string make;
        public static string model;
        public static string registration;
        public static string mileage;
        public static string yearMade;
        public static string price;
        public static string imageNav;






        // Methods -----------------------------------------------------------------------------------------------------------------

        public static void loadDB()
        {
            // load data into datasets  - this will be called on form2 the login screen            

             con.ConnectionString = dbProvider + dbSource;
             loadTblStaff();
             loadTblCars();


        }

        public static void loadTblStaff()
        {
            // prepare, open and load the staff table into dataset ds2

            con.Open();

                sql = "SELECT * FROM tblStaff";
                ds2 = new DataSet();

                da = new OleDbDataAdapter(sql, con);
                numOfRowsStaff = da.Fill(ds2, "tblStaff");

            con.Close();

        }

        public static void loadTblCars()
        {
            // prepare, open and load the cars table into dataset ds1

            con.Open();

                sql = "SELECT * FROM tblCars";
                ds1 = new DataSet();

                da = new OleDbDataAdapter(sql, con);
                numOfRowsCars = da.Fill(ds1, "tblCars");

            con.Close();
        }       

        public static int search(string Registration)
        {
            // this method will search for registration, if found it will return the index value , else it will return max value + 1          

            for (int count = 0; count < numOfRowsCars; count++)
            {
                reg = ds1.Tables["tblCars"].Rows[count][2].ToString();
                //MessageBox.Show(reg);
                if (Registration == reg)
                {
                    // Then return the index of the registration number                     
                    return count;
                }               
            }
            return numOfRowsCars;                
        }                 


        public static void insertStaffRecord(string Name, string Password)
        {
            // This method will be used on Form4 to insert a new row into the staff table

            // create the row and add it to the dataset
            DataRow staffRow = ds2.Tables["tblStaff"].NewRow();
            staffRow[0] = Name;
            staffRow[1] = Password;

            ds2.Tables["tblStaff"].Rows.Add(staffRow);

            // update the database
            da.Update(ds2, "tblStaff");
        }

        public static void insertCarRecord(string Make,string Model,string Registration, string Mileage, string yearMade, string Price, string CarPhoto)
        {
            // This method will be used on Form4 to insert a new row into the cars table 

            // create the row and add it to the dataset
            DataRow carsRow = ds1.Tables["tblCars"].NewRow();
            carsRow[0] = Make;
            carsRow[1] = Model;
            carsRow[2] = Registration;
            carsRow[3] = Mileage;
            carsRow[4] = yearMade;
            carsRow[5] = Price;
            carsRow[6] = CarPhoto;

            ds1.Tables["tblCars"].Rows.Add(carsRow);

            // update the database
            da.Update(ds1, "tblCars");
        }

        public static void deleteRecord()
        {
            // This method will be used to delete a row of data from the cars table
            ds1.Tables["tblCars"].Rows[Index].Delete();

            //update the database
            da.Update(ds1, "tblCars");
        }        
    }
}

I get the following exceptions:

for the insertStaffRecord() method:
InvalidOperationException - Update requires a valid InsertCommand when passed DataRow collection with new rows.

for the inserCarRecord() method:
InvalidOperationException - Update requires a valid InsertCommand when passed DataRow collection with new rows.

and finally for the deleteRecord() method:
InvalidOperationException - Update requires a valid DeleteCommand when passed DataRow collection with deleted rows.

Please advise.

Recommended Answers

All 2 Replies

Instantiate the OleDbCommandBuilder inside the method:

public static void loadTblCars()
        {
          // prepare, open and load the cars table into dataset ds1
          con.Open();
          sql = "SELECT * FROM tblCars";
          ds1 = new DataSet();
          da = new OleDbDataAdapter(sql, con);
          cb = new System.Data.OleDb.OleDbCommandBuilder(da);
          numOfRowsCars = da.Fill(ds1, "tblCars");
          con.Close();
        }  

I did as you suggested and it didn't work. I'm now getting the following :

InvalidOperatioNException - Missing the DataColumn 'Make' in the DataTable 'tblStaff' for the SourceColumn 'Make'.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.