0

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?

1
Contributor
1
Reply
2
Views
8 Years
Discussion Span
Last Post by BobLewiston
0

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?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.