Update DB with changes made to DataGridView

[phillydub]

So, I have successfully filled a DataGridView with the contents of a table from an Access database.

My problem is that when I try to use the DataAdapter's Update() method to save the changes made in the DataGridView to the database, I always get OleDbExceptions.

For example, if I insert a new row, I get OleDbException "Syntax error in INSERT INTO command". If I change an existing value in the DataGridView, it gives me the 'Syntax error in UPDATE command'.

What am I doing wrong? Here is the code that I have that reads the database into my datagrid.

da as DataAdapter
table as DataTable
ds as DataSet
bs as BindingSource
cmdb as OleDbCommandBuilder

    Public Sub fillUserTable()
        Dim query As String = "SELECT * FROM [User]"
        GetData(query)
        frmUserTable.DataGridView1.DataSource = bs
    End Sub

    Private Sub GetData(ByVal selectCommand As String)
        Try
            ' Create a new data adapter based on the specified query.
            Me.da = New OleDbDataAdapter(selectCommand, connString)

            ' Create a command builder to generate SQL update, insert, and
            ' delete commands based on selectCommand. These are used to
            ' update the database.
            Me.cmdb = New OleDbCommandBuilder(Me.da)

            ' Populate a new data table and bind it to the BindingSource.
            Me.table = New DataTable()

            Me.da.Fill(table)
            Me.bs.DataSource = table

        Catch ex As OleDbException
            MessageBox.Show("OH NOES")
        End Try
    End Sub

And again, I get these exceptions whenever I try to use the DataAdapter.Update() method, ie

     Me.da.Update(CType(Me.bs.DataSource, DataTable))

Any help would be greatly appreciated!

 

[cjard]

Read the DW2 link in my signature, section "Creating a Simple Data App"
It will show you properly how to:
conencting to db
reading data
related data
writing data

 

[Arg81]

why do people still create data applications that way

 

[cjard]

why do people still create data applications that way

Partly tradition, partly lame education, partly lack of interest or access to technical resources about what's new and cool, and partly because there's a rash of **** articles out there that are just stagnating over time and people are still following the tutorials.

Talking of getting things from the horse's mouth, i guess you and I should really be getting into the new .NET 3 data stuff and leaving this crappy old .NET 2 behind eh.. one day we'll have the same disdain for it was we do .Net 1 now.. 1.1 was 2003, 2 was 2005, it's already 2007 so maths dictates we upgrade. And struggle because we will ahve questions noone can answer and we'll have to pioneer so that we can teach others..

I did that with .NET 2 and i'm still getting frustrated at it.. I even resent writing the woords "CLick the DW2 link in my sig and read 'Creating a Simple... " it's just so wordy, and so many people have so many problems that would just all go away if they did things properly...

Sigh.

I'm back off to my C# and my PLSQL now.. got some bitfields to manipulate

 

[pachjo]

Hi,

I am having a lot of grief doing this too and as yet have not seen how to get around it via the DW2 link

My datagridview is bound to a bindingsource which in turn is bound to a dataset.

When I add records to the table in the dataset the datagridview updates accordingly.

When I perform a change to the datagridview cell in question via code (context menu - void record) the cell changes as expected from FALSE to TRUE.

However no matter what I try I cannot get the change made to the datagridview to write back to the SQL database.

I even tried the code from here but these events dont fire?

http://www.codeproject.com/vb/net/VB_DGV_UpdatingBoundDB.asp

Can anyone offer any suggestions please?

Thx