Solved

DataGridView - Save data to access database VB.net

Posted on 2014-01-13
13
17,351 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
  • 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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 500 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

820 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