Solved

Datagridview Update with SQLCommandBuilder Problem

Posted on 2012-03-18
5
370 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

Suggested Solutions

Title # Comments Views Activity
VB.NET 1 22
Help Required 2 32
Write to a printer using vb.net 9 26
convert null in sql server 12 33
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

770 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