DataGridView - Save data to access database VB.net

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)

Select allOpen 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()

Select allOpen 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.

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.

FillSchema (dt), not da. You are getting the schema (definition) of the table, not the DataAdapter.

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.

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)

Select allOpen 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.

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)

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?

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

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 "_"

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?

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 :-)".

FWIW, it vb.net, I use:

DG_BRL.AutoGenerateColumns = False

to avoid auto formatting (reformatting) the datagridview.

Mike