Results 1 to 8 of 8

Thread: Update of SQL Database for Data Table

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    314

    Update of SQL Database for Data Table

    I currently have the code below that when executed, reads the contents fo a SQL database and displays it in a data table. This can be the edited.
    Once edited, the save button is clicked, with the intention that the change is written back to the SQL database.

    Code:
    Public Class Test
        Dim SQLConnection As New SqlCeConnection(My.Settings.CurrentDatasource)
        Public dslog As New DataSet()
        Dim da As New SqlCeDataAdapter()
        Dim BindingSource1 As New BindingSource()
    
    Private Sub EditOutput_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim SQLCommand As String = "SELECT * FROM ControlRecords"
    
            Me.WindowState = FormWindowState.Maximized
            Try
                da = New SqlCeDataAdapter(SQLCommand, SQLConnection)
                da.Fill(dslog, "Log")
    
            Catch sqlExc As Exception
                MessageBox.Show(sqlExc.ToString, "SQL Exception Error!",
                    MessageBoxButtons.OK, MessageBoxIcon.Error)
                Exit Sub
            End Try
    
            BindingSource1.datasource = dslog.Tables("Log")
            Me.DataGridView1.DataSource = BindingSource1
    
            With Me.DataGridView1
                .Visible = True
                .AutoGenerateColumns = True
                .AlternatingRowsDefaultCellStyle.BackColor = Color.Lavender
                .BackColor = Color.WhiteSmoke
                .ForeColor = Color.MidnightBlue
                '.CellBorderStyle = DataGridViewCellBorderStyle.None
                .ColumnHeadersDefaultCellStyle.Font = New Font("Tahoma", 8.0!, FontStyle.Bold)
                .ColumnHeadersDefaultCellStyle.BackColor = Color.MidnightBlue
                .ColumnHeadersDefaultCellStyle.ForeColor = Color.WhiteSmoke
                .DefaultCellStyle.BackColor = Color.WhiteSmoke
                .ReadOnly = False
                .RowHeadersVisible = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToOrderColumns = True
                .AllowUserToResizeColumns = True
            End With
        End Sub
    the save button executes the following

    Code:
    Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
    
            da.Update(dslog, "Log")
    end Sub
    However when save is clicked an error is geenrated :-
    System.InvalidOperationException: 'Update requires a valid UpdateCommand when passed DataRow Collection with modified rows.'

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,643

    Re: Update of SQL Database for Data Table

    A DataAdapter doesn't create Add/Update/Delete commands. You need to either manually create the objects or use a CommandBuilder, if your table has a primary key.

    Here is a simple example,
    Code:
    Imports System.Data.SqlClient
    
    Public Class DataAdapterDemo
        'I always store my connectionstrings in the project Setting
        Private con As New SqlConnection(My.Settings.BooksDBConnectionString)
        Private da As New SqlDataAdapter("Select * From Books ORDER BY BookName", con)
        Private dt As New DataTable
    
        'I prefer using a BindingSource with the DataGridView because it has many built in function for working/manipulating the data
        Private bs As New BindingSource
    
        'If your only working with one table then you can use a CommandBuilder to create the SQL Add/Update/Delete commands
        Private cmdBuilder As New SqlCommandBuilder(da)
    
        Private Sub DataAdapterDemo_Load(sender As Object, e As EventArgs) Handles Me.Load
    
            Try
                con.Open()
    
                da.Fill(dt)
                bs.DataSource = dt
                Me.DataGridView1.DataSource = bs
    
                'Bind a Textbox to the bs Bindingsource
                Me.BookIdTextBox.DataBindings.Add("Text", bs, "BookId")
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Sub
    
        Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
            Try
                bs.EndEdit()
                da.Update(dt)
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Sub
    End Class
    Couple other things. If your usind a Bindingsource then you need to call Endedit method before calling the DataAdapter Update method.

    "SQL database and displays it in a data table" No, the datatable is the datasource for the bindingsource, the Datagridview is what displays the data.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    314

    Re: Update of SQL Database for Data Table

    That's really helpful. One thing that I don't understand is what adding the binding source should do.

    What it appears to do is make the data grid look more like a MS Access table, when additional records can be added.

    And why is it defined as a text box ? As whilst I added a text box to the form, the effect was on the data grid not the text box.

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,217

    Re: Update of SQL Database for Data Table

    Think of the BindingSource as a conductor of sorts... with the Grid and Textbox and other data elements members of the orchestra.... individually, they do their own thing... he conductor keeps them all working togtehre. The BS does the samehting. It makes sure they are all drawing all from the same data together. And no, it doesn't get defined as a textbox... a textbox was created and BOUND to it... the same way the grid was also bound to it. It was as an example to show that by changing the data in the textbox, it also updated the grid at the same time, because they are both bound to the same BindingSource. When you change the textbox, it activates an event in the BS that sends out a message to all bound object of that BS that says "Hey, I have new data, you need to refresh yourself and update it."
    Otherwise, it's something you have to coordinate yourself.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,643

    Re: Update of SQL Database for Data Table

    The Textbox is just there to show how to bind a textbox to a bindingsource. If you just want to just use a dgv then remove that code.

    As far as I know, using a bindingsource has no effect on the "look" of the dgv.

    Easy to test, change,

    Code:
    bs.DataSource = dt
                Me.DataGridView1.DataSource = bs
    
    to
    
    'bs.DataSource = dt
                Me.DataGridView1.DataSource = dt

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    314

    Re: Update of SQL Database for Data Table

    Looks like the way i was coding it was having undesired effect on the datagrid view.

    In the example above, is there any advantage of using a binding source (as I can't see one)

  7. #7
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,643

    Re: Update of SQL Database for Data Table

    In the example above, is there any advantage of using a binding source (as I can't see one)
    That example is as simple as it gets, it doesn't do anything but load a datatable into a dgv. If that all you want to do then a bindingsource doesn't add anything. If your going to want to do any sorting, filtering, validating..... then I'd use a bindingsource.

    https://docs.microsoft.com/en-us/dot...B)%26rd%3Dtrue

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    106,310

    Re: Update of SQL Database for Data Table

    The point of a BindingSource is to provide one point of contact for the bound data. It aggregates functionality that was spread across a number of places previously, primarily in the DefaultView of the bound DataTable and the CurrencyManager associated with the binding. If you need to access the bound data in code then I would suggest using a BindingSource and accessing the data through it. If you don't need to access the data at all in code then, as suggested, it doesn't really add anything. Personally, I always use one regardless, for the sake of consistency. Doing so doesn't hurt at all and it is there if you ever do need to access the data in code for any reason later on. BindingSources also make parent/child binding easy, e.g. if you want to select a parent record in one control and have the child data in another control filtered to only related records automatically.

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