0

Hello...

I just wanna ask why is that my edit is not working?
I have 2 tables in my database, Personal data and Spouse.
And I have each datagrids for both tables.
My problem was that, when I inserted this in my code

string queryString = "SELECT BlockNo, LotNo, Numberofoccupants, Firstname, Familyname,Dateofbirth,Age,Bloodtype,Placeofbirth,Religion FROM Personaldata ";loadDataGrid(queryString)

            I can  retrieve data from both of my tables, however when I used to click edit its not working nor directed to its repected form.`

On the otherhand, when I remove it, I can perform edit but, the data that I can retrieve are only coming from my table Personaldata.
The other datagrid that's is only intended for my Spouse table returns nothing.

Here's my whole code, pls do help me

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.Data.OleDb;

namespace sad
{
    public partial class Records : Form
    {
        public OleDbConnection database;
        DataGridViewButtonColumn editButton;
        DataGridViewButtonColumn deleteButton;
        int BlocNum;
        int BlocNum2;

        #region Records constructor
        public Records()
        {

            InitializeComponent();
            string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\EtaYuy\Documents\Database2.mdb;Persist Security Info=False";
            try
            {

                database = new OleDbConnection(connectionString);
                database.Open();
                //SQL query to list movies

> !!

                string queryString = "SELECT BlockNo, LotNo, Numberofoccupants, Firstname, Familyname,Dateofbirth,Age,Bloodtype,Placeofbirth,Religion FROM Personaldata ";

                loadDataGrid(queryString);

> For table Personaldata

**

                string queryString1= "SELECT BlockNo, Firstname, Familyname,Dateofbirth, Age,Bloodtype, Placeofbirth,Religion ,Startofoccupancy,Contactnumber,NumberofChildren,Numberofdogs,Vaccinatedwithantirabies FROM Spouse";

                loadDataGrid2(queryString1);

**For table Spouse

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;

        #endregion


            }
        }
        #region Load dataGrid
        public void loadDataGrid(string sqlQueryString)
        {

            OleDbCommand SQLQuery = new OleDbCommand();
            DataTable data = null;

            dataGridView1.DataSource = null;

            SQLQuery.Connection = null;
            OleDbDataAdapter dataAdapter = null;

            dataGridView1.Columns.Clear();

            //---------------------------------
            SQLQuery.CommandText = sqlQueryString;
            SQLQuery.Connection = database;
            data = new DataTable();
            dataAdapter = new OleDbDataAdapter(SQLQuery);
            dataAdapter.Fill(data);
            dataGridView1.DataSource = data;
            dataGridView1.AllowUserToAddRows = false; 
            dataGridView1.ReadOnly = true;
            dataGridView1.Columns[0].Visible = true;
            dataGridView1.Columns[1].Width = 100;
            dataGridView1.Columns[2].Width = 200;
            dataGridView1.Columns[3].Width = 200;
            dataGridView1.Columns[4].Width = 200;
            dataGridView1.Columns[5].Width = 200;
            dataGridView1.Columns[6].Width = 100;
            dataGridView1.Columns[7].Width = 100;
            dataGridView1.Columns[8].Width = 100;
            dataGridView1.Columns[9].Width = 100;



            // insert edit button into datagridview
            editButton = new DataGridViewButtonColumn();
            editButton.HeaderText = "Edit";
            editButton.Text = "Edit";
            editButton.UseColumnTextForButtonValue = true;
            editButton.Width = 80;
            dataGridView1.Columns.Add(editButton);
            // insert delete button to datagridview
            deleteButton = new DataGridViewButtonColumn();
            deleteButton.HeaderText = "Delete";
            deleteButton.Text = "Delete";
            deleteButton.UseColumnTextForButtonValue = true;
            deleteButton.Width = 80;
            dataGridView1.Columns.Add(deleteButton);
        }

        #endregion

        public void loadDataGrid2(string sqlQueryString1)
        {
            OleDbCommand SQLQuery1 = new OleDbCommand();
            DataTable data1 = null;
            dataGridView2.DataSource = null;
            SQLQuery1.Connection = null;
            OleDbDataAdapter dataAdapter1 = null;
            dataGridView2.Columns.Clear();
            SQLQuery1.CommandText = sqlQueryString1;
            SQLQuery1.Connection = database;
            data1 = new DataTable();
            dataAdapter1 = new OleDbDataAdapter(SQLQuery1);
            dataAdapter1.Fill(data1);
            dataGridView2.DataSource = data1;
            dataGridView2.AllowUserToAddRows = false;
            dataGridView2.ReadOnly = true;
            dataGridView2.Columns[0].Visible = true;
            dataGridView2.Columns[1].Width = 100;
            dataGridView2.Columns[2].Width = 200;
            dataGridView2.Columns[3].Width = 200;
            dataGridView2.Columns[4].Width = 200;
            dataGridView2.Columns[5].Width = 200;
            dataGridView2.Columns[6].Width = 100;
            dataGridView2.Columns[7].Width = 100;
            dataGridView2.Columns[8].Width = 100;
            dataGridView2.Columns[9].Width = 100;
            dataGridView2.Columns[10].Width = 100;
            dataGridView2.Columns[11].Width = 100;
            dataGridView2.Columns[12].Width = 100;




            // insert edit button into datagridview
            editButton = new DataGridViewButtonColumn();
            editButton.HeaderText = "Edit";
            editButton.Text = "Edit";
            editButton.UseColumnTextForButtonValue = true;
            editButton.Width = 80;
            dataGridView2.Columns.Add(editButton);
            // insert delete button to datagridview
            deleteButton = new DataGridViewButtonColumn();
            deleteButton.HeaderText = "Delete";
            deleteButton.Text = "Delete";
            deleteButton.UseColumnTextForButtonValue = true;
            deleteButton.Width = 80;
            dataGridView2.Columns.Add(deleteButton);



        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Hide();
            T frm = new T();
            frm.Show();
        }
        #region Delete/Edit button handling
        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            string queryString = "SELECT BlockNo, LotNo, Numberofoccupants, Firstname, Familyname, Dateofbirth, Age, Bloodtype, Placeofbirth, Religion FROM Personaldata WHERE BlocNo = BlockNo";

            int currentRow = int.Parse(e.RowIndex.ToString());
            try
            {
                string movieIDString = dataGridView2[2, currentRow].Value.ToString();
            BlocNum = int.Parse(movieIDString);
            }
            catch (Exception ex) { }
            if (dataGridView1.Columns[e.ColumnIndex] == editButton && currentRow >= 0)
            {
            string BlockNo=dataGridView1[2, currentRow].Value.ToString();
            string LotNo = dataGridView1[3, currentRow].Value.ToString();
            string Numberofoccupants = dataGridView1[4, currentRow].Value.ToString();
                string Firstname = dataGridView1[5, currentRow].Value.ToString();
                string Familyname = dataGridView1[6, currentRow].Value.ToString();
                string Dateofbirth = dataGridView1[7, currentRow].Value.ToString();
                string Age = dataGridView1[8, currentRow].Value.ToString();
                string Bloodtype = dataGridView1[9, currentRow].Value.ToString();
                string Placeofbirth = dataGridView1[10, currentRow].Value.ToString();
                string Religion = dataGridView1[11, currentRow].Value.ToString();

                //runs form 2 for editing 
                this.Hide();
                Update f2 = new Update();
                f2.BlockNo =BlockNo;
                f2.LotNo = Convert.ToInt32(LotNo);
                f2.Numberofoccupants = Numberofoccupants;

                f2.Familyname = Familyname;
                f2.Firstname = Firstname;
                f2.Dateofbirth = Dateofbirth;
                f2.Age = Age;
                f2.Bloodtype = Bloodtype;
                f2.Placeofbirth = Placeofbirth;
                f2.Religion = Religion;
                f2.id= BlocNum;
                f2.Show();


                dataGridView1.Update();

           }
            // delete button
            else if (dataGridView1.Columns[e.ColumnIndex] == deleteButton && currentRow >= 0)
            {
                // delete sql query
                string queryDeleteString = "DELETE FROM Personaldata where LotNo = "+BlocNum+"";
                OleDbCommand sqlDelete = new OleDbCommand();
                sqlDelete.CommandText = queryDeleteString;
                sqlDelete.Connection = database;
                sqlDelete.ExecuteNonQuery();
                loadDataGrid(queryString);
            }


        }
        #endregion

        private void button1_Click(object sender, EventArgs e)
        {
            textBox1.Clear();
            string queryString = "SELECT BlockNo, LotNo, Numberofoccupants, Firstname, Familyname, Dateofbirth, Age, Bloodtype, Placeofbirth, Religion FROM Personaldata";
            loadDataGrid(queryString);
        }

        private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {


            string queryString1 = "SELECT BlockNo, Firstname, Familyname,Dateofbirth, Age,Bloodtype, Placeofbirth,Religion ,Startofoccupancy,Contactnumber,NumberofChildren,Numberofdogs,Vaccinatedwithantirabies FROM Spouse WHERE BlocNo = BlockNo";

            int current = int.Parse(e.RowIndex.ToString());
            try
            {
                string movieIDString = dataGridView2[0, current].Value.ToString();
                BlocNum2= int.Parse(movieIDString);
            }
            catch (Exception ) { }
            if (dataGridView1.Columns[e.ColumnIndex] == editButton && current >= 0)
            {
                string BlockNo = dataGridView2[0, current].Value.ToString();
                string Firstname = dataGridView2[1, current].Value.ToString();
                string Familyname = dataGridView2[2, current].Value.ToString();
                string Dateofbirth = dataGridView2[3, current].Value.ToString();
                string Age = dataGridView2[4, current].Value.ToString();
                string Bloodtype = dataGridView2[5, current].Value.ToString();
                string Placeofbirth = dataGridView2[6, current].Value.ToString();
                string Religion = dataGridView2[7, current].Value.ToString();
                string Startofoccupancy = dataGridView2[8, current].Value.ToString();
                string Contactnumber = dataGridView2[9, current].Value.ToString();
                string NumberofChildren = dataGridView2[10, current].Value.ToString();
                string Numberofdogs = dataGridView2[11, current].Value.ToString();
                string Vaccinatedwithantirabies = dataGridView2[12, current].Value.ToString();



                this.Hide();
                Update2 f2 = new Update2();
                f2.BlockNo = Convert.ToInt32(BlockNo);
                f2.Firstname = Firstname;
                f2.Familyname = Familyname;
                f2.Dateofbirth = Dateofbirth;
                f2.Age = Age;
                f2.Bloodtype = Bloodtype;
                f2.Placeofbirth = Placeofbirth;
                f2.Religion = Religion;
                f2.Startofoccupancy = Startofoccupancy;
                f2.Contactnumber = Contactnumber;
                f2.NumberofChildren = NumberofChildren;
                f2.Numberofdogs = Numberofdogs;
                f2.Vaccinatedwithantirabies = Vaccinatedwithantirabies;
                f2.id1 = BlocNum2;
                f2.Show();


                dataGridView2.Update();


            }

            else if (dataGridView2.Columns[e.ColumnIndex] == deleteButton && current >= 0)
            {
                // delete sql query
                string queryDeleteString = "DELETE FROM Spouse where BlockNo = " + BlocNum2 + "";
                OleDbCommand sqlDelete = new OleDbCommand();
                sqlDelete.CommandText = queryDeleteString;
                sqlDelete.Connection = database;
                sqlDelete.ExecuteNonQuery();
                loadDataGrid2(queryString1);
            }

        }
        }
    }

Pls, help me:( Thank you

Edited by SkyCross: wreong inputs

2
Contributors
1
Reply
20
Views
3 Years
Discussion Span
Last Post by __avd
0

In order to insert/update/delete rows, you need to configure UpdateCommand, InsertCommand and DeleteCommand properties of OldDbDataAdapter.

Well I'm suggest you to learn/use the EDM.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.