DataGridView does not allow nulls.

Hi,

I'm using Visual Studio 2010 and the SQLite API. Here's the problem, I've got a generated data source for a SQLite Database table, I want to have an InsertCommand setup via the Query Builder which will insert only [Example] and [AssociativeID] into the SQL database, and not mess with the ID field, since the ID is a primary key and the SQLite database will automatically assign an autoincremented value for it.

I have a feeling it's impossible for me to update the DataGridView with the appropriate ID fields, since when the user adds a new row, it only inserts into the DataSet/DataAdapter, and the values aren't actually written into the SQL until I manually call Update on the TableAdapter.

However, the fact is, I just want to be able to allow the user to add a new row, but the DataError Method of the DataGridView is constantly throwing an error.


Here is the ExampleTable:

The database is very simple:
CREATE TABLE "ExampleTable" (
  [ID] INTEGER PRIMARY KEY,
  [Example] nvarchar NOT NULL,
  [AssociativeID] INT NOT NULL
  );


I've setup an XSD, and built an appropriate InsertCommand using the Query Builder for the table adapter.

The command looks like this:
INSERT INTO ExampleTable
                      (Example, AssociativeID)
VALUES     (@Example, @AssociativeID)


************ Parameters Collection Editor ************
The "Parameters Collection Editor" shows two values:
@Example
@AssociativeID


I noticed that I could add a parameter called @ID,
and set a field called Direction to:
Input
Output
InputOutput
ReturnValue

************ XSD Designer ************

ExampleTable
ID DataColumn
      AllowDBNull                  True/False      // Doesn't make a difference
      AutoIncrement                              // I don't want to programmatically set an ID for this field.
      DefaultValue            <DBNull>
      NullValue                  (Throw exception)      // Attempting to change this field results in "Property value is not valid."
      ReadOnly                  False


************ DataGridView ************
Columns:
ID
      ReadOnly = True // I don't want the user to mess with this field, since it is assigned automatically inside the SQL database.

Everything is setup correctly and refreshes properly, including changing row values.


Any attempt by the user to add a new row results in the following error:
  System.Data.NoNullAllowedException: Column 'ID' does not allow nulls.
   at System.Data.DataColumn.CheckNullable(DataRow row)
   at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent)
   at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Boolean suppressEnsurePropertyChanged, Int32 position, Boolean fireEvent, Exception& deferredException)
   at System.Data.DataTable.InsertRow(DataRow row, Int64 proposedID, Int32 pos, Boolean fireEvent)
   at System.Data.DataView.FinishAddNew(Boolean success)
   at System.Data.DataRowView.EndEdit()
   at System.Windows.Forms.CurrencyManager.EndCurrentEdit()
   at System.Windows.Forms.DataGridView.DataGridViewDataConnection.OnRowValidating(DataGridViewCellCancelEventArgs e)

   
I've tried handling this error manually by overriding the DataError method, it doesn't matter what I do, it will never save the changes.

This is a call I've used before, to get the new autoincremented ID from an SQL table after inserting a new row:

String insert_str = "INSERT INTO ExampleTable (Example, AssociativeID) VALUES(@Example, @AssociativeID); SELECT last_insert_rowid() AS NewID;";
sqlcmd.CommandText = insert_str.ToString();
sqlcmd.CommandType = CommandType.Text;
Object scalarresult = sqlcmd.ExecuteScalar();
Int64 newrow_id = Convert.ToInt64(scalarresult);

Select allOpen in new window

This works on this table without any problems.

So basically this is a two part problem.
Main problem is: How to allow the user to add new rows without having to add any values in the ID column, which is ReadOnly anyway.
Secondary problem: How to get the new ID from the database after doing an Update() and filling in the DataGridView.ID column.

I've been banging my head against the wall for almost five hours trying to figure out what I'm doing wrong. Please help!