[RESOLVED] DataTable not updating Database with DataAdapter

I am trying to update a database with info from a WinForm. I had no issues when using a “normal” SQL update command written by hand (parameters set to the text box values,) but I am trying to clean up and reduce my code and I thought I would bind the controls to a DataTable and use a DataAdapter's update command to achieve the same thing.

I have tried to get various combinations of setting parameters and update commands to work, but the Database is not getting updated from the new DataTable values. I have stepped through the code with each change and can see that the DataTable is getting the new textbox values, but those updates aren’t going to the Database.

Things I’ve tried: Letting the binding get the new values vs. setting the parameters manually. Using the command builder to build the update command, using the .UpdateCommand.ExecuteNonQuery(), command and of course a straight .Update(DataTable) command.

Below is the rough code that I am using (stripped out all the extras.) I am hoping someone can tell me what it is I am doing wrong/missing, or what correct path to take. Is there a "best practice" or a better way to do this?

Code:

---

 
Public Class frmDemog
        Dim dt_Test As New DataTable
        Dim da_Test As New SqlDataAdapter
        Dim SQLcmd As SqlCommand

        Private Sub frmDemog_Load(sender As Object, e As EventArgs) Handles MyBase.Load
                BuildSQL()
                FillInfo()
                BindControls()
        End Sub

        Private Sub BindControls()
                txtLName.DataBindings.Add("Text", dt_Test, "Last_Name")
                txtFName.DataBindings.Add("Text", dt_Demog, "First_Name")
        End Sub

        Private Sub ClearBindings()
                For Each c As Control In Me.Controls
                        c.DataBindings.Clear()
                Next
        End Sub

        Public Sub Update_Me(RefreshSearch As Boolean, RefreshView As Boolean)
                Try
                        With da_Test.UpdateCommand
                                .Parameters("@LName").Value = txtLName.Text
                                .Parameters("@FName").Value = txtFName.Text
                        End With

                        '** One version of updating that was tried
                        'If Not SQLcmd.Connection.State = ConnectionState.Open Then
                                'SQLcmd.Connection.Open()
                        'End If
                        'da_Test.UpdateCommand.ExecuteNonQuery()
                       
                        '** The update command that should work
                        da_Test.Update(dt_Test)

                Catch ex As Exception
                        MsgBox(ex.Message)
                End Try
        End Sub

        Public Sub FillInfo()
                dt_Test.Clear()
                da_Test.SelectCommand.Parameters("@ID").Value = frmMain.txtID.Text

                Try
                        da_Test.Fill(dt_Test)
                Catch ex As Exception
                        MsgBox(ex.Message)
                End Try
        End Sub


        Private Sub BuildSQL()
                '** Build Selection Query
                SQLcmd = New SqlCommand(String.Join(Environment.NewLine,
                        "SELECT ",
                                "data_Table.[Last_Name], ",
                                "data_Table.[First_Name], ",
                                "data_Table1.[Last_Name] + ', ' + data_Table1.[First_Name] as [AKA], ",
                        "FROM [DB].data_Table ",
                                "LEFT JOIN [DB].data_Table as data_Table1 ",
                                "ON data_Table.[ID] = data_Table1.[AKA_ID] ",
                        "WHERE data_Table.ID_Demog=@ID"
                        ), Vars.sqlConnACL)

                SQLcmd.Parameters.Add("@ID", SqlDbType.Int)
                da_Test.SelectCommand = SQLcmd

                '** Build Update Query
                SQLcmd = New SqlCommand(String.Join(Environment.NewLine,
                        "UPDATE [ACL].data_Table SET ",
                                "[Last_Name]  = @LName",
                                ",[First_Name]  = @FName",
                        "WHERE [ID_Demog] = @ID"
                        ), Vars.sqlConnACL)

                With SQLcmd.Parameters
                        .Add("@LName", SqlDbType.NVarChar, 255, "Last_Name")
                        .Add("@FName", SqlDbType.NVarChar, 255, "First_Name")
                End With
                da_Test.UpdateCommand = SQLcmd
        End Sub
End Class

---

One thing missing is that your update statement contains an @ID placeholder, but you haven't added a parameter for it.

If adding that doesn't solve it, do you get an error message?

