Solved

Updating network Access DB through DataGridView object

Posted on 2010-09-22
4
432 Views
Last Modified: 2012-05-10
Greetings Experts,

I have a small application that allows users to modify data within a network Access db.  The user selects which table to update via a combo box, loads the table to the grid and can then add/delete/update the records.  Clicking the Save button will save the changes to the db.  

Upon clicking the Save button, I get the error message:  "Operation must use an updateable query."  

Any suggestions on what needs to be adjusted to get this to work properly?
Public Class frmMain



    Dim dcBarCodeMaster As New OleDb.OleDbConnection(connectionString)

    Dim daBarCodeMaster As New OleDb.OleDbDataAdapter

    Dim cbBarCodeMaster As OleDb.OleDbCommandBuilder

    Dim dsBarCodeMaster As New DataSet

    Dim strTableName As String = ""



    Private Sub cmdLoadTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

    Handles cmdLoadTable.Click



        strTableName = cboTable.Text



        If strTableName.Length > 2 Then



            Me.dgvBarCodeMaster.DataSource = Nothing

            Me.dgvBarCodeMaster.Rows.Clear()



            Dim strQueryString As String = "SELECT * FROM " & strTableName



            Me.Cursor = Cursors.WaitCursor

          

            Try

                dsBarCodeMaster.Clear()

                daBarCodeMaster.SelectCommand = New OleDb.OleDbCommand(strQueryString, dcBarCodeMaster)

                cbBarCodeMaster = New OleDb.OleDbCommandBuilder(daBarCodeMaster)

                daBarCodeMaster.Fill(dsBarCodeMaster, strTableName)

            Catch ex As Exception

                MsgBox(ex.Message)

            End Try



            Me.dgvBarCodeMaster.DataSource = dsBarCodeMaster.Tables(strTableName)

            Me.dgvBarCodeMaster.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells

            Me.dgvBarCodeMaster.Refresh()



            Me.Cursor = Cursors.Default

        Else

            MessageBox.Show("Please select a valid db table")

            cboTable.Focus()

        End If 'strTableName.Length > 2 Then



    End Sub



    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

    Handles cmdSave.Click

        Dim ans As MsgBoxResult

        If (dsBarCodeMaster.HasChanges) Then

            ans = MsgBox("Changes have occurred!" & vbCrLf & "Do you wish to update?", MsgBoxStyle.YesNo, "Updates")

            If ans = vbYes Then

                Try

                    Dim cbbarcodemaster As New OleDbCommandBuilder(daBarCodeMaster)

                    daBarCodeMaster.Update(dsBarCodeMaster.Tables(strTableName))

                    dsBarCodeMaster.AcceptChanges()

                Catch exBarCodeMaster As Exception

                    MsgBox(exBarCodeMaster.Message)

                    Throw exBarCodeMaster

                Finally

                End Try



                MsgBox("Table updated!", MsgBoxStyle.Critical, strTableName)

            End If

        End If

    End Sub



End Class

Open in new window

0
Comment
Question by:gwosgood
  • 2
4 Comments
 
LVL 6

Expert Comment

by:matthewrhoades
ID: 33736034
Did you add an update query to your dataadapter?

You either need to define dataadapter.update or explicitly write an update query in your save routine.  Otherwise the select command might not have enough information to decide how to update correctly.

http://social.msdn.microsoft.com/forums/en-US/vbgeneral/thread/d84a4630-d297-4f78-9401-6862c07862f9

That article explains an explicit OLEDB command to update.

Otherwise (And I am not sure this is correct because I use SQL TableAdapter vs. DataAdapter) you can add an updatecommand property to the dataadapter with your update command text.

da.updateCommand = "update myTable set myField = " + @myField + " where myDelimiter = " + @ myDelim
0
 
LVL 18

Accepted Solution

by:
John (Yiannis) Toutountzoglou earned 500 total points
ID: 33736381
hi...Try this.....
For more details....
http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/6fc542f7-ecda-4968-a691-371c0b0c86b5
Imports System.Data.OleDb

Public Class Form1

    Dim myDA As OleDbDataAdapter

    Dim myDataSet As DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\myDB.mdb")

        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)

        con.Open()

        myDA = New OleDbDataAdapter(cmd)

        'Here one CommandBuilder object is required.

        'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object  

        Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)

        myDataSet = New DataSet()

        myDA.Fill(myDataSet, "MyTable")

        DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView

        con.Close()

        con = Nothing

    End Sub

 

    ' Save data back into database  

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Me.Validate()

        Me.myDA.Update(Me.myDataSet.Tables("MyTable"))

        Me.myDataSet.AcceptChanges()

    End Sub

End Class

Open in new window

0
 
LVL 2

Author Comment

by:gwosgood
ID: 33780846
jtoutou,


I copied your code snipet over exactly, and I get the same error message on this line:

"Operation must use an updateable query"
 -- Me.myDA.Update(Me.myDataSet.Tables("MyTable"))

Any suggestions?

Dim myDA As OleDbDataAdapter
Dim myDataSet As DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles MyBase.Load
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\pbpnas1000s\product\PBPLabels\PBPBarCodeMaster.mdb;Persist Security Info=False;")
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM FederalMogul", con)
        con.Open()
        myDA = New OleDbDataAdapter(cmd)
        'Here one CommandBuilder object is required.
        'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object  
        Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
        myDataSet = New DataSet()
        myDA.Fill(myDataSet, "MyTable")
        dgvBarCodeMaster.DataSource = myDataSet.Tables("MyTable").DefaultView
        con.Close()
        con = Nothing

End Sub

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles cmdSave.Click

        Me.Validate()
        Me.myDA.Update(Me.myDataSet.Tables("MyTable"))
        Me.myDataSet.AcceptChanges()


End Sub
0
 
LVL 2

Author Comment

by:gwosgood
ID: 33792258
partial solution to problem...

I commented out the Option Strict On code from the beginning of the project and the code runs fine now...

Ill keep working on a proper fix, but this will suffice in the mean time.  Thanks for your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Setting runtime form location 4 29
Showdialog 8 29
Help with deleting records by comparing two files using VB.NET 8 56
Get list of word ducuments in a folder 10 35
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now