[Note Update:]
My desire is for a user adding a new row to a datagridview (DGV) will be able to have that row created in an Access database, and to be able to create multiple rows and non-synchronous edits to those new rows. The DGV is being filled by a data set table, and I have found having the data set between the DGV and the database is the best practice (or at least allows for flexibility).
To make edits to multiple user created new rows, the data set needs to be able to retrieve the auto-increment primary key from the database, and then refill the DGV with the updated key values for the new rows. Or at least that is the only way I have found to have this work. The issue is that when I try to add a row to the datatable, it ends up making three rows instead of one.
[Old post deleted for brevity]
Here is how the DGV is first being filled and bound to the data set:
Dim ConMain As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MasterPath)
Dim ds As New DataSet
Public Sub UniversalFiller(ByVal QueryStringFill As String, ByVal DGV As DataGridView, ByVal AccessDBTableName As String)
If IsNothing(ds.Tables(AccessDBTableName)) Then
Else
ds.Tables(AccessDBTableName).Clear()
End If
ConMain.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MasterPath
Dim daZZ As New OleDbDataAdapter(QueryStringFill, ConMain)
Try
daZZ.Fill(ds, AccessDBTableName)
DGV.DataSource = ds
DGV.DataMember = AccessDBTableName
DGV.AutoResizeColumns()
Catch ex As Exception
WriteToErrorLog(ex.Message, ex.StackTrace, ex.GetHashCode, ex.Source, ex.ToString)
ds.Clear
MsgBox("There was an error filling the DGV, ds.cleared") 'this is for my notes, not user error'
End Try
End Sub
I still can't figure out this problem. This is called when the handling DGV.UserAddedRow. I've learned that the original code might not have been the best way to just Add 1 row, but even making a New Row runs into the same problem:
Dim daZZ As New OleDbDataAdapter(DBSelectQuery, ConMain)
Dim CurrentCellPoint As Point = DGV.CurrentCellAddress
Dim CurrentCellText As String = DGV.CurrentCell.Value.ToString
Dim cmdBldr As New OleDbCommandBuilder(daZZ)
cmdBldr.QuotePrefix = "["
cmdBldr.QuoteSuffix = "]"
MsgBox("1")
Dim DaZZDataRow As DataRow = ds.Tables(DTName).NewRow
MsgBox("2")
DaZZDataRow(CurrentCellPoint.X) = CurrentCellText
MsgBox("3")
ds.Tables(DTName).Rows.Add(DaZZDataRow)
MsgBox("4")
daZZ.InsertCommand = cmdBldr.GetInsertCommand()
MsgBox("5")
daZZ.Update(ds.Tables(DTName))
MsgBox("6")
ds.Tables(DTName).Clear()
daZZ.Fill(ds, DTName)
MsgBox("5")
daZZ.Update(ds.Tables(DTName))
DGV.CurrentCell = DGV(CurrentCellPoint.X, CurrentCellPoint.Y)
The problem is always the ds.Tables(DTName).Rows.Add() step. It doesn't matter what is put in the parentheses on .Add(). It can be anumber or any intiger including 0 and it will make 3 rows. It doesn't get called multiple times. Just calling the ds.Tables(DTName).Rows.Add().
If that's not the right way to add one blank row, what is? I could post the SQL insert/update command, but that's not the problem because the add is making three rows, not 1. To be clear it will only run through the MsgBox items once and yield 3 rows.
Also curious to me, the InsertAt command yields the same result:
ds.Tables(DTName).Rows.InsertAt(DaZZDataRow, CurrentCellPoint.Y)
instead of ds.Tables(DTName).Rows.Add() still yields 3 new rows. I've also tried this on a new scratch DB from Access and get the same problem.
Here is a working example of the code, as an VS2012 Project plus dummy database. It's pretty bare-bones, ie no error handling, but it shows the error and also allows deleting rows as an added convenience. Thanks to anyone who takes a look.
I took a back-to-basics approach to this question, concentrating more on the issue of having the DataGridView show the generated AutoNumber ID value when I add a new record. I started by adding a DataGridView control to my form and using the "Add Project Data Source..." wizard to connect it to the table named [Chemicals] in my Access database "Database1.accdb". (An earlier version of the sample code in the question referred to a table named [Chemicals], so that's what I used.)
When I was finished I had the following "stuff" in my project:
a DataGridView control (dataGridView1) on my form, which was bound to a BindingSource (see below)
a DataSet (database1DataSet) associated with my Access database
a BindingSource (chemicalsBindingSource), associated with the [Chemicals] table in database1DataSet, to serve as the DataSource for my DataGridView
a TableAdapter (chemicalsTableAdapter) to manage the data flow between the DataSet and the actual database.
The wizard also gave me the following line of code in my Form1_Load method. (I used C#, but VB.NET would be very similar.)
// TODO: This line of code loads data into the 'database1DataSet.Chemicals' table. You can move, or remove it, as needed.
this.chemicalsTableAdapter.Fill(this.database1DataSet.Chemicals);
That was all I needed to run my application and see existing records in my DataGridView.
Now, to push DataGridView updates back to the database and retrieve the AutoNumber value for a newly-created record I used the RowLeave event. I found that the UserAddedRow event fired too soon (immediately after the user started typing in the new row), and I wanted to wait until the user had finished editing the row before doing anything with it.
The code I wound up using was as follows (again, C#)
private void Form1_Load(object sender, EventArgs e)
{
fillErUp();
}
private void fillErUp()
{
// (moved from Form1_Load to its own method so it could also be called from _RowLeave, below)
// TODO: This line of code loads data into the 'database1DataSet.Chemicals' table. You can move, or remove it, as needed.
this.chemicalsTableAdapter.Fill(this.database1DataSet.Chemicals);
}
private void dataGridView1_RowLeave(object sender, DataGridViewCellEventArgs e)
{
DataGridView dgv = (DataGridView)sender;
if (dgv.IsCurrentRowDirty)
{
Cursor.Current = Cursors.WaitCursor;
dgv.EndEdit(); // flush changes from DataGridView to BindingSource
chemicalsBindingSource.EndEdit(); // flush changes from BindingSource to DataSet
this.chemicalsTableAdapter.Update(this.database1DataSet); // update database
int currentRow = e.RowIndex;
if (Convert.ToInt32(dgv.Rows[currentRow].Cells[0].Value) < 0)
{
// negative AutoNumber value indicates that the row has just been added
int currentCol = e.ColumnIndex;
fillErUp(); // re-fill the table in the DataSet, and hence the DataGridView as well
dgv.CurrentCell = dataGridView1[currentCol, currentRow];
}
Cursor.Current = Cursors.Default;
}
}
This certainly isn't complete production code but I hope it helps.
I took another stab at my previous solution to see if I could accomplish the same results without relying on any Visual Studio "wizardry". My approach this time was:
I opened a new C# Windows Forms project.
I dragged-and-dropped a DataSet object, a BindingSource object, and a DataGridView object onto the blank form. They were named dataSet1, bindingSource1, and dataGridView1, respectively.
I created Form1_Load and dataGridView1_RowLeave event handlers, and then added my own code that wound up looking like this:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace dgvTest2
{
public partial class Form1 : Form
{
OleDbConnection con = new OleDbConnection();
OleDbDataAdapter da = new OleDbDataAdapter();
OleDbCommandBuilder cb;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Public\Database1.accdb";
con.Open();
da.SelectCommand = new OleDbCommand("SELECT * FROM Chemicals", con);
cb = new OleDbCommandBuilder(da);
cb.QuotePrefix = "["; cb.QuoteSuffix = "]";
this.dataSet1.Tables.Add(new DataTable("Chemicals"));
this.bindingSource1.DataSource = this.dataSet1;
this.bindingSource1.DataMember = "Chemicals";
this.dataGridView1.DataSource = this.bindingSource1;
fillErUp();
}
private void fillErUp()
{
this.dataSet1.Tables["Chemicals"].Clear();
da.Fill(this.dataSet1.Tables["Chemicals"]);
}
private void dataGridView1_RowLeave(object sender, DataGridViewCellEventArgs e)
{
DataGridView dgv = (DataGridView)sender;
if (dgv.IsCurrentRowDirty)
{
Cursor.Current = Cursors.WaitCursor;
dgv.EndEdit(); // flush changes from DataGridView to BindingSource
bindingSource1.EndEdit(); // flush changes from BindingSource to DataSet
da.Update(this.dataSet1.Tables["Chemicals"]); // update database
int currentRow = e.RowIndex;
if (dgv.Rows[currentRow].Cells[0].Value == DBNull.Value)
{
// a null ID value indicates that the row has just been added
int currentCol = e.ColumnIndex;
fillErUp(); // re-fill the table in the DataSet, and hence the DataGridView as well
dgv.CurrentCell = dataGridView1[currentCol, currentRow];
}
Cursor.Current = Cursors.Default;
}
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
con.Close();
}
}
}