Editing with ADO.NET

Hi,

Im develeping the front end for a database, i've coded the adding and deleting buttons fine, but I can't quite figure out the Edit button, this is the code I have used for adding records....

Dim drNewRow As DataRow
        drNewRow = DataSetStock1.Tables("Stock").NewRow()
        drNewRow("Stock No") = txtStockNo.Text
        drNewRow("Type") = txtType.Text
        drNewRow("Description") = txtDescription.Text
        Try
            DataSetStock1.Tables("Stock").Rows.Add(drNewRow)
            With OleDbDataAdapter1
                .Update(DataSetStock1)
                .Fill(DataSetStock1)
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Can anybody offer me any advice?
foster30Asked:
Who is Participating?
 
vb_jonasConnect With a Mentor Commented:
Edit depends on which row you edit, I guess you know which row it is, otherwise you will have to get it:

        ' Find the stock no with id 1

        FindStockId = ???
        Dim dr As DataRow = Me.Db1DataSet1.Tables("Stocks").Rows.Find(FindStockId)

        dr("Type") = "Nnnnn"
        dr("Description") = "Nnnnn"

        ' Save data back to database, and refill the dataset to get freshdata from db:

            With OleDbDataAdapter1
                .Update(DataSetStock1)
                .Fill(DataSetStock1)
            End With

        ' the dataadapter keeps track of all the rows you edited and sends them to the db when you call update.
0
 
vb_jonasCommented:
the easiest would be to use the commandbuilder, it builds an insert query, after that its just editing the datarows and then doing dataadapter-update.
0
 
vb_jonasCommented:
just add the row:

Dim cmdb as new OleDbCommandBuilder(OleDbDataAdapter1)

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
foster30Author Commented:

Private EditCommandBuilder As OleDb.OleDbCommandbuilder <<< class header

EditCommandBuilder = New OleDb.OleDbCommandBuilder(OleDbDataAdapter1) <<< frm load

how do I do I initiate the sql?
0
 
vb_jonasCommented:
Im not sure where you are xactly. How did you build your select-query? And do you have a unique id field?
0
 
foster30Author Commented:
I built the sql query automatically in the dataset on the form. There is a unique ID field called 'Stock No'
0
 
trainsdseCommented:
Your EditCommandBuilder has created the insert statement now if you've placed the

EditCommandBuilder = New OleDb.OleDbCommandBuilder(OleDbDataAdapter1) <<< frm load

line below where you populate your DataAdapter.  The code you were running before that sets the fields and then calls update should now run correctly as written.

The problem all along was that you were calling the update method of the data adapter but you never told the data adapter what sql to write for an insert, etc...

The CommandBuilder tells the data adapter what that sql should look like.

Thomas
0
 
foster30Author Commented:
"The code you were running before that sets the fields and then calls update should now run correctly as written"

Do you mean the code i put in the original comment for the add button?


what code do I put underneath the edit button then?
0
 
trainsdseCommented:
Yes the original code you posted would have worked fine if you'd had the CommandBuilder part going.
0
 
vb_jonasCommented:
when you use fields with spaces its good to use brackets, "[Stock no]"
0
 
foster30Author Commented:
sorted, thanks guys.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.