UPDATE There has been quite a few changes in the code, but the problem still persists. I was able to delete from the customer table (and the entire row on the DGV) but it does not reach across all the other tables. I followed some advice and added a variable and a DGV click event that points to the customerId in the customer table, but really do not know how to hit the other tables for deletion. I have included the entire class.
Here is the updated code:
public partial class DeleteCustomer : Form
{
private int selectCustomerID;
public DeleteCustomer()
{
InitializeComponent();
dgvLoad();
}
private void toLandingButton_Click(object sender, EventArgs e)
{
this.Hide();
LandingPage landingPage = new LandingPage();
landingPage.Show();
}
private void deleteCustomerButton_Click(object sender, EventArgs e)
{
if (customerDeleteDataGridView.SelectedRows.Count > 0)
{
DialogResult result = MessageBox.Show("Are you sure you want to permanently delete this item?", "Delete Item?",
MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
string constr = ConfigurationManager.ConnectionStrings["MySqlKey"].ConnectionString;
MySqlConnection con = new MySqlConnection(constr);
con.Open();
String deleteCustomer = @"DELETE FROM customer
WHERE customerId = @CUSTOMERID";
MySqlCommand customerDelete = new MySqlCommand(deleteCustomer, con);
customerDelete.Parameters.AddWithValue("@CUSTOMERID", selectCustomerID);
customerDelete.ExecuteNonQuery();
String deleteAddress = @"DELETE FROM address
WHERE addressId = @ADDRESSID";
MySqlCommand addressDelete = new MySqlCommand(deleteAddress, con);
addressDelete.Parameters.AddWithValue("@ADDRESSID", customerDeleteDataGridView.SelectedRows.Count);
addressDelete.ExecuteNonQuery();
String deleteCountry = @"DELETE FROM country
WHERE countryId = @COUNTRYID";
MySqlCommand countryDelete = new MySqlCommand(deleteCountry, con);
countryDelete.Parameters.AddWithValue("@COUNTRYID", customerDeleteDataGridView.SelectedRows.Count);
countryDelete.ExecuteNonQuery();
String deleteCity = @"DELETE FROM city
WHERE cityId = @CITYID";
MySqlCommand cityDelete = new MySqlCommand(deleteCity, con);
cityDelete.Parameters.AddWithValue("@CITYID", customerDeleteDataGridView.SelectedRows.Count);
cityDelete.ExecuteNonQuery();
con.Close();
dgvLoad();
MessageBox.Show("Customer Deleted", "Complete!");
}
else
{
MessageBox.Show("Process Canceled.", "Canceled!");
}
}
else
{
MessageBox.Show("Please Select at Least One Row.", "Null Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
// Adds information from the database to the DGV upon loading
private void dgvLoad()
{
string constr = ConfigurationManager.ConnectionStrings["MySqlKey"].ConnectionString;
MySqlConnection con = new MySqlConnection(constr);
con.Open();
String sqlString = @"
SELECT customer.customerID, customerName,
address.address, address2, postalCode, phone,
city.city, country.country
FROM country, city, address, customer
WHERE customer.addressId = address.addressId
AND address.cityID = city.cityID
AND city.countryId = country.countryId";
MySqlCommand cmd = new MySqlCommand(sqlString, con);
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
DataTable customerDT = new DataTable();
adp.Fill(customerDT);
customerDeleteDataGridView.DataSource = customerDT;
customerDeleteDataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
customerDeleteDataGridView.AllowUserToAddRows = false;
con.Close();
}
// Clears the selection bar upon loading and until user selects a row
private void myBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
{
customerDeleteDataGridView.ClearSelection();
}
// DGV click event that points to the customerId cloumn in the DB table
private void customerDeleteDataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0)
{
selectCustomerID = (int)customerDeleteDataGridView.Rows[e.RowIndex].Cells[0].Value;
}
}
}
Thank you for your patience with me and as always, any help/guidance will be most appreciated.
CodePudding user response：
Replace WHERE cusomterId = @CUSTOMERID" to WHERE customerId = @CUSTOMERID"
String deleteCustomer = @"DELETE FROM customer
WHERE cusomterId = @CUSTOMERID";
MySqlCommand customerDelete = new MySqlCommand(deleteCustomer, con);
customerDelete.Parameters.AddWithValue("@CUSTOMERID", customerId);
customerDelete.ExecuteNonQuery();
CodePudding user response：
After working this for a while and getting some help via a conference call, the problem is fixed. I needed to add the other variables for Address, City and Country then add their row index to the DGV cell click event at the bottom of the class.
Here is the completed, working code:
public partial class DeleteCustomer : Form
{
private int selectCustomerID;
private string selectAddressID;
private string selectCityID;
private string selectCountryID;
public DeleteCustomer()
{
InitializeComponent();
dgvLoad();
}
private void toLandingButton_Click(object sender, EventArgs e)
{
this.Hide();
LandingPage landingPage = new LandingPage();
landingPage.Show();
}
private void deleteCustomerButton_Click(object sender, EventArgs e)
{
if (customerDeleteDataGridView.SelectedRows.Count > 0)
{
DialogResult result = MessageBox.Show("Are you sure you want to permanently delete this item?", "Delete Item?",
MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
string constr = ConfigurationManager.ConnectionStrings["MySqlKey"].ConnectionString;
MySqlConnection con = new MySqlConnection(constr);
con.Open();
String deleteCustomer = @"DELETE FROM customer
WHERE customerId = @CUSTOMERID";
MySqlCommand customerDelete = new MySqlCommand(deleteCustomer, con);
customerDelete.Parameters.AddWithValue("@CUSTOMERID", selectCustomerID);
customerDelete.ExecuteNonQuery();
String deleteAddress = @"DELETE FROM address
WHERE address = @ADDRESSID";
MySqlCommand addressDelete = new MySqlCommand(deleteAddress, con);
addressDelete.Parameters.AddWithValue("@ADDRESSID", selectAddressID);
addressDelete.ExecuteNonQuery();
String deleteCity = @"DELETE FROM city
WHERE city = @CITYID";
MySqlCommand cityDelete = new MySqlCommand(deleteCity, con);
cityDelete.Parameters.AddWithValue("@CITYID", selectCityID);
cityDelete.ExecuteNonQuery();
String deleteCountry = @"DELETE FROM country
WHERE country = @COUNTRYID";
MySqlCommand countryDelete = new MySqlCommand(deleteCountry, con);
countryDelete.Parameters.AddWithValue("@COUNTRYID", selectCountryID);
countryDelete.ExecuteNonQuery();
con.Close();
dgvLoad();
MessageBox.Show("Customer Deleted", "Complete!");
}
else
{
MessageBox.Show("Process Canceled.", "Canceled!");
}
}
else
{
MessageBox.Show("Please Select at Least One Row.", "Null Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
// Adds information from the database to the DGV upon loading
private void dgvLoad()
{
string constr = ConfigurationManager.ConnectionStrings["MySqlKey"].ConnectionString;
MySqlConnection con = new MySqlConnection(constr);
con.Open();
String sqlString = @"
SELECT customer.customerID, customerName,
address.address, address2, postalCode, phone,
city.city, country.country
FROM country, city, address, customer
WHERE customer.addressId = address.addressId
AND address.cityID = city.cityID
AND city.countryId = country.countryId";
MySqlCommand cmd = new MySqlCommand(sqlString, con);
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
DataTable customerDT = new DataTable();
adp.Fill(customerDT);
customerDeleteDataGridView.DataSource = customerDT;
customerDeleteDataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
customerDeleteDataGridView.AllowUserToAddRows = false;
con.Close();
}
// Clears the selection bar upon loading and until user selects a row
private void myBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
{
customerDeleteDataGridView.ClearSelection();
}
private void customerDeleteDataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0)
{
selectCustomerID = (int)customerDeleteDataGridView.Rows[e.RowIndex].Cells[0].Value;
selectAddressID = (string)customerDeleteDataGridView.Rows[e.RowIndex].Cells[2].Value;
selectCityID = (string)customerDeleteDataGridView.Rows[e.RowIndex].Cells[6].Value;
selectCountryID = (string)customerDeleteDataGridView.Rows[e.RowIndex].Cells[7].Value;
}
}
}
This was my first post here and I wanted to thank those who tried to help out and I will work on improving communications.