none
Basics of creating a good insertcommand RRS feed

  • Question

  • To get autoincrement field values I have to first perform an insert of a record into a database.  If I create a command builder for my data adapter in the following fashion:

     

    Dim daOperation As New OleDbDataAdapter("Select * from Operation", cnJetDB)

     

    Dim bldOperations As New OleDbCommandBuilder(daOperation)

     

    Dim WithEvents dtOperation As New DataTable

    If I add a new row with data as in the following lines:

     

    Dim dtRow As DataRow = dtOperation.NewRow

    dtRow = dtOperation.NewRow

    dtRow(

    "Reservoir") = "Emigration"

    dtOperation.Rows.Add(dtRow)

    daOperation.Update(dtOperation

    My database has the following fields:

    ID - autonumber, Reservoir - varchar(30), YEAR - Integer, MONTH - Integer, DAY - Integer, DATEdata - Date, INFLOW - Double, OUTFLOW - Double, ELEVATION - Double, AREA - Double, VOLUME - Double, EVAP - Double

    I receive an unhandled exception for my insert command (from bldOperations.Getinsertcommand, the daOperation.Insertcommand is nothing at this point)

    "INSERT INTO Operation (Reservoir, YEAR, MONTH, DAY, DATEdata, INFLOW, OUTFLOW, ELEVATION, AREA, VOLUME, EVAP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

    If, on the other hand, I create a basic insert command with the following code, I can add a record but only handle one field:

     

    Dim daCmdInsert As New OleDbCommand

    daCmdInsert.CommandText =

    "Insert Into Operation (Reservoir) VALUES (?)"

    daCmdInsert.Parameters.Add(

    New OleDbParameter("Reservoir", OleDbType.VarChar, 30, _

     

    "Reservoir"))

    daOperation.InsertCommand = daCmdInsert

    If I attempt to add another field into the insert, YEAR, for example with the following:

    daCmdInsert.CommandText =

    "Insert Into Operation (Reservoir,YEAR) VALUES (?,?)"

    daCmdInsert.Parameters.Add(

    New OleDbParameter("Reservoir", OleDbType.VarChar, 30, _

     

    "Reservoir"))

    daCmdInsert.Parameters.Add(

    New OleDbParameter("YEAR", OleDbType.BigInt, 8, _

     

    "YEAR"))

    I get the following error when attempting to update:

    System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.UpdateRowExecute(RowUpdatedEventArgs rowUpdatedEvent, IDbCommand dataCommand, StatementType cmdIndex) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)"

    Any help at figuring out what's going on here would be appreciated.  Sorry about the various colors of the post.  Not sure how to control that.

    Thanks,

    Craig


    Salt Lake
    Wednesday, June 23, 2010 7:46 PM

