DataGridView inserts primary key

Question

I'm trying to get a basic DataGridView to insert new rows into a table. The table has an auto incrementing primary key (Identity 1,1) I'm having two problems with this.

The first problem is the DataSet which the DataGridView is populated from complains with the the primary key in the row is null. (I hide the primary key field from the DataGridView)

This problem disappears when I disable constrain checking or modify the DataSet to ignore the primary key being null.

The next problem is SQL Server complains about the DataSet trying to insert a value into the primary key field. It doesn't like it when the DataSet specifies the value for the primary key.

I'm very new to using C# and I've never used a DataGrid before.

Answer 1

As far as your PRIMARY KEY problem, this is how I have my simple table structured and it works fine with inserting, updating, selecting, and deleting

CREATE TABLE [dbo].[INVENTORY](
[id] [int] IDENTITY(1,1) NOT NULL,
[L1] [varchar](20) NOT NULL,
[L2] [varchar](20) NOT NULL,
[L3] [varchar](20) NOT NULL,
[L4] [varchar](12) NULL,
[L5] [varchar](4) NULL,
[L6] [datetime] NULL,
[L7] [bit] NOT NULL,
[L8] [bit] NOT NULL,
[L9] [varchar](4) NULL,
[L10] [varchar](4) NULL)

Now, as as far as the code goes, tt sounds like you need to create a global DataAdapter and re-use it for your database backend connections. I will assume you are using SQL. I haven't seen your code, but you shouldn't be referencing your primary key in your INSERT or UPDATE commands since I assume your PRIMARY KEY is auto-incrementing and NOT NULL. Start by creating a DataAdapter with your SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand. The example below only shows SelectCommand, but Update/Insert/DeleteCommand all work the same and you just need to use the correct SQL command syntax.

private void ReadDB()
{
   try
   {
       string connectionString = "server=(local)\\SQLEXPRESS;" +
             "Trusted_Connection=yes; database=INVENTORY";

       myConnection = new SqlConnection(connectionString);

       myConnection.Open();

       myDataSet = new DataSet();

       myDataSet.CaseSensitive = true;
       DataAdapter = new SqlDataAdapter();
       DataAdapter = CreateInventoryAdapter();
       DataAdapter.TableMappings.Add("Table", "INVENTORY");

       DataAdapter.Fill(myDataSet);
   } catch (Exception ex) { // Do Something }
}

private SqlDataAdapter CreateInventoryAdapter()
{
     SqlDataAdapter adapter = new SqlDataAdapter();
     ....
     command = new SqlCommand("SELECT * FROM INVENTORY", myConnection);
     adapter.SelectCommand = command;

     return adapter;
     ....
}

Answer 2

The problem was the queries in the dataset were not being updated when I changed the properties of elements in the dataset. All I had to do was reconfigure the dataset.