[02/03] addnew, edit and delete

hi again,

i created a form that shows single record at a time.
at the top i have an add/insert new button, delete button and save button.

here's my code....

At my Module I have these codes

Code:

---

'Assuming I already have a connection.
Public myAdapter as New OleDBAdapter("SELECT * FROM table1", myConnection)
Public myDataset as DataSet
Public myDatatable as DataTable

---

At Form1 Form_Load

Code:

---

'Assuming I already have a connection.
Dim Insert as New OleDBCommand("Insert Into table1 (column1, column2) Values (:column1, :column2)", myConnection)

Insert.Parameters.Add(":column1", Int32, 6, "column1")
Insert.Parameters.Add(":column2", String, 10, "column2")

Dim Update as New OleDBCommand("Update table1 Set column1 = :column1, column2 = :column2 Where column1 = :column1)", myConnection)

Update.Parameters.Add(":column1", Int32, 6, "column1")
Update.Parameters.Add(":column2", String, 10, "column2")

Dim Delete as New OleDBCommand("Delete From table1 Where column1 = :column1)", myConnection)

Delete.Parameters.Add(":column1", Int32, 6, "column1")

With myAdapter
.InsertCommand = Insert
.UpdateCommand = Update
.DeleteCommand = Delete
End With

Dim myDataset as New DataSet
Dim myDatatable as New DataTable

myAdapter.Fill(myDataset, "table1")
myDatatable = myDataset.Tables("table1")

textbox1.DataBindings.Add("Text", myDatatable, Int32, 6, "column1")
textbox2.DataBindings.Add("Text", myDatatable, String, 20, "column2")

---

At my Add New Button Click

Code:

---

myDatatable.DefaultView.AddNew()
'I added the next code coz the above code wont clear out the textboxes text. If you guys know a better way please feel free to tell me.
textbox1.text = nothing
textbox2.text = nothing

---

At my Delete Button Click

Code:

---

myDatatable.Rows(BindingContext(myDatatable).Position).Delete()

---

At my Save Button Click

Code:

---

DirectCast(Me.BindingContext(myDatatable), CurrencyManager).EndCurrentEdit()
myAdapter.Update(myDatatable)

---

the problem im having is when i click on the Add New button and type in valid values for textbox1 and textbox2 and then click save, it doesn't go through... i get a concurrency error which im still trying to figure out the meaning.

but when i do a normal update, meaning when i just change a value in textbox1 or textbox2 then click save, it works fine... delete works ok as well.

any suggestions?

i figured out why i get the concurrency error. it seems that when i do this code

vb Code:

myDatatable.DefaultView.AddNew()

i stay on the same rowindex after I click the AddNew which means when i try to clear the fields, it edits the currentrecord (which is an existing record) and not add the new record.

now to simplify the problem, i need to go the rowindex of the newly added row and make the changes there... unfortunately, im having a bit of a problem in doing this. using this code

vb Code:

Me.BindContext(myDatatable).Postion = myDatatable.DefaultView.Count

does me no good.

any other suggestions please... thanks.