"Dynamic SQL generation for the UpdateCommand is not supported" error

[emaduddeen]

Hi Everyone,

I'm using this code to do an update to the database but get this error:

"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information"

The primary key is on the ID column of the table and that value is stored in intParentID. When I ran the debugger I found the intParentID variable did have a value in it so at least that was not a problem.

            strSQL = "Select * " & _
                       "From Parents " & _
                      "Where ID = @ParentID"

            ' Create the data adapter for the Parent Details.
            '------------------------------------------------
            objDataAdapterSingleParent = New SqlDataAdapter(strSQL, _
                                          (BuildConnectionString("EMAD-PC\SQLEXPRESS", "Islamic Academy Of Peace")))

            ' Add the parameter to the parameters collection of the Parent Details Data Adapter.
            '-----------------------------------------------------------------------------------
            objDataAdapterSingleParent.SelectCommand.Parameters.Add("@ParentID", SqlDbType.Int)

Later in the code I use this:

            ' Store the selected customer ID into the parameter slot of the SQL data adapter.
            '--------------------------------------------------------------------------------
            objDataAdapterSingleParent.SelectCommand.Parameters("@ParentID").Value = intParentID

                ' Have the command builder create an update SQL command.
                '-------------------------------------------------------
                objDataAdapterSingleParent.UpdateCommand = objCommandBuilderParentDetails.GetUpdateCommand

This is where the error is displayed.

Can you help resolve this one?

Thanks.

Truly,
Emad

 

[jmcilhinney]

Try setting the MissingSchemaAction property of the DataAdapter to AddWithKey before calling Fill.

 

[emaduddeen]

Hi,

I tried this but get the same error.

                ' Fill the dataset with data.
                '----------------------------
                objDataAdapterSingleParent.MissingSchemaAction = MissingSchemaAction.AddWithKey
                objDataAdapterSingleParent.Fill(objDataSetSingleParent, "Parent Details")

Truly,
Emad

 

[jmcilhinney]

Are you actually using the same DataAdapter object each time?

 

[emaduddeen]

Hi,

Yes it's the same.

Here is the sub procedure so you can see what I'm trying to do here.

I appreciate all the time you have been taking to help me.

    Public Sub RefreshIndividualTextBoxes()

        ' This parent ID is the key column for the Parents table.
        '--------------------------------------------------------
        With LightGridParentNames
            intParentID = .CurrentRow.Cells(.Columns.GetColumnIndex("ID")).Text
            ' intParentID = objDataSetParentNames.Tables("ParentsEmad").Rows(.CurrentRow.Index).Item("ID")
        End With

        objDataSetSingleParent.Clear()

        ' Check to see if an item was selected.
        '--------------------------------------
        If LightGridParentNames.SelectedIndex <> -1 Then

            ' Adjust colours and the enabling of some button if necessary.
            '-------------------------------------------------------------
            If FormMain.RibbonButtonParentsAndChildrenEdit.Enabled = False Then

                FormMain.RibbonButtonParentsAndChildrenEdit.Enabled = True
                FormMain.RibbonButtonParentsAndChildrenDelete.Enabled = True

                ' Cancel editing if it is active.
                '--------------------------------
                If EditBoxFirstName.Style.BackColor() = Color.LawnGreen Then
                    ActivateEditing(False)
                End If
            End If

            ' Store the selected customer ID into the parameter slot of the SQL data adapter.
            '--------------------------------------------------------------------------------
            objDataAdapterSingleParent.SelectCommand.Parameters("@ParentID").Value = intParentID

            Try
                ' Fill the dataset with data.
                '----------------------------
                objDataAdapterSingleParent.MissingSchemaAction = MissingSchemaAction.AddWithKey
                objDataAdapterSingleParent.Fill(objDataSetSingleParent, "Parent Details")

                ' Grab the 1st. individual data row.
                '-----------------------------------
                objDataRowSingleParent = objDataSetSingleParent.Tables("Parent Details").Rows(0)

                ' Run through the text boxes on the form, and if they match up with a field from the 
                ' record, load them into the text boxes.
                '-----------------------------------------------------------------------------------
                For Each objSingleControl In GroupBoxParentDetailsInfo.Controls

                    If TypeOf objSingleControl Is EditBox Then

                        ' Each text box control starts with "EditBox" followed by the column name.
                        ' This line will get the table's column name from the text box control.
                        '-------------------------------------------------------------------------
                        strName = Mid(objSingleControl.Name, 8)

                        Try
                            ' Grab the data from the DataRow and place it into a text box.
                            '-------------------------------------------------------------
                            objSingleControl.text = objDataRowSingleParent(strName).ToString

                        Catch exException As Exception
                            MessageBox.Show("Data row column not the same as control name: " & _
                                            objSingleControl.Name)
                        End Try

                    End If
                Next

                ' Populate the correct Payment Plan radio buttons.
                '-------------------------------------------------
                Select Case objDataRowSingleParent("PaymentPlan").ToString
                    Case "A"
                        RadioButtonPlanA.Checked = True

                    Case "B"
                        RadioButtonPlanB.Checked = True

                    Case "C"
                        RadioButtonPlanC.Checked = True

                    Case "D"
                        RadioButtonPlanD.Checked = True

                End Select

                ' Populate the correct Schedule radio buttons.
                '---------------------------------------------
                Select Case objDataRowSingleParent("PaymentSchedule").ToString
                    Case "1"
                        RadioButton9Monthly.Checked = True

                    Case "2"
                        RadioButton4Quarterly.Checked = True

                    Case "3"
                        RadioButton2BiAnnual.Checked = True

                End Select

            Catch ex As Exception
                MessageBox.Show(ex.Message)

            End Try
        End If
    End Sub

If you see anything else that I should be doing please let me know.

Truly,
Emad