Results 1 to 10 of 10

Thread: [RESOLVED] DataTable not updating Database with DataAdapter

  1. #1

    Thread Starter
    Member _cerberus_'s Avatar
    Join Date
    Jun 2019
    Location
    Minnesota, USA
    Posts
    37

    Resolved [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
    Last edited by _cerberus_; Jul 11th, 2019 at 07:07 PM.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: DataTable not updating Database with DataAdapter

    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?

  3. #3

    Thread Starter
    Member _cerberus_'s Avatar
    Join Date
    Jun 2019
    Location
    Minnesota, USA
    Posts
    37

    Re: DataTable not updating Database with DataAdapter

    Quote Originally Posted by si_the_geek View Post
    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

  4. #4

    Thread Starter
    Member _cerberus_'s Avatar
    Join Date
    Jun 2019
    Location
    Minnesota, USA
    Posts
    37

    Re: DataTable not updating Database with DataAdapter

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

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: DataTable not updating Database with DataAdapter

    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.

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: DataTable not updating Database with DataAdapter

    Quote Originally Posted by _cerberus_ View Post
    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.

  7. #7

    Thread Starter
    Member _cerberus_'s Avatar
    Join Date
    Jun 2019
    Location
    Minnesota, USA
    Posts
    37

    Re: DataTable not updating Database with DataAdapter

    Quote Originally Posted by jmcilhinney View Post
    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

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: DataTable not updating Database with DataAdapter

    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.

  9. #9

    Thread Starter
    Member _cerberus_'s Avatar
    Join Date
    Jun 2019
    Location
    Minnesota, USA
    Posts
    37

    Re: DataTable not updating Database with DataAdapter

    Quote Originally Posted by jmcilhinney View Post
    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.

  10. #10
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: DataTable not updating Database with DataAdapter

    Quote Originally Posted by _cerberus_ View Post
    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_ View Post
    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_ View Post
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width