Hello vb.net forum,
I've tried posting this on other forums but have gotten nowhere. I'm having a difficult time getting my datagridview to update my access database. The information fills the datagridview correctly but my changes are not (or i'm not) saving to the datatable when I update.
I've searched trying to find what I've done wrong and from this thread: http://www.vbforums.com/showthread.p...a-in-Databases post #1, jmcilhinney states " 'That will most likely involve data-binding but that is not a data access issue." under the 4th code segment. I believe this is where I'm hanging myself up. I've also tried this thread http://www.vbforums.com/showthread.p...a-datagridview which shows the table contains no changes. Using this code, I get the message "The table contains no changes to save.". For the life of me, I can not get this to edit the database. Any help would be greatly appreciated.
I'm using VS2012 to connect to an Access 2007 DB with a Microsoft.Ace.OLEDB.12.0 provider. .Net Framework 4.0
Here is my Current Code:
I've tried posting this on other forums but have gotten nowhere. I'm having a difficult time getting my datagridview to update my access database. The information fills the datagridview correctly but my changes are not (or i'm not) saving to the datatable when I update.
I've searched trying to find what I've done wrong and from this thread: http://www.vbforums.com/showthread.p...a-in-Databases post #1, jmcilhinney states " 'That will most likely involve data-binding but that is not a data access issue." under the 4th code segment. I believe this is where I'm hanging myself up. I've also tried this thread http://www.vbforums.com/showthread.p...a-datagridview which shows the table contains no changes. Using this code, I get the message "The table contains no changes to save.". For the life of me, I can not get this to edit the database. Any help would be greatly appreciated.
I'm using VS2012 to connect to an Access 2007 DB with a Microsoft.Ace.OLEDB.12.0 provider. .Net Framework 4.0
Here is my Current Code:
VB.NET:
Public Class frm_History
'Private acsbind As New BindingSource
Private DimID As String
Private acsDataTable As New DataTable
Private acsda As New OleDb.OleDbDataAdapter
Public Sub Dim_Hist(DimID As String)
Dim acscmd, acscmdUp, acscmdDel, acscmdIns As New OleDb.OleDbCommand
dgv_Dim.DataSource = vbNull
strsql = "SELECT ID, TimeStamp, Press, Cycle, Inspector, Dimension FROM Dimension WHERE DimID = @DimID ORDER BY TimeStamp Desc"
strDel = "DELETE FROM Dimension WHERE ID = @ID"
strIns = "INSERT INTO Dimemsion (TimeStamp, Press, Cycle, Inspector, Dimension) VALUES (@TimeStamp, @Press, @Cycle, @Inspector, @Dimension)"
strUp = "UPDATE Dimension SET TimeStamp = @TimeStamp, Press = @Press, Cycle = @Cycle, Inspector = @Inspector, Dimension = @Dimension WHERE ID = @ID"
'Delete Command
acscmdDel.Connection = acsconn
acscmdDel.CommandText = strDel
acscmdDel.Parameters.Add("@ID", OleDb.OleDbType.Integer, 4, "ID")
acsda.DeleteCommand = acscmdDel
'Insert Command
acscmdIns.Connection = acsconn
acscmdIns.CommandText = strIns
acscmdIns.Parameters.AddWithValue("@TimeStamp", "TimeStamp")
acscmdIns.Parameters.Add("@Press", OleDb.OleDbType.Integer, 2, "Press")
acscmdIns.Parameters.Add("@Cycle", OleDb.OleDbType.Integer, 2, "Cycle")
acscmdIns.Parameters.Add("@Inspector", OleDb.OleDbType.Char, 100, "Inspectors")
acscmdIns.Parameters.Add("@Dimension", OleDb.OleDbType.Decimal, 4, "Dimension")
acsda.InsertCommand = acscmdIns
'Update Command
acscmdUp.Connection = acsconn
acscmdUp.CommandText = strUp
acscmdUp.Parameters.AddWithValue("@TimeStamp", "TimeStamp")
acscmdUp.Parameters.Add("@Press", OleDb.OleDbType.Integer, 2, "Press")
acscmdUp.Parameters.Add("@Cycle", OleDb.OleDbType.Integer, 2, "Cycle")
acscmdUp.Parameters.Add("@Inspector", OleDb.OleDbType.Char, 100, "Uppectors")
acscmdUp.Parameters.Add("@Dimension", OleDb.OleDbType.Decimal, 4, "Dimension")
acscmdUp.Parameters.Add("@ID", OleDb.OleDbType.Integer, 4, "ID")
acsda.UpdateCommand = acscmdUp
'Select Command
acscmd.Connection = acsconn
acscmd.CommandText = strsql
acscmd.Parameters.AddWithValue("@DimID", DimID)
acsda.SelectCommand = acscmd
acsda.MissingSchemaAction = MissingSchemaAction.AddWithKey
acsDataTable.Clear()
acsda.Fill(acsDataTable)
dgv_Dim.DataSource = acsDataTable
dgv_Dim.RowHeadersVisible = False
dgv_Dim.Columns(1).Width = 130
dgv_Dim.Columns(2).Width = 40
dgv_Dim.Columns(3).Width = 50
dgv_Dim.Columns(4).Width = 100
dgv_Dim.Columns(5).Width = 60
dgv_Dim.Columns(0).Visible = False
dgv_Dim.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
dgv_Dim.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
dgv_Dim.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
dgv_Dim.Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
dgv_Dim.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
End Sub
'Update Edit
Private Sub dgv_Dim_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles dgv_Dim.CellEndEdit
'Not updating acsdatatable?
If acsDataTable.GetChanges() Is Nothing Then
MessageBox.Show("The table contains no changes to save.")
Else
Dim rowsAffected As Integer = acsda.Update(acsDataTable)
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
Me.Dim_Hist(DimID)
End Sub