Connection must be valid and open again
Posted by: Terri Kelley
Date: January 17, 2005 07:10PM

This may be a bit long bit I wanted to post code so hopefully someone can give a hand. I am trying to delete a row in a database and getting the exception connection must be valid and open. What I do is load a login on a form. If the login is admin then we go to admin mdi (login loads a dataset and checks users). From there select on the menu to update and the update form is loaded and a new dataset is loaded (different name etc), a combobox populated with a list of employees. Select from the combo and the rest of the form is populated. Click the delete button, a message box warning of the delete. If ok then delete the record but that is where the error is.
When you login, I see a connection to the mysql and it doesn't go away. Using MySql Administrator the time continues to go up. From the admin mdi when you click update I still see one connection (new data adapter and ds loaded) but the time has reset. When I click the combo, select a record and then click the delete button, I get the exception. I also then see two connections and the timer starts from 0 on both. The update form is returned, repopulated after that delete click (supposed to anyway) So, I am assuming that since I am going from login to a new update form the connection is getting reset. But since I am using the same form in update the connection is persistant. As I said, I am using a data adapter. Those two connections are maintained until I actually exit the application. I tried using an explicit open like I did when doing an add new employee but that didn't work in this case. It did work with add new employee. Trying the explicit open I get that a connection is already open in debugger when watching that.
So here is the code, variables are either in the subs or up top to be available for all subs:
In public sub new I call UpdateEmpConnect()

Private Sub UpdateEmpConnect()
'called from Sub New to edit existing employee
'TODO: update emp record.
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)
End Sub

And from formload I call Populatecombo

Private Sub PopulateCombo()
'called from formload
'sets primary key of datatable and stops RdoAdminCheckedChanged
combofill = false
Dim dc(1) As DataColumn
dc(0) = dtUpEmp.Columns("uid")
dtUpEmp.PrimaryKey = dc
'binds data to combobox and fills box
cboSelectEmp.DataSource = dtUpEmp
cboSelectEmp.DisplayMember = "name"
cboSelectEmp.ValueMember = "uid"
'adds empty row to combox and sets box to that row
drUpEmp = dsUpEmp.Tables(0).NewRow
drUpEmp("name") = "Select Name"
drUpEmp("uid") = 0
Dim dName As New DateTime
drUpEmp("dob") = dName
dsUpEmp.Tables(0).Rows.Add(drUpEmp)
cboSelectEmp.SelectedIndex = cboSelectEmp.FindStringExact("Select Name")
'Allows RdoAdminCheckedChanged to check for change
combofill = true
End Sub

Next I click the combobox to select the employee:

Private Sub CboSelectEmpSelectedIndexChanged(sender As System.Object, e As System.EventArgs)
'load the uid and find values
Try
'get selected row
Dim rowSel As DataRowView
rowsel = Ctype(cboSelectEmp.SelectedItem, DataRowView)
'populate the fields
txtUser.Text = rowSel("username").ToString
txtFirstName.Text = rowSel("firstname").ToString
txtMi.Text = rowSel("mi").ToString
txtLastName.Text = rowSel("lastname").ToString
Dim mydate As Date = Convert.ToDateTime(rowSel("dob").tostring)
Dim mydatedob as String = mydate.ToString("MMddyyyy")
txtMaskDob.Text = mydatedob
txtMaskSsn.Text = rowSel("ssn").ToString
txtStreet.Text = rowSel("street").ToString
txtCity.Text = rowSel("city").ToString
txtState.Text = rowSel("state").ToString
txtMaskPhone.Text = rowSel("phone").ToString
txtMaskZip.Text = rowSel("zip").ToString
'selects correct radio button
Dim empstat As String
empstat = rowSel("emptype").ToString
If empstat = "a" Then
rdoAdmin.PerformClick()
Else If empstat = "i" Then
rdoinactive.PerformClick()
Else
rdoEmp.PerformClick()
End If
Catch newempex As Exception
MessageBox.Show(newempex.Message)
End Try
End Sub

Then click the delete button to try to delete the record:

Private Sub BtnDeleteClick(sender As System.Object, e As System.EventArgs)
If MessageBox.Show("Are you sure you wish to remove this entry from the database?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = DialogResult.Yes Then
'TODO: delete the emp from database
'get the selected row and delete it
Dim cnDelEmp As New MySqlConnection(cnnOffice)
cnDelEmp.Open( )
Dim drDelEmp As Datarow = dtUpEmp.Rows(cboSelectEmp.SelectedIndex)
drDelEmp.Delete()
Try
Dim delemp As New MySqlCommandBuilder(daUpEmp)
'delete data from database
daUpEmp.Update(dsUpEmp, "upemp")
Messagebox.Show("Record has been deleted")
Catch newempex As Exception
MessageBox.Show(newempex.Message)
End Try
'Get current data from database
UpdateEmpConnect()
'clear the form
resetform()
'populate the combo from current
PopulateCombo()

End If

End Sub

In this last, is where I was trying to do the explicit open and close. If I dont do that I still get the same error on the connection. Using the above in the debugger, the line daUpEmp.Update(dsUpEmp, "upemp") is where I get the connection is already open.
So, after all that long windedness, anyone have any ideas on a workaround. I know there has been some discussion on connections not opening or closing with a data adapter using the connector/Net but there doesn't seem to be a way to close the previous connection. Curious is that the add a new employee works. Difference is I don't populate a combo, just a form with textboxes is displayed for the user.

Thanks

tk

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.