Datagridview Update with SQLCommandBuilder Problem

I have a WinForms app and have a situation where I fill a Datagridview with info and then the connection to the Database is closed.    It could some time later that the person needs to edit a cell in the Datagridview.  

This code works with the connection already open.  But, if it is not open, what do I need to add to this code to get the Update to work?   The connection to the sqlserver needs to be re-established.    Or, does the connection need to be Open at all times?

         Try
                 Dim cb As New Data.SqlClient.SqlCommandBuilder(DataAdapter)
                 DataAdapter.Update(Dataset, "Memo")
        Catch ex As Exception
                 MessageBox.Show(ex.Message, "Error")
        End Try

I am trying to follow the best practice of closing the connection when not needed.
browe68Asked:
Who is Participating?
 
CodeCruiserConnect With a Mentor Commented:
Dim conn As New SqlConnection("connectionstring")
conn.open

DataAdapter.SelectCommand.Connection = conn
DataAdapter.InsertCommand.Connection = conn
DataAdapter.UpdateCommand.Connection = conn
DataAdapter.DeleteCommand.Connection = conn
0
 
CodeCruiserCommented:
DataAdapter manages its connection itself. Do you close the connection explicitly?
0
 
browe68Author Commented:
I do close it explicitly b/c it could be minutes/hour before the update/edit may occur (depending upon user activity) and I do not want to keep the connection open that long.  So, I close it.

Maybe that is the wrong approach.    Any suggestions?
0
 
CodeCruiserCommented:
Then initialize and open it again before you call the Update method.

Create and open a new connection and set it as insertcommand, updatecommand, deletecommand connection property.
0
 
browe68Author Commented:
Any examples of that?  Thanks very much for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.