Quote:

---

Originally Posted by si_the_geek

One thing missing is that your update statement contains an @ID placeholder, but you haven't added a parameter for it.

If adding that doesn't solve it, do you get an error message?

---

No, adding that did not solve it. Below is what I used to set the @ID parameter. I stepped through again to verify that testID was setting correctly and the DataTable was seeing the new textbox value. Everything is good until the .Update() where the changes seem to get lost.

Code:

---

        Dim testID As Integer = frmDGV.dgvSearch.CurrentRow.Cells(0).Value
        da_Test.UpdateCommand.Parameters("@ID").Value = testID

---

Sorry, forgot to mention that there is NO ERROR occurring when the Update command runs.

When you call Update on a data adapter, there are only three possible outcomes:

The call fails and an exception is thrown.
The call succeeds and returns zero.
The call succeeds and returns a non-zero value.

Which is it in your case? Option 1 requires you to examine the exception to see what the cause was. Option 2 means that there were no changes to save. Option 3 means that it worked exactly as intended. There is no fourth option.

Quote:

---

Originally Posted by _cerberus_

No, adding that did not solve it. Below is what I used to set the @ID parameter. I stepped through again to verify that testID was setting correctly and the DataTable was seeing the new textbox value. Everything is good until the .Update() where the changes seem to get lost.

Code:

---

        Dim testID As Integer = frmDGV.dgvSearch.CurrentRow.Cells(0).Value
        da_Test.UpdateCommand.Parameters("@ID").Value = testID

---

---

What are you doing setting the Value of a parameter of the UpdateCommand to begin with? If you're calling Update on a data adapter then all parameter values come from the DataTable and you don't set them yourself. You should follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data to learn how to use ADO.NET correctly in various scenarios.

Quote:

---

Originally Posted by jmcilhinney

When you call Update on a data adapter, there are only three possible outcomes:

The call fails and an exception is thrown.
The call succeeds and returns zero.
The call succeeds and returns a non-zero value.

Which is it in your case? Option 1 requires you to examine the exception to see what the cause was. Option 2 means that there were no changes to save. Option 3 means that it worked exactly as intended. There is no fourth option.

---

I read through your post, Retrieving-and-Saving-Data-in-Databases, and modified the code accordingly. To answer your question, the Update command returns "1" Which by what you're telling me is that it says it's doing exactly what it should be doing. However, IT IS NOT. I even stepped through the code to make sure the Data Table had the updated textbox value. It did. But when it refills the DataTable from the Fill command, the new value is not there. Although I'm a novice at programming, I am pretty sure what I have SHOULD be working.

Here is the code I used so you can see the changes I've made:

Code:

---

Imports System.ComponentModel
Imports System.Data.SqlClient

Public Class frmTEST
    Dim dt_Test As New DataTable
    Dim da_Test As New SqlDataAdapter
    Dim SQLcmd As SqlCommand

    Private Sub frmDemog_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        BuildSQL()
        Fill_Data()
        BindControls()
    End Sub

    Private Sub BindControls()
        txtLName.DataBindings.Add("Text", dt_Test, "Last_Name")
        txtFName.DataBindings.Add("Text", dt_Test, "First_Name")
    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Update_Me(True)
    End Sub
       
    Public Sub Update_Me(RefreshView As Boolean)
        Try
            da_Test.Update(dt_Test)

            If RefreshView Then Fill_Data()

        Catch SqlExceptionErr As SqlException
            MsgBox(SqlExceptionErr.Message, vbCritical, "Error")
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error")
        End Try
    End Sub

    Public Sub Fill_Data()
        dt_Test.Clear()
        da_Test.SelectCommand.Parameters("@ID").Value = frmDGV.dgvSearch.CurrentRow.Cells(0).Value

        Try
            Dim ret As Integer = da_Test.Fill(dt_Test)
            MsgBox(ret)
        Catch SqlExceptionErr As SqlException
            MsgBox(SqlExceptionErr.Message, vbCritical, "Error")
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error")
        End Try
    End Sub

    Private Sub BuildSQL()
        '** Build Selection Query
        SQLcmd = New SqlCommand(String.Join(Environment.NewLine,
            "SELECT ",
                "data_Test.[ID_Demog], ",
                "data_Test.[Last_Name], ",
                "data_Test.[First_Name] ",
            "FROM [DB].data_Test ",
            "WHERE data_Test.ID=@ID"
            ), Vars.sqlConnACL)

        SQLcmd.Parameters.Add("@ID", SqlDbType.Int)
        da_Test.SelectCommand = SQLcmd

        '** Build Update Query
        SQLcmd = New SqlCommand(String.Join(Environment.NewLine,
            "UPDATE [DB].data_Test SET ",
                "[Last_Name] = @LName,",
                "[First_Name] = @FName",
            "WHERE [ID_Demog] = @ID"
            ), Vars.sqlConnACL)

        With SQLcmd.Parameters
            .Add("@LName", SqlDbType.NVarChar, 255, "Last_Name") 'Required
            .Add("@FName", SqlDbType.NVarChar, 255, "First_Name") 'Required
            .Add("@ID", SqlDbType.Int, 4, "ID")
        End With

        da_Test.UpdateCommand = SQLcmd

        da_Test.MissingSchemaAction = MissingSchemaAction.AddWithKey
    End Sub
