Need help with a databound form and Dataset: Urgent!!

This has become frustrating and I know It has to be so easy.  

I have Dataset loaded from a single table on an access back end.  I'm using a form with 6 text boxes Name, address, etc. all are databound to fields in the dataset.

I used a commandbuilder object and want to update the database.  All the examples I've found speak of using a dataset w/ a datagrid.  Even the VS help uses a datagrid as the example.  

I just want to Insert, Update and Delete data on what is a completely databound form, but i'm having noting but problems.

Example:

 to delete the currently displayed record i do the following:

                iResult = MsgBox("Deleting this Customer will remove all related Addresses and Parts!" & vbCrLf & "Are You Sure?", MsgBoxStyle.YesNo Or MsgBoxStyle.Exclamation, "Delete Customer")
                If iResult = MsgBoxResult.Yes Then
                    Me.DataSet11.Customer_Bill_To.Rows(mbmCustomers.Position).Delete()
                End If
                Me.OleDbDataAdapter1.Update(DataSet11, "Customer_Bill_To")


this lets me delete the currently displayed record - then nothing else will delete.

what am i doing wrong?

How do you append a new record?

One of the big supposed advantages of the dataset was that i could hold off on the Updates(wether inserts, updates, and/or deletes)  until they closed the form or something like that.  but i can;t get past how to use this control.

Please,Please,Please...No examples referencing a datagrid :P

500 points because it's very urgent

Thanks
Mike

mnichols1202Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SanclerCommented:
Here's a little demo.  Fairly rudimentary, but it illustrates the main points.  The form has the following controls on it

txtName
txtAddress
btnBack
btnForward
btnNew
btnDelete
lblPosition

The database table, "Customers", has three fields in it

CustomerID - an AutoNumber Primary Key - otherwise the commandbuilder won't work
Name
Address

And here's the code

    Private con As OleDbConnection
    Private da As OleDb.OleDbDataAdapter
    Private ds As New DataSet
    Private WithEvents mbmCustomers As BindingManagerBase


    Private Sub TextBoxDataBase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con = getConnection()
        Dim sql As String = "SELECT * FROM Customers"
        da = New OleDbDataAdapter(sql, con)
        da.Fill(ds)
        Dim cb As New OleDbCommandBuilder(da)
        BindControls()
    End Sub

    Private Sub BindControls()
        txtName.DataBindings.Add("Text", ds.Tables(0), "Name")
        txtAddress.DataBindings.Add("Text", ds.Tables(0), "Address")
        mbmCustomers = BindingContext(ds.Tables(0))
        lblPosition.Text = mbmCustomers.Position + 1 & " of " & mbmCustomers.Count & " records"
    End Sub

    Private Sub mbmCustomers_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles mbmCustomers.PositionChanged
        lblPosition.Text = mbmCustomers.Position + 1 & " of " & mbmCustomers.Count & " records"
    End Sub

    Private Sub btnBack_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnBack.Click
        mbmCustomers.Position -= 1
    End Sub

    Private Sub btnForward_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnForward.Click
        mbmCustomers.Position += 1
    End Sub

    Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
        Dim dr As DataRow = ds.Tables(0).NewRow
        ds.Tables(0).Rows.Add(dr)
        mbmCustomers.Position = mbmCustomers.Count - 1
        lblPosition.Text &= " [NEW]"
        txtName.Focus()
    End Sub

    Private Sub btnDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim iResult As MsgBoxResult = MsgBox("Deleting this Customer will remove all related Addresses and Parts!" & vbCrLf & "Are You Sure?", MsgBoxStyle.YesNo Or MsgBoxStyle.Exclamation, "Delete Customer")
        If iResult = MsgBoxResult.Yes Then
            ds.Tables(0).Rows(mbmCustomers.Position).Delete()
        End If
        btnBack_Click(Nothing, Nothing)
    End Sub

    Private Sub TextBoxDataBase_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        mbmCustomers.EndCurrentEdit()
        If ds.HasChanges Then
            Dim iResult As MsgBoxResult = MsgBox("Changes have been made to the data!" & vbCrLf & "Do you wish to save them?", MsgBoxStyle.YesNo Or MsgBoxStyle.Exclamation, "Changed data")
            If iResult = MsgBoxResult.Yes Then
                da.Update(ds)
            End If
        End If
    End Sub

As to what you were doing wrong, I can't be sure, but I imagine that it involved not changing the .Position of mbmCustomers.

Roger
SanclerCommented:
Correction.  I largely copied your code for the delete.  But I now see where it could have been going wrong other than with the .Position issue.  Substitute this

    Private Sub btnDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim iResult As MsgBoxResult = MsgBox("Deleting this Customer will remove all related Addresses and Parts!" & vbCrLf & "Are You Sure?", MsgBoxStyle.YesNo Or MsgBoxStyle.Exclamation, "Delete Customer")
        If iResult = MsgBoxResult.Yes Then
            Dim dr As DataRowView = mbmCustomers.Current
            dr.Delete()
        End If
        mbmCustomers_PositionChanged(Nothing, Nothing)
    End Sub

for the equivalent in my earlier post.

Roger

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mnichols1202Author Commented:
Thanks!  Understand it a bit better now!

Wish there was better documentation in VS for the Binding Manager since it seems so necesary to databinding in .net w/o a datagrid

Thanks again


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.