Solved

Inserting, deleting and editing rows in an Access database

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
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 this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

717 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