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:
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"); } // <--PROBLEM
catch (Exception exc) { MessageBox.Show (exc.Message); }
dataSet.AcceptChanges ();
}
I don't think the problem is with initializing the SQL commands. Here's the code for that (shown without the "Delete SQL Command" section). No exceptions are thrown.
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?