Solved

Cannot update database using dataAdapter and CommandBuilder.

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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…
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…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

777 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