End Class

---

I'm not sure how that's not throwing an exception. Your SELECT statement indicates that you're creating a column named ID_Demog but then the parameter for your UPDATE statement species using a column named ID as the source column. What ID column?

Also, I would suggest that you bind your DataTable to a BindingSource and bind that to your controls, then call Validate on the form and EndEdit on the BindingSource before saving.

Also, there's no need to build your SQL like that. VB supports multiline String literals nowadays.

Quote:

---

Originally Posted by jmcilhinney

I'm not sure how that's not throwing an exception. Your SELECT statement indicates that you're creating a column named ID_Demog but then the parameter for your UPDATE statement species using a column named ID as the source column. What ID column?

Also, I would suggest that you bind your DataTable to a BindingSource and bind that to your controls, then call Validate on the form and EndEdit on the BindingSource before saving.

Also, there's no need to build your SQL like that. VB supports multiline String literals nowadays.

---

I did as you suggested - create a BindingSourse and bind the DataTable and controls to it - and called the EndEdit before the Update. And you know what? It worked!!! So that tells me that the DataTable was not ending the edit previously. I do like the idea of using BindingSources, I just haven't done it yet. What is the purpose of the Me.Validate?

Correct - I could write it as multi-string literals, but I was having some issues at one time and got tired of parsing out all the vbTAB and vbCRLF that get added into the command text. Doing it this way only adds a single vbCRLF. But I will probably go back to multi-string literals since it does look and write better.

QUESTION: In your thread "" you suggested "creating a Data Source to generate a typed DataSet and TableAdapters." Is there any advantage to this and how do I do it? I know I can create data sources in VS designer, but I'd prefer to do it via code. And why would I create a single table DataSet instead of a DataTable? Is there an advantage of some kind? I am looking to increase performance...

Thank you for your help and suggestions! I look forward to reading more of your CodeBank threads.

Quote:

---

Originally Posted by _cerberus_

I did as you suggested - create a BindingSourse and bind the DataTable and controls to it - and called the EndEdit before the Update. And you know what? It worked!!! So that tells me that the DataTable was not ending the edit previously. I do like the idea of using BindingSources, I just haven't done it yet. What is the purpose of the Me.Validate?

---

Complex data-binding in WinForms requires an IList or an IListSource, where the GetList method of the IListSource returns an IList. The DataTable class implements the IListSource interface. It's GetList method returns its DefaultView, which is a DataView. That means that each item will be a DataRowView when you bind a DataTable.

The DataRowView class implements the IEditableObject interface and that is made use of when binding. What happens is that an editing session is started when you first start editing the data for an item and the changes made are not committed until that session is ended. The session will be implicitly ended when the user navigates to another record but, if you try to save changes before that happens, you won't be saving those particular changes unless you explicitly end the editing session. That's what the Validate and EndEdit calls do.

I used to be under the impression that calling EndEdit was enough but I have since discovered that calling Validate is required too. The specific purpose of Validate is to validate the last control that had focus, i.e. raise the Validating and Validated events. I'm not 100% sure what that has to do with committing pending changes that EndEdit does cover but there is definitely something. It may be that last field edited is in an even more pending state than the last record edited but I haven't looked into it specifically.

