Connection must be valid and open exception
Posted by: Terri Kelley
Date: December 16, 2004 10:05AM

I am trying to add a new row to the db using MySql Connector. Mysql is running on the network using slackware as the os. This is a windows form app where the admin is entering a new employee into a mysql db. Admin fills in the form and clicks submit. It checks to make sure entries are valid then calls this to insert the new row:

Private Sub AddEmp()
Dim cnnUpOffice As String
cnnUpOffice = "Database = data; DataSource = server; User ID=user; Password=password;"
Dim cmdUpEmp As String
cmdUpEmp = "Select * from emp"
Dim daUpEmp As New MySqlDataAdapter(cmdUpEmp, cnnUpOffice)
Dim dsUpEmp As New DataSet
daUpEmp.Fill(dsUpEmp, "emp")
Dim dtUpEmp As New DataTable
dtUpEmp = dsUpEmp.Tables(0)
Dim newRow As DataRow = dsUpEmp.Tables("emp").NewRow
newRow("username") = txtUser.Text
newRow("firstname") = txtFirstName.Text
newRow("lastname") = txtlastname.Text
newRow("ssn") = Convert.ToInt32(strDataSsn)
newRow("street") = txtStreet.Text
newRow("city") = txtCity.Text
newRow("state") = txtState.Text
newRow("zip") = Convert.ToInt32(strDataZip)
strHashedPass = ModuleHash.PassHash(txtPass.Text)
newRow("password") = strHashedPass
newRow("phone") = Convert.ToInt64(strDataPhone)
newRow("emptype") = txtEmpType.Text
newRow("mi") = txtMi.Text
newRow("inactive") = 0
newRow("dob") = strDataDob
Dim upcb As New MySqlCommandBuilder(daUpEmp)
daUpEmp.Update(dsUpEmp, "emp")
MessageBox.Show("Employee Added")

Catch newempex As Exception
End Try

End Sub

What I get is the error listed in the title. I know my connection works because we go thru a login screen using a dsNewEmp along the same syntax as above. It is my understanding that the adapter will open the connection when called then close it when done. But obviously I am missing something.
At first the user enters data into the form then clicks a submit button. Data on the form is then checked for duplicate items in the dataset. If no duplicates then proceeds to this to add the new record. That part works. I used a different dataset etc from this one. Originally I tried to use the same ds and da in both the duplicate check and here but had the same error. So tried this, using all new in this subroutine. Same error.
Any help appreciated.


Options: ReplyQuote

Written By
Connection must be valid and open exception
December 16, 2004 10:05AM

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.