unable to delete record in database

[tlhorses]

In the following form, I populate a combobox which is bound. In the select I concetante lastname and firstname and that is the displaymember. I also create a row in the code which will be the first item displayed to the user in the combo which tells them to select name. displayvalue is the primary key of the database, uid. After it is populated the user selects a name and the rest of the form is populated with the info for that name. All of that works well. But when the user clicks the delete button to delete that record it doesn't update the database. No exceptions are thrown (now). I am using mysql as the db.

RobEmDee if you read this, note it is now in C# instead of vb.net. Your fault...lol.

Also I declared the datarows, sets, tables and adapter at the top so I could use them between subs. Hope that is right?

 

Here is the code:

This populates the combo when the form is loaded.

private void PopulateCombo()
{
//called from form load;
cmdUpEmp = "Select *, Concat(lastname, ', ' , firstname) as name From emp Order By name ";
daUpEmp = new MySqlDataAdapter(cmdUpEmp, cnnOffice);
dsUpEmp = new DataSet();
daUpEmp.Fill(dsUpEmp, "upemp");
dtUpEmp = dsUpEmp.Tables[0];
//prevents rdochanged subs from executing their code;
combofill = false;
//bind the combo;
DataColumn[] dc = new DataColumn[1];
dc[0] = dtUpEmp.Columns["uid"];
dtUpEmp.PrimaryKey = dc;
cboSelectEmp.DataSource = dtUpEmp;
cboSelectEmp.DisplayMember = "name";
cboSelectEmp.ValueMember = "uid";
//create a new row to initially display in the combo;
drUpEmp = dsUpEmp.Tables[0].NewRow();
drUpEmp["name"] = "Select Name";
drUpEmp["uid"] = 0;
DateTime dName = new DateTime();
drUpEmp["dob"] = dName;
dsUpEmp.Tables[0].Rows.Add(drUpEmp);
cboSelectEmp.SelectedIndex = cboSelectEmp.FindStringExact("Select Name");
//lets the rdochanged subs run;
combofill = true;
}

 

This is after the user selects the name wanted and they click the delete button:

 

private void BtnDeleteClick(object sender, System.EventArgs e)
{
if (MessageBox.Show("Are you sure you wish to remove this entry from the database?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes) {
	try {
		//deletes the select name row added in populatecombo()
		DataView dv = dtUpEmp.DefaultView;
		Object[] blankrow = new Object[1];
		dv.Sort = "name";
		blankrow[0] = "Select Name";
		int i = dv.Find(blankrow);
		dtUpEmp.Rows[i].Delete();
		//deletes the current row selected in the combobox
		drUpEmp = dtUpEmp.Rows[cboSelectEmp.SelectedIndex];
		drUpEmp.Delete();
		dsUpEmp.AcceptChanges();
		//updates the database
		MySqlCommandBuilder delemp = new MySqlCommandBuilder(daUpEmp);
		daUpEmp.Update(dsUpEmp, "upemp");
		MessageBox.Show("Record has been deleted");
	} catch (Exception newempex) {
		MessageBox.Show(newempex.Message);
	}
	//reset form and re-populate the combo with current
	resetform();
	PopulateCombo();
}
}

 

I thought that perhaps since I was adding a new row in the populate that maybe I needed to delete that row first because it doesn't need to go to the database. I am not sure on that since it is created in code whether it would go or not. Same applies for the "name" that I create in the select statement. Don't know if that becomes an actual col in the ds or dt or not. So don't know if I need to try to delete it. Tried to but got an exception say col not found so guess it doesn't.

Anyway, watching the connection on the server I get one the first time I access the db from the login form. The connection stays but sleeps. It resets when I hit the populate combo sub on this form load. Then it sleeps again until I hit the populate combo at the end of btndelete sub. I would have thought it would access the db at the line daUpEmp.Update(dsUpEmp, "upemp"); above but it doens't appear to. I was getting a connection must be valid or open exception until I put the line in dsUpEmp.AcceptChanges();.

So, anyone have any ideas why I cannot update the database?

 

Thanks

 

tk

[RobEmDee]

tlhorses:

When you call AcceptChanges() on a DataTable all edits are finalized......DataRowStates reset and deleted rows removed. I think that because you are calling AcceptChanges() before doing the db updates, there is nothing pending for the DataAdapter to handle.

 

Nice move to C#....... ;)

[tlhorses]

arg...you are correct. So moved I moved it down below the .update. Now getting the famous connection must be valid or open exception. That is basically what started the re write of the delete sub. Cannot figure out why the exception is thrown.

 

tk

[RobEmDee]

tlhorses:

Try double-checking to make sure the connection is already opened and/or can be opened before executing the Update:

 

if(daUpEmp.UpdateCommand.Connection.State != ConnectionState.Open)
{
   try
   {
       daUpEmp.UpdateCommand.Connection.Open();
   }
   catch(Exception ex)
   {
       // Connection to db failed
       MessageBox.Show("Connection error: \n" + ex.ToString());
   }
}

 

I think you probably just need to explicitly Open() the connection before calling Update().

[tlhorses]

Well, I checked and there is a connection. I think a Connector/Net has a bug (using MySql) because it should close connection after it uses it. But there is a persistant connection from the time of logon with the app. Explicit open tells me there is one already open. So, I changed connectors to MyODBC. So in the above code change the MySql... stuff to Odbc... . Now the connection opens and closes as the data adapter uses it. So that looks right. But now get an exception on the following line from above:

 

daUpEmp.Update(dsUpEmp, "upemp");

 

************** Exception Text **************

System.NullReferenceException: Object reference not set to an instance of an object.

at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)

