?
Solved

DataGridView - Save data to access database VB.net

Posted on 2014-01-13
13
Medium Priority
?
18,333 Views
Last Modified: 2014-10-04
Good afternoon,

I've been searching all over the web for help with this but I'm not getting exactly what I want. I have an application that uses an access database. The data shows in DataGridView. Its displaying the data just fine in the datagridview but when I make changes in the DataGridView, its not saving. Please review my code and tell me what I'm doing wrong?

    Private Sub GetData_BRL_Click(sender As Object, e As EventArgs) Handles GetData_BRL.Click
        Dim con As New OleDbConnection
        Dim ds As New DataSet
        Dim dt As New DataTable
        Dim da As New OleDbDataAdapter

        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\data\PersonelTracker.accdb"
        con.Open()
        ds.Tables.Add(dt)
        da = New OleDbDataAdapter("Select * from BadgeRequestLog", con)
        da.Fill(dt)
        DG_BRL.DataSource = dt.DefaultView
        con.Close()


    End Sub


    Private Sub Upload_BRL_Click(sender As Object, e As EventArgs) Handles Upload_BRL.Click

        Dim con As New OleDbConnection
        Dim ds As New DataSet
        Dim dt As New DataTable
        Dim da As New OleDbDataAdapter

        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\data\PersonelTracker.accdb"
        con.Open()
        ds.Tables.Add(dt)
        da = New OleDbDataAdapter("Select * from BadgeRequestLog", con)
        da.Update(dt)
        con.Close()

    End Sub

Open in new window

0
Comment
Question by:AnthonySmithMCP
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 40
ID: 39777846
First of all, you are making a mistake that unfortunately shows up everywhere on the Internet, so everybody repeats it.

You do not need a DataSet when you work with only one DataTable. The role of the DataSet is to establish relations between tables, so if you have only one table, or if you have many tables without relations between them (relations established through code, not the ones you have in the database), a Dataset is overkill and uses up resources for no reason.

All the lines with ds in you code are thus useless.

Contrary to standard ADO, Update does not work automatically in ADO.NET. You need to provide the SQL commands (INSERT, UPDATE and DELETE) that the Update method will use to make the changes in the database. The old ADO did that automatically, but since ADO.NET is geared toward professional developers, they provided more powerful ways to do things. These sometimes require more work.

You can provide your own, and sometimes you must, but since you work from a single table, as long as that table has a primary key, you can use a tool provided by ADO.NET to generate the necessary commands.

First of all, before calling Fill, call FillSchema so that ADO.NET has a better idea of the structure of your table:
   da.FillSchema(dt)
    da.Fill(dt)

Open in new window

Then, before you call Update, create a CommandBuilder. This is the object that will create the SQL commands for you:
		Dim con As New OleDbConnection
		Dim dt As New DataTable
		Dim da As OleDbDataAdapter
		Dim cb As OleDbCommandBuilder

		con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\data\PersonelTracker.accdb"
		con.Open()
		da = New OleDbDataAdapter("Select * from BadgeRequestLog", con)
		cb = New OleDbCommandBuilder(da)
		da.Update(dt)
		con.Close()

Open in new window

Note that I removed ds.

Note also that I removed the New for the DataAdapter in your series of variable declarations. Since you call New later in the code, do not do it at the declaration. This creates a first DataAdapter that you never use and that needs to be destroyed when the second New OleDbDataAdapter is encountered in the code.
0
 
LVL 1

Author Comment

by:AnthonySmithMCP
ID: 39778066
JamesBurger,

Thanks for your assistance.  When I add the line "da.fillschema(da)" I get an error in my code (see screenshot) I did everything you said (I commented out the da.fillschema(da) line) but it sill wont update. Not sure if I'm doing something wrong. Please see my attached screenshot and code.

code
0
 
LVL 40
ID: 39778102
FillSchema (dt), not da. You are getting the schema (definition) of the table, not the DataAdapter.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 1

Author Comment

by:AnthonySmithMCP
ID: 39785310
I maybe doing something else wrong. Because even if I change it to FillSchema (dt), I still get the same error "Overload resolution failed because no accessible "fillSchema" accepts this number of arguments" (see screenshot).

Any other suggestions? Again thank you for your help.screenshot
0
 
LVL 1

