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
