Question How to save changes in database after editing datagridview

[TCarlo]

Hi,

I'm new to programming vb.net. And i wont to try to save changes that i have made in a datagridview to my database.
Only, it it is not working. No changes are saved to the database. However the datagridview is filled with the results of my query.

My source looks like this:

Imports System
Imports System.Data

Public Class Form1
Dim ConDBF As OleDb.OleDbConnection
Dim ConCMD As OleDb.OleDbCommand
Dim DataAdap As OleDb.OleDbDataAdapter
Dim myDs As New DataSet()
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim ConStr As String
ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\TST_MAP\ITEST3\;Extended Properties=dBASE IV;User ID=Admin;Password="
ConDBF = New OleDb.OleDbConnection(ConStr)
ConCMD = New OleDb.OleDbCommand("SELECT * FROM Leerling WHERE NAAM Like '%ormans%' and VOORNAAM like '%rl%' ", ConDBF)
DataAdap = New OleDb.OleDbDataAdapter(ConCMD)
DataAdap.Fill(myDs, "Leerling")
BindingSource1.DataSource = myDs.Tables("Leerling")
DataGridView1.DataSource = BindingSource1
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
DataAdap.Update(myDs, "Leerling")
End Sub
End Class

What do i have to do to save changes made in my datagridview to my database?

Thanks in advance!
Carlo

 

[jmcilhinney]

Most likely the changes are saved and you are just looking in the wrong place or at the wrong time. First, test the value returned by your Update call. If no exception is thrown then it is succeeding, so the result must be either zero, in which case there were no changes to save, or non-zero, in which case there were changes and they were saved. If it's the latter, follow the first link in my signature to learn how to manage local data files.

 

[TCarlo]

Thanks fo the reply!
What i forgot to tell...there was a exeption ...'Update requires a valid UpdateCommand when passed DataRow collection with modified rows.' and also i use a DBase IV file, i dont know if this makes any difference for my problem.

 

[jmcilhinney]

It makes a big difference. That means that there is not any SQL code provided to save your edited rows back to the database. I probably should have realised that from your code but it's easy to miss things when you don't know what you're looking for. You can also make your code easier to read by wrapping it in

 or, preferably, [xcode=vb] tags.

To fix your problem, you need to assign an OleDbCommand to the data adapter's UpdateCommand property, containing the appropriate SQL code and parameters to save your modified rows back to the database.  You can either do it explicitly or else use an OleDbCommandBuilder.  You can find an example of each [URL="http://www.vbforums.com/showthread.php?t=469872"]here[/URL].

 

[jmcilhinney]

By the way, it's pointless importing System and System.Data in your code file because they are already imported project-wide by default. You can check out the References page of the project properties for the default assembly references and namespace imports. You can import additional namespaces for the whole project there if they are likely to be used in more than one code file. The sensible option for you would be to import System.Data.OleDb, either project-wide or in your code file. That way you won't have keep qualifying types from that namespace over and over.

 

[TCarlo]

Thanks again for the reply! I need it and still need it!

I tried whole today all the solutions that I could find, also your suggestion to use the commandbuilder. I always read that i need a table with a primary key. My (DBase IV) table has no primary key so i tried to
provide one ( see code). After all the effort I put in, still no result!
I provide the code until know…
Do you have more tips? It seems that putting Dbase IV files into a datagridview and saving changes is a difficult matter. (for my especially)
Thanks in advance!


Public Class Form1
Dim ConDBF As OleDb.OleDbConnection
Dim ConCMD As OleDb.OleDbCommand
Dim DataAdap As OleDb.OleDbDataAdapter
Dim myDT As New DataTable()
Dim CmdBuild As OleDb.OleDbCommandBuilder
Dim columns(1) As DataColumn

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim ConStr As String

ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\TST_MAP\ITEST4\;Extended Properties=dBASE IV;User ID=Admin;Password="
ConDBF = New OleDb.OleDbConnection(ConStr)
ConCMD = New OleDb.OleDbCommand("SELECT * FROM Leerling WHERE NAAM Like '%ormans%' and VOORNAAM like '%rl%' ", ConDBF)

DataAdap = New OleDb.OleDbDataAdapter(ConCMD)

CmdBuild = New OleDb.OleDbCommandBuilder(DataAdap)

DataAdap.Fill(myDT)
columns(0) = myDT.Columns("INSHNUMMER")
myDT.PrimaryKey = columns

BindingSource1.DataSource = myDT

DataGridView1.DataSource = BindingSource1
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
CmdBuild.GetUpdateCommand()
DataAdap.Update(myDT)
End Sub

End Class

 

[jmcilhinney]

First up, I specifically asked you to use formatting tags with your code snippets in my last post and you have ignored the request. If you would like us to volunteer our time to help you then please do all you can to help us. Reading unformatted code is a chore. It takes basically no effort on your part to format your code snippets using the tools provided in the advanced editor and it makes a big difference to us. Your cooperation on this point will be greatly appreciated and maximise your chances of getting the help you want.

As for the issue, it comes down to the ability to uniquely identify a single record. A command builder is no use to you if you don't have a primary key because, without that, how can the system possibly know what record to update? The purpose of a PK is to identify a record from all others. Without it, the system doesn't know what record to update so it won't even try. If you don't have a primary key then it's up to you to write your own UPDATE statement with the appropriate WHERE clause to identify the record to update. The question is, if you are able to uniquely identify a row, why is that column or combination of columns not the primary key?

Having said all that, why don't you just add a primary key? It is very rare that it is appropriate for a database table not to have a primary key. Most databases have a mechanism to add an identifying value to each record that is solely for that purpose and doesn't actually have anything to do with the data, e.g. an identity in SQL Server, an AutoNumber in Access or a sequence in Oracle. I've never used dBase but I'm sure that it provides something similar. Once you have added a PK, the command builder will build the commands for you, assuming that you include the PK in the query and it involves only one table.

 

[TCarlo]

You were right, I didn’t formatted my source code. Sorry for that!
By formatted tags, I presume you mean the use of tabs to increase the readability.
Something like this?


Finally I found the solution for my problem thanks to you.
I hope I may contact you in the future if I struggle with other problems?
Many thanks for the time you have invested in my problem!
Friendly greetings!

 

[jmcilhinney]

I'm talking about using the tools provided on the tool bar. You simply need to copy and paste your code and then use the

 or [xcode] button to wrap it in formatting tags.  If you use [xcode] then the option is "vb" (without quotes).