?
Solved

Editing with ADO.NET

Posted on 2006-05-16
11
Medium Priority
?
194 Views
Last Modified: 2013-11-13
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?
0
Comment
Question by:foster30
  • 5
  • 4
  • 2
11 Comments
 
LVL 12

Expert Comment

by:vb_jonas
ID: 16691460
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
 
LVL 12

Expert Comment

by:vb_jonas
ID: 16691481
just add the row:

Dim cmdb as new OleDbCommandBuilder(OleDbDataAdapter1)

0
 

Author Comment

by:foster30
ID: 16691548

Private EditCommandBuilder As OleDb.OleDbCommandbuilder <<< class header

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

how do I do I initiate the sql?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 12

Expert Comment

by:vb_jonas
ID: 16694337
Im not sure where you are xactly. How did you build your select-query? And do you have a unique id field?
0
 

Author Comment

by:foster30
ID: 16694378
I built the sql query automatically in the dataset on the form. There is a unique ID field called 'Stock No'
0
 
LVL 3

Expert Comment

by:trainsdse
ID: 16694643
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
 

Author Comment

by:foster30
ID: 16694808
"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
 
LVL 3

Expert Comment

by:trainsdse
ID: 16694929
Yes the original code you posted would have worked fine if you'd had the CommandBuilder part going.
0
 
LVL 12

Accepted Solution

by:
vb_jonas earned 750 total points
ID: 16696589
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
 
LVL 12

Expert Comment

by:vb_jonas
ID: 16696590
when you use fields with spaces its good to use brackets, "[Stock no]"
0
 

Author Comment

by:foster30
ID: 16697864
sorted, thanks guys.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Screencast - Getting to Know the Pipeline

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question