Solved

Inserting, deleting and editing rows in an Access database

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

708 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

17 Experts available now in Live!

Get 1:1 Help Now