?
Solved

ADO.Net in C# INSERT Problems

Posted on 2006-06-14
3
Medium Priority
?
239 Views
Last Modified: 2010-04-16
Hi,

I asked a previous question on this topic and thought I had a working answer but was then still unable to get it to work so i'm posting this again.  I need to figure out why I get an error when doing an insert from the following code:

OleDbDataAdapter daOurAdapter;
            DataSet dsResults;

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

            this.instantiateDatabase();

            try
            {
                //create an adapter to interact with the database
                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"];

                DataRow drCustomerResults = dtCustomers.NewRow();              
               
                //fill in all the data on this form 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
                dtCustomers.Rows.Add(drCustomerResults);

                if (!dsResults.HasErrors)
                {

//THIS LINE BELOW DOES NOT WORK, CAUSES AN ERROR: "SYNTAX ERROR in INSERT INTO statement"
                    daOurAdapter.Update(dsResults, "Customers");

                    dsResults.AcceptChanges();
                }                
            }
            catch (Exception ex)
            {
                string strMsg = "Error Message = " + ex.Message;
            }


As you can see, the daOurAdapter.Update line does not work even though I constructed a command builder object.  Is there anything else I need to do?  My understanding is that it should handle basic inserts, updates, and deletes.

Thanks,
Nathan
0
Comment
Question by:ironwill96
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
prosh0t earned 2000 total points
ID: 16907256
just a guess..

try adding this after creating the command builder:

cmdBuilder.QuotePrefix = "[";
cmdBuilder.QuoteSuffix = "]";

0
 
LVL 2

Author Comment

by:ironwill96
ID: 16907594
That fixed it!  Thanks a ton!!!  Now, what exactly is that doing?  Creating some sort of escape character for quotes?  None of the inputted data contained quotes though (the user input) so why would I have to put that in to make it work?

Thanks,
Nathan
0
 
LVL 7

Expert Comment

by:prosh0t
ID: 16907626
Usually this happens when your column or table names are set to keywords that the database system uses in the background.  I think 'username' and 'password' are keywords in this case... so if they don't have brackets around them in  your query, the query is invalid because the database doesn't know whether it's a column name or the system keyword.

0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

765 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