Looking for sample app the uses data binding with a grid control and textboxes

[emadeva]

Hi Everyone,

I'm looking to see if anyone has a sample app or has a link to such an app that uses data binding with a grid control and textboxes and is able to insert, change and delete from a datatable which gets updated to a sql server database.

I have done an app that has the grid control and the textboxes already and is able to change and delete from the datatable and that action is reflected in the grid control because it has the datatable as the binding source.

The problem I'm having is when the user tries to insert into the datatable I get a concurrency violation error when using the .Update statement to save the changes to the database. This is happening because I don't know exactly how to get those inserts synchronised correcly.

I'm sure I'm also doing the processing wrong but here is what I'm doing.
When the user clicks an insert button I insert a new row into the datatable. The user now has the ability to enter text into the textboxes. When the user is finished doing that the user can click the save button which issues a .Update statement. That's where the error is displayed.

If you have the type of sample app I'm looking for I can see how to insert into the datatable and do the .Udate correctly.

Thanks in advance.

Truly,
Emad

[jmcilhinney]

Show us your SELECT statement and your UPDATE statement, i.e. the actual SQL code.

[emadeva]

Hi,

Thanks for the reply.

Since the form is not too big at the moment, I hope it's ok to show the code for the entire form.

Thanks in advance for the help.

Here's the code:

Imports System.Data.SqlClient

