Updating DataTable Problem

JeffU

Member
Joined
May 2, 2013
Messages
6
Programming Experience
1-3
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:
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
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,481
Location
Sydney, Australia
Programming Experience
10+
The CellEndEdit event is completely not the place that you should be saving data. At the very least you should be handling the CellValueChanged event. Even then, changes made in one cell are not going to be committed to the underlying DataTable. That won't happen until the user navigates to a different row. If you want to force it to happen, which you should always do before saving anyway, then bind through a BindingSource and call EndEdit on that BindingSource first.
 

JeffU

Member
Joined
May 2, 2013
Messages
6
Programming Experience
1-3
I got the same response from another forum. I switched the celleditend to an update button which then showed the error I was having. I'm now getting data type mismatch. I'm not too sure how to set the data type when the options don't match the data types in access.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,481
Location
Sydney, Australia
Programming Experience
10+
Firstly, what's up with this:
VB.NET:
        '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, "[B]Inspector[U]s[/U][/B]")
        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, "[B][U]Up[/U]pector[U]s[/U][/B]")
        acscmdUp.Parameters.Add("@Dimension", OleDb.OleDbType.Decimal, 4, "Dimension")
        acscmdUp.Parameters.Add("@ID", OleDb.OleDbType.Integer, 4, "ID")
        acsda.UpdateCommand = acscmdUp
Do columns with those highlighted names even exist?

As for the data type mismatch, I'm guessing that it's here:
VB.NET:
        'Insert Command
        acscmdIns.Connection = acsconn
        acscmdIns.CommandText = strIns
        [B]acscmdIns.Parameters.AddWithValue("@TimeStamp", [U]"TimeStamp"[/U])[/B]
        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
        [B]acscmdUp.Parameters.AddWithValue("@TimeStamp", [U]"TimeStamp"[/U])[/B]
        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
What's the data type of that TimeStamp column? I'm guessing that it's not a text column so, if that's the case, why are you assigning a text value to the parameter? Surely that is supposed to be the current date/time, not the String "TimeStamp".
 

JeffU

Member
Joined
May 2, 2013
Messages
6
Programming Experience
1-3
I've fixed the spelling errors but I don't fully understand how to trouble shoot the mismatch. My access database type is Date/time for that column in the format mm/dd/yyyy hh:mm:ss AM.
 

JeffU

Member
Joined
May 2, 2013
Messages
6
Programming Experience
1-3
I thought addwithvalue let me do a float type variable but that was when I was using celleditend which was hiding that error. I've followed code banks and tutorials but that has only gotten me so far.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,481
Location
Sydney, Australia
Programming Experience
10+
The point of AddWithValue is to allow you to add a parameter and set its Value property easily in one call. It does exactly what the name says: it adds a parameter with a value. You seem to be under the impression that the second argument is the name of a DataColumn to get the value from, as the fourth argument is for Add. That just shows that you haven't actually read the documentation. What you're doing is trying to save the literal text "TimeStamp" into the TimeStamp column, which obviously doesn't make snese. You should be using Add for that parameter just like the others and specifying the appropriate OleDbType value.
 

JeffU

Member
Joined
May 2, 2013
Messages
6
Programming Experience
1-3
Got it! Well sort of. I don't understand how to set the type for the timestamp so that it matches access but I really only need to edit the dimension cell to I removed the the timestamp from the insert/update commands. Thank you for helping me with the spelling errors I wasn't seeing and celledit end that I was misusing.
 

JeffU

Member
Joined
May 2, 2013
Messages
6
Programming Experience
1-3
Yea, I found a msdn page detailing the options. I couldn't get the type to work correctly since the oledbtype DBTimeStamp format was yyyymmddhhmmss when the access database is set to date/time which has a format mm-dd-yyyy hh:mm:ss. The timestamp is just for the dimension entry time, which doesn't need to be edited. Omitting that parameter from the command and sql statement for both update and insert fixed my problem by avoiding it.
 
Top Bottom