Answers

  • Craig,

    The error you're seeing is most likely because Year is a reserved word.  To help the database engine understand when you're referring to the column named Year, rather than the Year function, enclose the column name in square brackets in your queries.

    With the general scenario (submitting updates where the key value is auto-generated in an Access database), I have two suggestions.

     

    1. Set the DataColumn's AutoIncrementSeed and AutoIncrementStep properties to -1 so that the placeholder values generated for pending inserts can not conflict with values that may exist in the database.  Following this approach, you'll get values of -1, -2, -3, ...
    2. Handle the DataAdapter's RowUpdated event and execute a SELECT @@IDENTITY query to retrieve the key value for each new row you submit.

     

    Here's some sample code that I hope will prove helpful.

     

    Sub Main()
        Dim path As String = ".\Test.mdb"
        Dim csBuilder As New OleDbConnectionStringBuilder
        csBuilder.Provider = "Microsoft.Jet.OLEDB.4.0"
        csBuilder.DataSource = path
        Dim connectionString As String = csBuilder.ConnectionString
        Dim sql As String
    
        Dim connection As New OleDbConnection(connectionString)
    
        'Create the database and table if it does not already exist
        If Not File.Exists(path) Then
            Dim catalog As Object = CreateObject("ADOX.Catalog")
            catalog.Create(connectionString)
            catalog.ActiveConnection.Close()
    
            connection.Open()
            sql = "CREATE TABLE TestTable " & _
                  "(ID int identity(10, 10) PRIMARY KEY" & _
                  ", Field1 varchar(255), [Year] int)"
            With New OleDbCommand(sql, connection)
                .ExecuteNonQuery()
            End With
        Else
            connection.Open()
        End If
    
    
        'Create the DataAdapter
        sql = "SELECT ID, Field1, [Year] FROM TestTable"
        Dim adapter As New OleDbDataAdapter(sql, connection)
    
        'Create the InsertCommand
        sql = "INSERT INTO TestTable (Field1, [Year]) VALUES (?, ?)"
        adapter.InsertCommand = New OleDbCommand(sql, connection)
        With adapter.InsertCommand
            .Parameters.Add("@Field1", OleDbType.VarWChar, 255, "Field1")
            .Parameters.Add("@Year", OleDbType.Integer, 0, "Year")
        End With
    
        'Handle the RowUpdated event
        AddHandler adapter.RowUpdated, AddressOf GetNewIdHandler
    
        'Create the DataTable, add some rows
        Dim table As New DataTable("TestTable")
        table.Columns.Add("ID", GetType(Int32))
        table.Columns.Add("Field1", GetType(String))
        table.Columns.Add("Year", GetType(Integer))
        With table.Columns("ID")
            .AutoIncrement = True
            'BEST PRACTICE: Ensure placeholder key values for inserts 
            'will not conflict with values that may exist in the database
            .AutoIncrementSeed = -1
            .AutoIncrementStep = -1
        End With
    
        table.Rows.Add(Nothing, "First Row", DateTime.Now.Year - 1)
        table.Rows.Add(Nothing, "Second Row", DateTime.Now.Year)
        table.Rows.Add(Nothing, "Third Row", DateTime.Now.Year + 1)
    
        Console.WriteLine("Before Update")
        For Each row As DataRow In table.Rows
            Console.WriteLine("  {0} has ID of {1}", _
                              row("Field1"), row("ID"))
        Next
        Console.WriteLine()
    
        adapter.Update(table)
    
        Console.WriteLine("After Update")
        For Each row As DataRow In table.Rows
            Console.WriteLine("  {0} has ID of {1}", _
                              row("Field1"), row("ID"))
        Next
        Console.WriteLine()
    
        connection.Close()
    End Sub
    
    Private Sub GetNewIdHandler(ByVal sender As Object, _
                                ByVal e As OleDbRowUpdatedEventArgs)
        If e.Status = UpdateStatus.Continue _
           AndAlso e.StatementType = StatementType.Insert Then
            Dim sql As String = "SELECT @@IDENTITY"
            Dim cmd As New OleDbCommand(sql, e.Command.Connection)
            e.Row("ID") = cmd.ExecuteScalar()
        End If
    End Sub

    David Sceppa
    Thursday, June 24, 2010 8:36 PM
    Moderator

