none
Update SQL Database Table with a DataTable RRS feed

  • Question

  • I can correctly update a SQL database table with a datatable as follows:

    DimresultsDataTable AsNewDataTable()

    DimdtpHExportDataTable AsNewDataTable()

    DimcnString AsString= <<<ConnectionString>>>

    UsingcnSQL1 AsNewSqlConnection

    cnSQL1.ConnectionString = cnString

    Usingadapter1 = NewSqlDataAdapter("SELECT SampleNo, Results, Complete_Date, Dex_Row_Id "

    & "FROM LIMS.dbo.Analytical_Sample_Log_ResultsInfo", cnSQL1)

    Dimbuilder1 AsNewSqlCommandBuilder(adapter1)

    adapter1.UpdateCommand = builder1.GetUpdateCommand()

    UsingNewSqlCommandBuilder(adapter1)

    adapter1.Fill(resultsDataTable)

    resultsDataTable.PrimaryKey = NewDataColumn() {resultsDataTable.Columns("Dex_Row_Id")}

    dtpHExportDataTable = resultsDataTable.Clone()

    AddResultsRow(dtpHExportDataTable, 13581, "4.4", "2015-01-01", 45598)

    AddResultsRow(dtpHExportDataTable, 13590, "5.5", "2015-01-01", 45618)

    AddResultsRow(dtpHExportDataTable, 13604, "6.6", "2015-01-01", 45655)

    resultsDataTable.Merge(dtpHExportDataTable)

    ShowResult(resultsDataTable) ‘looks perfect

    adapter1.Update(resultsDataTable)                                        ‘database table IS updated correctly

    EndUsing

    EndUsing

    EndUsing

    However, when I expand the above code to my spreadsheet workbook page application, the resultsDataTable (shown by the DevExpress ShowResult(resultsDataTable)) displays the correct information using the code above as well as the code below. But the database table is not updated using the code below:

    Dimworksheet AsWorksheet= SpreadsheetControl.Document.Worksheets.ActiveWorksheet

    Dimrange AsRange= worksheet.Selection

    DimrangeHasHeaders AsBoolean= True

    DimresultsDataTable AsNewDataTable()

    DimdtpHExportDataTable AsDataTable= worksheet.CreateDataTable(range, rangeHasHeaders)

    DimcnString AsString= "<<<ConnectionString>>>"

    UsingcnSQL1 AsNewSqlConnection

    cnSQL1.ConnectionString = cnString

    Usingadapter1 = NewSqlDataAdapter("SELECT SampleNo, Results, Complete_Date, Dex_Row_Id "

    & "FROM LIMS.dbo.Analytical_Sample_Log_ResultsInfo", cnSQL1)

    Dimbuilder1 AsNewSqlCommandBuilder(adapter1)

    adapter1.UpdateCommand = builder1.GetUpdateCommand()

    UsingNewSqlCommandBuilder(adapter1)

    adapter1.Fill(resultsDataTable)

    resultsDataTable.PrimaryKey = NewDataColumn() {resultsDataTable.Columns("Dex_Row_Id")}

    dtpHExportDataTable = resultsDataTable.Clone()

    Dimexporter AsDataTableExporter= worksheet.CreateDataTableExporter(range, dtpHExportDataTable, rangeHasHeaders)

    ' Perform the export.

    exporter.Export()

    resultsDataTable.Merge(dtpHExportDataTable)

    ShowResult(resultsDataTable) ‘looks perfect

    adapter1.Update(resultsDataTable) )                                           ‘database table is NOT updated

    EndUsing

    EndUsing

    EndUsing

    Sunday, September 6, 2015 4:36 PM

Answers

  • No, it's the resultsDataTable that has to have Modified Rows, that's the one you're updating. You are checking that the dtpHExportDataTable has Modified rows, plus you're manually setting them to Modified ... however, you're setting them to Modified (the rows in dtpHExportDataTable) *after* you've done the Merge! You need to do that *before* the Merge.

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by LHendren Friday, September 11, 2015 1:32 PM
    Friday, September 11, 2015 1:18 AM

All replies

  • Does your spreadsheet have the PK? I guess it probably does, or else you wouldn't have said that the results of the Merge looked perfect.

    My guess is that the RowState for the merged data is not "Modified" ... take a look at that in the debugger after you've done the Merge. Set the debugger breakpoint just after the Merge. Pick a Row that you know has changed from the Export, and in either the Watch window or the Immediate window look at it like this:

    resultsDataTable.Rows(0).RowState

    Replace 0 with whatever row number you're looking at.

    And, while you're at it, you may want to look at the RowState of the row in the dtpHExportDataTable too.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, September 6, 2015 8:23 PM
  • Bonnie, thank you for the reply.  I just saw your above post.  Here is my latest code.  Unfortunately, still not updating the database table.  Rows are modified.

                       ' Perform the export.
                        exporter.Export()

                        resultsDataTable.Merge(dtpHExportDataTable)

                        For index = 0 To dtpHExportDataTable.Rows.Count - 1
                            dtpHExportDataTable.Rows(index).SetModified()
                        Next

                        For Each row As DataRow In dtpHExportDataTable.Rows
                            ' These are the actual rows that have some kind of changes
                            If row.RowState = DataRowState.Modified Then
                                MsgBox("modified")        'All of my rows are modified (affirmative)
                            Else
                                MsgBox("no changes")
                            End If
                        Next

                        Try
                            adapter1.Update(resultsDataTable)
                        Catch ex As Exception
                            MsgBox("Update failed")
                        End Try

    Thursday, September 10, 2015 8:19 PM
  • No, it's the resultsDataTable that has to have Modified Rows, that's the one you're updating. You are checking that the dtpHExportDataTable has Modified rows, plus you're manually setting them to Modified ... however, you're setting them to Modified (the rows in dtpHExportDataTable) *after* you've done the Merge! You need to do that *before* the Merge.

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by LHendren Friday, September 11, 2015 1:32 PM
    Friday, September 11, 2015 1:18 AM
  • Bonnie, once again thank you so much! 

    Another un-named source recommended modifying the dtpHExportDataTable.  The merge placement was my mistake.  Your suggestions resolved my problem, and I am now free to expand the application the way I need it.

    Friday, September 11, 2015 1:40 PM
  • You're welcome! Glad I could help!  =0)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, September 12, 2015 4:58 AM