Avatar of browe68
browe68
 asked on

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.
Visual Basic.NETMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Nasir Razzaq

8/22/2022 - Mon
Nasir Razzaq

DataAdapter manages its connection itself. Do you close the connection explicitly?
browe68

ASKER
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?
Nasir Razzaq

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
browe68

ASKER
Any examples of that?  Thanks very much for your help.
ASKER CERTIFIED SOLUTION
Nasir Razzaq

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.