at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)

at caccttime.frmNewEmp.BtnDeleteClick(Object sender, EventArgs e) in g:\Documents and Settings\tkelley\My Document\SharpDevelop Projects\caccttime\frmNewEmp.cs:line 745

at System.Windows.Forms.Control.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message& m)

at System.Windows.Forms.ButtonBase.WndProc(Message& m)

at System.Windows.Forms.Button.WndProc(Message& m)

at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)

at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)

at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

 

Which would normally mean something isn't instantiated. But all that is as far as I can tell. I am trying to use the same adapter, dataset etc between the two subs so those are declared at the start of the class.

Ideas?

 

tk

[RobEmDee]

tlhorses:

Have you tried other Update overloads?

 

// e.g.
daUpEmp.Update(dsUpEmp.Tables[0]);

 

 

Well, I checked and there is a connection. I think a Connector/Net has a bug (using MySql) because it should close connection after it uses it. But there is a persistant connection from the time of logon with the app. Explicit open tells me there is one already open. So, I changed connectors to MyODBC. So in the above code change the MySql... stuff to Odbc... . Now the connection opens and closes as the data adapter uses it. So that looks right. But now get an exception on the following line from above:

 

daUpEmp.Update(dsUpEmp, "upemp");

 

************** Exception Text **************

System.NullReferenceException: Object reference not set to an instance of an object.

at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)

at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)

at caccttime.frmNewEmp.BtnDeleteClick(Object sender, EventArgs e) in g:\Documents and Settings\tkelley\My Document\SharpDevelop Projects\caccttime\frmNewEmp.cs:line 745

at System.Windows.Forms.Control.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message& m)

at System.Windows.Forms.ButtonBase.WndProc(Message& m)

at System.Windows.Forms.Button.WndProc(Message& m)

at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)

at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)

at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

 

Which would normally mean something isn't instantiated. But all that is as far as I can tell. I am trying to use the same adapter, dataset etc between the two subs so those are declared at the start of the class.

Ideas?

 

tk

[tlhorses]

Yep, same error.

So here is the current code and say we are going to update or delete a record:

 

