added row record affact existing record in bound datagridview

Question

in my project, there are 1 datagridview and 2 textbox, 1 button
datagridview and 2 textbox are bound to the same datatable 
which only have 2 fields, CustomerOrderNo and FactoryOrderNo

the function i want to realize is simple,when the form is loading,
datagridview and textbox is bounded, when the user click the button to add new order,all
the textbox's text is cleared waiting for the new input,after the input is 
finished, the user click again the button to confirm the adding,this new record is added to the datatable and at the same time, it is also reflected in the datagridview. here is my code

' form loading event
  Private Sub frmOrder_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Dim c As Order = New Order 
        ds = c.GetOrder 'this return a dataset which comes from a select * from order command,DS is already declared in the class
        DataGridView1.DataSource = ds.Tables(0)
        DataGridView1.AllowUserToAddRows = False
        txtCustomerOrderNo.DataBindings.Add("text", ds.Tables(0), "customerorderno")
        txtFactoryOrderNo.DataBindings.Add("text", ds.Tables(0), "factoryorderno")
    End Sub
'button click event
 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
dim s as string
dim r as datarow

  s=""      's is used to check the datatable record         
If Button1.Text = "Add Order" Then
            Button1.Text = "Confirm Adding"
            Dim c As Control
            For Each c In TableLayoutPanel1.Controls
                If TypeOf c Is TextBox Then
                    c.Text = ""
                End If
            Next

' this will list the record count and each record before adding new record
 For Each r In ds.Tables(0).Rows
                s = s & r("customerorderno") & "/" & r("factoryorderno") & vbCrLf
            Next

            MsgBox(ds.Tables(0).Rows.Count  & " records" & vbCrLf & s)


        ElseIf Button1.Text = "confirm adding" Then

            Dim Dr As DataRow
            Dr = ds.Tables(0).NewRow()
            Dr("customerorderno") = txtCustomerOrderNo.Text
            Dr("factoryorderno") = txtFactoryOrderNo.Text

            ds.Tables(0).Rows.Add(Dr)
' this will list the record count and the record content after the adding new record
 For Each r In ds.Tables(0).Rows
                s = s & r("customerorderno") & "/" & r("factoryorderno") & vbCrLf
            Next

            MsgBox(ds.Tables(0).Rows.Count & " records" & vbcrlf & vbCrLf & s)


        End If

now i find the returning msgbox is a surprise, indeed, a new record is added, but the existing record is also affacted,this is 
not what i need.  Please let me know why this happen, how to fix it

Tks
Roland


PS:
my returning msgbox as following
when the user click the button at first time, msgbox is
  2 records
  1827/NVS11122
  1923/NVS11124
After the second click on button,the msgbox is
  3 records
  test-c/test-f
  1923/nvs11124
  test-c/test-f

                            

Answer 1

Hello,

One of the first things I am seeing is it appears (not sure) that you are clearing TextBox controls that are data bound. Also there is no reason to loop thru the data as done but instead you can use a LINQ statement to get data back. The following code and screen shots demo using mocked data how to add a new row and show the two fields and record count (was not concerned about everything looking perfect other wise I would have not used a MessageBox but instead display info in a child form in read-only mode). You would also be better off seperating current data from data being added in regards to the controls and do this in a child form. Any ways the following code is basic, easy to read and test.

Please note there are two sections of code, one part for a form while the other, language extension belongs in a code module.

Form code

Public Class SomeForm
    WithEvents ds As New DataSet
    Private Sub SomeForm_Load( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load
        Dim c As Order = New Order
        ds.Tables.Add(c.GetOrder)
        DataGridView1.DataSource = ds.Tables(0)
        DataGridView1.AllowUserToAddRows = False
        txtCustomerOrderNo.DataBindings.Add("text", ds.Tables(0), "customerorderno")
        txtFactoryOrderNo.DataBindings.Add("text", ds.Tables(0), "factoryorderno")
    End Sub
    Private Sub cmdAddNewRecord_Click( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles cmdAddNewRecord.Click
        Try
            Dim Dr As DataRow
            Dr = ds.Tables(0).NewRow()
            Dr("Identifier") = ds.Tables(0).FieldLastValue(Of Int32)("Identifier") + 1
            Dr("customerorderno") = txtCustomerOrderNoNew.Text
            Dr("factoryorderno") = txtFactoryOrderNoNew.Text
            ds.Tables(0).Rows.Add(Dr)
        Catch ex As Exception
            MessageBox.Show(String.Format("Failed to add record.{0}Error: {1}", Environment.NewLine, ex.Message))
            Exit Sub
        End Try
        txtCustomerOrderNoNew.Text = ""
        txtFactoryOrderNoNew.Text = ""
        Dim Msg = CStr(ds.Tables(0).Rows.Count - 1) & _
            Environment.NewLine & _
            String.Join( _
                Environment.NewLine, _
                ( _
                    From T In ds.Tables(0).AsEnumerable _
                    Select Item = String.Concat( _
                        T.Field(Of String)("customerorderno"), "/", _
                        T.Field(Of String)("factoryorderno"))).ToArray _
                )
        MessageBox.Show(Msg)
    End Sub
End Class
Public Class Order
    Public Sub New()
    End Sub
    Public Function GetOrder() As DataTable
        Using MockedData As New DataTable()
            MockedData.Columns.AddRange(New DataColumn() _
                { _
                    New DataColumn("Identifier", GetType(System.Int32)), _
                    New DataColumn("customerorderno", GetType(System.String)), _
                    New DataColumn("factoryorderno", GetType(System.String)) _
                } _
            )
            MockedData.Rows.Add(New Object() {1, "CUST-A", "F100"})
            MockedData.Rows.Add(New Object() {2, "CUST-B", "F123"})
            MockedData.Rows.Add(New Object() {3, "CUST-C", "F500"})
            Return MockedData
        End Using
    End Function
End Class

Code module

<System.Diagnostics.DebuggerStepThrough()> _
<System.Runtime.CompilerServices.Extension()> _
Public Function FieldLastValue(Of T)(ByVal dt As DataTable, ByVal ColumnName As String) As T
    Return dt.Rows.Item(dt.Rows.Count - 1).Field(Of T)(dt.Columns(ColumnName))
End Function

Screen shots

---

KSG

Answer 2

Please note that in my last reply the record count is off (just noticed) because I did a -1 on the record count in error. Remove -1 returns the proper count.

---

KSG

Answer 3

Thought of one more thing, if you place a break-point on the Try (see below) in the code I provided and click on the magnifier image you can look at the current data. Step thru the code to after the add and do the same thing to see the table after the add.

---

KSG

Answer 4

Hi Kevininstructor

my VB level is a little away from Linq,  right now, i need some time to decide if you solve my question.

in order to overcome this problem, i use drag and drop controls(dataset,bindingsource,adapter) to the main window, and bind them to the corresponding table, no problem to retrieve and update data this way.

Tks

Roland