Adding a record to MS Access database..

[birthjay]

I have a database file that when I click on the "ADD" button should allow me to add a record to the end of the database. The problem is that it DOES add the record, however it also overwrites whatever record I am currently own. So I have two identical records and one missing. It appears that I need some code to go to the end (empty) part of the database or some code that will not overwrite whatever record I am own. This is a local MS Access database by the way.

What do I need?

Private Sub btnAdd_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnAdd.Click
'Begin an Add operation or cancel the current operation

If btnAdd.Text = "&Cancel" Then 'Cancel an Add or Edit
LockTextBoxes()
EnableNavigation()
btnSave.Enabled = False
btnAdd.Text = "&Add"
RejectChanges()
mblnAdding = False
Else 'Begin an Add operation

UnlockTextBoxes()
ClearText()
txtAuthor.Focus()
DisableNavigation()
btnSave.Enabled = True
btnAdd.Text = "&Cancel"
lblRecordNumber.Text = ""
mblnAdding = True
End If
End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnSave.Click
'Save the new record for an Add or Edit

If mblnAdding Then 'Add in progress
Try
Dim newRow As DataRow = DsBooks1.Books.NewRow
newRow("Author") = txtAuthor.Text
newRow("ISBN") = txtISBN.Text
newRow("Title") = txtTitle.Text
DsBooks1.Books.Rows.Add(newRow)
Catch exc As Exception
MessageBox.Show("Unable to add the record." & _
ControlChars.NewLine & exc.Message, "Books")
End Try
mblnAdding = False
lblRecordNumber.Text = "Record Added at the end of the table"
End If

'Actions to take to complete an Add or an Edit
LockTextBoxes()
EnableNavigation()
btnSave.Enabled = False
btnAdd.Text = "&Add"
mblnIsDirty = True
End Sub

[Lunatic3]

I see no code that does the update part, at least your code is not clear enough. As you may know by now adding a new record to the dataset is not all you need. In order to add a new record you need a valid 'insert command' , to update a record you need a valid 'update command' and to delete a record you need a valid 'delete command'. All those commands are sql statments that you may write to perform needed task. However you can do all (adding insert,delete and update records) at desgin time for your ease. However you need a primary key in your data table so that desginer can create those commands for you. If you need more assistant just tell me.

[Pirate]

Ok , I wrote you this sub , it saves your current data to new datarow and add it to the collection and update everything . works fine .

Public Sub SaveRecords(ByVal MyTable As String)
        Dim conn As New OleDbConnection("connection string here")
        Dim strSQL As String = "SELECT * FROM " & MyTable
        Dim adp As New OleDbDataAdapter(strSQL, conn)
        Dim builder As New OleDbCommandBuilder(adp)
        Dim ds As New DataSet
        adp.Fill(ds, MyTable)
        Dim dr As DataRow = ds.Tables(MyTable).NewRow()
        dr("AUTHOR") = YOURDATA1
        dr("ISBN") = YOURDATA2
        dr("Title") = YOURDATA3
        ds.Tables(MyTable).Rows.Add(dr)
        adp.Update(ds, MyTable)
        MessageBox.Show("Successfully Saved")
    End Sub

[Pirate]

All what really need to do is just : pass the table name that's being added to , and don't forget to change the YOURDATA1 and .... 2 and ...3 . The connection string also , looks funny , change it too .

[birthjay]

I thought I had posted this earlier. This is the whole project. I copied it from a book. The problem is that when I "ADD" a record, it overwrites the current one that I am on....as well....as adds the new one to the end of the table (as it should).

Zip file

[Pirate]

Don't bother yourself trying to figure out this problem , use the function I posted if you like .

[birthjay]

I tried your code. For one thing there should be a prefix on part of it. For example,...

OleDbDataAdapter should be oledb.OleDbDataAdapter

I still had a little trouble with it but I am pretty much of a newbie. I am about to take an advanced VB.NET class starting Wednesday so I will figure it then.

Thanks for the help.

[Pirate]

Put this at the very top of your form . This will shorten declaring variables , so you don't have to type oledb prefix .

Imports System.Data.OleDb