Author Comment

by:AnthonySmithMCP
ID: 39785459
I maybe wrong but I think my problem is this. When I click the button to execute function "Private Sub GetData_BRL_Click" it pulls the data from the database and displays it.

Then I make changes to it in the datagrid.

Then I click the upload button to execute the "Private Sub Upload_BRL_Click" to attempt to save the changes.

In the code for  "Private Sub Upload_BRL_Click" its getting the data from the database again

        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\data\PersonelTracker.accdb"
        con.Open()
        da = New OleDbDataAdapter("Select * from BadgeRequestLog", con)

Open in new window


So right now the data adapter/data table don't have any new information to update when. it's updating it with the same data that it just pulled from the database..

Does this make any sense? I know what I'm trying to say but might not be explaining it correctly.
0
 
LVL 40
ID: 39785521
Sorry, my last answer was incomplete. I focused on the fact that you did not fill the right object, and since I was not in Visual Studio, I did not have IntelliSense to provide me with details that you cannot remember with the thousands of methods that exist in the Framework.

Read the error message carefully. ...no accessible "FillSchema" accepts this number of arguments.

FillSchema requires 2 parameters and you provided only one.

Try

da.FillSchema(dt, , SchemaType.Source)
0
 
LVL 1

Author Comment

by:AnthonySmithMCP
ID: 39785562
Thank you, That did take away the error. However I'm still faced with my original problem. The data is not saving? I wrote a post earlier. Did you have a chance to read it? Could that be the problem? Maybe I need to declare the data table  and adapter outside the sub routines?
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 2000 total points
ID: 39785812
Defining the DataAdapter at the Form level is not absolutely necessary, but is usually a good practice, because it makes it easier to insure that the SelectCommand used to fill the DataTable is the same one used by the CommandBuilder to create the update commands.

Looking at your code carefully however, I now see why it does not work for your purpose. In your Upload_BRL_Click, you create a new DataTable from scratch then update it. It's not the same DataTable as the one that was used to fill the grid.

There are different ways to deal with that, but the simplest one would  be to declare your da and dt variables at the form level, between Public Class Form1 and the first Sub. That way you are using the same DataAdapter and the same DataTable everywhere.
0
 
LVL 1

Author Comment

by:AnthonySmithMCP
ID: 39785859
JamesBurger,

I thought that might have been the problem. I did what you're saying earlier but got a "Syntax error in UPDATE statement".  (see screenshot) I'm looking around to see what that mean.

Will the data in the database have any effect on this? I simply want it to overwrite what's there...
error3
0
 
LVL 1

Author Comment

by:AnthonySmithMCP
ID: 39785975
I got it working. Thanks for you help. After making your changes the last problem was my database fields had spaces. I replaced them with dashes "_"
0
 
LVL 40
ID: 39786021
You are creating a New DataAdapter.

Everytime you call New, you create a new object. So although da is the same variable as the one you used to fill the DataTable, it is not the same DataAdapter.

The CommandBuilder might thus not be building an UPDATE command that works with your DataTable.

Hard so say exactly, because the dialog hides your code in the screenshot.

Also, as stated in an earlier post, the CommandBuilder can work properly only if you have a primary key in your database table. Otherwise, you need to provide your own SQL UPDATE command, in a format that can be used by the DataAdapter. That is something I won't go into in a simple post.

Do you have a primary key?
0
 
LVL 40
ID: 39786085
Our 2 last post crossed. Glad to see that the problem is resolved.

A few words about field names.

As you have seen, spaces sometimes cause problems in field names. This could also happen with table and query names. Using square brackets in the queries sometimes solve that problem (i.e. [User Name]), but not always.

Using underscores is better, but it is an old technique that comes from times where only uppercase letters were allowed in field names. USER_NAME is easier to read than USERNAME.

But since we can use lowercase characters nowadays, a more common way of doing it is to use uppercase characters at the beginning of each word: UserName.

When I see underscores, I always think "There is an old programmer". Which basically means "he learned to program at the same time I did, but is unable to get rid of old habits :-)".
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40361760
FWIW, it vb.net, I use:

DG_BRL.AutoGenerateColumns = False

to avoid auto formatting (reformatting) the datagridview.

Mike
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month11 days, 15 hours left to enroll

752 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