Saving changes from DataGridView to database

[CaptainX]

Hi,

Just starting with VB 2010. I have a project with (currently) 4 forms - all containing a DataGridView populated from MS Access via a DataAdapter / DataView and working in the same way (just the SQL string changes)

Pseudo-code is thus:

'Open connect (working fine)

'create a data adapter/fill it and create dataview

Dim ds As DataSet
Dim da As System.Data.OleDb.OleDbDataAdapter
Dim dv As DataView
Dim strSQL as string


' create a data adapter
strSQL = "SELECT * FROM Employers"
da = New System.Data.OleDb.OleDbDataAdapter(strSQL, myConnection)

' fill dataset
ds = New DataSet '(strSQL)
da.Fill(ds, strSQL)

dv = New DataView(ds.Tables(0), "EmployerID <>0", "Employer DESC", DataViewRowState.CurrentRows)
EmployerDatGridView.DatSource = dv


'variable are declared at the Form Level, so after making changes / in the form closing event

'update changes
Dim cb As New System.Data.OleDb.OleDbCommandBuilder(da) 'Dont know why I added this (found it somewhere but see I dont use it!)
da.update(ds, "SELECT * FROM Employers")

===> This is working fine in all but one of the forms, but the final line errors

"OleDbException was unhandled by user code - No value given for one of more required parameters.

???????? There is nothing odd about the data in this dataset, the changes I am making are legal , and not constrained at the DB level, so dont understand why it fails, and worse - HAVE NO IDEA HOW TO LOOK FOR WHAT IS WRONG.

Any ideas ? The connection is still OPEN so still linked to the database...

The code may not be great (been looking in various helps and forums), so welcome for any suggestions - but esp why I get this error and how to try and debug it

many thanks, steve

[IanRyder]

Hi,

This is something I posted recently. See if it helps with your issue. In this case I am using SQL objects, but just change these to OleDb objects.

Imports System.Data.SqlClient

Public Class Form1
  'Here we define the connection to the database
  Private sqlConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
  'Here we define a DataAdapter to hold all the SQL commands to interact with your database
  Private daEmployees As New SqlDataAdapter("Select * From Employees", sqlConn)
  'Here we use a CommandBuilder to specifically create the Update, Insert and Delete commands for the DataAdapter
  Private sqlCmndBuilder As New SqlCommandBuilder(daEmployees)
  'Here we create a DataSet to hold our information
  Private myDS As New DataSet

  Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    'Here we load our information into our DataSet using the DataAdapter's Fill method
    daEmployees.Fill(myDS, "Employees")
    'Here we set the DataSource of the DataGridView to the correct Table in the DataSet
    DataGridView1.DataSource = myDS.Tables(0)
  End Sub

  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    'Since the DataGridView is bound to the DataTable in the Dataset any changes that
    'are made in the DataGridView are mirrored in the underlying table in the Dataset
    'This includes all updates to existing records, inserted rows and deleted rows
    'We can therefore use the Update method of the DataAdapter to persist all your
    'Changes back to the database by passing the correct DataSet table as a parameter
    daEmployees.Update(myDS.Tables(0))
    MsgBox("Employee Details Updated!")
  End Sub
End Class

Hope that helps.

Cheers,

Ian

[CaptainX]

Thanks for the suggestion, but basically the code that I was running, and doesnt seem to work (for me).


The DataGrid was created using (initially) the VS Data et and dragging it to the form to populated the rows and also created (btw) a binding source, table adapter etc.

However I coded an update to point it at my own dataset, so wondered if that was cusing the update to fail. But removing those links didnt make any difference.

The only reason I started this way is because I want to have drop down combo boxes in some of the grid cell s to access lookup info in other tables (unless of course there is another way I can do this ......)

S