[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ADO.Net - Cannot get DataSet to Update into Database

Posted on 2006-05-28
10
Medium Priority
?
423 Views
Last Modified: 2012-05-05
OK,

Needless to say i'm quite frustrated with ADO.net compared to ADO or even good old DAO from my VB 6 days.  I am trying in vain to get changes i've made to a DataRow to populate back to the database  No matter what I do it doesnt work or I get errors about not having a properly formatted Update command.  Perhaps I really just don't understand how ADO.Net is supposed to work.  I thought you have this disconnected dataset that you make changes to in-memory and then when you call someDataSet.AcceptChanges(); it would populate all your changes back to the database for you, perhaps optionally passing just the source table you want to repopulate.  However, that does not seem to be the case.  I've seen all kinds of conflicting answers on EE as well as on PSC and other websites about how to best do this and whether to just use ADO instead of ADO.Net.

So, my first question:  Is ADO.Net a REPLACEMENT for ADO, or  IN ADDITION TO ADO?  Are there speed advantages to using ADO.net vs. ADO when you don't really care about disconnected datasets so much?  ADO.Net is such a radically different way of thinking i'm almost ready to take a performance hit and revert to ADO style recordsets b/c it makes sense and "works" correctly the way I intend every time.

My second question, my code is shown below for opening the database and then doing updates on one row inside it.  Please let me know what i'm doing wrong so that it doesnt work.
/*
        * Open a connection to the database
        */
        private void openDatabase()
        {
            this.dbConnect = new OleDbConnection();
            this.dbConnect.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                @"Data source= .\main.mdb";
            try
            {
                //attempt to open the database
                //for now just leave this connection open
                this.dbConnect.Open();
            }
            catch (Exception ex)
            {
                string errmsg = "Error Message: " + ex.Message;
            }

        }

private void btnUpdateCompany_Click(object sender, EventArgs e)
        {
            //update all of the relevant information after verifying that they have all the proper
            //fields filled out
          //removed all the verify code for EE as it just takes up space and works fine

            OleDbDataAdapter daOurAdapter;
            DataSet dsResults;

            //do a search
            this.mstrSQL = "SELECT * FROM Customers WHERE CustomerID = " + this.CustomerID;

            this.openDatabase();
            try
            {
                //create the adapter with the SQL string and the ADO style connection (dbConnect is initialized in this.openDatabase() from above)
                daOurAdapter = new OleDbDataAdapter(this.mstrSQL, this.dbConnect);

                //make our dataset object an actual dataset
                dsResults = new DataSet();

                //now fill our dataset object with data (if any)
                daOurAdapter.Fill(dsResults, "Customers");

                //we now have a DataSet object that contains any results that have been returned by our query
                //create a DataTable object representing the table returned
                DataTable dtCustomers = dsResults.Tables["Customers"];

                //check to see if any data is actually contained in the results
                if (dtCustomers.Rows.Count > 0)
                {
                    DataRow drCustomerResults = dtCustomers.Rows[0];
                    drCustomerResults.BeginEdit();
                    //update all the data from the form inputs now
                    drCustomerResults["CompanyName"] = txtCompanyName.Text;
                    drCustomerResults["SpecialNotes"] = txtSpecialNotes.Text;
                    drCustomerResults["Username"] = txtUsername.Text;
                    drCustomerResults["Password"] = txtPassword.Text;
                    drCustomerResults["EmailAddress"] = txtEmailAddress.Text;
                    drCustomerResults["FirstName"] = txtFirstName.Text;
                    drCustomerResults["LastName"] = txtLastName.Text;
                    drCustomerResults["PhoneNumber"] = txtPhoneNumber.Text;
                    drCustomerResults["FaxNumber"] = txtFaxNumber.Text;
                    drCustomerResults["Country"] = txtCountry.Text;
                    drCustomerResults["BillingAddress"] = txtAddress.Text;
                    drCustomerResults["BillCity"] = txtCity.Text;
                    drCustomerResults["BillState"] = cboState.Text;
                    drCustomerResults["BillZipCode"] = txtZipCode.Text;
                    drCustomerResults["CreditCardType"] = cboCreditCard.Text;
                    drCustomerResults["CreditCardNumber"] = txtCreditCardNumber.Text;
                    drCustomerResults["ShipAddress"] = txtShipAddress.Text;
                    drCustomerResults["ShipCity"] = txtShipCity.Text;
                    drCustomerResults["ShipState"] = cboShipState.Text;
                    drCustomerResults["PreferredShipping"] = cboPreferredShipping.SelectedIndex;
                    drCustomerResults["ShippingNumber"] = txtShippingNumber.Text;
                    drCustomerResults["ShipZip"] = txtShipZipCode.Text;
                    drCustomerResults["ShipCountry"] = txtShipCountry.Text;
                    if (txtShipAddress.Enabled == false)
                        drCustomerResults["ShippingSame"] = 1;
                    else                    
                        drCustomerResults["ShippingSame"] = 0;                                        
                    //push all changes to the database
                    drCustomerResults.EndEdit();
                   // drCustomerResults.AcceptChanges();
                    DataSet dsChanges = dsResults.GetChanges();
                    if (dsChanges != null)
                    {
                        if (!dsChanges.HasErrors)
                        {
                            dsResults.Merge(dsChanges);
                            //very important to put the table name
//ERROR OCCURS HERE..  
                            daOurAdapter.Update(dsResults, "Customers");

                            dsResults.AcceptChanges();
                        }
                    }
                    //daOurAdapter.Update(dsChanges, "Customers");
//I"D PREFER TO BE ABLE TO JUST MAKE THE CALL BELOW AND HAVE IT ALL UPDATE..WHY DOESNT THIS WORK?
                    //dsResults.AcceptChanges();
                }
            }
            catch (Exception ex)
            {
                string strMsg = "Error Message = " + ex.Message;
            }

            this.closeDatabase();
            this.Close();
        }

I stuck in some comments about my issue in the code itself.  I really really hope that there is something stupid i'm overlooking to get ADO.Net to update the database without having to create temp Datasets to merge changes into or doing .insertCommand string type stuff to form the update SQL into the Adapter.  That all seems rather silly to me and I don't see the point, especially if I don't particularly need reusability on this form, there is no real reason to waste all the time writing re-usable SQL commands!

Thanks in advance,
Nathan
0
Comment
Question by:ironwill96
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 11

Expert Comment

by:anyoneis
ID: 16780307
IMHO, ADO.NET is a replacement for ADO. That opinion is based on capabilities and on what I have heard coming out of Redmond. (I would stake my butler's life on that opinion! :-)

When you call DataSet.AcceptChanges(), you are saying, take all of the inserts, updates, and deletes you have been keeping track of and make them the current data in the DataSet - that's all yopu are saying! That operation has no connection with the real database. Only the Adapter.Update() will persist your changes.

Comments about specific protions of your code/comments:

>>                //now fill our dataset object with data (if any)
>>                daOurAdapter.Fill(dsResults, "Customers");

Actually, the line will create or emptyandrefill a DataTable object called "Customers" in the dataset (actual specific behavior depends on prexistance of table and options set in the DataAdapter - and on whether or not multiple result sets are returned by the query.)

>>                //create a DataTable object representing the table returned
>>                DataTable dtCustomers = dsResults.Tables["Customers"];

Why? Why not just update the records directly in the DataSet (OK, in the DataTable "Customers" in the DataSet). The DataSet keeps multiple versions of the data - original, current, and proposed. There is no need to make a copy.

I probably missed some things - e.g. why specifically you are getting the error - I suspect that the tablename in dsChanges is "Table1" - all it is getting is a rowset when you call GetChanges, so it would not know that the table was called.


If you want to do things this way, I would recommend "Microsoft® ADO.NET (Core Reference) " by David Sceppa. If you want a MUCH better way, I would strongly recommend "Data Binding with Windows Forms 2.0 : Programming Smart Client Data Applications with .NET (Microsoft Net Development Series) (Paperback) " by Brian Noyes.

David





0
 
LVL 2

Expert Comment

by:HeidarV
ID: 16781838
Hello

In addition to David's comment if you just want to insert a row in the access table do the following:
if you are using .net 1.1 use OleDbCommand and execute a sql query to insert your data.
if you are using .net 2 open a dataset file and drag your access table from server explorer to the dataset. In .net 2 tables in dataset can have a connection to the datasource. Know build your solution and go to your form. drag your new dataset from toolbox to the form and drag the table (tableadapter) from the toolbox to the form.
now you can easily insert rows to the datasource by calling the Insert method of tableadapter.

hope you success
Heidar
0
 
LVL 2

Author Comment

by:ironwill96
ID: 16784291
Good information from both of you but i'm looking for some actual code examples of how to do a successful update of a record after the code I have up there makes the changes (assuming that i'm doing the changes correctly of course).

Thanks,
Nathan
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 96

Expert Comment

by:Bob Learned
ID: 16785148
Are you asking for a way to update a record, or write the changes back to the database using the data adapter?

Bob
0
 
LVL 11

Accepted Solution

by:
anyoneis earned 2000 total points
ID: 16785963
Here is a mod our your example that updates....


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb ;
namespace datasetupdateexqample
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
        }
        /*
        * Open a connection to the database
        */
        OleDbConnection dbConnect;
        string mstrSQL;
        int CustomerID;

        private void openDatabase()
        {
            this.dbConnect = new OleDbConnection();
            //this.dbConnect.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
            //    @"Data source= .\main.mdb";

            this.dbConnect.ConnectionString =
            @"Provider=SQLOLEDB;Data Source=(local);Initial Catalog=Test;"
            + "Integrated Security=SSPI";

            try
            {
                //attempt to open the database
                //for now just leave this connection open
                this.dbConnect.Open();
            }
            catch (Exception ex)
            {
                string errmsg = "Error Message: " + ex.Message;
            }

        }

        private void Button1_Click(object sender, EventArgs e)
        {
            //update all of the relevant information after verifying that they have all the proper
            //fields filled out
            //removed all the verify code for EE as it just takes up space and works fine

            OleDbDataAdapter daOurAdapter;
            DataSet dsResults;

            //do a search
            this.mstrSQL = "SELECT * FROM Customers WHERE CustomerID = 1";

            this.openDatabase();
            try
            {
                //create the adapter with the SQL string and the ADO style connection (dbConnect is initialized in this.openDatabase() from above)
                daOurAdapter = new OleDbDataAdapter(this.mstrSQL, this.dbConnect);
                // Set up insert, update, and delete commands
                OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(daOurAdapter);
                //make our dataset object an actual dataset
                dsResults = new DataSet();

                //now fill our dataset object with data (if any)
                daOurAdapter.Fill(dsResults, "Customers");

                //we now have a DataSet object that contains any results that have been returned by our query
                //create a DataTable object representing the table returned
                DataTable dtCustomers = dsResults.Tables["Customers"];

                //check to see if any data is actually contained in the results
                if (dtCustomers.Rows.Count > 0)
                {
                    DataRow drCustomerResults = dtCustomers.Rows[0];
                    drCustomerResults.BeginEdit();
                    //update all the data from the form inputs now

                    drCustomerResults["Name"] = drCustomerResults["Name"] + ".";
                    //move proposed rowversion to to current
                    drCustomerResults.EndEdit();

                    if (!dsResults.HasErrors)
                    {
                        daOurAdapter.Update(dsResults, "Customers");
                        dsResults.AcceptChanges();
                    }
                }
            }
            catch (Exception ex)
            {
                string strMsg = "Error Message = " + ex.Message;
            }

            this.closeDatabase();
            this.Close();
        }

        void closeDatabase()
        {
        }
    }

}

