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 Validate(string Name, string Pin)
{
// this method will be used to validate login credentials - it will return 0 for invalid, 1 for staff privalages and 2 for admin
// check to see if log in details are valid
for (int count = 0; count < numOfRowsStaff; count++)
{
userName = ds2.Tables["tblStaff"].Rows[count][0].ToString();
userPin = ds2.Tables["tblStaff"].Rows[count][1].ToString();
// MessageBox.Show("username: " + userName + '\n' + "Pin Number: " + userPin);
if (Name == userName && Pin == userPin)
{
valid = true;
break;
}
else
{
valid = false;
}
}
if (valid && Pin == "9999")
{
// returning 2 will load the admin screen
return 2;
}
else if (valid)
{
// returning 1 will load the staff screen
return 1;
}
else
{
// returning 0 will clear the input fields and return an error message
return 0;
}
}
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 updateForm3()
{
// This method will create a new instance of Form3 with the update value of the index - this method will be called any time data needs changed
Form3 updated = new Form3();
updated.Show();
}
public static void updateForm4()
{
// This method will create a new instance of Form3 with the update value of the index - this method will be called any time data needs changed
Form4 updated = new Form4();
updated.Show();
}
public static void loadRecords(int Index)
{
// This method will be used to load the appropriate values - It will take an index as its parameters
// load the appropriate data
make = ds1.Tables["tblCars"].Rows[Index][0].ToString();
model = ds1.Tables["tblCars"].Rows[Index][1].ToString();
registration = ds1.Tables["tblCars"].Rows[Index][2].ToString();
mileage = ds1.Tables["tblCars"].Rows[Index][3].ToString();
yearMade = ds1.Tables["tblCars"].Rows[Index][4].ToString();
price = ds1.Tables["tblCars"].Rows[Index][5].ToString();
imageNav = ds1.Tables["tblCars"].Rows[Index][6].ToString();
}
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");
}
public static void clearValues()
{
// This method will set all data to blank for loading the admin page
make = "";
model = "";
registration = "";
mileage = "";
yearMade = "";
price = "";
imageNav = "";
}
}
}
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.
