Getting error message in SqlDataAdapter.Update()

Question

I have a DB table (which is empty in this example)

create table words
(
    id int not null,
    word nvarchar(50) not null
)

and a DataGridView which I'm filling like that:



    private SqlConnection _conn;
    private SqlDataAdapter _wordsAdapter;
    private DataTable _wordsDataTable = new DataTable();
    private DataGridView _tblWords;

    private void FillWords()
    {
        _wordsAdapter = new SqlDataAdapter(new SqlCommand("select id, word from words", _conn));
        _wordsAdapter.Fill(_wordsDataTable);

        DataGridViewColumn column = new DataGridViewTextBoxColumn();
        column.DataPropertyName = "id";
        column.Name = "id";
        _tblWords.Columns.Add(column);

        column = new DataGridViewTextBoxColumn();
        column.DataPropertyName = "word";
        column.Name = "word";
        _tblWords.Columns.Add(column);

        _tblWords.DataSource = _wordsDataTable;
    }

    private void Update()
    {
        _wordsAdapter.InsertCommand = new SqlCommand("insert into words (id, word) values (@id, @word)", _conn);
        SqlParameter p = _wordsAdapter.InsertCommand.Parameters.Add("@id", SqlDbType.Int);
        p.SourceColumn = "id";
        p.SourceVersion = DataRowVersion.Original;
        p = _wordsAdapter.InsertCommand.Parameters.Add("@word", SqlDbType.NVarChar);
        p.SourceColumn = "word";
        p.SourceVersion = DataRowVersion.Original;
        _wordsAdapter.Update(_wordsDataTable);
    }

Then I fill a single row of _tblWords with some values and call Update(). And get this message:

Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll
Additional information: Cannot insert the value NULL into column 'id', table 'DB.MDF.dbo.words'; column does not allow nulls. INSERT fails.

Why the value for 'id' is not taken from DataTable? What am I doing wrong?

UPDATE: After inserting this code in the beginning of Update() function, everything works fine:



    private void Update()
    {
        _wordsDataTable.Rows.Clear();
        _wordsDataTable.Rows.Add(0, "xxx");
        ...

So the problem only appears when I fill the DataTable through DataGridView. Why?!

You are taking data from the datagridview and inserting it into the table. ID column should be primary key or identity column and hence it is not allowing you to insert a duplicate value. If you want to update the word column in the table, then use UPDATE statement. — Nagaraj Raveendran, Jul 14 '16 at 19:49
The table is empty, so the row is INSERTED in this particular case. There are no duplicates because the row inserted is the first row in the table. But I still get the error. — cesarito, Jul 14 '16 at 19:56
I'm sorry, actually _cb is not necessary in this example. Changed it for 'new SqlCommand()' — cesarito, Jul 15 '16 at 08:48
Have you looked at this in the debugger and checked that you have values in the data table? — Matt, Jul 15 '16 at 11:00
Yes, I have values. Actually DataRow is absolutely identical when I add it through DataGridView and when I add it like this _wordsDataTable.Rows.Add(0, "xxx"); — cesarito, Jul 15 '16 at 11:57

score 0 · Answer 1 · answered Jul 15 '16 at 12:17

0

I am guessing that the problem is related to the last empty row in the DataGridView.

You can try something like this instead of the last _wordsAdapter.Update(_wordsDataTable); line

var haveDbNull = _wordsDataTable.Rows.Cast<DataRow>().ToLookup(r => r.ItemArray.Contains(DBNull.Value));

Debug.Print("have DbNull values: " + haveDbNull[true].Count()); // check this number in the Debug Output window
Debug.Print("don't have DbNull values: " + haveDbNull[false].Count()); 

_wordsAdapter.Update(haveDbNull[false].ToArray());  // to update just the rows that dont have DBNull values

answered Jul 15 '16 at 12:17

Slai

22,144
5
45
53

The row that is inserted has no null values. That's the problem. When I insert it value by value through DataGridView, I get the error. When I insert it to DataTable programmatically, everything is all right. – cesarito Jul 15 '16 at 13:23
I am not sure why you use `p.SourceVersion = DataRowVersion.Original;`. You should remove those or change them to `p.SourceVersion = DataRowVersion.Current;` https://msdn.microsoft.com/en-us/library/system.data.datarowversion – Slai Jul 15 '16 at 14:22
I tried Current as well, with the same result. If I remove it at all, there will be another error, saying that the parameter value is not supplied. The problem is that it takes null value instead of the current value from DataTable. – cesarito Jul 15 '16 at 14:53
then I give up .. You can try with the sample at http://csharp.net-informations.com/dataadapter/datagridview-sqlserver.htm and go from there – Slai Jul 15 '16 at 15:35

score 0 · Answer 2 · answered Jul 15 '16 at 15:46

0

I found some solution. I just remove the added row and add the same values programmatically.



    object[] rowValues = dt.Rows[row].ItemArray;
    dt.Rows.RemoveAt(row);
    dt.Rows.Add(rowValues);

Does anyone know any less ugly way to do it?

answered Jul 15 '16 at 15:46

cesarito

136
2
5

score 0 · Answer 3 · answered Jul 15 '16 at 16:40

0

The solution is very simple as always...


    _dtWords.EndInit();

answered Jul 15 '16 at 16:40

cesarito

136
2
5

Getting error message in SqlDataAdapter.Update()

3 Answers3