S
SyntaxTerra
Guest
Hey guys wondering if anyone can help with this, weird things are happening when updating a data table from my datagridview and i cant figure it out..
I have a data grid view called petdatagridview when i make changes to certain rows in the grid view and hit the update button everything works fine, i can change multiple cell values of different rows without problems, but this doesn't work with all rows for some reason. certain rows will throw a concurrency violation (but not all) and if i hit the refresh button on the form (reloads table into the datagridview) then try and alter a cell and hit update it tells me that there is no new changes in the datagridview, im really stumped..
Heres my code:
under the class heading......
Public Class managePetInfo
Dim MyCommand As New MySqlCommand
Dim Myadapter As New MySqlDataAdapter
Dim MyBuilder As MySqlCommandBuilder
Dim conn As New MySqlConnection(connectionstring)
Dim MyDataTable As New DataTable
Dim MyDataset As DataSet = New DataSet
Dim ds As DataSet = New DataSet
In the form load....
Public Sub managePetInfo_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'Load pet data into petdatagridview
Try
'Load pet data into petdatagridview
petDataGridView.DataSource = DataAccessLayer.LoadPetData()
Catch ex As Exception
MsgBox(ex.Message)
End Try
'set command text, connection, fill data adapter, etc
MyCommand.CommandText = "select * from pet"
MyCommand.Connection = conn
Myadapter.SelectCommand = MyCommand
MyBuilder = New MySqlCommandBuilder(Myadapter)
Myadapter.Fill(MyDataset, "pet")
MyDataTable = MyDataset.Tables("pet")
petDataGridView.DataSource = MyDataTable
End Sub
In the update button event.....
Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
If MyDataset.HasChanges() = False Then
MessageBox.Show("The table contains no changes to save.")
Else
Dim rowsAffected As Integer = Myadapter.Update(MyDataTable)
If rowsAffected = 0 Then
MessageBox.Show("No rows were affected by the save operation.")
Else
MessageBox.Show(rowsAffected & " rows were affected by the save operation.")
End If
End If
Validate()
Myadapter.Update(MyDataset.Tables("pet"))
MyDataset.AcceptChanges()
End Sub
Ive had a lot of issues trying to get this to work, if anyone can see any issues in my code or can suggest anything, anything at all i would greatly appreciate it. Or if anyone has there own version of code that updates an sql table from data grid view changes that they can show me. It would be immensely helpful.
Oh and not sure if it is relevant but here is the function used to refresh the data grid view, which is the same function that loads data into the data grid view:
Public Shared Function LoadPetData()
Dim conn As New MySqlConnection(connectionstring)
Dim table As New DataTable()
Dim adapter As New MySqlDataAdapter("SELECT * FROM pet", conn)
Try
managePetInfo.petDataGridView.DataSource = table
adapter.Fill(table)
Catch ex As Exception
MsgBox(ex.Message)
End Try
Return table
End Function
Please help me!
Continue reading...
I have a data grid view called petdatagridview when i make changes to certain rows in the grid view and hit the update button everything works fine, i can change multiple cell values of different rows without problems, but this doesn't work with all rows for some reason. certain rows will throw a concurrency violation (but not all) and if i hit the refresh button on the form (reloads table into the datagridview) then try and alter a cell and hit update it tells me that there is no new changes in the datagridview, im really stumped..
Heres my code:
under the class heading......
Public Class managePetInfo
Dim MyCommand As New MySqlCommand
Dim Myadapter As New MySqlDataAdapter
Dim MyBuilder As MySqlCommandBuilder
Dim conn As New MySqlConnection(connectionstring)
Dim MyDataTable As New DataTable
Dim MyDataset As DataSet = New DataSet
Dim ds As DataSet = New DataSet
In the form load....
Public Sub managePetInfo_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'Load pet data into petdatagridview
Try
'Load pet data into petdatagridview
petDataGridView.DataSource = DataAccessLayer.LoadPetData()
Catch ex As Exception
MsgBox(ex.Message)
End Try
'set command text, connection, fill data adapter, etc
MyCommand.CommandText = "select * from pet"
MyCommand.Connection = conn
Myadapter.SelectCommand = MyCommand
MyBuilder = New MySqlCommandBuilder(Myadapter)
Myadapter.Fill(MyDataset, "pet")
MyDataTable = MyDataset.Tables("pet")
petDataGridView.DataSource = MyDataTable
End Sub
In the update button event.....
Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
If MyDataset.HasChanges() = False Then
MessageBox.Show("The table contains no changes to save.")
Else
Dim rowsAffected As Integer = Myadapter.Update(MyDataTable)
If rowsAffected = 0 Then
MessageBox.Show("No rows were affected by the save operation.")
Else
MessageBox.Show(rowsAffected & " rows were affected by the save operation.")
End If
End If
Validate()
Myadapter.Update(MyDataset.Tables("pet"))
MyDataset.AcceptChanges()
End Sub
Ive had a lot of issues trying to get this to work, if anyone can see any issues in my code or can suggest anything, anything at all i would greatly appreciate it. Or if anyone has there own version of code that updates an sql table from data grid view changes that they can show me. It would be immensely helpful.
Oh and not sure if it is relevant but here is the function used to refresh the data grid view, which is the same function that loads data into the data grid view:
Public Shared Function LoadPetData()
Dim conn As New MySqlConnection(connectionstring)
Dim table As New DataTable()
Dim adapter As New MySqlDataAdapter("SELECT * FROM pet", conn)
Try
managePetInfo.petDataGridView.DataSource = table
adapter.Fill(table)
Catch ex As Exception
MsgBox(ex.Message)
End Try
Return table
End Function
Please help me!
Continue reading...