No Key Value Error

[Jigabyte]

Hi,

I do a search for a row:-

                sSQL = "SELECT * FROM DO_DEPARTMENT_ORDER WHERE DO_STORE_CODE='" & sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_ST_STORECODE") & "' AND "
                sSQL = sSQL & "DO_DEPT_CODE = '" & sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_DP_DEPT_CODE") & "'"
                sSQL += " AND DO_CY_CODE = '" & sOutputCompanyCode & "'"
                sqlDAStoreDepts = New SqlCeDataAdapter
                sqlDAStoreDepts.SelectCommand = New SqlCeCommand(sSQL, CeData)
                sqlDSStoreDepts = New DataSet
                sqlDAStoreDepts.Fill(sqlDSStoreDepts, "DO_DEPARTMENT_ORDER")

I amend some of the data (not the columns which make up the unique key):-

                        sqlDSStoreDepts.Tables(0).Rows(0).Item("DO_CY_CODE") = sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_CY_CODE")
                        sqlDSStoreDepts.Tables(0).Rows(0).Item("DO_DEPT_NAME") = sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_DEPT_NAME")
                        sqlDSStoreDepts.Tables(0).Rows(0).Item("DO_SORT_ORDER") = rppUtils.iNullTest(sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_SORT_ORDER"))
                        sqlDSStoreDepts.Tables(0).Rows(0).Item("DO_RECORD_STATUS") = "NO_CHANGE"

I then update the live database:-

                sqlCBStoreDepts = New SqlCeCommandBuilder(sqlDAStoreDepts)
                sqlDAStoreDepts.Update(sqlDSStoreDepts, "DO_DEPARTMENT_ORDER")

Its at this point I get the following error:-

Dynamic SQL generation for the update command is not supported against a select command that does not return any key column information

Does anyone know why I am getting this error. All I want to do is search for a row; if it exists update some columns and if not create a new row.

Thanks for any help,

Jiggy!

[techgnome]

In order for it to build the query to do the update, it needs to know how to find the row that was updated. It does this by using the Primary Key (PKey) set on the table. Since you are doing a SELECT * on the table, that tells me that you are returning all of the columns in the table, but none of them are marked as the PKey.

You need to set a PKey on the table.

-tg

[Jigabyte]

Thank you for your reply. I have added a primary key to the table but still get the same error. When getting the data (select *) do I need to some how tell it what the primary key is or will the data adapter know this?

[Jigabyte]

Hi,

I have rewritten the routine to this but still get the same error when updating an existing row :-

Public Sub xmlImportDeptOrder(ByVal lblMessage As Label, ByVal sFilename As String)

        Dim sqlDAStoreDepts As SqlCeDataAdapter
        Dim sqlDSStoreDepts As New DataSet
        Dim sqlCBStoreDepts As SqlCeCommandBuilder
        Dim sqlDRStoreDepts As DataRow
        Dim sqlDSXMLStoreDepts As New DataSet
        Dim iRows As Int16
        Dim iRowExists As Int16
        Dim sqlSearch As New DataSet
        Dim PrimaryKeyColumns(1) As DataColumn
        Dim objFindValues(1) As Object
        Dim sSQL As String

        Try
            'Read the xml data into a dataset
            sqlDSXMLStoreDepts.ReadXml(xmlInputPath & sFilename)
            sOutputCompanyCode = sGetCompanyCode(sFilename)

            'Build dataset for live data to be updated.
            sSQL = "SELECT * FROM DO_DEPARTMENT_ORDER WHERE DO_CY_CODE = '" & sOutputCompanyCode & "'"
            sqlDAStoreDepts = New SqlCeDataAdapter
            sqlDAStoreDepts.SelectCommand = New SqlCeCommand(sSQL, CeData)
            sqlDSStoreDepts = New DataSet
            sqlDAStoreDepts.Fill(sqlDSStoreDepts, "DO_DEPARTMENT_ORDER")

            PrimaryKeyColumns(0) = sqlDSStoreDepts.Tables(0).Columns("DO_STORE_CODE")
            PrimaryKeyColumns(1) = sqlDSStoreDepts.Tables(0).Columns("DO_DEPT_CODE")
            sqlDSStoreDepts.Tables(0).PrimaryKey = PrimaryKeyColumns

            For iRows = 0 To sqlDSXMLStoreDepts.Tables(0).Rows.Count - 1

                lblMessage.Text = "Importing " & sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_ST_STORECODE") & "-" & iRowExists
                Application.DoEvents()

                Try
                    objFindValues(0) = sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_ST_STORECODE")
                    objFindValues(1) = sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_DP_DEPT_CODE")
                    sqlDRStoreDepts = sqlDSStoreDepts.Tables(0).Rows.Find(objFindValues)

                    If sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_RECORD_STATUS") = "DELETED" Then
                        If iRowExists Then
                            sqlDSStoreDepts.Tables(0).Rows(0).Delete()
                        End If
                    ElseIf Not (sqlDRStoreDepts Is Nothing) Then
                        sqlDRStoreDepts.BeginEdit()
                        sqlDRStoreDepts("DO_CY_CODE") = sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_CY_CODE")
                        sqlDRStoreDepts("DO_DEPT_NAME") = sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_DEPT_NAME")
                        sqlDRStoreDepts("DO_SORT_ORDER") = rppUtils.iNullTest(sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_SORT_ORDER"))
                        sqlDRStoreDepts("DO_RECORD_STATUS") = "NO_CHANGE"
                        sqlDRStoreDepts.EndEdit()
                    Else
                        sqlDRStoreDepts = sqlDSStoreDepts.Tables(0).NewRow
                        sqlDRStoreDepts("DO_CY_CODE") = sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_CY_CODE")
                        sqlDRStoreDepts("DO_STORE_CODE") = sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_ST_STORECODE")
                        sqlDRStoreDepts("DO_DEPT_CODE") = sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_DP_DEPT_CODE")
                        sqlDRStoreDepts("DO_DEPT_NAME") = sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_DEPT_NAME")
                        sqlDRStoreDepts("DO_SORT_ORDER") = rppUtils.iNullTest(sqlDSXMLStoreDepts.Tables(0).Rows(iRows).Item("SD_SORT_ORDER"))
                        sqlDRStoreDepts("DO_NOTES") = ""
                        sqlDRStoreDepts("DO_OWNER") = ""
                        sqlDRStoreDepts("DO_RECORD_STATUS") = "NO_CHANGE"

                        sqlDSStoreDepts.Tables(0).Rows.Add(sqlDRStoreDepts)
                    End If

                    'Update live database
                    sqlCBStoreDepts = New SqlCeCommandBuilder(sqlDAStoreDepts)
                    sqlDAStoreDepts.Update(sqlDSStoreDepts, "DO_DEPARTMENT_ORDER")

                Catch ex As Exception
                    MessageBox.Show(ex.Message, "Error In clsTransfer-xmlImportDeptOrder-For", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1)

                End Try

            Next

            sqlCBStoreDepts.Dispose()
            sqlDSStoreDepts.Dispose()
            sqlDAStoreDepts.Dispose()

            File.Delete(xmlInputPath & sFilename)

        Catch sqlEx As SqlCeException
            Dim sqlError As SqlCeError

            For Each sqlError In sqlEx.Errors
                MessageBox.Show(sqlEx.Message, "clsTransfer - SQL - xmlImportDeptOrder", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1)
            Next

        Catch exGeneric As Exception
            MessageBox.Show(exGeneric.Message, "clsTransfer - xmlImportDeptOrder", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1)

        End Try

    End Sub

I really do not understand why I get this error. How in the hell do you update an existing row in a table gr gr gr gr gr gr.com

Thanks for any help

Jiggy

[techgnome]

Honestly? I don't use adaptors... I use command objects and do it all by hand...

-tg