Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Nov 2008
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    dataAdapter.Update problem

    I can read in an SQL table ("Person.Contact") from AdventureWorks and step through it one row at a time, but when I try to save a record, either one I'm inserting or one I'm editting, I get the following exception:

    Incorrect syntax near ','. Must declare scalar variable "@ContactID".

    Here's the code:
    Code:
        private void btnSave_Click (object sender, EventArgs e)
        {
            DataRow row = dataTable.Rows [currentRecord];
            row.BeginEdit ();
    
            // get data from input TextBoxes
            row ["ContactID"]    = txtContactID.Text;
            row ["FirstName"]    = txtFirstName.Text;
            row ["LastName"]     = txtLastName.Text;
            row ["Phone"]        = txtPhone.Text;
            row ["EmailAddress"] = txtEmailAddress.Text;
    
            row.EndEdit ();
    
            try { dataAdapter.Update (dataSet, "Person.Contact"); }      // HERE'S THE PROBLEM
            catch (Exception exc) { MessageBox.Show (exc.Message); }
    
            dataSet.AcceptChanges ();
        }
    I don't think the problem is with inializing the SQL commands. Here's the code for that (shown without the "Delete SQL Command" section). No exceptions are thrown.

    Code:
        private void InitializeCommands ()
        {
            // Preparing Insert SQL Command
            try
            {
                dataAdapter.InsertCommand = conn.CreateCommand ();
                dataAdapter.InsertCommand.CommandText = 
                    "INSERT INTO Person.Contact (ContactID, FirstName, LastName, Phone, 
                    EmailAddress) VALUES (@ContactID, @FirstName, @LastName, @Phone, 
                    @EmailAddress)";
                AddParams (dataAdapter.InsertCommand, "ContactID, FirstName, LastName, 
                    Phone, EmailAddress");
            }
            catch (Exception exc) { MessageBox.Show (exc.Message, "InsertCommand"); }
    
            // Preparing Update SQL Command
            try
            {
                dataAdapter.UpdateCommand = conn.CreateCommand ();
                dataAdapter.UpdateCommand.CommandText = 
                    "UPDATE Person.Contact SET FirstName = @FirstName, LastName = 
                    @LastName, Phone = @Phone, EmailAddress = @EmailAddress WHERE 
                    ContactID = @ContactID";
                AddParams (dataAdapter.UpdateCommand, "ContactID, FirstName, LastName, 
                    Phone, EmailAddress");
            }
            catch (Exception exc) { MessageBox.Show (exc.Message, "UpdateCommand"); }
        }
    
        // add column name(s) supplied in params (prefixed with '@') into Parameters 
        // collection of SqlCommand class
        // SqlDbType.Char: type of parameter, 0: size of parameter, column: column name
        private void AddParams (SqlCommand cmd, params string [ ] columns)
        {
            foreach (string column in columns)
                cmd.Parameters.Add ("@" + column, SqlDbType.Char, 0, column); 
        }
    }
    Any ideas?

  • #2
    Regular Coder
    Join Date
    Nov 2008
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts
    On the off-chance that this will help, this is the exact kind of exception that's occurring:

    System.Runtime.InteropServices.ExternalException

    Are there any other properties of the Exception class besides Message and StackTrace that might help me figure this out?

  • #3
    New to the CF scene
    Join Date
    Apr 2009
    Location
    Florida
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    all I can say is that, thank you very much for posting this codes in you blog. I am self studying website programming, so right now I am just collecting tools and resources and compiling it. this codes that you have give will be an additional tools for me and it will be added in my archieve. Ivan, Calypso Resort Towers
    Last edited by Ivan Karl; 04-06-2009 at 05:22 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •