• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2822
  • Last Modified:

Cannot update database using dataAdapter and CommandBuilder.

This is a follow up to another question I asked recently.  I am still having problems, but I know a little more, so let's try again.

Background is that I am trying to modify some existing vb.net (VS2005) forms.  These forms have a DataGridView that was bound to a non-updatable View on SQL server 2000.  I have been asked to modify them so that the user can update data directly on the dgv.

Here is what I have done:

1) Created new database tables in SQL server, thus eliminating the non-updatable views.  I can readily update, delete, and insert using Enterprise manager.

2) I modified the existing VB form changing the FROM part of the SELECT statement to reference the new table, instead of the non-updatable view.

Here is some code:
 Dim DS As New DataSet
 Dim da As New SqlClient.SqlDataAdapter

Private Sub LoadDgvItemTypeCodes()
        Me.Cursor = Cursors.WaitCursor
        Dim SqlLine As String

>>> Create SQL Line  <<<<
        SqlLine = "SELECT ID, "
        SqlLine = SqlLine & "RTRIM(ItemClass1) AS [Item Code*], "
        SqlLine = SqlLine & "RTRIM(Description) AS [Description] "

        SqlLine = SqlLine & "FROM FSER_ItemTypeCodes "

        SqlLine = SqlLine & "ORDER BY ItemClass1 "

>>>>  'SQL Line is created, now load the DataGridView <<<<<<<
        Dim TediaConnectionString As String = "Data Source='" & Server & "';Initial Catalog=Tedia;Integrated Security=true"
        Dim TediaConnection As New SqlClient.SqlConnection(TediaConnectionString)

        Dim cmd As New SqlClient.SqlCommand(SqlLine, TediaConnection)

        da.SelectCommand = cmd
        Dim cb As New SqlClient.SqlCommandBuilder(da)

>>> Next three lines created as a stab at correcting the problem <<<<
        da.InsertCommand = cb.GetInsertCommand()
        da.UpdateCommand = cb.GetUpdateCommand()
        da.DeleteCommand = cb.GetDeleteCommand()

        da.Fill(DS, "MyTable")
        dgvItemTypeCodes.DataSource = DS.Tables("MyTable")

        Me.Cursor = Cursors.Default
    End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        If dgvItemtypeCodes.CurrentCell.IsInEditMode Or DS.HasChanges Then
            Dim Answer As String = MsgBox("Would you like to save your changes ?", MsgBoxStyle.YesNo + MsgBoxStyle.DefaultButton2)
            If Answer = vbNo Then
                Exit Sub
>>>>>> Here is the line that actually tries the update <<<<<<<<<<<<<
                    Dim UpdateNameCount As Integer = da.Update(DS.Tables("MyTable"))
                    MessageBox.Show(UpdateNameCount & " Forecast Record(s) Updated")
                Catch ex As System.Exception
                End Try
            End If
        End If
    End Sub

The problem I am having is that when I add a line and click the "Update" button, I receive a message telling me that I can't insert a NULL value into the datatable.

This is what the Immediate pane returns when I try to look at the InsertCommand:
    CommandText: "INSERT INTO [FSER_ItemTypeCodes] DEFAULT VALUES"
    CommandTimeout: 30
    CommandType: Text {1}
    Connection: {System.Data.SqlClient.SqlConnection}
    Container: Nothing
    DesignTimeVisible: True
    Notification: Nothing
    NotificationAutoEnlist: False
    Parameters: {System.Data.SqlClient.SqlParameterCollection}
    Site: Nothing
    Transaction: Nothing
    UpdatedRowSource: None {0}

Here is what the immediate pane returns when I try to return the value of the row that I just added to the dgv(XX is the correct value that I'm trying to insert into the database table):
?ds.Tables ("MyTable").Rows (21).Item ("Item Code*").ToString

Last time I had to manually write 150 lines of code to force all the changes to the database, I've got 20 more of these to update, so I sure would like to find a solution to the problem !!

  • 2
2 Solutions
I think that part of  your issue is that your initial SELECT statment that you pass into the Adapter & subsequently the command builder (via the Adapter) only selects the ID field & a couple others:

  SqlLine = "SELECT ID, "
        SqlLine = SqlLine & "RTRIM(ItemClass1) AS [Item Code*], "
        SqlLine = SqlLine & "RTRIM(Description) AS [Description] "

In order for the command builder to work like you want to, you need to include ALL the fields from the table with a "SELECT * FROM <table name>".

Your other option is to write the Insert, Update & Delete statements manually and add them to the Adapter:

Adapter.InsertCommand.CommandType = SqlCommandTypes.Text  (I am going by memory on this one)
Adapter.InsertCommand.CommandText = "INSERT INTO <my table> ...."

Adapter.UpdateCommand.CommandText = "UPDATE <my table> SET ..."

A third option is similar to the one above, except you use a stored procedure to do the insert, update & delete.

The bottom line is if you want to use the automated tools built into the Framework, you can't restrict the data selected from your SELECT command.

SteveB2175Author Commented:

Thanks, but my datatable only has the 3 columns I listed (ID, ItemClass1, and Description).

Should be pretty simple, eh ?? :) :)

I've spent quite a bit of time on this problem, and I've got to think that this is some kind of problem with .net that doesn't recogonize the changes I've made.

I finally got tired of messing with it and started from scratch.  Created a brand new form, re-wrote the code (actually I cut and pasted a lot of it) and guess what BANG !!! works perfect first time out of the box.  Sigh...

try have a look here


type some data in the one of the grids or create/delete a  row(s)

then do the update made to the tables

SteveB2175Author Commented:

Thanks for the download, it works for me, and is similar to what I coded.

As I mentioned above, I "solved" the problem by re-creating the forms, and all is well now.

I feel fairly certain that VS didn't pick up on the changes I made to the forms, and that caused the problem.  I don't know enough about the internal architecture to trouble shoot such a problem, so re-creating the form seemed to be my only option.

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now