David
0
 
LVL 3

Expert Comment

by:sohilm
ID: 16786858
You are missing the commandbuilder object. In ADO.net you have to tell it what insert and update command to use to update the tables, to make it easier MSFT give you the command builder object. After you declare your data adapter, declare a commandbuilder object. That's it, pretty simple after that.

//create the adapter with the SQL string and the ADO style connection (dbConnect is initialized in this.openDatabase() from above)
daOurAdapter = new OleDbDataAdapter(this.mstrSQL, this.dbConnect);
OleDbCommandBuilder cmdOurBuilder = new OleDbComandBuilder(daOurAdapter);

0
 
LVL 2

Expert Comment

by:HeidarV
ID: 16787207
Hello Nathan,

are you using .net 1 or 2?

Heidar
0
 
LVL 2

Author Comment

by:ironwill96
ID: 16793501
I'm using .Net 2.0.

Thanks for all the help everyone, I am trying out your suggestions now.

Nathan
0
 
LVL 2

Author Comment

by:ironwill96
ID: 16793621
Thanks sohilm but AnyoneIs posted his example (with the also correct creation of the Command Builder object) before you.  I appreciate all the help.  I can't believe it was something so simple (as usual).  A++ help AnyoneIs, now I can quit trying to figure out ways to Murder ADO.Net on my computer!

Thanks again,
Nathan
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Screencast - Getting to Know the Pipeline
Suggested Courses

825 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