update a database from a datagrid

[Omega3]

Hello,

I have the following problem

A datagrid binded to a dataset. The dataset gets data from a database through a dataAdapter. The data is comming from 2 tables in the database.

Dim da as new oledbDataAdapter ("Select F1 F2 .. from Table1 inner join Table2" , connection)

The goal is to let the user edit data in the datagrid and then click a button to update the database.

I test this and it works when the data comes from a single table such as :

Dim da as new oledbDataAdapter ("Select F1 F2 .. from Table1" , connection)

When the data comes from two tables I get the following error message
"Dynamic SQL generation is not supported against multiple base tables"

Is there a way to achieve this ?

I am thinking that it may be possible to use 2 dataAdapter one for each table and also use the original dataAdapter with the sql join query.

another important detail is that I use commandbuilder here is the code for the update functionality

Dim newds As DataSet = ds.GetChanges()

        DataGrid2.DataSource = newds

        Dim cmdbuider As New OleDbCommandBuilder(da)

        If newds Is Nothing Then
            MsgBox("you haven't done anything")
        Else
            If da.Update(newds, "person") Then
                MsgBox("update successfully")
            End If
        End If

I appreciate any help.

 

[cjard]

You must write the update, insert and delete queries yourself. The IDE isnt smart enough to generate the SQL itself - it's a very hard problem! If Microsoft could program the IDE to be that smart, they might as well go on and automate the developer out of the loop entirely

 

[Omega3]

How to "ask" datagrid what changes happen

Thanks for your previous response,

I still have a question. If the updates most be generated buy hand how can we know what staments to write.

In other words the user interacts with the datagrid where he can insert delete or update a record how can we "ask" the datagrid what kind of operation was perform buy the user in other to write the appropriate statments.

Thanks.

 

[cjard]

You dont - the datatable tracks internally what kind of changed happened for a particular row (whether that row was updated, inserted or deleted).

All you must do is provide an UPDATE INSERT and DELETE statement wrapped command (with relevant parameters mapped to source columns) in the Data Adapter's InsertCommand / UpdateCommand / DeleteCommand properties.

The adapter will call the relevant one when running through the data table. For more info, take a read of Data Walkthroughs 1.1 in my signature