Quote:

---

Originally Posted by _cerberus_

Correct - I could write it as multi-string literals, but I was having some issues at one time and got tired of parsing out all the vbTAB and vbCRLF that get added into the command text. Doing it this way only adds a single vbCRLF. But I will probably go back to multi-string literals since it does look and write better.

---

There's no need to parse out anything. The database will simply ignore additional whitespace anyway and no Tab characters will be added unless you add them, so don't add them if you don't want them. If you're concerned about alignment in the code editor then you have two options:

1. Line everything up with your code and rely on the database ignoring the whitespace, e.g.

Code:

---

        Dim sql = "SELECT *
                  FROM MyTable
                  WHERE Id = @Id"

---

2. Line everything up against the left margin, e.g.

Code:

---

        Dim sql = "
SELECT *
FROM MyTable
WHERE Id = @Id"

---

or:

Code:

---

        Dim sql = "SELECT *
FROM MyTable
WHERE Id = @Id"

---

I use option 2 and it seems to me that so do most other experienced developers. Sometimes it matters because you may not want the extra whitespace in the result but, for SQL, it's of no consequence either way. Maybe you're talking about Tab characters used in option 1 to force the alignment but, as I said, it's of no consequence in SQL and, in other cases, just use option 2.

Quote:

---

Originally Posted by _cerberus_

QUESTION: In your thread "" you suggested "creating a Data Source to generate a typed DataSet and TableAdapters." Is there any advantage to this and how do I do it? I know I can create data sources in VS designer, but I'd prefer to do it via code. And why would I create a single table DataSet instead of a DataTable? Is there an advantage of some kind? I am looking to increase performance...

---

If you want the best performance then stick to using ADO.NET directly, because any other option will use that under the hood anyway and add another layer on top. Personally, I haven't used typed DataSets for a long time and use Entity Framework almost exclusively nowadays. Again, it uses ADO.NET under the hood so it's not done for the sake of performance. Unless you can show that there's a significant performance difference, you should be concerned about writing and maintaining your code. That's where typed DataSets and EF or other ORMs help.

In the case of a typed DataSet, the wizard will generate a number of classes specific to your database schema and most of those classes either inherit or wrap the standard ADO.NET classes. Specifically, you get a derived DataSet class, derived DataTable classes and derived DataRow classes. You also get table adapter classes that each wrap a data adapter. The DataSet class has a property for each DataTable, which means that you don't have to use a magic string to identify a specific DataTable. The DataRow classes each have a property for each column, meaning that you don't have to use a magic string to identify a specific field. Those two facts mean that you get full Intellisense and compiler support, so you won't have to wait until run time to determine whether you have misspelled a table or column name.

The two main complaints I hear about typed DataSets from experienced developers aren't really justified and seem to arise because those people haven't actually used them. One complaint is that you shouldn't closely couple your UI and your data access, which is based on the fact that you can add DataSets and table adapters to a WinForms form in the designer. The thing is, while you CAN do that, you don't HAVE TO do that. Creating a typed DataSet and using it are two different things. You can create a Data Source, which includes a typed DataSet, using the Data Sources window. Once that's done, you CAN drag a table or column to a form and have controls and bindings created automatically. There's no requirement to do that though. You can create a completely independent data access layer if you want. It can even be in a separate project to your UI. You can use a typed DataSet in code in exactly the same way as you use an untyped DataSet. The code is just simpler.

The other objection is that a typed DataSet is a black box that hides a lot from you and limits customisation. That's true to an extent but not to the extent that is usually implied. You can open the DataSet in the designer and see each DataTable and table adapter. You can modify a lot there, including names and data types, plus you can add queries to the table adapters, which means that you can use the same table adapter to retrieve all data from a table or only some based on a filter. Your additional queries can even include joins, as long as the result set has the same schema as the original table. If you need a result set with a different schema, you simply add another DataTable, which will come with its own table adapter, and you get to specify the schema and the query. You have to know what you're going to need at design time but how often is that not the case anyway? In those rare cases where you do need to execute an ad hoc query, you can always mix in a little bit of raw ADO.NET for that specifically.