By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

dataAdapter.Update problem

P: 93
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:
Expand|Select|Wrap|Line Numbers
  1.         private void btnSave_Click (object sender, EventArgs e)
  2.         {
  3.             DataRow row = dataTable.Rows [currentRecord];
  4.             row.BeginEdit ();
  5.  
  6.             // get data from input TextBoxes
  7.             row ["ContactID"]    = txtContactID.Text;
  8.             row ["FirstName"]    = txtFirstName.Text;
  9.             row ["LastName"]     = txtLastName.Text;
  10.             row ["Phone"]        = txtPhone.Text;
  11.             row ["EmailAddress"] = txtEmailAddress.Text;
  12.  
  13.             row.EndEdit ();
  14.  
  15.             try { dataAdapter.Update (dataSet, "Person.Contact"); }        // HERE'S THE PROBLEM
  16.             catch (Exception exc) { MessageBox.Show (exc.Message); }
  17.  
  18.             dataSet.AcceptChanges ();
  19.         }
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.

Expand|Select|Wrap|Line Numbers
  1.         private void InitializeCommands ()
  2.         {
  3.             // Preparing Insert SQL Command
  4.             try
  5.             {
  6.                 dataAdapter.InsertCommand = conn.CreateCommand ();
  7.                 dataAdapter.InsertCommand.CommandText = 
  8.                     "INSERT INTO Person.Contact (ContactID, FirstName, LastName, Phone, 
  9.                     EmailAddress) VALUES (@ContactID, @FirstName, @LastName, @Phone, 
  10.                     @EmailAddress)";
  11.                 AddParams (dataAdapter.InsertCommand, "ContactID, FirstName, LastName, 
  12.                     Phone, EmailAddress");
  13.             }
  14.             catch (Exception exc) { MessageBox.Show (exc.Message, "InsertCommand"); }
  15.  
  16.             // Preparing Update SQL Command
  17.             try
  18.             {
  19.                 dataAdapter.UpdateCommand = conn.CreateCommand ();
  20.                 dataAdapter.UpdateCommand.CommandText = 
  21.                     "UPDATE Person.Contact SET FirstName = @FirstName, LastName = 
  22.                     @LastName, Phone = @Phone, EmailAddress = @EmailAddress WHERE 
  23.                     ContactID = @ContactID";
  24.                 AddParams (dataAdapter.UpdateCommand, "ContactID, FirstName, LastName, 
  25.                     Phone, EmailAddress");
  26.             }
  27.             catch (Exception exc) { MessageBox.Show (exc.Message, "UpdateCommand"); }
  28.         }
  29.  
  30.         // add column name(s) supplied in params (prefixed with '@') into Parameters collection of 
  31.         // SqlCommand class
  32.         // SqlDbType.Char: type of parameter, 0: size of parameter, column: column name
  33.         private void AddParams (SqlCommand cmd, params string [ ] columns)
  34.         {
  35.             foreach (string column in columns)
  36.                 cmd.Parameters.Add ("@" + column, SqlDbType.Char, 0, column); 
  37.         }
  38. }
Any ideas?
Mar 21 '09 #1
Share this Question
Share on Google+
2 Replies


P: 93
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?
Mar 22 '09 #2

code green
Expert 100+
P: 1,726
You are using SQL variables that have not been declared.
VALUES (@ContactID, @FirstName, @LastName, @Phone,
Even if they had been declared they would be empty anyway.
Mar 24 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.