Looking for sample app the uses data binding with a grid control and textboxes
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
Re: Looking for sample app the uses data binding with a grid control and textboxes
Show us your SELECT statement and your UPDATE statement, i.e. the actual SQL code.
Re: Looking for sample app the uses data binding with a grid control and textboxes
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:
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