Updating with DataGridView Problems - VS2012 to Access 2007 DB

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 2968 Views - Last Post: 03 May 2013 - 03:57 PM Rate Topic: -----

#1 JeffU  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 02-May 13

Updating with DataGridView Problems - VS2012 to Access 2007 DB

Posted 02 May 2013 - 08:52 AM

Hello dream.in.code 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.....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.....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:
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



Is This A Good Question/Topic? 0
  • +

Replies To: Updating with DataGridView Problems - VS2012 to Access 2007 DB

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9059
  • View blog
  • Posts: 34,017
  • Joined: 12-June 08

Re: Updating with DataGridView Problems - VS2012 to Access 2007 DB

Posted 02 May 2013 - 09:08 AM

First off - where oh where are you try/catches around your SQL statements? You should *ALWAYS* have try catches around them to catch failures, bad connections, etc and not bomb ugly.

Second - you named a column 'TimeStamp'? I believe that is a reserved word and if so you need to use brackets [] around it.

Third - is your connection object ever opened?

Fourth:
acscmdIns.Parameters.AddWithValue("@TimeStamp", "TimeStamp")

You want the value of '@Timestamp' to be the string "Timestamp"?

Fifth - naming convention - why is your method's name "Dim_Hist"? That seems super strange.
Was This Post Helpful? 0
  • +
  • -

#3 JeffU  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 02-May 13

Re: Updating with DataGridView Problems - VS2012 to Access 2007 DB

Posted 02 May 2013 - 09:23 AM

1) I'm not that experienced with vb.net so I haven't established that habit. I thought the approach of adding parameters method was correct. Also, this is only a small part of a larger program that functions prefectly. I'm able to select/update information in other ways, but can get the datagridwiew to work.

2) I haven't ran into a single problem with this before. It save correctly to the database with the timestamp. Like I said before, the other parts of my program functions correctly.

3) ...omg that might be it.

4) Yes, its the name of the Datagridview column. This information displays correctly.

5) Dim_Hist is for Dimension History. There are a lot of variable and my habit is to use an underscore between types.
Was This Post Helpful? 0
  • +
  • -

#4 JeffU  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 02-May 13

Re: Updating with DataGridView Problems - VS2012 to Access 2007 DB

Posted 02 May 2013 - 09:31 AM

View PostJeffU, on 02 May 2013 - 09:23 AM, said:

3) ...omg that might be it.


No that didn't work. Also, I'm pretty sure I don't need to open the connection since i'm not using a query, but I don't know exactly.
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9059
  • View blog
  • Posts: 34,017
  • Joined: 12-June 08

Re: Updating with DataGridView Problems - VS2012 to Access 2007 DB

Posted 02 May 2013 - 09:49 AM

To be honest - that whole setup looks like a mess. It could be any number of issues.
Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Updating with DataGridView Problems - VS2012 to Access 2007 DB

Posted 02 May 2013 - 11:38 AM

First Why in your update statement do you have in the parameters @Inspector and Uppectors as the field name. Also, in the Insert you have Inspector and Inspectors in the field name.

Looks like you need to fix some of this first.

EDIT:
also AddWithValue does not work the same was Add does. So
acscmdUp.Parameters.AddWithValue("@TimeStamp", "[TimeStamp]") 

is invalid as the argument after the , is looking for a value not a column name.
If that code is running, it should give you a type mismatch error.

Check the intellisense on what it's expecting.


Also, move your code to a button_click event while you're debugging this. I'm pretty sure the CellEndEdit hides information on errors you may be actually getting.

Also, I'm not 100% sure but pretty sure that Add also needs a default value defined.

This post has been edited by CharlieMay: 02 May 2013 - 11:51 AM

Was This Post Helpful? 1
  • +
  • -

#7 JeffU  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 02-May 13

Re: Updating with DataGridView Problems - VS2012 to Access 2007 DB

Posted 02 May 2013 - 02:01 PM

@CharlieMay

The cellendedit was defiantly hiding errors, thank you. After correcting the spelling errors i'm now getting data type mismatch in my update command. I'm not 100% clear on how to use add property.
Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Updating with DataGridView Problems - VS2012 to Access 2007 DB

Posted 02 May 2013 - 02:10 PM

OK, have you thought about cutting out 90% of that code? I really think you're taking the long way to what it appears you're trying to achieve.

Declare your DimID as Integer
Declare a New oledbconnection
Declare a oledbdataadapter
Declare a New Datatable

These should be at the forms scope so that they can be access in other methods.

Sub Dim_Hist(DimID as Integer)
  Dim strSQL as string = "Your select statement, just like you have it now"
  yourDataAdapter = New OledbDataAdapter(strSQL, yourconnection)
  yourDataAdapter.SelectCommand.Parameters.AddWithValue("@DimID", oledb.oledbtype.integer, 4, "DimID").Value = DimID
  yourDataAdapter.Fill(YourDataTable)
  dgv_Dim.DataSource = YourDataTable
