Solved

Inserting, deleting and editing rows in an Access database

Posted on 2006-06-16
1
214 Views
Last Modified: 2010-04-23
I am new to VB.net and have become stuck trying to insert, delete and edit a dataset.  
I can connect and read the data and bind it to a datagrid, but cannot get it to update.

Here is my code:

  Dim da As New OleDbDataAdapter
        Dim dbCommand As New OleDbCommand
        Dim dt As New DataTable
        Dim dr As DataRow

        dbConnection.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.mdb"
        Try
            dbConnection.Open()

        Catch ex As Exception
            MsgBox("Cannot open Database" & vbCr & ex.Message, MsgBoxStyle.Critical, "Error!")
            Exit Sub
        End Try
        Dim SQLStr As String
        SQLStr = "select * from driver"
        dbCommand.Connection = dbConnection
        dbCommand.CommandType = CommandType.Text

        dbCommand.CommandText = SQLStr
        da.SelectCommand = dbCommand

        da.Fill(dt)

        grdShift.DataSource = dt
        dr = dt.NewRow
        dr("driverNo") = "xyz"
        dr("driverName") = "1234567"
        dt.Rows.Add(dr)
        da.Update(dt)



Thanks,
BSTRGE
0
Comment
Question by:bsturge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 16922056
You will need to give your dataadapter the necessary commands before it will update the database.  You have already given it a SelectCommand, but it will also need an UpdateCommand (to change existing records), an InsertCommand (to add new records) and/or a DeleteCommand (to remove existing records).  In fact, all you would need for your existing code, adding a new record, is the InsertCommand.

There are two ways to give the dataadapter the necessary commands.  You can code them yourself or you can use a CommandBuilder.  The second is the simpler, although it will not work for some commands if your Access table doesn't have a primary key which VB.NET recognises.  Try this

        da.SelectCommand = dbCommand 'your existing line
        Dim cb As New OleDbCommandBuilder(da) 'new line, immediately afterwards

Even in the absence of a recognisable Primary Key that will probably be OK for the present exercise - just inserting one new record.  If it gives problems for updating or deleting, come back with a bit more information about your Access table and a description of the problem and any error message/s.

By the way, while there is nothing wrong with testing the connection in a Try ... Catch block as you have done (a) you do not need explicitly to open and close connections when using a dataadapter - it happens automatically; and (b) if you do explicitly open a connection, you should also include code, somewhere, explicitly to close it.

Roger
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 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