Solved

Cannot update database using dataAdapter and CommandBuilder.

Posted on 2007-11-19
4
2,803 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 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

24 Experts available now in Live!

Get 1:1 Help Now