Cannot update database using dataAdapter and CommandBuilder.

Posted on 2007-11-19
Medium Priority
Last Modified: 2013-11-26
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 !!

Question by:SteveB2175
  • 2
LVL 14

Accepted Solution

ptakja earned 1000 total points
ID: 20314233
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.


Author Comment

ID: 20314431

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...

LVL 18

Assisted Solution

vbturbo earned 1000 total points
ID: 20335520
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


Author Comment

ID: 20361562

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Native ability to set a user account password via AD GPO was removed because the passwords can be easily decrypted by any authenticated user in the domain. Microsoft recommends LAPS as a replacement and I have written an article that does something …
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…
Watch the video of Kernel Migrator for SharePoint, which demonstrate the process easily of migration from SharePoint to SharePoint, OneDrive for Business & Google Drive servers, Public Folder to SharePoint, File Server to SharePoint. The tool has va…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question