• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

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

0
mnichols1202
Asked:
mnichols1202
  • 2
1 Solution
 
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
0
 
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
0
 
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


0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now