{
public class frmNewEmp : System.Windows.Forms.Form
{ //snip
		private string cnnOffice="Driver={MySQL ODBC 3.51 Driver}; Database=db; Server=server; UID=uid; Password=pw;
	private System.Data.DataSet dsNewEmp;
	private System.Data.DataSet dsUpEmp;
	private System.Data.DataTable dtNewEmp;
	private System.Data.DataTable dtUpEmp;
	private OdbcDataAdapter daUpEmp;
	private OdbcDataAdapter daNewEmp;
	private DataRow drUpEmp;
	private string cmdUpEmp;
	private string cmdNewEmp;
           //snip
	public frmNewEmp()
	{
		//base.New();
		this.InitializeComponent();
		if (PublicModule.empUpdate == false) {
			NewEmpConnect();
		} else {
			UpdateEmpConnect();
			PopulateCombo();
		}
	}

 

so off to updateempconnect:

 

public void UpdateEmpConnect()
{
cmdUpEmp = "Select *, Concat(lastname, ', ' , firstname) as name From emp Order By name ";
daUpEmp = new OdbcDataAdapter(cmdUpEmp, cnnOffice);
dsUpEmp = new DataSet();
daUpEmp.Fill(dsUpEmp, "upemp");
dtUpEmp = dsUpEmp.Tables[0];
}

 

Then populatecombo:

 

private void PopulateCombo()
{
//called from form load;
//prevents rdochanged subs from executing their code;
combofill = false;
//bind the combo;
DataColumn[] dc = new DataColumn[1];
dc[0] = dtUpEmp.Columns["uid"];
dtUpEmp.PrimaryKey = dc;
cboSelectEmp.DataSource = dtUpEmp;
cboSelectEmp.DisplayMember = "name";
cboSelectEmp.ValueMember = "uid";
//create a new row to initially display in the combo;
drUpEmp = dsUpEmp.Tables[0].NewRow();
drUpEmp["name"] = "Select Name";
drUpEmp["uid"] = 0;
DateTime dName = new DateTime();
drUpEmp["dob"] = dName;
dsUpEmp.Tables[0].Rows.Add(drUpEmp);
cboSelectEmp.SelectedIndex = cboSelectEmp.FindStringExact("Select Name");
//lets the rdochanged subs run;
combofill = true;
}

 

From there a user selects a name from the combo then clicks the delete button to delete that record:

 

private void BtnDeleteClick(object sender, System.EventArgs e)
{
if (MessageBox.Show("Are you sure you wish to remove this entry from the database?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes) {
	try {
		//deletes the select name row added in populatecombo()
		DataView dv = dtUpEmp.DefaultView;
		Object[] blankrow = new Object[1];
		dv.Sort = "name";
		blankrow[0] = "Select Name";
		int i = dv.Find(blankrow);
		dtUpEmp.Rows[i].Delete();
		//deletes the current row selected in the combobox
		drUpEmp = dtUpEmp.Rows[cboSelectEmp.SelectedIndex];
		drUpEmp.Delete();
		//updates the database
		OdbcCommandBuilder delemp = new OdbcCommandBuilder(daUpEmp);
		daUpEmp.Update(dsUpEmp, "upemp");
		dsUpEmp.AcceptChanges();
		MessageBox.Show("Record has been deleted");
	} catch (Exception newempex) {
		MessageBox.Show(newempex.Message);
	}
	//reset form and re-populate the combo with current
	resetform();
	PopulateCombo();
}
}

 

And the line daUpEmp.Update(dsUpEmp, "upemp") is where the exception is thrown:

Object reference not set to an instance of an object.

 

 

tk

Edited January 24, 2005 by tlhorses

[RobEmDee]

tlhorses:

I noticed that you are not actually deleting your dummy row. Try replacing this:

//deletes the select name row added in populatecombo()
DataView dv = dtUpEmp.DefaultView;
Object[] blankrow = new Object[1];
dv.Sort = "name";
//( Find() only works on PrimaryKey Column - you have PK of uid)
blankrow[0] = "Select Name";
int i = dv.Find(blankrow);
dtUpEmp.Rows[i].Delete();

 

With this:

//deletes the select name row added in populatecombo()
DataRow row = dtUpEmp.Rows.Find(0);
row.Delete();

 

More importantly, I don't think that the CommandBuilder would succeed at creating an UPDATE SQL statement from a SELECT statement which included a concatenation of two fields. You will probably need to manually configure your DataAdapters using the VS Designer.....in fact, I would suggest that you use the Designer to create your db objects in your current situation...it will save you a lot of time and heartache.

Edited January 24, 2005 by RobEmDee

[tlhorses]

ok, question on the delete row you suggested. That row added may not be the first row, in fact it is not. So I am not sure that will work.

VS Designer I will have a problem with since I am not using VS.Net. Using SharpDevelop so may have to look into if there is a way to do that in it. If I could just do a concatenate when I fill the combo I would but couldn't figure out how to do that either. So did it in the select statement. Have to look at that again.

Also, as you posted in another of my threads, C# is less coding. Still cleaning it up from the vb.net version though.

 

Thanks for the help so far.

 

tk

[tlhorses]

Well just to simplify matters I removed the concat in the select statement and also removed the blank row I added to the combo. So the populatecombo now just has:

cboSelectEmp.DataSource = dtUpEmp;
cboSelectEmp.DisplayMember = "lastname";
cboSelectEmp.ValueMember = "uid";

 

 

the click just has:

       drUpEmp = dtUpEmp.Rows[cboSelectEmp.SelectedIndex];
       drUpEmp.Delete();
       //updates the database
       OdbcCommandBuilder delemp = new OdbcCommandBuilder(daUpEmp);
       daUpEmp.Update(dsUpEmp, "upemp");
       dsUpEmp.AcceptChanges();

 

Still get the Object reference not set to an instance of an object exception thrown at:

daUpEmp.Update(dsUpEmp, "upemp");

 

tk

[PlausiblyDamp]

If you step through the code in a debugger what are the values for daUpEmp and dsUpEmp when you reach the line that throws the error?

[tlhorses]

I placed a watch on these:

daUpEmp = {Microsoft.Data.Odbc.OdbcDataAdapter}

dsUpEmp = {System.Data.DataSet}

These remain the same from the time this form is loaded all the way past the catch.

I expanded them in the watch window and saw that under daUpEmp the cmd items (eg cmdDelete) showed <undefined value> even after passing through the Commandbuilder.

I also put a watch on daUpEmp.Update which had 5 update catagories under it. Each update there had daUpEmp.Update+0//method + offset as a value. There was one item under each of those. The first one was System.Data.DataSet and had an undefined value. Same for the other four, .DataRow, .DataTable, DataSet(string null), and .DataSet (tablemapping). These also remained the same through the run.

 

tk

 

EDIT:

Also, this is an mdichild form. It is called from mdiAdmin. So basically the user gets a login fomr. If the user is an admin then it goes to mdiAdmin. From the menu at the top of the mdiAdmin the user selects employees > Update which starts this form. Code on the mdiAdmin to call this is:

private void mnuEmpUpdate_Click(object sender, System.EventArgs e)
{
PublicModule.empUpdate = true;
Form frmNewEmp = new frmNewEmp(); 
frmNewEmp.MdiParent = this;
frmNewEmp.Show();
}

As I watched the click event here, the program would get to Form frmNewEmp = new FrmNewEmp(); and go to frmNewEmp, initialize it, make the connection listed in previous message, load the combo then return here and pass through the .Show to finally display the frmNewEmp. From there is as I said above.

 

tk

Edited January 24, 2005 by tlhorses

[RobEmDee]

tlhorses:

Just to clarify....the code below will find the row with a PrimaryKey of 0......which would be your dummy row with uid set to 0.

 

//deletes the select name row added in populatecombo()
DataRow row = dtUpEmp.Rows.Find(0);
row.Delete();

 

Have you considered just trashing the CommandBuilder and putting together the code yourself? May be worth it at this stage.

[tlhorses]

Rob,

Thanks, didn't think of that pk that way. I have thought of trashing the CommandBuilder but would really like to get it working since I have a lot more to do on this project working with tables.

Also did notice one thing. I let it run through the exception and then looked in the combobox. That record I deleted is no longer listed so it is deleting it from the dt. But exit out of the form then come back and the record is there so not getting it out of the db. With the exception I knew it wasn't getting it there but didn't realize it was removing it from dt or ds. But also thought I was getting a fresh set from the db when I repopulated the combo.

 

tk

[tlhorses]

Ok, a bit more on this. Again I am using MySql as the db. I have used two different connectors and I get an exception at the exact same line. Using the ODBC connector I get Object reference not set to an instance of an object. Using the /NET connector I get Connection must be valid and open. Don't know if that helps or not. I wonder if I am having a problem with "emp" in that line? Is there another way to specify that? There is bound to be a way to carry the same data adapter, ds and dt through the form no matter what sub I am in.

 

tk

[RobEmDee]

tk:

Are you still using an '*' in your original SELECT statement you are using to instantiate the DataAdapter? If so, you should try a complete statement with the field names.

 

Ok, a bit more on this. Again I am using MySql as the db. I have used two different connectors and I get an exception at the exact same line. Using the ODBC connector I get Object reference not set to an instance of an object. Using the /NET connector I get Connection must be valid and open. Don't know if that helps or not. I wonder if I am having a problem with "emp" in that line? Is there another way to specify that? There is bound to be a way to carry the same data adapter, ds and dt through the form no matter what sub I am in.

 

tk

[tlhorses]

Ok, I finally got this stinking thing working.

Up in the Public class I declare the connection:

private MySqlConnection cnUpEmp;

 

In the initial updateconnection sub I then instantiate it and use it in the data adapter instead of the string for the connection which opens it, then I close it at the end of that sub.

 

private void UpdateEmpConnect()
{
cmdUpEmp = "Select * From emp Order By lastname ";
cnUpEmp = new MySqlConnection(cnnOffice);
daUpEmp = new MySqlDataAdapter(cmdUpEmp, cnUpEmp);
MySqlCommandBuilder delemp = new MySqlCommandBuilder(daUpEmp);
dsUpEmp = new DataSet();
daUpEmp.Fill(dsUpEmp, "upemp");
dtUpEmp = dsUpEmp.Tables[0];
cnUpEmp.Close();
}

 

Then after the user selects the name from the combo he clicks the delete button. I had to manually open the connection, do the .update, then close the connection again.

 

private void BtnDeleteClick(object sender, System.EventArgs e)
{
if (MessageBox.Show("Are you sure you wish to remove this entry from the database?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes) {
	try {
		//deletes the current row selected in the combobox
		drUpEmp = dtUpEmp.Rows[cboSelectEmp.SelectedIndex];
		drUpEmp.Delete();
		cnUpEmp.Open();
		daUpEmp.Update(dsUpEmp, "upemp");
		dsUpEmp.AcceptChanges();
		cnUpEmp.Close();
		MessageBox.Show("Record has been deleted");
	} catch (Exception newempex) {
		MessageBox.Show(newempex.Message);
	}
	//reset form and re-populate the combo with current
	resetform();
	PopulateCombo();
}
}

 

Not the prettiest thing but it finally works.

 

tk

[tlhorses]

RobEmDee,

Forgot to say thanks for plugging along with me on this. Also you were correct on me not deleting my row. Your's worked. Don't know why but I have a tendecy to make things harder.

Also the concat in the select statement didn't work with the commandbuilder. So I ended up creating a "calculated" col in my dt that combined lastname and firstname and using that as the display member for my combo. Then delete both the "Select Name" row and the col before .update.

Again, thanks for the help!

 

tk