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
        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
        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?

Re: dataAdapter.Update problem 80 80

On the off-chance that this will help, this is the exact kind of exception that's occurring:


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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.