Solved

Inserting, deleting and editing rows in an Access database

Posted on 2006-06-16
1
200 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
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
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…

774 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