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 = "";
        }
    }
}

Okay My bad, I forgot to do the delete, I now did it and it succesfully deleted. SO Now I just need to get the insert working and I can sleep! Please advise on my situation, thanks in advance.

I really need help with this, the string is displaying the right data i want to input into the database, but I keep getting OleDBException was unhandled : No value given for one or more required parameters.But before when my string wasn't formatted properly, it was insert data into the database. Whats changed since i got it formatted?

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);
            MessageBox.Show(insertStaffSQL);

            OleDbCommand staffCom = new OleDbCommand(insertStaffSQL, con);

            staffCom.ExecuteNonQuery();
            MessageBox.Show("Data stored succesfully");

            staffCom.Dispose();
            con.Close();
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.