Public Class FormBooksInLibrary

    Private intID As Integer = Nothing ' Used as a primary key.

    ' Declare form objects that will be called from this form.
    '---------------------------------------------------------

    ' These are for the sql Select statements.
    '-----------------------------------------
    Private strSelectStatement As String = _
            "Select BookTitle, ID, TotalBooks, ISBN " & _
              "From Books " & _
              "Order By 1 "

    ' Database objects.
    '------------------
    Private myConnection As New SqlConnection(FormMain.strDatabaseConnection)
    Private myDataAdapter As New SqlDataAdapter(strSelectStatement, myConnection)
    Private myDataTable As New DataTable

    ' Binding sources.
    '-----------------
    Dim myGridBindingSource As BindingSource = New BindingSource

    Private Sub FormBooksInLibrary_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        LightGridBooks.Focus()

        Cursor = Cursors.WaitCursor

        InitialiseDataAdapter()

        LoadBooksIntoDataGrid()

        Cursor = Cursors.Default
    End Sub

    Private Sub InitialiseDataAdapter()

        ' Initialise commands.
        '---------------------
        Dim commandUpdate As New SqlCommand("UPDATE Books " & _
                                        "SET BookTitle = @BookTitle, " & _
                                            "TotalBooks = @TotalBooks, " & _
                                            "ISBN = @ISBN " & _
                                      "WHERE ID = @ID", myConnection)

        Dim commandDelete As New SqlCommand("DELETE FROM Books WHERE ID = @ID", myConnection)

        Dim commandInsert As New SqlCommand("INSERT INTO Books " & _
                                               "(BookTitle, TotalBooks, ISBN) " & _
                                            "VALUES (@BookTitle, @TotalBooks, @ISBN)", myConnection)

        ' Initialise parameters for placeholders.
        '----------------------------------------
        commandUpdate.Parameters.Add("@BookTitle", SqlDbType.VarChar, 30, "BookTitle")
        commandUpdate.Parameters.Add("@TotalBooks", SqlDbType.Int, 4, "TotalBooks")
        commandUpdate.Parameters.Add("@ISBN", SqlDbType.VarChar, 30, "ISBN")
        commandUpdate.Parameters.Add("@ID", SqlDbType.Int, 10, "ID")

        commandDelete.Parameters.Add("@ID", SqlDbType.Int, 10, "ID")

        commandInsert.Parameters.Add("@BookTitle", SqlDbType.VarChar, 30, "BookTitle")
        commandInsert.Parameters.Add("@TotalBooks", SqlDbType.Int, 4, "TotalBooks")
        commandInsert.Parameters.Add("@ISBN", SqlDbType.VarChar, 30, "ISBN")

        ' Initialise DataAdapter commands.
        '---------------------------------
        myDataAdapter.UpdateCommand = commandUpdate
        myDataAdapter.DeleteCommand = commandDelete
        myDataAdapter.InsertCommand = commandInsert

        Me.myDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

    End Sub

    Private Sub LoadBooksIntoDataGrid()

        ' Start out clean.
        '-----------------
        LightGridBooks.Rows.Clear()

        ' Set up the exception catch.
        '----------------------------
        Try
            myDataAdapter.Fill(myDataTable)
            '        If myDataAdapter.Fill(myDataTable) = 0 Then
            'myDataTable.Reset()
            'Else

            ' Set up binding sources.
            '------------------------
            myGridBindingSource.DataSource = myDataTable

            ' Display the data in the data grid.
            '-----------------------------------
            With LightGridBooks
                .ClearSort(0)
                .Columns(0).Filter = ""
                .DataSource = myGridBindingSource
            End With

            ' Bind the TextBoxes to the DataTable.
            '-------------------------------------
            editBoxTitle.DataBindings.Add("Text", myGridBindingSource, "BookTitle")
            editBoxTotalBooks.DataBindings.Add("Text", myGridBindingSource, "TotalBooks")
            editBoxIsbn.DataBindings.Add("Text", myGridBindingSource, "ISBN")

            ' End If

        Catch exSqlErrors As SqlException

            MessageBox.Show("Sorry, I can't display your data " & _
                            "because of this SQL error:" & vbCrLf & vbCrLf & exSqlErrors.Message, _
                            "Other Error")

        Catch exErrors As Exception
            MessageBox.Show("Sorry, I can't display your data " & _
                            "because of this error:" & vbCrLf & vbCrLf & exErrors.Message, _
                            "Other Error")
        End Try
    End Sub

    Private Sub UpdateDataSource()

        Dim intTotalChanges As Integer = 0

        ' Update the connection object.
        '------------------------------
        myConnection.ConnectionString = FormMain.strDatabaseConnection

        Try
            Validate()
            myGridBindingSource.EndEdit()

            intTotalChanges = myDataAdapter.Update(myDataTable)

            LightGridBooks.Refresh()

            Me.Text = "Books in Library *** Total changes made: " & intTotalChanges

        Catch exSqlErrors As SqlException

            MessageBox.Show("Sorry, I can't save your data " & _
                            "because of this SQL error:" & vbCrLf & vbCrLf & exSqlErrors.Message, _
                            "Other Error")

        Catch exErrors As Exception
            MessageBox.Show("Sorry, I can't save your data " & _
                            "because of this error:" & vbCrLf & vbCrLf & exErrors.Message, _
                            "Other Error")
        End Try

    End Sub

    Private Sub LightGridBooks_RowsChange(ByVal source As System.Object, ByVal e As PureComponents.EntrySet.Lists.LightGrid.ChangeEventArgs) Handles LightGridBooks.RowsChange

        With LightGridBooks

            If .SelectedIndex <> -1 Then ' The user clicked in the grid.

                ' Syncronize the bound details with the DataGrid.
                '------------------------------------------------
                myGridBindingSource.Position = .SelectedIndex

                UpdateDataSource()

                ' Allow these.
                '-------------
                RibbonButtonDelete.Enabled = True
            End If

        End With

    End Sub

    Private Sub RibbonButtonInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RibbonButtonInsert.Click

        ' This .Select will stop the concurancy error.
        '---------------------------------------------
        LightGridBooks.Select()

        Dim myDataRow As DataRow

        ' Insert a new row into the DataTable.
        '-------------------------------------
        myDataRow = myDataTable.NewRow
        myDataTable.Rows.Add(myDataRow)

        ' Point the Grid to the new row.
        '-------------------------------
        myGridBindingSource.Position = myDataTable.Rows.Count - 1

        ' Blank these out because we are inserting a new row.
        '----------------------------------------------------
        editBoxTitle.Text = ""
        editBoxTotalBooks.Text = ""
        editBoxIsbn.Text = ""

    End Sub

    Private Sub RibbonButtonSaveChanges_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RibbonButtonSaveChanges.Click

        UpdateDataSource()
    End Sub

    Private Sub RibbonButtonDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RibbonButtonDelete.Click

        '  myDataTable.Rows.RemoveAt(LightGridBooks.SelectedIndex)
        DirectCast(myGridBindingSource.Current, DataRowView).Row.Delete()

        UpdateDataSource()

    End Sub

    Private Sub LightGridBooks_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LightGridBooks.Leave

        ' Don't allow these.
        '-------------------
        RibbonButtonDelete.Enabled = False
    End Sub
End Class

Truly,
Emad