Solved

Cannot update database using dataAdapter and CommandBuilder.

Posted on 2007-11-19
4
2,802 Views
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")
       
        cmd.Connection.Close()
        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
            Else
                Try
>>>>>> 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
                    System.Windows.Forms.MessageBox.Show(ex.Message)
                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:
?da.InsertCommand
{System.Data.SqlClient.SqlCommand}
    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
"xx"

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

Thanks
0
Comment
Question by:SteveB2175
  • 2
4 Comments
 
LVL 14

Accepted Solution

by:
ptakja earned 250 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.

0
 

Author Comment

by:SteveB2175
ID: 20314431
ptakja,

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

0
 
LVL 18

Assisted Solution

by:vbturbo
vbturbo earned 250 total points
ID: 20335520
try have a look here

https://filedb.experts-exchange.com/incoming/ee-stuff/5731-Relations.zip

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

then do the update made to the tables

vbturbo
0
 

Author Comment

by:SteveB2175
ID: 20361562
vbturbo,

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now