Solved

Datagridview Update with SQLCommandBuilder Problem

Posted on 2012-03-18
5
378 Views
Last Modified: 2012-03-27
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.
0
Comment
Question by:browe68
  • 3
  • 2
5 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37740181
DataAdapter manages its connection itself. Do you close the connection explicitly?
0
 

Author Comment

by:browe68
ID: 37743076
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37743943
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
 

Author Comment

by:browe68
ID: 37744548
Any examples of that?  Thanks very much for your help.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 37774278
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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question