Solved

Updating network Access DB through DataGridView object

Posted on 2010-09-22
4
435 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need a starter for ETL protocol? 4 65
Sending receiving text messages in vb.net 15 48
Get month and date in a format 4 32
vb.net convert long time to mm:ss 23 13
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

860 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