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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
CodeCruiserCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.