All replies

  • Craig,

    The error you're seeing is most likely because Year is a reserved word.  To help the database engine understand when you're referring to the column named Year, rather than the Year function, enclose the column name in square brackets in your queries.

    With the general scenario (submitting updates where the key value is auto-generated in an Access database), I have two suggestions.

     

    1. Set the DataColumn's AutoIncrementSeed and AutoIncrementStep properties to -1 so that the placeholder values generated for pending inserts can not conflict with values that may exist in the database.  Following this approach, you'll get values of -1, -2, -3, ...
    2. Handle the DataAdapter's RowUpdated event and execute a SELECT @@IDENTITY query to retrieve the key value for each new row you submit.

     

    Here's some sample code that I hope will prove helpful.

     

    Sub Main()
        Dim path As String = ".\Test.mdb"
        Dim csBuilder As New OleDbConnectionStringBuilder
        csBuilder.Provider = "Microsoft.Jet.OLEDB.4.0"
        csBuilder.DataSource = path
        Dim connectionString As String = csBuilder.ConnectionString
        Dim sql As String
    
        Dim connection As New OleDbConnection(connectionString)
    
        'Create the database and table if it does not already exist
        If Not File.Exists(path) Then
            Dim catalog As Object = CreateObject("ADOX.Catalog")
            catalog.Create(connectionString)
            catalog.ActiveConnection.Close()
    
            connection.Open()
            sql = "CREATE TABLE TestTable " & _
                  "(ID int identity(10, 10) PRIMARY KEY" & _
                  ", Field1 varchar(255), [Year] int)"
            With New OleDbCommand(sql, connection)
                .ExecuteNonQuery()
            End With
        Else
            connection.Open()
        End If
    
    
        'Create the DataAdapter
        sql = "SELECT ID, Field1, [Year] FROM TestTable"
        Dim adapter As New OleDbDataAdapter(sql, connection)
    
        'Create the InsertCommand
        sql = "INSERT INTO TestTable (Field1, [Year]) VALUES (?, ?)"
        adapter.InsertCommand = New OleDbCommand(sql, connection)
        With adapter.InsertCommand
            .Parameters.Add("@Field1", OleDbType.VarWChar, 255, "Field1")
            .Parameters.Add("@Year", OleDbType.Integer, 0, "Year")
        End With
    
        'Handle the RowUpdated event
        AddHandler adapter.RowUpdated, AddressOf GetNewIdHandler
    
        'Create the DataTable, add some rows
        Dim table As New DataTable("TestTable")
        table.Columns.Add("ID", GetType(Int32))
        table.Columns.Add("Field1", GetType(String))
        table.Columns.Add("Year", GetType(Integer))
        With table.Columns("ID")
            .AutoIncrement = True
            'BEST PRACTICE: Ensure placeholder key values for inserts 
            'will not conflict with values that may exist in the database
            .AutoIncrementSeed = -1
            .AutoIncrementStep = -1
        End With
    
        table.Rows.Add(Nothing, "First Row", DateTime.Now.Year - 1)
        table.Rows.Add(Nothing, "Second Row", DateTime.Now.Year)
        table.Rows.Add(Nothing, "Third Row", DateTime.Now.Year + 1)
    
        Console.WriteLine("Before Update")
        For Each row As DataRow In table.Rows
            Console.WriteLine("  {0} has ID of {1}", _
                              row("Field1"), row("ID"))
        Next
        Console.WriteLine()
    
        adapter.Update(table)
    
        Console.WriteLine("After Update")
        For Each row As DataRow In table.Rows
            Console.WriteLine("  {0} has ID of {1}", _
                              row("Field1"), row("ID"))
        Next
        Console.WriteLine()
    
        connection.Close()
    End Sub
    
    Private Sub GetNewIdHandler(ByVal sender As Object, _
                                ByVal e As OleDbRowUpdatedEventArgs)
        If e.Status = UpdateStatus.Continue _
           AndAlso e.StatementType = StatementType.Insert Then
            Dim sql As String = "SELECT @@IDENTITY"
            Dim cmd As New OleDbCommand(sql, e.Command.Connection)
            e.Row("ID") = cmd.ExecuteScalar()
        End If
    End Sub

    David Sceppa
    Thursday, June 24, 2010 8:36 PM
    Moderator
  • David,

    Beautiful!  It worked mahvellously.  I've been away on a trip and was pleased that you had responded with a workable answer.  However, I would like to ask for more information.  I have statements for all my tables that I BELIEVED created the insert, update, and delete commands automatically for me, namely:

     Dim WithEvents daOperation As New OleDbDataAdapter("Select * from Operation", cnJetDB)
     Dim bldOperations As New OleDbCommandBuilder(daOperation)
     Dim WithEvents dtOperation As New DataTable

    I had believed that the OleDBCommandBuilder would create the insert, update and delete statements and most of the time it did.  When I attempt to insert or update without obtaining the autoincrement value, everything works just fine.  However, when I attempted to insert and then obtain the autoincrement value I received the error that the insert statement did not exist.  After manually entering the insert command as shown above the update and other commands seemed to work just fine.  What is going on?

    Craig


    Salt Lake
    Thursday, July 1, 2010 9:08 PM
  • Craig,

    Glad to hear that the code worked for you.  And welcome back.

    The error you described doesn't ring a bell.  If you can post a small repro scenario, I can try to take a look.


    David Sceppa
    Thursday, July 1, 2010 9:18 PM
    Moderator
  • David,

    When I tried a test, it worked well.  However, in the program itself, I have problems with insert syntax.   I have two questions:  Do you see anything wrong with the syntax AND how could an OleDbCommandBuilder be used to bypass the need to write my own insert statements.  First, the syntax for the insert statement (I have brackets around all variables to avoid the problem of reserved words):

      Dim sqlInsBud As String = "INSERT INTO ModelBudget ([Name],[PageHdg],[BegYear],[EndYear]," & _
       "[BegSoilMatch],[Iterations],[ProgName],[ExeName],[PrintName],[TypeYear],[PrintResData]," & _
       "[PrintAllRes],[PrintStage],[PrintArea],[PrintCapacity],[LinesPerPage],[PrintInflows]," & _
       "[PrintMinFlows],[PrintLandArea],[PrintInOut],[PrintAnnYield],[PrintPerYield],[CalcPrivDomestic]," & _
       "[IsOpen],[Filename],[LastUser],[CreatedBy],[LastOpened],[LastClosed] VALUES (?,?,?,?,?,?,?,?,?,?,?," & _
       "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"
      taModelBudget.InsertCommand = New OleDbCommand(sqlInsBud, CNModelFile)
      With taModelBudget.InsertCommand
       .Parameters.Add("@Name", OleDbType.VarWChar, 200, "Name")
       .Parameters.Add("@PageHdg", OleDbType.VarWChar, 200, "PageHdg")
       .Parameters.Add("@BegYear", OleDbType.Integer, 0, "BegYear")
       .Parameters.Add("@EndYear", OleDbType.Integer, 0, "EndYear")
       .Parameters.Add("@BegSoilMatch", OleDbType.Boolean, 0, "BegSoilMatch")
       .Parameters.Add("@Iterations", OleDbType.Integer, 0, "Iterations")
       .Parameters.Add("@ProgName", OleDbType.VarWChar, 50, "ProgName")
       .Parameters.Add("@ExeName", OleDbType.VarWChar, 50, "ExeName")
       .Parameters.Add("@PrintName", OleDbType.VarWChar, 50, "PrintName")
       .Parameters.Add("@TypeYear", OleDbType.Integer, 0, "TypeYear")
       .Parameters.Add("@PrintResData", OleDbType.Boolean, 0, "PrintResData")
       .Parameters.Add("@PrintAllRes", OleDbType.Boolean, 0, "PrintAllRes")
       .Parameters.Add("@PrintStage", OleDbType.Boolean, 0, "PrintStage")
       .Parameters.Add("@PrintArea", OleDbType.Boolean, 0, "PrintArea")
       .Parameters.Add("@PrintCapacity", OleDbType.Boolean, 0, "PrintCapacity")
       .Parameters.Add("@LinesPerPage", OleDbType.Integer, 0, "LinesPerPage")
       .Parameters.Add("@PrintInflows", OleDbType.Boolean, 0, "PrintInflows")
       .Parameters.Add("@PrintMinFlows", OleDbType.Boolean, 0, "PrintMinFlows")
       .Parameters.Add("@PrintLandArea", OleDbType.Boolean, 0, "PrintLandArea")
       .Parameters.Add("@PrintInOut", OleDbType.Boolean, 0, "PrintInOut")
       .Parameters.Add("@PrintAnnYield", OleDbType.Boolean, 0, "PrintAnnYield")
       .Parameters.Add("@PrintPerYield", OleDbType.Boolean, 0, "PrintPerYield")
       .Parameters.Add("@CalcPrivDomestic", OleDbType.Boolean, 0, "CalcPrivDomestic")
       .Parameters.Add("@IsOpen", OleDbType.Boolean, 0, "IsOpen")
       .Parameters.Add("@FileName", OleDbType.VarWChar, 100, "FileName")
       .Parameters.Add("@LastUser", OleDbType.VarWChar, 50, "LastUser")
       .Parameters.Add("@CreatedBy", OleDbType.VarWChar, 50, "CreatedBy")
       .Parameters.Add("@LastOpened", OleDbType.Date, 0, "LastOpened")
       .Parameters.Add("@LastClosed", OleDbType.Date, 0, "LastClosed")
      End With
    

    Here's the code association with the OleDbCommandBuilder.  I don't think I have it set up corrrectly because the commands don't seem to be generated automatically.

     Dim cmdModelBudget As String = "SELECT * FROM ModelBudget ORDER BY Name;"
     Dim WithEvents taModelBudget As New OleDbDataAdapter(cmdModelBudget, CNModelFile)
     Dim WithEvents dtModelBudget As New DataTable("ModelBudget")
     Dim bldModelBudget As New OleDbCommandBuilder(taModelBudget)
    

    Is there something more that needs to be done?  Anyhow, thanks for any suggestions you might have.

    Craig


    Salt Lake
    Wednesday, July 7, 2010 4:06 PM
  • Instantiating a CommandBuilder and linking it to a DataAdapter, as you've shown in your code snippet, automatically creates updating logic that will handle submitting updates.  If you want to handle the updates with your own code, do not create the CommandBuilder.

    You can tell the CommandBuilder to to surround the table and column names with square brackets by setting the QuotePrefix and QuoteSuffix properties to "[" and "]", respectively.

    It's been a while and I don't remember off hand whether you can use a CommandBuilder and still handle the RowUpdated event to fetch the database-generated auto-increment values.

    The CommandBuilder is simpler to use in code, but it's not as flexible.  Supplying your own updating logic gives you more control and better performance since the CommandBuilder needs to query your database at run-time to get the meta-data (table and column names, key information) required to generate the updating logic.


    David Sceppa
    Wednesday, July 7, 2010 8:42 PM
    Moderator
  • David,

    Setting the QuotePrefix and QuoteSuffix properties to "[" and "]" respectively worked with my test datatable.  The RowUpdated event fired and gave me the proper ID for the autoincrement variable.  The next step is to test it with my production datatable.  Thanks for your help and suggestions.

    In adding this addition to the production datatable I encountered a problem.  I am using a DataGridView to add records to, delete from or update the datatable.  The program does not use the taModelBudget_RowUpdated code unless I add a record to the rows in the dtModelBudget TableNewRow method with a dtModelBudget.Rows.Add(aRow) statement and immediately do an update.  After I do that, when the DataGridView finally enters a record I receive the error that a row with that ID already exists.  If I eliminate the those Rows.Add and .Update statements, the taModelBudget_RowUpdated code is skipped entirely.  Is there a DataGridView method I can use that will do the same thing as the RowUpdated event?

    What would be the proper DataGridView method to use in this case? I've tried DataGridView methods that look promising and haven't found the correct one yet. 

    Craig


    Salt Lake
    Sunday, July 11, 2010 4:27 PM
  • Craig,

    The control and the event are very separate things.  However, there are common scenarios where making changes using the control are cached and not written to the row and you may need to programmatically tell the control to write the changes to the row prior to submitting your updates.  If the changes are not written to the DataTable, then the DataAdapter does not see pending changes to submit, which would explain why the RowUpdated event does not fire.  It sounds like this is a WinForms (or WPF) application.  Please confirm (or deny) and describe how you've bound the grid to the DataTable and I should be able to help show you how to write the changes cached in the control to the row programmatically prior to submitting your updates.


    David Sceppa
    Monday, July 12, 2010 9:08 PM
    Moderator
  • David,
    Yes it is a Windows Forms Application.  I Have defined the datatable and bound it to the DataGridView as follows:
     ' Define ModelBudget datatable
     Dim CNstr As String = _
     "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Work\WATBUDG\Models\ModelData.mdb"
     Dim CN As New OleDbConnection(CNstr)
     Public cmdModelBudget As String = "SELECT * FROM ModelBudget ORDER BY Name;"
     Public WithEvents taModelBudget As New OleDbDataAdapter(cmdModelBudget, CN)
     Public WithEvents dtModelBudget As New DataTable("ModelBudget")
     Dim bldModelBudget As New OleDbCommandBuilder(taModelBudget)
     bldModelBudget.QuotePrefix = "["
     bldModelBudget.QuoteSuffix = "]"
     taModelBudget.Fill(dtModelBudget)
    
     '----------------------------------------------
     'Bind dtModelBudget to DataGridView
     'Set up UserModelDataGridView
     '----------------------------------------------
     UserModelsDataGridView.DataSource = dtModelBudget
     SetSelectModel()
    
     ' SetSelectModel() defines columns for DataGridView
     Private Sub SetSelectModel()
     With UserModelsDataGridView
      .Columns(0).Visible = True
      .Columns(0).HeaderText = "Model No."
      .Columns(0).ReadOnly = True
      .Columns(1).Width = 120
      .Columns(1).HeaderText = "Name"
      .Columns(2).HeaderText = "Printed Heading"
      .Columns(2).Width = 200
      For i As Integer = 3 To 23
      .Columns(i).Visible = False
      Next
      .Columns(24).HeaderText = "Is Open?"
      .Columns(25).HeaderText = "In File"
      .Columns(26).HeaderText = "Last User"
      .Columns(27).HeaderText = "Created By"
      .Columns(28).HeaderText = "Date Opened"
     End With
     End Sub
    
    Thanks for any assistance you can give.
    Craig
    <noscript></noscript>
    Monday, July 12, 2010 9:50 PM
  • I found one way of handling this problem.  Here are the steps:

    • For the DataGridView set the AllowUserToAddRows property to False.
    • Create a button that says something like "Add Row".
    • In the code behind the button I placed the following snipet:
       If MsgBox("Create a new model?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
       Dim aRow As DataRow = dtModelBudget.NewRow()
       dtModelBudget.Rows.Add(aRow)
       taModelBudget.Update(dtModelBudget)
       UserModelsDataGridView.Refresh() 'Adds a row to the DataGridView
       UserModelsDataGridView.Rows(UserModelsDataGridView.Rows.Count - 1).Selected = True 'Selects last row
       UserModelsDataGridView.Refresh()
       End If

    I suspect that there are better ways of doing this.  If someone has a suggestion, I'm ready to hear it.

    Craig


    Salt Lake

    Tuesday, July 13, 2010 7:36 PM
  • That's definitely one way of doing it.

    Here's some simple code I added to a form's Load event that may help.  In your code, prior to calling DataAdapter.Update, check the number of pending changes (you can use DataTable.Select(...).Length as shown in the code snippet) to make sure there the last set of changes are not stored in the bound control(s).  If there are cached changes, the code snippet shows how to call the EndCurrentEdit method on the CurrencyManager to write the changes cached within bound controls to the DataTable.  Based on the information you've provided, it sounds like this is part of the issue you're encountering.  (I would have written the code in VB, but I wrote it while I was off line and had forgotten you were working in VB.)

     

    //Create and populate table
    DataTable table = new DataTable();
    DataColumn idColumn = table.Columns.Add("ID", typeof(int));
    table.Columns.Add("OtherColumn", typeof(string));
    table.Rows.Add(1, "Row 1");
    table.Rows.Add(2, "Row 2");
    table.Rows.Add(3, "Row 3");
    
    //Mark existing rows as unchanged
    table.AcceptChanges();
    
    //Make ID Column generate placeholder values -1, -2, -3, ...
    idColumn.AutoIncrement = true;
    idColumn.AutoIncrementSeed = -1;
    idColumn.AutoIncrementStep = -1;
    
    DataGridView grid = new DataGridView();
    grid.DataSource = table;
    grid.Size = new Size(this.Width - 16, this.Height - 70);
    grid.Anchor = AnchorStyles.Top | AnchorStyles.Left | 
        AnchorStyles.Bottom | AnchorStyles.Right;
    this.Controls.Add(grid);
    
    Button submitButton = new Button();
    submitButton.Text = "Submit Changes";
    submitButton.AutoSize = true;
    submitButton.Top = grid.Height + 5;
    submitButton.Left = this.Width - 20 - submitButton.Width;
    submitButton.Anchor = AnchorStyles.Bottom | AnchorStyles.Right;
    submitButton.Click += delegate(object myDelegate, EventArgs myE)
    {
        CurrencyManager cm = (CurrencyManager)this.BindingContext[table];
        cm.EndCurrentEdit();
    
        int numInserts, numUpdates, numDeletes;
        numInserts = table.Select("", "", DataViewRowState.Added).Length;
        numUpdates = table.Select("", "", DataViewRowState.ModifiedCurrent).Length;
        numDeletes = table.Select("", "", DataViewRowState.Deleted).Length;
    
        StringBuilder message = new StringBuilder();
        message.AppendLine(string.Format("{0} insert(s)", numInserts));
        message.AppendLine(string.Format("{0} update(s)", numUpdates));
        message.AppendLine(string.Format("{0} delete(s)", numDeletes));
        MessageBox.Show(message.ToString(), "Changes to submit");
    };
    this.Controls.Add(submitButton);
    

     


    David Sceppa
    Tuesday, July 13, 2010 10:09 PM
    Moderator
  • David,

    Thanks again.  That seems to be a more solid and general way of handling the issue since the DataGridView can create new records and return the autoincrement values if desired.  Because all the .NET language flavors are similar it took awhile to realize that it's excellent C# but needs a tad of translation for my project.  It is greatly appreciated.

    Craig


    Salt Lake
    Tuesday, July 13, 2010 11:51 PM
  • No worries, Craig.

    Let the DataTable generate placeholder auto-increment values for you.  Then let the DataAdapter's RowUpdated event retrieve the actual values the database generated.  Using the negative placeholder values ensures the placeholder values won't match anything that could exist in the database.

    If you're still having problems with the bound controls and having the changes written to your DataTable prior to calling DataAdapter.Update, please start a new thread and provide repro steps and I'm sure someone (maybe me) will be able to lend a hand.


    David Sceppa
    Wednesday, July 14, 2010 7:35 PM
    Moderator