End Sub



Now you want to call Dim_Hist(your dim id value) which populates your dgv.

That's it.

To save changes (and you'll want to put this in an event like Dgv_RowValidated
Dim cb as New OledbCommandBuilder(yourdataadapter)
yourdataadapter.update(yourdatatable)

Dim_Hist(DimID)


The commandbuilder will build insert update and delete commands based on the datatable
Was This Post Helpful? 0
  • +
  • -

#9 JeffU  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 02-May 13

Re: Updating with DataGridView Problems - VS2012 to Access 2007 DB

Posted 02 May 2013 - 03:20 PM

Stripping the delete/update/insert sql commands is just that. I followed tutorials on how to populate and update edits through dgv.
http://www.vbforums....ta-in-Databases
The thrid section shows exactly what i'm trying to acomplish. how does your code handle delete/update/insert commands?
Was This Post Helpful? 0
  • +
  • -

#10 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Updating with DataGridView Problems - VS2012 to Access 2007 DB

Posted 02 May 2013 - 05:32 PM

The commandbuilder handles it.

Have you set up an application against your database and tried it?

It would really take nothing more than renaming the procedure slightly, adding a button to call the routine and pass an ID and a button to run the update code.

This post has been edited by CharlieMay: 02 May 2013 - 05:33 PM

Was This Post Helpful? 0
  • +
  • -

#11 JeffU  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 02-May 13

Re: Updating with DataGridView Problems - VS2012 to Access 2007 DB

Posted 02 May 2013 - 06:50 PM

Omg...I don't know what happen but now I'm getting the error "Could not find file 'obj\Release\AST-Inpection.exe'. AST-Inpection"

What happpen? It won't clean or build.....

Ok...it seems it just can't build from an error in my code. I thought my solution was corrupted.. I panicked.
Was This Post Helpful? 0
  • +
  • -

#12 JeffU  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 02-May 13

Re: Updating with DataGridView Problems - VS2012 to Access 2007 DB

Posted 02 May 2013 - 07:07 PM

This will not even build. If I comment all of this out, then it will as normal.
    Private DimID As String
    Private acsDataTable As New DataTable
    Private acsda As New OleDb.OleDbDataAdapter

    'Update Edit
    Private Sub Dim_Hist(DimID As String)
        strsql = "SELECT ID, TimeStamp, Press, Cycle, Inspector, Dimension FROM Dimension WHERE DimID = @DimID ORDER BY [TimeStamp] Desc"
        acsda = New OleDb.OleDbDataAdapter(strsql, acsconn)
        acsda.SelectCommand.Parameters.AddWithValue("@DimID", "DimID").Value = DimID
        acsda.Fill(acsDataTable)
        dgv_Dim.DataSource = acsDataTable

        dgv_Dim.RowHeadersVisible = False
        dgv_Dim.Columns(1).Width = 130
        dgv_Dim.Columns(2).Width = 35
        dgv_Dim.Columns(3).Width = 35
        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
    Private Sub btn_update_Click(sender As Object, e As EventArgs) Handles btn_update.Click
        acsda.Update(acsDataTable)
        Me.Dim_Hist(DimID)
    End Sub

Was This Post Helpful? 0
  • +
  • -

#13 dave_mark  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 15-March 13

Re: Updating with DataGridView Problems - VS2012 to Access 2007 DB

Posted 02 May 2013 - 09:44 PM

modi123_1 is correct.. better you put try catch end try statement so that you can trace the flow of your program.. better use dataset in query and command.executenonquery in non query statement..
Was This Post Helpful? 0
  • +
  • -

#14 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Updating with DataGridView Problems - VS2012 to Access 2007 DB

Posted 03 May 2013 - 02:32 AM

Sorry, line 9 should have been add and not addwithvalue
acsda.SelectCommand.Parameters.Add("@DimID", oledb.oledbtype.integer, 4, "DimID").Value = DimID

This post has been edited by CharlieMay: 03 May 2013 - 02:34 AM

Was This Post Helpful? 0
  • +
  • -

#15 JeffU  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 02-May 13

Re: Updating with DataGridView Problems - VS2012 to Access 2007 DB

Posted 03 May 2013 - 05:31 AM

DimID is not an integer, It's a string. The code builds the DimID as COMPANY:PART:DIM then 1,2,3,or 4 depending on which of the dimension it is recording. It is not the unique line ID. Its just an easy way to pull all of the dimensions of one part's dimension. My program also graphs dimensional data of the part based on 4 different dimensions. I tried using that line as oledb.oledbtype.char but the same error occurred.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2