dataAdapter.Update problem

[thedougster]

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?

 

[hmckillop]

You probably want to post this on the VB.NET forum as its not a SQL issue.
to me it looks like you are trying to generate insert and update statements which you dont need (e.g. if you use the command object, the .update or .add method is all that should be required in addition to creating your parameters.
Anyway, wrong forum

"I'm living so far beyond my income that we may almost be said to be living apart

 

[thedougster]

To me it looks like it's related to .Net alright, but also to SQL, and not at all to Visual Basic.

 

[markros]

Yes, it's C# code. Why you need a helper method and can not set parameters directly?

 

[thedougster]

markros:

Because I'm a newbie and I don't really know what I'm doing. I'm just stumbling around in the dark trying to pattern my code after examples I see in online tutorials. Can you show me what you mean?

 

[markros]

Try instead of AddParameters method the direct approach for now

dataAdapter.UpdateCommand.Parameters.AddWithValue('@FirstName','First Name');
dataAdapter.UpdateCommand.Parameters.AddWithValue('@LastName','Last Name');

etc.

and see if you can make this to work.

You may also check this

http://mudassarkhan.wordpress.com/2009/01/10/using-select-stored-procedures-adonet/

though it's slightly different from what you're trying.

 

[markros]

You may also check

http://forums.asp.net/t/1400469.aspx

and here it's better to ask your question in C# forum.