Solved

DataGridView - Save data to access database VB.net

Posted on 2014-01-13
13
16,584 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

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
FillSchema (dt), not da. You are getting the schema (definition) of the table, not the DataAdapter.
0
 
LVL 1

Author Comment

by:AnthonySmithMCP
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Author Comment

by:AnthonySmithMCP
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
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

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
FWIW, it vb.net, I use:

DG_BRL.AutoGenerateColumns = False

to avoid auto formatting (reformatting) the datagridview.

Mike
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now