Update DB/ BindingSource

[Huascar82]

Hi Guys,

I've got a problem updating my DB. Below I added the code I run to populate the dataset and to save records. I think my problem is coming from running a SELECT statement with multiple tables, but I don't know how to fix it. The error I'm getting is "Dynamic SQL generation is not supported against multiple base tables."

'both strSQLs are the same, one just executes from a stored procedure
'strSQL = "SELECT * FROM tblDeploymentData, DataSourceSummary WHERE tblDeploymentData.[Asset Tag]" & _
        '"= DataSourceSummary.[Asset Tag] AND tblDeploymentData.[Change Record]='" & strChangeRecord & "'"
        strSQL = "EXECUTE PCR_QueryChangeRecord '" & strChangeRecord & "'"

        objCommand = New OdbcCommand(strSQL, objConnection)
        objCommand.CommandType = CommandType.Text
        objDataAdapter.SelectCommand = objCommand

            objDataAdapter.Fill(objDataSet)

            BindFullDetail.DataSource = objDataSet
            BindFullDetail.DataMember = objDataSet.Tables(0).TableName

            navFullDetail.BindingSource = BindFullDetail
            navFullDetail.Dock = DockStyle.Bottom

Changes are done in from data bound to controls of my form. After the changes I run this to update the DB. At this point I get the "Dynamic SQL generation is not supported against multiple base tables"

        If objDataSet.HasChanges Then
            Dim objCommandBuilder As New OdbcCommandBuilder(objDataAdapter)
            objDataAdapter.Update(objDataSet.Tables(0))
        End If

If I take out the .tables(0) from my update command I no longer recieve an error but when I go back to the SQL DB to check the changes the changes where not done on the DB. And I'm using the ODBC components because my boss wants to use a DSN for the connection.

Someone please help.

 

[jmcilhinney]

You can't use a CommandBuilder if your query involves more than one table, plain and simple. How would the CommandBuilder know which of the two or more tables to insert data to, update it in or delete it from? If your query involves two or more tables then it's up to you to create the DeleteCommand, InsertCommand and UpdateCommand for your DataAdapter yourself. Here's an example of doing that, among other ADO.NET scenarios.

 

[cjard]

Change the way you do data access.. Read the DW2 link in my signature, starting with Creating A Simple Data App. Go on from there when yo uunderstand it, to displaying and saving related data