Update DataGridView Databas
Update DataGridView Databas
Update DataGridView Databas
Update DataGridView Databas
Update DataGridView Databas
Update DataGridView Databas Update DataGridView Databas Update DataGridView Databas Update DataGridView Databas Update DataGridView Databas Update DataGridView Databas Update DataGridView Databas Update DataGridView Databas
Update DataGridView Databas Update DataGridView Databas
Update DataGridView Databas
Go Back  Xtreme Visual Basic Talk > > > Update DataGridView Databas


Reply
 
Thread Tools Display Modes
  #1  
Old 03-06-2014, 08:43 AM
sirmilt sirmilt is offline
Contributor
 
Join Date: Jan 2003
Location: Florida
Posts: 451
Question Update DataGridView Databas


I am working on a program in Visual Basic 2010 with an Access database using a DataGridView and related controls that I drag onto a form from the Data Source.

I have been searching (unsuccessfully) for a tutorial or any instruction that show me how to save changes or add new records. From all I have read using the normal Insert or update with an SQL statement modifying the database is not necessary but I can't find any details on how to manipulate the Data Source or DataGridView to accomplish this or if it is even possible.

Any help or recommendations would be greatly appreciated.

Thanks,
Milt
Reply With Quote
  #2  
Old 03-06-2014, 10:22 AM
CharlieMay's Avatar
CharlieMay CharlieMay is offline
Regular
 
Join Date: Sep 2009
Location: Indiana
Posts: 89
Default

Typically, with a single table datasource you can use the commandbuilder to update the database through the dataadapter.

Code:
Dim con as new OledbConnection("<valid connectionstring>")
Dim ds as New DataSet
dim Da as New OledbDataAdapter("<valid select statement>", con)
da.fill(ds)
DataGridView1.DataSource = ds.Tables(0)
at this point, you should have a datagridview populated allowing you to edit a field, add a new row or hilight the row and press delete

Then
Code:
Dim cb as New OleCommandBuilder(da) '<this builds a statement based off the datasource
da.Update(ds) '<- this should commit the changes back to the datasource
Of course, you can still manipulate the datatable contained in the dataset via code (ie, ds.Tables(0).rows.remove or dim dr as datarow = ds.tables(0).newrow). This is just a quick example of how to populate a grid and use it along with the commandbuilder to update the table.
__________________
I thought I knew what I was doing...
...And then I turned on Option Strict
Reply With Quote
  #3  
Old 03-06-2014, 03:36 PM
sirmilt sirmilt is offline
Contributor
 
Join Date: Jan 2003
Location: Florida
Posts: 451
Default

Thank you for the quick response, CharlieMay.
If I have questions or problems when I try this out, I'll post them.

Milt
Reply With Quote
  #4  
Old 03-06-2014, 04:48 PM
sirmilt sirmilt is offline
Contributor
 
Join Date: Jan 2003
Location: Florida
Posts: 451
Default

Well, I didn't get far. Here are the first 5 lines of my code:

Public Class Form1
Dim con As New oledbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\MyCollections.mdb;")
Dim ds As New DataSet
Dim da As New OledbDataAdapter("SELECT * FROM tblBooks")
da.fill(ds)
TblBooksDataGridView.DataSource = ds.tables(0)

And here are the errors
'oledbConnection' is not defined.
Type 'OledbDataAdapter' is not defined.
da Declaration expected.
Declaration expected.

Interestingly the last error was the default name of the DataGridView

Also I wasn't sure if this code belonged over the Class or within the Class.

Milt
Reply With Quote
  #5  
Old 03-06-2014, 06:25 PM
sirmilt sirmilt is offline
Contributor
 
Join Date: Jan 2003
Location: Florida
Posts: 451
Default

Solved all of the problems, I forgot to enter:
Imports System.Data
Imports System.Data.OleDb

Now for another try.

Milt
Reply With Quote
  #6  
Old 03-07-2014, 09:27 AM
sirmilt sirmilt is offline
Contributor
 
Join Date: Jan 2003
Location: Florida
Posts: 451
Default Update DataGridView Database

It appears that I am getting closer, thanks to your help, CharlieMay.

I think that part of my problem is where the suggested code goes.
Does it precede the Class title or is it in the form load code?
Milt
Reply With Quote
  #7  
Old 03-07-2014, 06:02 PM
CharlieMay's Avatar
CharlieMay CharlieMay is offline
Regular
 
Join Date: Sep 2009
Location: Indiana
Posts: 89
Default

It will more than likely go in multiple places.

In the form_load, you would only want to populate the datagridview with the datasource.

You will have to widen the scope of the dataadapter and dataset so that you can use an update button or some similar way of saving your changes back to the database. If you declare the dataadapter and the dataset inside the form_load you won't have access to them when you click the save button.

Your save button could be nothing more than

Instantiating the CommandBuilder with the dataadapter and updating the ds. Again, the scope of the da and the ds will need to be at Class level so that they are available throughout the class.

Again, this is just a quick and dirty populate a gridview from a datasource and update the database with the commandbuilder to reflect the changes made to said datasource.

Here's a little tip on using the Form_Load. Make sure you wrap this code inside a try catch and display the ex.Message in the catch. The form_load can bury an error making you think nothing is wrong. The try catch will catch this exception and the messagebox will tell you what it is.
__________________
I thought I knew what I was doing...
...And then I turned on Option Strict
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Update DataGridView Databas
Update DataGridView Databas
Update DataGridView Databas Update DataGridView Databas
Update DataGridView Databas
Update DataGridView Databas
Update DataGridView Databas Update DataGridView Databas Update DataGridView Databas Update DataGridView Databas Update DataGridView Databas Update DataGridView Databas Update DataGridView Databas
Update DataGridView Databas
Update DataGridView Databas
 
Update DataGridView Databas
Update DataGridView Databas
 
-->