Inserting, deleting and editing rows in an Access database

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
bsturgeAsked:
Who is Participating?
 
SanclerConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.