I have been trying to get my database insert and delete to work for a long time now, and I still cant get it working. I now have just 2 hours left before I absolutely have to have it finished and I can only insert numeric values. I suspect im formatting it wrong? But my delete method also doesn't work, what is wrong with my code? please help, this is becoming incredibly stressful and I need some sleep. Any advice is appreciated!
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;
private static string insertStaffSQL;
private static string insertCarSQL;
// 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
con.Open();
DataRow staffRow = ds2.Tables["tblStaff"].NewRow();
staffRow[0] = Name;
staffRow[1] = Password;
ds2.Tables["tblStaff"].Rows.Add(staffRow);
insertStaffSQL = string.Format("insert into tblStaff values((0),(1))", Name, Password);
OleDbCommand staffCom = new OleDbCommand(insertStaffSQL, con);
staffCom.ExecuteNonQuery();
MessageBox.Show("Data stored succesfully");
staffCom.Dispose();
con.Close();
// 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);
con.Open();
insertCarSQL = string.Format("insert into tblCars values((0),(1),(2),(3),(4),(5),(6))", Make, Model, Registration, Mileage, yearMade, Price, CarPhoto);
OleDbCommand CarCom = new OleDbCommand(insertCarSQL, con);
CarCom.ExecuteNonQuery();
MessageBox.Show("Data stored succesfully");
CarCom.Dispose();
con.Close();
// 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 = "";
}
}
}